Heap Table in PostgreSQL

Mike's Notes

Note

Resources

• Resource

References

• Reference

Repository

• Home > Ajabbi Research > Library >
• Home > Handbook > 

Last Updated

18/04/2025

Article

By: Mike Peters

On a Sandy Beach: 20/04/2025

Mike is the inventor and architect of Pipi and the founder of Ajabbi.

words

Code

snippit

Mike Notes

Some fascinating notes here about how the PostgreSQL database stores data. This information was prompted by an article in the Data Engineering Weekly

Resources

• https://www.dataengineeringweekly.com/p/data-engineering-weekly-185
• https://drew.silcock.dev/blog/how-postgres-stores-data-on-disk
• https://ketansingh.me/posts/how-postgres-stores-rows/
• https://www.postgresql.org/docs/current/storage.html
• https://www.youtube.com/watch?v=L-dw1yRFYVg
• https://www.youtube.com/watch?v=1D81vXw2T_w
• https://medium.com/quadcode-life/structure-of-heap-table-in-postgresql-d44c94332052
• https://pgpedia.info/categories/data-directory.html

Notes from Quadcode

• Data in Heap tables can be stored randomly on different pages and different places within the page. If you want to sort the data, use ORDER BY.

• Each table has its own metadata with lots of information.

• If the table is larger than a gigabyte, then Postgres begins to split it into separate files with a size of 1 gigabyte.

• PostgreSQL uses a fixed page size, commonly 8 kB. The page structure simultaneously grows from top to bottom and from bottom to top.

• Each page has a header, transaction pointers, a meta layer for each tuple and for each row.

• The page has Fillfactor — the ability to store information only up to a certain level.

• The object identifier is used only to generate the structure object number, but not for the content.

Notes from Drews Dev Blog

• Loading a nice fresh Postgres install
• Exploring the database folders
• Let’s play with some data
• Show me the files
• What’s the heap?
• So what’s a page?
• Page layout
• What happens when a row gets modifed or deleted?
• What about indexes?
• Why would I ever need to know any of this?
• Further reading
• Future topics
• Updates
• Footnotes