Heap Table in PostgreSQL

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