69.1. Database File Layout
This section describes the storage format at the level of files and directories.
Traditionally, the configuration and data files used by a database
cluster are stored together within the cluster's data
directory, commonly referred to as
(after the name of the
environment variable that can be used to define it). A common location for
. Multiple clusters,
managed by different server instances, can exist on the same machine.
directory contains several subdirectories and control
files, as shown in
. In addition to
these required items, the cluster configuration files
are traditionally stored in
, although it is possible to place them elsewhere.
Table 69.1. Contents of
||A file containing the major version number of PostgreSQL|
||Subdirectory containing per-database subdirectories|
||File recording the log file(s) currently written to by the logging collector|
Subdirectory containing cluster-wide tables, such as
||Subdirectory containing transaction commit timestamp data|
||Subdirectory containing files used by the dynamic shared memory subsystem|
||Subdirectory containing status data for logical decoding|
||Subdirectory containing multitransaction status data (used for shared row locks)|
||Subdirectory containing LISTEN/NOTIFY status data|
||Subdirectory containing replication slot data|
||Subdirectory containing information about committed serializable transactions|
||Subdirectory containing exported snapshots|
||Subdirectory containing permanent files for the statistics subsystem|
||Subdirectory containing temporary files for the statistics subsystem|
||Subdirectory containing subtransaction status data|
||Subdirectory containing symbolic links to tablespaces|
||Subdirectory containing state files for prepared transactions|
||Subdirectory containing WAL (Write Ahead Log) files|
||Subdirectory containing transaction commit status data|
A file used for storing configuration parameters that are set by
||A file recording the command-line options the server was last started with|
A lock file recording the current postmaster process ID (PID),
cluster data directory path,
postmaster start timestamp,
Unix-domain socket directory path (empty on Windows),
first valid listen_address (IP address or
For each database in the cluster there is a subdirectory within
, named after the database's OID in
. This subdirectory is the default location
for the database's files; in particular, its system catalogs are stored
Each table and index is stored in a separate file. For ordinary relations,
these files are named after the table or index's
which can be found in
for temporary relations, the file name is of the form
is the backend ID of the backend which created the file, and
is the filenode number. In either case, in addition to the main file (a/k/a
main fork), each table and index has a
free space map
), which stores information about free space available in
the relation. The free space map is stored in a file named with the filenode
number plus the suffix
. Tables also have a
, stored in a fork with the suffix
to track which pages are known to have no dead tuples. The visibility map is
described further in
. Unlogged tables and indexes
have a third fork, known as the initialization fork, which is stored in a fork
with the suffix
Note that while a table's filenode often matches its OID, this is
necessarily the case; some operations, like
and some forms
, can change the filenode while preserving the OID.
Avoid assuming that filenode and table OID are the same.
Also, for certain system catalogs including
contains zero. The
actual filenode number of these catalogs is stored in a lower-level data
structure, and can be obtained using the
When a table or index exceeds 1 GB, it is divided into gigabyte-sized
. 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
In principle, free space map and visibility map forks could require multiple
segments as well, though this is unlikely to happen in practice.
A table that has columns with potentially large entries will have an
table, which is used for out-of-line storage of
field values that are too large to keep in the table rows proper.
links from a table to
table, if any.
for more information.
The contents of tables and indexes are discussed further in Section 69.6 .
Tablespaces make the scenario more complicated. Each user-defined tablespace
has a symbolic link inside the
directory, which points to the physical tablespace directory (i.e., the
location specified in the tablespace's
This symbolic link is named after
the tablespace's OID. Inside the physical tablespace directory there is
a subdirectory with a name that depends on the
server version, such as
. (The reason for using
this subdirectory is so that successive versions of the database can use
location value without conflicts.)
Within the version-specific subdirectory, there is
a subdirectory for each database that has elements in the tablespace, named
after the database's OID. Tables and indexes are stored within that
directory, using the filenode naming scheme.
tablespace is not accessed through
, but corresponds to
. Similarly, the
tablespace is not accessed through
, but corresponds to
function shows the entire path
) of any relation. It is often useful
as a substitute for remembering many of the above rules. But keep in
mind that this function just gives the name of the first segment of the
main fork of the relation - you may need to append a segment number
to find all
the files associated with the relation.
Temporary files (for operations such as sorting more data than can fit in
memory) are created within
or within a
subdirectory of a tablespace directory
if a tablespace other than
is specified for them.
The name of a temporary file has the form
is the PID of the owning backend and
distinguishes different temporary files of that backend.