postgres system catalog

• postgresql.org/docs/14/catalogs.html

Metadata of all cluster objects (such as tables, indexes, data types, or functions) is stored in tables that belong to the system catalog.

• Each database has its own set of tables (and views) that describe the objects of this database.
• Several system catalog tables are common to the whole cluster; they do not belong to any particular database (technically, a dummy database with a zero id is used), but can be accessed from all of them. 'system wide' tables

Names of all system catalog tables begin with pg_, like in pg_database. Column names start with a three-letter prefix that usually corresponds to the table name, like in datname. In all system catalog tables, the column declared as the primary key is called oid (object identifier); its type, which is also called oid, is a 32-bit integer.

• pg_catalog is used for system catalog tables.
• information_schema provides an alternative view for the system catalog as defined by the sql standard.
• pg_toast is used for objects related to TOAST
• pg_temp comprises temporary tables. Although different users create temporary tables in different schemas called pg_temp_N, everyone refers to their objects using the pg_temp alias.

search_path parameter is implicitly extended with pg_catalog and (if necessary) pg_temp schemas.

System Wide Metadata

pg_database

pg_stat_database

• Transactions

Transaction information can be found in the columns xact_commit and xact_rollback, which contain the number of transactions the database has committed and rolled back respectively. This will help show just how active a database is, as well as spot possible failures with programs who may be erroring / rolling back at an alarming rate.

• Disk and Memory access

Information on whether or not data is retrieved from disk or memory is stored in the blks_read and blks_hit columns. Blks_read shows the number of blocks this database read from disk, while blks_hit shows the number of blocks that were found in PostgreSQL’s buffer cache (represented by the shared_buffers parameter). Since RAM is much faster than disk, we would ideally see blks_hit consistently higher than blks_read, and if not, we can re-evaluate our available memory.

• Tuples

The next few columns deal with tuples. Tup_returned is the number of rows returned in the database, which is the number of rows read by sequential scans if from a table, or the number of index entries returned when from an index”. Tup_fetched is the number of rows fetched in the database, meaning they were a result of bitmap scans, which is the number of table rows fetched by bitmap scans if from a table, or table rows fetched by simple index scans if using an index.

We also have tup_inserted, tup_updated, and tup_deleted, which represents the number of tuples inserted, updated, and deleted in this database respectively. This will help understand how data enters, changes, and leaves the database. Since updated and deleted tuples result in dead rows, high values in these columns would suggest autovacuum operations be tuned to meet the needs of the database activity.

• Conflicts

If the database in question is a standby server, the column conflicts comes in handy as a way to track how many queries were canceled due to conflicts with the standby being in ‘recovery mode’. If not a standby cluster, this column can be ignored.

• Temporary files / data

At times, queries will need to write to temporary files. This can happen when the amount of work_mem allocated to the connection has been used up, and needs to continue a sort operation on disk rather than in memory. The column temp_files tracks the number of these files that were created, and temp_bytes tracks the total size of all temporary files used. This data can help inform work_mem tuning, or even finding queries that could use re-writing if temp file size is too big.

• Deadlocks

The deadlocks column tracks how many times a deadlock occurs. Since a deadlock can cause errors for queries that otherwise wouldn’t error, it’s good to track this and make sure applications aren’t stepping on each others feet.

• Read and write times

The columns blk_read_time and blk_write_time tracks the total number of milliseconds that backends in the database spend reading and writing data, which can be helpful if trying to compare / improve disk read/write speed

• Stats reset

This column, stats_reset, simply shows a timestamp (with time zone) of the last time the stats mentioned in this row has been reset. A null value means they haven’t been reset since inception, or even possibly a crash of the database what may have wiped out these stats.

pg_stat_bgwriter - info about Checkpoints and The Background Writer

• Checkpoints

A checkpoint happens either on schedule (represented by the checkpoint_timeout parameter), or when the maximum amount of WAL files have been used since the last checkpoint, and needs to force a checkpoint. Either way, a checkpoint writes dirty buffers to disk, and there are four columns tracking it.

The columns checkpoints_timed and checkpoints_req show the number of scheduled checkpoints occur (timed) and the number of requested checkpoints (also referred to as forced). A high climbing value of checkpoint_req could suggest an insufficient max_wal_size value.

Columns checkpoint_write_time and checkpoint_sync_time record the total amount of time (in milliseconds) the checkpoint process has spent writing and syncing to disk.

Finally, buffers_checkpoint is the total number of buffers written to disk by checkpoints.

• Background Writer

The background writer is a separate process that writes dirty buffers to disk, which ideally reduces the amount of work the checkpointer needs to do.

The column buffers_clean represents the number of buffers written to disk by the background process. When compared to buffers_checkpoint, it shows how much of the workload is handled by each process (with the added knowledge that background writer has the possibility of writing buffers multiple times if they change often, vs less frequently with a timed checkpoint).

Maxwritten_clean represents the number of times the background writer reached the maximum number of pages to flush each time it runs (controlled with the bgwriter_lru_maxpages parameter).