How are Postgres database files updated?

by Kevin Lee   Last Updated October 15, 2018 17:06 PM

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.

Tags : postgresql

Related Questions

Postgres gist slow index f_unaccent

Updated April 09, 2018 20:06 PM