CODEWITHSUNDEEP
postgresql
Mangu Singh Rajpurohit
Mangu Singh Rajpurohit

Reputation: 11420

How does postgres stores row in page, when row size exceeds available free size in page ?

I am exploring storage mechanism of postgres. I know that postgres is using page like structure(each of size 8K) to store rows. One page can contain more than one row. I also know that TOASTing is done by postgres, when the row can not be contained in given page.

But I am not certain about following scerio :-

I am referring TOAST. Following para is bit unclear :-

When a row that is to be stored is "too wide" (the threshold for that is 2KB by default), the TOAST mechanism first attempts to compress any wide field values. If that isn't enough to get the row under 2KB, it breaks up the wide field values into chunks that get stored in the associated TOAST table. Each original field value is replaced by a small pointer that shows where to find this "out of line" data in the TOAST table. TOAST will attempt to squeeze the user-table row down to 2KB in this way, but as long as it can get below 8KB, that's good enough and the row can be stored successfully.

Why it's talking about two sizes 8K and 2K ? Why postgres checks for threshold 2K ?

Thanks in advance.

Upvotes: 4

Views: 2436

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

First, I should clarify that “enough room in the table page” has nothing to do with the question if an attribute is TOASTed or not.

The paragraph you quote describes how TOAST tries to reduce the size of a table row that exceeds 2KB by first compressing the values and then storing them “out of line” in a TOAST table.

The idea is to reduce the size such that a row does not use up more than a quarter of the space in a table block. But if that fails, and the row ends up bigger than 2KB after TOASTing, that is no problem either, as long if the resulting row fits into one 8KB block.

A table row is always stored in a single table block. If there is not enough space left in any existing block, a new table block is allocated and the existing blocks are left with some empty space. This empty space can still be used for other, smaller new rows.

The limits of 8KB for a table block and 2KB for the threshold for TOASTing are somewhat arbitrary and based on experience. You can change them if you are ready to recompile PostgreSQL (from PostgreSQL v11 on, you can specify the block size when you create the database cluster with initdb), but I have not heard any reports that this is a good idea.

Upvotes: 8

Related Questions