While exploring PostgreSQL internals, I stumbled upon an interesting internal detail about how it stores long rows ⚡
PostgreSQL stores data in B-Trees and requires one row not to exceed the page size (about 8KB). So, when we insert a row longer than 8KB, it first tries to compress the overflowing data using its built-in compression algorithms. if it works, then great!
If it still does not fit, then TOAST comes into the picture. The core idea is to segment the data into chunks and store them in a dedicated TOAST table, while the original table holds the reference (checksum and virtual address) to ensure efficient retrieval.
The TOAST table for a particular table is named pg_toast_<table_oid>; you can find them with a simple query. This table stores the toast chunks (compressed if required), each identified by a chunk ID and sequence number for ordered retrieval.
We as users of PostgreSQL need not worry about storage as the database transparently manages it. Still, TOASTed data can incur slight performance overhead due to the additional layer of indirection and lookup.
We cannot completely avoid the TOAST, but we can minimize the need for it by designing the schema well, some best practices are
- choose data types suited for the expected data size
- for long columns, pick types (like
bytea) that offer built-in compression - Track TOAST activity using
pg_toast_tableto identify bottlenecks - TOAST comes in multiple flavors, pick the one that suits your needs
For example, the PLAIN strategy offers a good balance for frequently accessed, compressible data; while EXTENDED works well for infrequently accessed data.
⚡ I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.
youtube.com/c/ArpitBhayani