This keeps the rows in the main table file compact. There’s a downside though: when TOASTed rows are accessed, it requires additional I/O to another file (+ potentially more CPU cost for decompression). Flexible, but can hurt performance!
November 19, 2025 at 1:56 PM
This keeps the rows in the main table file compact. There’s a downside though: when TOASTed rows are accessed, it requires additional I/O to another file (+ potentially more CPU cost for decompression). Flexible, but can hurt performance!
TOASTing applies a combination of compression and offloading large values to a TOAST table. This has three columns: chunk_id, chunk_seq, and chunk_data. It divides large values up into chunks, places those in this separate table, and then creates a “pointer” to this data from the main table file.
November 19, 2025 at 1:56 PM
TOASTing applies a combination of compression and offloading large values to a TOAST table. This has three columns: chunk_id, chunk_seq, and chunk_data. It divides large values up into chunks, places those in this separate table, and then creates a “pointer” to this data from the main table file.
Wide tables may have multi-kb rows. Above a certain size threshold (say 2kb) Postgres opts to use The Oversized-Attribute Storage Technique (TOAST!) instead of packing everything into the table file.
November 19, 2025 at 1:56 PM
Wide tables may have multi-kb rows. Above a certain size threshold (say 2kb) Postgres opts to use The Oversized-Attribute Storage Technique (TOAST!) instead of packing everything into the table file.
Every row (tuple) is stored in a file that is divided up into 8kb pages. Typically, a single 8kb page can store tens or hundreds of rows, but this depends on the number of and type of columns.
November 19, 2025 at 1:56 PM
Every row (tuple) is stored in a file that is divided up into 8kb pages. Typically, a single 8kb page can store tens or hundreds of rows, but this depends on the number of and type of columns.
This is a convenient mechanism for exposing multiple versions to different transactions. This is also the reason why Postgres needs VACUUM. Over time, old rows can cause table bloat and need to be reclaimed or cleaned up, requiring dedicated VACUUM (FULL) operations or tools like pg_repack.
November 12, 2025 at 7:37 PM
This is a convenient mechanism for exposing multiple versions to different transactions. This is also the reason why Postgres needs VACUUM. Over time, old rows can cause table bloat and need to be reclaimed or cleaned up, requiring dedicated VACUUM (FULL) operations or tools like pg_repack.
When an existing row is updated, a new tuple is created with a new version of that row. The ID of the transaction that made the change is set as the xmax for the old version and xmin for the new version.
Which version of a row a transaction sees depends on its transaction ID and isolation level.
Which version of a row a transaction sees depends on its transaction ID and isolation level.
November 12, 2025 at 7:37 PM
When an existing row is updated, a new tuple is created with a new version of that row. The ID of the transaction that made the change is set as the xmax for the old version and xmin for the new version.
Which version of a row a transaction sees depends on its transaction ID and isolation level.
Which version of a row a transaction sees depends on its transaction ID and isolation level.
Writing the data page happens later on via checkpointing, background jobs, or forced flushes due to memory page eviction.
The WAL is the key to all of this! It facilitates high-performance I/O and crash recovery.
The WAL is the key to all of this! It facilitates high-performance I/O and crash recovery.
November 7, 2025 at 3:47 PM
Writing the data page happens later on via checkpointing, background jobs, or forced flushes due to memory page eviction.
The WAL is the key to all of this! It facilitates high-performance I/O and crash recovery.
The WAL is the key to all of this! It facilitates high-performance I/O and crash recovery.
(3) A new record is inserted into the memory buffer for the WAL. It contains all the information needed to reconstruct the insert.
(4) The WAL is flushed to disk (via fsync or similar) to ensure the data resides in durable storage. After this succeeds, Postgres return success to the client.
(4) The WAL is flushed to disk (via fsync or similar) to ensure the data resides in durable storage. After this succeeds, Postgres return success to the client.
November 7, 2025 at 3:47 PM
(3) A new record is inserted into the memory buffer for the WAL. It contains all the information needed to reconstruct the insert.
(4) The WAL is flushed to disk (via fsync or similar) to ensure the data resides in durable storage. After this succeeds, Postgres return success to the client.
(4) The WAL is flushed to disk (via fsync or similar) to ensure the data resides in durable storage. After this succeeds, Postgres return success to the client.
(1) Postgres receives the query and determines where to place it. This could already be in memory, or it may have to load / create one.
(2) The record is written to this page in memory only. The page is marked as “dirty” meaning it needs to get flushed to disk in the future, but not immediately.
(2) The record is written to this page in memory only. The page is marked as “dirty” meaning it needs to get flushed to disk in the future, but not immediately.
November 7, 2025 at 3:47 PM
(1) Postgres receives the query and determines where to place it. This could already be in memory, or it may have to load / create one.
(2) The record is written to this page in memory only. The page is marked as “dirty” meaning it needs to get flushed to disk in the future, but not immediately.
(2) The record is written to this page in memory only. The page is marked as “dirty” meaning it needs to get flushed to disk in the future, but not immediately.
