Ever wondered how PostgreSQL actually stores your data in tables? What about the indexes? Where and how are they stored? The PostgreSQL database does a splendid job of managing your persistent data by using files on your regular file systems.
In order to explore this topic in detail, 2ndQuadrant arranged a live webinar “Understanding the PostgreSQL table page layout”. The session was hosted by Nikhil Sontakke, PostgreSQL Developer at 2ndQuadrant.
This webinar explored how to understand the PostgreSQL table page layout better, in addition to covering the following topics:
- How does PostgreSQL cope with large multi-GB sized tables?
- What happens when we do an INSERT, UPDATE or a DELETE on a row?
- How about large rows?
- How does TOAST work?
Those who weren’t able to attend the live webinar can now view the recording here.
Due to limited time, some of the questions were not answered during the live webinar, so answers by the host are provided below:
Question: Any plans for a REDO log?
Answer: The current WAL infrastructure already implements roll-forward recovery which is basically REDO.
Question: When making an update, Postgres may be actually copying contents of modified row to a new location within the same or another page. In a “before update” trigger function, is this how Postgresql manages the data referred to as OLD and NEW records?
Answer: The “before update” trigger gets access to the in-memory representation of the previous version and the new version of the row. In terms of TOASTed data fields, it’s required to call PG_DETOAST_DATUM macro on it before accessing its value
Question: Can the fill factor be changed after a db has a significant amount of data?
Answer: Sure, ALTER TABLE and ALTER INDEX both support specifying a value for the FILLFACTOR. However, note that the value will not have an effect on existing data. You might have to VACUUM FULL/CLUSTER a table or resort to a REINDEX of an INDEX to get the effects of the FILLFACTOR to come into play for the entire TABLE or INDEX.
Question: Is it a good practice to use data=writeback with ext4?
Answer: With data mode set to ‘writeback‘ your system is not completely safe as per the ext4 filesystem documentation on Linux Kernel source. This mode will probably provide the best performances on ext4 but a crash+recovery can cause incorrect data to appear in files that were written shortly before the crash. So, it is not advised with Postgres and the recommendation would be to set data mode to ordered in the ext4 file system.
To be the first to know about upcoming PostgreSQL webinars by 2ndQuadrant, visit our Webinars page.
For any questions, comments, or feedback, please visit our website or send an email to webinar@2ndquadrant.com.