Wiki / System Catalogs
System Catalogs
Compare versionsCatalogs and views, their columns, and how their structures change.
Reading PG 18current·24 versions documented, 7.4 to 20
-
Whenever ANALYZE is running, the pg_stat_progress_analyze view will contain a row for each backend that is currently running that command. The tables below describe the information that will be reported and provide information about how to interpret…
-
Whenever an application like pg_basebackup is taking a base backup, the pg_stat_progress_basebackup view will contain a row for each WAL sender process that is currently running the BASE_BACKUP replication command and streaming the backup. The table…
-
Whenever CLUSTER or VACUUM FULL is running, the pg_stat_progress_cluster view will contain a row for each backend that is currently running either command. The tables below describe the information that will be reported and provide information about…
-
Whenever COPY is running, the pg_stat_progress_copy view will contain one row for each backend that is currently running a COPY command. The table below describes the information that will be reported and provides information about how to interpret …
-
Whenever CREATE INDEX or REINDEX is running, the pg_stat_progress_create_index view will contain one row for each backend that is currently creating indexes. The tables below describe the information that will be reported and provide information abo…
-
When data checksums are being enabled or disabled on a running cluster, the pg_stat_progress_data_checksums view will contain a row for the launcher process.
-
Whenever REPACK, CLUSTER, or VACUUM FULL is running, the pg_stat_progress_repack view will contain a row for each backend that is currently running one of these commands.
-
Whenever VACUUM is running, the pg_stat_progress_vacuum view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming. The tables below describe the information that will be reported and provide infor…
-
The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.
-
The pg_stat_all_indexes view will contain one row for each index in the current database, showing statistics about accesses to that specific index. The pg_stat_user_indexes and pg_stat_sys_indexes views contain the same information, but filtered to …
-
The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table. The pg_stat_user_tables and pg_stat_sys_tables views contain the same informa…
-
The pg_stat_archiver view will always have a single row, containing data about the archiver process of the cluster.
-
The pg_stat_autovacuum_scores view will contain one row for each table in the current database (including TOAST tables), showing the current autovacuum scores for that specific table.
-
The pg_stat_bgwriter view will always have a single row, containing data about the background writer of the cluster.
-
The pg_stat_checkpointer view will always have a single row, containing data about the checkpointer process of the cluster.
-
The pg_stat_database view will contain one row for each database in the cluster, plus one for shared objects, showing database-wide statistics.
-
The pg_stat_database_conflicts view will contain one row per database, showing database-wide statistics about query cancels occurring due to conflicts with recovery on standby servers. This view will only contain information on standby servers, sinc…
-
The pg_stat_gssapi view will contain one row per backend, showing information about GSSAPI usage on this connection. It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connection.
-
The pg_stat_io view will contain one row for each combination of backend type, target I/O object, and I/O context, showing cluster-wide I/O statistics. Combinations which do not make sense are omitted. Currently, I/O on relations (e.g. tables, index…
-
The pg_stat_kind_info view contains one row for each registered statistics kind, including both built-in and custom kinds.
-
The pg_stat_lock view will contain one row for each lock type, showing cluster-wide locks statistics.
-
The pg_stat_recovery view will contain at most one row, showing statistics about the recovery state of the startup process.
-
The pg_stat_recovery_prefetch view will contain only one row. The columns wal_distance, block_distance and io_depth show current values, and the other columns show cumulative counters that can be reset with the pg_stat_reset_shared function.
-
The pg_stat_replication view will contain one row per WAL sender process, showing statistics about replication to that sender's connected standby server. Only directly connected standbys are listed; no information is available about downstream stand…
-
The pg_stat_replication_slots view will contain one row per logical replication slot, showing statistics about its usage.
-
PostgreSQL accesses certain on-disk information via SLRU (simple least-recently-used) caches. The pg_stat_slru view will contain one row for each tracked SLRU cache, showing statistics about access to cached pages. For each SLRU cache that's part of…
-
The pg_stat_ssl view will contain one row per backend or WAL sender process, showing statistics about SSL usage on this connection. It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connectio…
-
At least one row per subscription, showing information about the subscription workers. See pg_stat_subscription for details.
-
The pg_stat_subscription_stats view will contain one row per subscription.
-
Same as pg_stat_all_indexes, except that only indexes on system tables are shown.
-
Same as pg_stat_all_tables, except that only system tables are shown.
-
The pg_stat_user_functions view will contain one row for each tracked function, showing statistics about executions of that function. The track_functions parameter controls exactly which functions are tracked.
-
Same as pg_stat_all_indexes, except that only indexes on user tables are shown.
-
Same as pg_stat_all_tables, except that only user tables are shown.
-
The pg_stat_wal view will always have a single row, containing data about WAL activity of the cluster.
-
The pg_stat_wal_receiver view will contain only one row, showing statistics about the WAL receiver from that receiver's connected server.
-
Similar to pg_stat_all_tables, but counts actions taken so far within the current transaction (which are not yet included in pg_stat_all_tables and related views). The columns for numbers of live and dead rows and vacuum and analyze actions are not …
-
Same as pg_stat_xact_all_tables, except that only system tables are shown.
-
Similar to pg_stat_user_functions, but counts only calls during the current transaction (which are not yet included in pg_stat_user_functions).
-
Same as pg_stat_xact_all_tables, except that only user tables are shown.
-
The pg_statio_all_indexes view will contain one row for each index in the current database, showing statistics about I/O on that specific index. The pg_statio_user_indexes and pg_statio_sys_indexes views contain the same information, but filtered to…
-
The pg_statio_all_sequences view will contain one row for each sequence in the current database, showing statistics about I/O on that specific sequence.
-
The pg_statio_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about I/O on that specific table. The pg_statio_user_tables and pg_statio_sys_tables views contain the same inform…
-
Same as pg_statio_all_indexes, except that only indexes on system tables are shown.
-
Same as pg_statio_all_sequences, except that only system sequences are shown. (Presently, no system sequences are defined, so this view is always empty.)
-
Same as pg_statio_all_tables, except that only system tables are shown.
-
Same as pg_statio_all_indexes, except that only indexes on user tables are shown.
-
Same as pg_statio_all_sequences, except that only user sequences are shown.
-
Same as pg_statio_all_tables, except that only user tables are shown.
-
The catalog pg_aggregate stores information about aggregate functions. An aggregate function is a function that operates on a set of values (typically one column from each row that matches a query condition) and returns a single value computed from …
-
The catalog pg_am stores information about relation access methods. There is one row for each access method supported by the system. Currently, only tables and indexes have access methods. The requirements for table and index access methods are disc…
-
The catalog pg_amop stores information about operators associated with access method operator families. There is one row for each operator that is a member of an operator family. A family member can be either a search operator or an ordering operato…
-
The catalog pg_amproc stores information about support functions associated with access method operator families. There is one row for each support function belonging to an operator family.
-
The catalog pg_attrdef stores column default expressions and generation expressions. The main information about columns is stored in pg_attribute. Only columns for which a default expression or generation expression has been explicitly set will have…
-
The catalog pg_attribute stores information about table columns. There will be exactly one pg_attribute row for every column in every table in the database. (There will also be attribute entries for indexes, and indeed all objects that have pg_class…
-
The catalog pg_auth_members shows the membership relations between roles. Any non-circular set of relationships is allowed. Because user identities are cluster-wide, pg_auth_members is shared across all databases of a cluster: there is only one copy…
-
The catalog pg_authid contains information about database authorization identifiers (roles). A role subsumes the concepts of “users” and “groups”. A user is essentially just a role with the rolcanlogin flag set. Any role (with or without rolcanlogin…
-
The catalog pg_autovacuum stores optional per-relation configuration parameters for the autovacuum daemon.
-
The catalog pg_cast stores data type conversion paths, both built-in and user-defined. It should be noted that pg_cast does not represent every type conversion that the system knows how to perform; only those that cannot be deduced from some generic…
-
The catalog pg_class describes tables and other objects that have columns or are otherwise similar to a table. This includes indexes (but see also pg_index), sequences (but see also pg_sequence), views, materialized views, composite types, and TOAST…
-
The catalog pg_collation describes the available collations, which are essentially mappings from an SQL name to operating system locale categories. See Section 23.2 for more information.
-
The catalog pg_constraint stores check, not-null, primary key, unique, foreign key, and exclusion constraints on tables. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) User-defined con…
-
The catalog pg_conversion describes encoding conversion functions. See CREATE CONVERSION for more information.
-
The catalog pg_database stores information about the available databases. Databases are created with the CREATE DATABASE command. Consult Chapter 22 for details about the meaning of some of the parameters. Unlike most system catalogs, pg_database is…
-
The catalog pg_db_role_setting records the default values that have been set for run-time configuration variables, for each role and database combination. Unlike most system catalogs, pg_db_role_setting is shared across all databases of a cluster: t…
-
The catalog pg_default_acl stores initial privileges to be assigned to newly created objects.
-
The catalog pg_depend records the dependency relationships between database objects. This information allows DROP commands to find which other objects must be dropped by DROP CASCADE or prevent dropping in the DROP RESTRICT case. See also pg_shdepen…
-
The catalog pg_description stores optional descriptions (comments) for each database object. Descriptions can be manipulated with the COMMENT command and viewed with psql's \d commands. Descriptions of many built-in system objects are provided in th…
-
The pg_enum catalog contains entries showing the values and labels for each enum type. The internal representation of a given enum value is actually the OID of its associated row in pg_enum.
-
The catalog pg_event_trigger stores event triggers. See Chapter 38 for more information.
-
The catalog pg_extension stores information about the installed extensions. See Section 36.17 for details about extensions.
-
The catalog pg_foreign_data_wrapper stores foreign-data wrapper definitions. A foreign-data wrapper is the mechanism by which external data, residing on foreign servers, is accessed.
-
The catalog pg_foreign_server stores foreign server definitions. A foreign server describes a source of external data, such as a remote server. Foreign servers are accessed via foreign-data wrappers.
-
The catalog pg_foreign_table contains auxiliary information about foreign tables. A foreign table is primarily represented by a pg_class entry, just like a regular table. Its pg_foreign_table entry contains the information that is pertinent only to …
-
The catalog pg_index contains part of the information about indexes. The rest is mostly in pg_class.
-
The catalog pg_inherits records information about table and index inheritance hierarchies. There is one entry for each direct parent-child table or index relationship in the database. (Indirect inheritance can be determined by following chains of en…
-
The catalog pg_init_privs records information about the initial privileges of objects in the system. There is one entry for each object in the database which has a non-default (non-NULL) initial set of privileges. Objects can have initial privileges…
-
The catalog pg_language registers languages in which you can write functions or stored procedures. See CREATE LANGUAGE and Chapter 40 for more information about language handlers.
-
The catalog pg_largeobject holds the data making up “large objects”. A large object is identified by an OID assigned when it is created. Each large object is broken into segments or “pages” small enough to be conveniently stored as rows in pg_largeo…
-
The catalog pg_largeobject_metadata holds metadata associated with large objects. The actual large object data is stored in pg_largeobject.
-
The catalog pg_listener supports the LISTEN and NOTIFY commands.
-
The catalog pg_namespace stores namespaces. A namespace is the structure underlying SQL schemas: each namespace can have a separate collection of relations, types, etc. without name conflicts.
-
The catalog pg_opclass defines index access method operator classes. Each operator class defines semantics for index columns of a particular data type and a particular index access method. An operator class essentially specifies that a particular op…
-
The catalog pg_operator stores information about operators. See CREATE OPERATOR and Section 36.14 for more information.
-
The catalog pg_opfamily defines operator families. Each operator family is a collection of operators and associated support routines that implement the semantics specified for a particular index access method. Furthermore, the operators in a family …
-
The catalog pg_parameter_acl records configuration parameters for which privileges have been granted to one or more roles. No entry is made for parameters that have default privileges. Unlike most system catalogs, pg_parameter_acl is shared across a…
-
The catalog pg_partitioned_table stores information about how tables are partitioned.
-
The catalog pg_pltemplate stores “template” information for procedural languages.
-
The catalog pg_policy stores row-level security policies for tables. A policy includes the kind of command that it applies to (possibly all commands), the roles that it applies to, the expression to be added as a security-barrier qualification to qu…
-
The catalog pg_proc stores information about functions, procedures, aggregate functions, and window functions (collectively also known as routines). See CREATE FUNCTION, CREATE PROCEDURE, and Section 36.3 for more information. If prokind indicates t…
-
The catalog pg_publication contains all publications created in the database. For more on publications see Section 29.1.
-
The catalog pg_publication_namespace contains the mapping between schemas and publications in the database. This is a many-to-many mapping.
-
The catalog pg_publication_rel contains the mapping between relations and publications in the database. This is a many-to-many mapping. See also Section 53.18 for a more user-friendly view of this information.
-
The catalog pg_range stores information about range types. This is in addition to the types' entries in pg_type.
-
The pg_replication_origin catalog contains all replication origins created. For more on replication origins see Chapter 48. Unlike most system catalogs, pg_replication_origin is shared across all databases of a cluster: there is only one copy of pg_…
-
The catalog pg_rewrite stores rewrite rules for tables and views.
-
The catalog pg_seclabel stores security labels on database objects. Security labels can be manipulated with the SECURITY LABEL command. For an easier way to view security labels, see Section 53.23. See also pg_shseclabel, which performs a similar fu…
-
The catalog pg_sequence contains information about sequences. Some of the information about sequences, such as the name and the schema, is in pg_class
-
The catalog pg_shdepend records the dependency relationships between database objects and shared objects, such as roles. This information allows PostgreSQL to ensure that those objects are unreferenced before attempting to delete them. See also pg_d…
-
The catalog pg_shdescription stores optional descriptions (comments) for shared database objects. Descriptions can be manipulated with the COMMENT command and viewed with psql's \d commands. See also pg_description, which performs a similar function…
-
The catalog pg_shseclabel stores security labels on shared database objects. Security labels can be manipulated with the SECURITY LABEL command. For an easier way to view security labels, see Section 53.23. See also pg_seclabel, which performs a sim…
-
The catalog pg_statistic stores statistical data about the contents of the database. Entries are created by ANALYZE and subsequently used by the query planner. Note that all the statistical data is inherently approximate, even assuming that it is up…
-
The catalog pg_statistic_ext holds definitions of extended planner statistics. Each row in this catalog corresponds to a statistics object created with CREATE STATISTICS.
-
The catalog pg_statistic_ext_data holds data for extended planner statistics defined in pg_statistic_ext. Each row in this catalog corresponds to a statistics object created with CREATE STATISTICS. Normally there is one entry, with stxdinherit = fal…
-
The catalog pg_subscription contains all existing logical replication subscriptions. For more information about logical replication see Chapter 29. Unlike most system catalogs, pg_subscription is shared across all databases of a cluster: there is on…
-
The catalog pg_subscription_rel contains the state for each replicated relation in each subscription. This is a many-to-many mapping. This catalog only contains tables known to the subscription after running either CREATE SUBSCRIPTION or ALTER SUBSC…
-
The catalog pg_tablespace stores information about the available tablespaces. Tables can be placed in particular tablespaces to aid administration of disk layout. Unlike most system catalogs, pg_tablespace is shared across all databases of a cluster…
-
The catalog pg_transform stores information about transforms, which are a mechanism to adapt data types to procedural languages. See CREATE TRANSFORM for more information.
-
The catalog pg_trigger stores triggers on tables and views. See CREATE TRIGGER for more information.
-
The pg_ts_config catalog contains entries representing text search configurations. A configuration specifies a particular text search parser and a list of dictionaries to use for each of the parser's output token types. The parser is shown in the pg…
-
The pg_ts_config_map catalog contains entries showing which text search dictionaries should be consulted, and in what order, for each output token type of each text search configuration's parser. PostgreSQL's text search features are described at le…
-
The pg_ts_dict catalog contains entries defining text search dictionaries. A dictionary depends on a text search template, which specifies all the implementation functions needed; the dictionary itself provides values for the user-settable parameter…
-
The pg_ts_parser catalog contains entries defining text search parsers. A parser is responsible for splitting input text into lexemes and assigning a token type to each lexeme. Since a parser must be implemented by C-language-level functions, creati…
-
The pg_ts_template catalog contains entries defining text search templates. A template is the implementation skeleton for a class of text search dictionaries. Since a template must be implemented by C-language-level functions, creation of new templa…
-
The catalog pg_type stores information about data types. Base types and enum types (scalar types) are created with CREATE TYPE, and domains with CREATE DOMAIN. A composite type is automatically created for each table in the database, to represent th…
-
The catalog pg_user_mapping stores the mappings from local user to remote. Access to this catalog is restricted from normal users, use the view pg_user_mappings instead.
-
The pg_aios view lists all Asynchronous I/O handles that are currently in-use. An I/O handle is used to reference an I/O operation that is being prepared, executed or is in the process of completing. pg_aios contains one row for each I/O handle. Thi…
-
The pg_available_extension_versions view lists the specific extension versions that are available for installation. See also the pg_extension catalog, which shows the extensions currently installed.
-
The pg_available_extensions view lists the extensions that are available for installation. See also the pg_extension catalog, which shows the extensions currently installed.
-
The view pg_backend_memory_contexts displays all the memory contexts of the server process attached to the current session. pg_backend_memory_contexts contains one row for each memory context.
-
The view pg_config describes the compile-time configuration parameters of the currently installed version of PostgreSQL. It is intended, for example, to be used by software packages that want to interface to PostgreSQL to facilitate finding the requ…
-
The pg_cursors view lists the cursors that are currently available. Cursors can be defined in several ways: via the DECLARE statement in SQL via the Bind message in the frontend/backend protocol, as described in Section 54.2.3 via the Server Program…
-
The pg_dsm_registry_allocations view shows shared memory allocations tracked in the dynamic shared memory (DSM) registry.
-
The view pg_file_settings provides a summary of the contents of the server's configuration file(s). A row appears in this view for each “name = value” entry appearing in the files, with annotations indicating whether the value could be applied succe…
-
The view pg_group exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows the names and members of all roles that are marked as not rolcanlogin, which is an approximation to the set of roles …
-
The view pg_hba_file_rules provides a summary of the contents of the client authentication configuration file, pg_hba.conf. A row appears in this view for each non-empty, non-comment line in the file, with annotations indicating whether the rule cou…
-
The view pg_ident_file_mappings provides a summary of the contents of the client user name mapping configuration file, pg_ident.conf. A row appears in this view for each non-empty, non-comment line in the file, with annotations indicating whether th…
-
The view pg_indexes provides access to useful information about each index in the database.
-
The view pg_locks provides access to information about the locks held by active processes within the database server. See Chapter 13 for more discussion of locking. pg_locks contains one row per active lockable object, requested lock mode, and relev…
-
The view pg_matviews provides access to useful information about each materialized view in the database.
-
The view pg_policies provides access to useful information about each row-level security policy in the database.
-
The pg_prepared_statements view displays all the prepared statements that are available in the current session. See PREPARE for more information about prepared statements. pg_prepared_statements contains one row for each prepared statement. Rows are…
-
The view pg_prepared_xacts displays information about transactions that are currently prepared for two-phase commit (see PREPARE TRANSACTION for details). pg_prepared_xacts contains one row per prepared transaction. An entry is removed when the tran…
-
The view pg_publication_sequences provides information about the mapping between publications and sequences.
-
The view pg_publication_tables provides information about the mapping between publications and information of tables they contain. Unlike the underlying catalog pg_publication_rel, this view expands publications defined as FOR ALL TABLES and FOR TAB…
-
The pg_replication_origin_status view contains information about how far replay for a certain origin has progressed. For more on replication origins see Chapter 48.
-
The pg_replication_slots view provides a listing of all replication slots that currently exist on the database cluster, along with their current state. For more on replication slots, see Section 26.2.6 and Chapter 47.
-
The view pg_roles provides access to information about database roles. This is simply a publicly readable view of pg_authid that blanks out the password field.
-
The view pg_rules provides access to useful information about query rewrite rules.
-
The view pg_seclabels provides information about security labels. It as an easier-to-query version of the pg_seclabel catalog.
-
The view pg_sequences provides access to useful information about each sequence in the database.
-
The view pg_settings provides access to run-time parameters of the server. It is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW,…
-
The view pg_shadow exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows properties of all roles that are marked as rolcanlogin in pg_authid. The name stems from the fact that this table sh…
-
The pg_shmem_allocations view shows allocations made from the server's main shared memory segment. This includes both memory allocated by PostgreSQL itself and memory allocated by extensions using the mechanisms detailed in Section 36.10.11. Note th…
-
The pg_shmem_allocations_numa shows how shared memory allocations in the server's main shared memory segment are distributed across NUMA nodes. This includes both memory allocated by PostgreSQL itself and memory allocated by extensions using the mec…
-
The view pg_stats provides access to the information stored in the pg_statistic catalog. This view allows access only to rows of pg_statistic that correspond to tables the user has permission to read, and therefore it is safe to allow public read ac…
-
The view pg_stats_ext provides access to information about each extended statistics object in the database, combining information stored in the pg_statistic_ext and pg_statistic_ext_data catalogs. This view allows access only to rows of pg_statistic…
-
The view pg_stats_ext_exprs provides access to information about all expressions included in extended statistics objects, combining information stored in the pg_statistic_ext and pg_statistic_ext_data catalogs. This view allows access only to rows o…
-
The view pg_tables provides access to useful information about each table in the database.
-
The view pg_timezone_abbrevs provides a list of time zone abbreviations that are currently recognized by the datetime input routines. The contents of this view change when the TimeZone or timezone_abbreviations run-time parameters are modified.
-
The view pg_timezone_names provides a list of time zone names that are recognized by SET TIMEZONE, along with their associated abbreviations, UTC offsets, and daylight-savings status. (Technically, PostgreSQL does not use UTC because leap seconds ar…
-
The view pg_user provides access to information about database users. This is simply a publicly readable view of pg_shadow that blanks out the password field.
-
The view pg_user_mappings provides access to information about user mappings. This is essentially a publicly readable view of pg_user_mapping that leaves out the options field if the user has no rights to use it.
-
The view pg_views provides access to useful information about each view in the database.
-
The view pg_wait_events provides description about the wait events.