Search PostgreSQL documentation

Partial IndexesManual ChaptersPG1811.8. Partial Indexes A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index con…Partial Indexes Partial Match AlgorithmManual ChaptersPG1865.4.4.2. Partial Match Algorithm GIN can support “partial match” queries, in which the query does not determine an exact match for one or more keys, but the possible matches fall within a r…Partial Match Algorithm PARTITION BY ClauseManual ChaptersPG18PARTITION BY Clause The PARTITION BY clause is a PostgreSQL extension.PARTITION BY Clause Partition MaintenanceManual ChaptersPG185.12.2.2. Partition Maintenance Normally the set of partitions established when initially defining the table is not intended to remain static. It is common to want to remove partitions holdi…Partition Maintenance PARTITION OF ClauseManual ChaptersPG18PARTITION OF Clause The PARTITION OF clause is a PostgreSQL extension.PARTITION OF Clause Partition PruningManual ChaptersPG185.12.4. Partition Pruning Partition pruning is a query optimization technique that improves performance for declaratively partitioned tables. As an example: SET enable_partition_pruning = on…Partition Pruning Partitioned TablesManual ChaptersPG1829.4.4. Partitioned Tables If the publication contains a partitioned table, the publication parameter publish_via_partition_root determines which row filter is used. If publish_via_partition…Partitioned Tables Partitioning and Constraint ExclusionManual ChaptersPG185.12.5. Partitioning and Constraint Exclusion Constraint exclusion is a query optimization technique similar to partition pruning. While it is primarily used for partitioning implemented usi…Partitioning and Constraint Exclusion Partitioning Using InheritanceManual ChaptersPG185.12.3. Partitioning Using Inheritance While the built-in declarative partitioning is suitable for most common use cases, there are some circumstances where a more flexible approach may be u…Partitioning Using Inheritance Passing Query Parameters Using an SQLDAManual ChaptersPG1834.7.2.3. Passing Query Parameters Using an SQLDA The general steps to use an SQLDA to pass input parameters to a prepared query are: Create a prepared query (prepared statement) Declare an …Passing Query Parameters Using an SQLDA Password AuthenticationManual ChaptersPG1820.5. Password Authentication There are several password-based authentication methods. These methods operate similarly but differ in how the users' passwords are stored on the server and how…Password Authentication Password Hashing FunctionsManual ChaptersPG18F.26.2. Password Hashing Functions The functions crypt() and gen_salt() are specifically designed for hashing passwords. crypt() does the hashing and gen_salt() prepares algorithm parameters…Password Hashing Functions passwordcheck — verify password strengthManual ChaptersPG18F.24. passwordcheck — verify password strength The passwordcheck module checks users' passwords whenever they are set with CREATE ROLE or ALTER ROLE. If a password is considered too weak, it…passwordcheck — verify password strength PathsManual ChaptersPG188.8.5. Paths Paths are represented by lists of connected points. Paths can be open, where the first and last points in the list are considered not connected, or closed, where the first and l…Paths Pattern MatchingManual ChaptersPG189.7. Pattern Matching There are three separate approaches to pattern matching provided by PostgreSQL: the traditional SQL LIKE operator, the more recent SIMILAR TO operator (added in SQL:199…Pattern Matching PatternsManual ChaptersPG18Patterns The various \d commands accept a pattern parameter to specify the object name(s) to be displayed. In the simplest case, a pattern is just the exact name of the object. The character…Patterns PDFManual ChaptersPG18J.3.3. PDF To produce a PDF rendition of the documentation using FOP, you can use one of the following commands, depending on the preferred paper format: For A4 format: doc/src/sgml$ make po…PDF Peer AuthenticationManual ChaptersPG1820.9. Peer Authentication The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optio…Peer Authentication Per-Command PoliciesManual ChaptersPG18Per-Command Policies ALL Using ALL for a policy means that it will apply to all commands, regardless of the type of command. If an ALL policy exists and more specific policies exist, then bo…Per-Command Policies Per-Statement ReportManual ChaptersPG18Per-Statement Report With the -r option, pgbench collects the following statistics for each statement: latency — elapsed transaction time for each statement. pgbench reports an average value…Per-Statement Report Per-Transaction LoggingManual ChaptersPG18Per-Transaction Logging With the -l option (but without the --aggregate-interval option), pgbench writes information about each transaction to a log file. The log file will be named prefix.n…Per-Transaction Logging Performance TipsManual ChaptersPG18Chapter 14. Performance Tips Query performance can be affected by many things. Some of these can be controlled by the user, while others are fundamental to the underlying design of the syste…Performance Tips pg_aggregateManual ChaptersPG1852.2. pg_aggregate 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…pg_aggregate pg_aiosManual ChaptersPG1853.2. pg_aios 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…pg_aios pg_amManual ChaptersPG1852.3. pg_am 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 acces…pg_am pg_amcheckManual ChaptersPG18pg_amcheck pg_amcheck — checks for corruption in one or more PostgreSQL databases Synopsis pg_amcheck [option...] [dbname]pg_amcheck pg_amopManual ChaptersPG1852.4. pg_amop 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 famil…pg_amop pg_amprocManual ChaptersPG1852.5. pg_amproc 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 o…pg_amproc pg_archivecleanupManual ChaptersPG18pg_archivecleanup pg_archivecleanup — clean up PostgreSQL WAL archive files Synopsis pg_archivecleanup [option...] archivelocation oldestkeptwalfilepg_archivecleanup pg_attrdefManual ChaptersPG1852.6. pg_attrdef 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 d…pg_attrdef pg_attributeManual ChaptersPG1852.7. pg_attribute 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 al…pg_attribute pg_auth_membersManual ChaptersPG1852.9. pg_auth_members 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…pg_auth_members pg_authidManual ChaptersPG1852.8. pg_authid 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 …pg_authid pg_available_extension_versionsManual ChaptersPG1853.4. pg_available_extension_versions The pg_available_extension_versions view lists the specific extension versions that are available for installation. See also the pg_extension catalog, w…pg_available_extension_versions pg_available_extensionsManual ChaptersPG1853.3. pg_available_extensions The pg_available_extensions view lists the extensions that are available for installation. See also the pg_extension catalog, which shows the extensions current…pg_available_extensions pg_backend_memory_contextsManual ChaptersPG1853.5. pg_backend_memory_contexts The view pg_backend_memory_contexts displays all the memory contexts of the server process attached to the current session. pg_backend_memory_contexts contai…pg_backend_memory_contexts pg_basebackupManual ChaptersPG18pg_basebackup pg_basebackup — take a base backup of a PostgreSQL cluster Synopsis pg_basebackup [option...]pg_basebackup pg_buffercache — inspect PostgreSQL buffer cache stateManual ChaptersPG18F.25. pg_buffercache — inspect PostgreSQL buffer cache state The pg_buffercache module provides a means for examining what's happening in the shared buffer cache in real time. It also offers…pg_buffercache — inspect PostgreSQL buffer cache state pg_castManual ChaptersPG1852.10. pg_cast 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…pg_cast pg_checksumsManual ChaptersPG18pg_checksums pg_checksums — enable, disable or check data checksums in a PostgreSQL database cluster Synopsis pg_checksums [option...] [[ -D | --pgdata ]datadir]pg_checksums