CODEWITHSUNDEEP
postgresql
Kevin Lee
Kevin Lee

Reputation: 1191

How are Postgres database files updated?

I'm curious how the /base files are updated for a Postgres database. Do the segments correspond to segments of each table, so that only a few segment files are updated after an update to subset of the table? Or will the update to the table cause changes to all or a majority of the segment files?

Basically, I'm interested in the reliability of these updates to the /base files for backup purposes. Given the usage of WAL for PITR, I have a feeling that the files are not updated in any coherent way for my purposes.

According to the documentation:

When a table or index exceeds 1 GB, it is divided into gigabyte-sized segments. The first segment's file name is the same as the filenode; subsequent segments are named filenode.1, filenode.2, etc. This arrangement avoids problems on platforms that have file size limitations. (Actually, 1 GB is just the default segment size. The segment size can be adjusted using the configuration option --with-segsize when building PostgreSQL.) In principle, free space map and visibility map forks could require multiple segments as well, though this is unlikely to happen in practice.

Upvotes: 0

Views: 419

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247215

At the link you quote, you will also find that tables and indexes are organized in 8KB blocks, and a table row is always contained in one block.

So modifying a row will affect at most two blocks: the one that contains the old row version, and one where the new version is written. So no more than two table files can be affected.

Indexes are less predictable: if the update is HOT, nothing will be modified. If an index page split becomes necessary, several blocks may be affected.

Since your main concern seems to be reliability: modifications to the data files are asynchronous to the SQL statements that cause them. The information is logged in WAL and then written to shared memory. The modifications will hit the disk during the next checkpoint at latest.

The WAL is responsible for the durability of all transactions since the last checkpoint.

Upvotes: 2

Related Questions