0

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.

2

The linked documentation page says:

Each table and index is stored in a separate file. […] each table and index has a free space map … stored in a file named with the filenode number plus the suffix _fsm. Tables also have a visibility map, stored in a fork with the suffix _vm

So the actual table data ends up in a single file, or in multiple segments all belonging to the same 'virtual' file.

However, a table update can also affect indexes, or statistical information in the system tables.

In any case, the WAL tracks all changes to all tables, and at the end of each transaction, all files are in a coherent state.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.