This is a list of terms and their meaning in the context of PostgreSQL and relational database systems in general.
Atomicity, Consistency, Isolation, and Durability. This set of properties of database transactions is intended to guarantee validity in concurrent operation and even in event of errors, power failures, etc.
A function that combines (aggregates) multiple input values, for example by counting, averaging or adding, yielding a single output value.
For more information, see Section 9.21.
See Also Window function (routine).
The act of collecting statistics from data in tables and other relations to help the query planner to make decisions about how to execute queries.
(Don't confuse this term with the ANALYZE option to the EXPLAIN command.)
For more information, see ANALYZE.
In reference to a datum: the fact that its value cannot be broken down into smaller components.
In reference to a database transaction: see atomicity.
The property of a transaction that either all its operations complete as a single unit or none do. In addition, if a system failure occurs during the execution of a transaction, no partial results are visible after recovery. This is one of the ACID properties.
An element with a certain name and data type found within a tuple.
A set of background processes that routinely perform vacuum and analyze operations. The auxiliary process that coordinates the work and is always present (unless autovacuum is disabled) is known as the autovacuum launcher, and the processes that carry out the tasks are known as the autovacuum workers.
For more information, see Section 25.1.6.
A process within an instance that is in charge of some specific background task for the instance. The auxiliary processes consist of the autovacuum launcher (but not the autovacuum workers), the background writer, the checkpointer, the logger, the startup process, the WAL archiver, the WAL receiver (but not the WAL senders), and the WAL writer.
Process of an instance which acts on behalf of a client session and handles its requests.
(Don't confuse this term with the similar terms Background Worker or Background Writer).
Process within an instance, which runs system- or user-supplied code. Serves as infrastructure for several features in PostgreSQL, such as logical replication and parallel queries. In addition, Extensions can add custom background worker processes.
For more information, see Chapter 48.
An auxiliary process that writes dirty data pages from shared memory to the file system. It wakes up periodically, but works only for a short period in order to distribute its expensive I/O activity over time to avoid generating larger I/O peaks which could block other processes.
For more information, see Section 20.4.5.
A binary copy of all database cluster files. It is generated by the tool pg_basebackup. In combination with WAL files it can be used as the starting point for recovery, log shipping, or streaming replication.
Space in data pages which does not contain current row versions, such as unused (free) space or outdated row versions.
The first user initialized in a database cluster.
This user owns all system catalog tables in each database. It is also the role from which all granted permissions originate. Because of these things, this role may not be dropped.
This role also behaves as a normal database superuser.
Some operations will access a large number of pages. A Buffer Access Strategy helps to prevent these operations from evicting too many pages from shared buffers.
A Buffer Access Strategy sets up references to a limited number of shared buffers and reuses them circularly. When the operation requires a new page, a victim buffer is chosen from the buffers in the strategy ring, which may require flushing the page's dirty data and possibly also unflushed WAL to permanent storage.
Buffer Access Strategies are used for various operations such as sequential scans of large tables, VACUUM, COPY, CREATE TABLE AS SELECT, ALTER TABLE, CREATE DATABASE, CREATE INDEX, and CLUSTER.
A conversion of a datum from its current data type to another data type.
For more information, see CREATE CAST.
The SQL standard uses this term to indicate what is called a database in PostgreSQL's terminology.
(Don't confuse this term with system catalog).
For more information, see Section 23.1.
A type of constraint defined on a relation which restricts the values allowed in one or more attributes. The check constraint can make reference to any attribute of the same row in the relation, but cannot reference other rows of the same relation or other relations.
For more information, see Section 5.4.
A point in the WAL sequence at which it is guaranteed that the heap and index data files have been updated with all information from shared memory modified before that checkpoint; a checkpoint record is written and flushed to WAL to mark that point.
A checkpoint is also the act of carrying out all the actions that are necessary to reach a checkpoint as defined above. This process is initiated when predefined conditions are met, such as a specified amount of time has passed, or a certain volume of records has been written; or it can be invoked by the user with the command CHECKPOINT.
For more information, see Section 30.5.
An auxiliary process that is responsible for executing checkpoints.
See Relation.
Any process, possibly remote, that establishes a session by connecting to an instance to interact with a database.
The operating system user that owns the data directory and under which the postgres process is run. It is required that this user exist prior to creating a new database cluster.
On operating systems with a root user, said user is not allowed to be the cluster owner.
The act of finalizing a transaction within the database, which makes it visible to other transactions and assures its durability.
For more information, see COMMIT.
The concept that multiple independent operations happen within the database at the same time. In PostgreSQL, concurrency is controlled by the multiversion concurrency control mechanism.
An established line of communication between a client process and a backend process, usually over a network, supporting a session. This term is sometimes used as a synonym for session.
For more information, see Section 20.3.
The property that the data in the database is always in compliance with integrity constraints. Transactions may be allowed to violate some of the constraints transiently before it commits, but if such violations are not resolved by the time it commits, such a transaction is automatically rolled back. This is one of the ACID properties.
A restriction on the values of data allowed within a table, or in attributes of a domain.
For more information, see Section 5.4.
A system which, if enabled, accumulates statistical information about the instance's activities.
For more information, see Section 28.2.
See Data directory.
A named collection of local SQL objects.
For more information, see Section 23.1.
A collection of databases and global SQL objects, and their common static and dynamic metadata. Sometimes referred to as a cluster. A database cluster is created using the initdb program.
In PostgreSQL, the term cluster is also sometimes used to refer to an instance. (Don't confuse this term with the SQL command CLUSTER.)
See also cluster owner, the operating-system owner of a cluster, and bootstrap superuser, the PostgreSQL owner of a cluster.
See Instance.
A role having superuser status (see Section 22.2).
Frequently referred to as superuser.
The base directory on the file system of a server that contains all data files and subdirectories associated with a database cluster (with the exception of tablespaces, and optionally WAL). The environment variable PGDATA is commonly used to refer to the data directory.
A cluster's storage space comprises the data directory plus any additional tablespaces.
For more information, see Section 73.1.
The basic structure used to store relation data. All pages are of the same size. Data pages are typically stored on disk, each in a specific file, and can be read to shared buffers where they can be modified, becoming dirty. They become clean when written to disk. New pages, which initially exist in memory only, are also dirty until written.
The internal representation of one value of an SQL data type.
An SQL command which removes rows from a given table or relation.
For more information, see DELETE.
A user-defined data type that is based on another underlying data type. It acts the same as the underlying type except for possibly restricting the set of allowed values.
For more information, see Section 8.18.
The assurance that once a transaction has been committed, the changes remain even after a system failure or crash. This is one of the ACID properties.
See Transaction ID.
A software add-on package that can be installed on an instance to get extra features.
For more information, see Section 38.17.
A physical file which stores data for a given relation. File segments are limited in size by a configuration value (typically 1 gigabyte), so if a relation exceeds that size, it is split into multiple segments.
For more information, see Section 73.1.
(Don't confuse this term with the similar term WAL segment).
A means of representing data that is not contained in the local database so that it appears as if were in local table(s). With a foreign data wrapper it is possible to define a foreign server and foreign tables.
For more information, see CREATE FOREIGN DATA WRAPPER.
A type of constraint defined on one or more columns in a table which requires the value(s) in those columns to identify zero or one row in another (or, infrequently, the same) table.
A named collection of foreign tables which all use the same foreign data wrapper and have other configuration values in common.
For more information, see CREATE SERVER.
A relation which appears to have rows and columns similar to a regular table, but will forward requests for data through its foreign data wrapper, which will return result sets structured according to the definition of the foreign table.
For more information, see CREATE FOREIGN TABLE.
Each of the separate segmented file sets in which a relation is stored. The main fork is where the actual data resides. There also exist two secondary forks for metadata: the free space map and the visibility map. Unlogged relations also have an init fork.
A storage structure that keeps metadata about each data page of a table's main fork. The free space map entry for each page stores the amount of free space that's available for future tuples, and is structured to be efficiently searched for available space for a new tuple of a given size.
For more information, see Section 73.3.
A type of routine that receives zero or more arguments, returns zero or more output values, and is constrained to run within one transaction. Functions are invoked as part of a query, for example via SELECT. Certain functions can return sets; those are called set-returning functions.
Functions can also be used for triggers to invoke.
For more information, see CREATE FUNCTION.
See UTC.