CODEWITHSUNDEEP
postgresqlstorage
Dmitrey Sysolyatin
Dmitrey Sysolyatin

Reputation: 3

How does Postgres update a variable length record in the heap file

I have read the article "http://rachbelaid.com/introduction-to-postgres-physical-storage/" about physical storage. But I didn't understand how Postgres updates a variable length record in the file. Does Postgres shift adjacent row or creates a new row and marks old row as deleted?

Upvotes: 0

Views: 260

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

You should read the documentation itself to get reliable information.

PostgreSQL tables are divided in blocks of 8kB size. Each record (the official word is “tuple”) is stored in one block.

PostgreSQL tries to shrink tuples that exceed 2kB by storing variable length data types out-of-line in the “TOAST table” associated with the table. This technique makes sure that even large rows can be stored in a single block.

PostgreSQL never updates tuples. During an UPDATE statement, a new tuple is inserted into a block that has enough free space, and the old one is marked as replaced. This is to implement transaction isolation; the old tuple is still visible to concurrent statements with a snapshot older than the updating transaction.

Old tuples are eventually freed by the autovacuum background process. The free space can then be reused by new tuples.

Upvotes: 1

Related Questions