No. The two key spaces do not overlap. They are distinguished by the objsubid value in pg_locks: objsubid=1 for single bigint keys, objsubid=2 for two-integer key pairs.

The storage size in bytes, including compression if applied. It returns the actual storage size, not the logical size.

false. By default, the message is not flushed immediately.

Only when the built-in log collector is running. It returns false if the log collector is not active.

pg_try_advisory_lock() is non-blocking and returns immediately with true/false. pg_advisory_lock() blocks and waits if necessary until the lock is acquired.

It forces a snapshot to be logged immediately, which is useful for logical decoding on standby servers to avoid waiting for the background writer.

18 bytes, regardless of the actual size of the represented value.

Yes. Superusers can cancel/terminate any backend. Roles with pg_signal_backend can cancel/terminate non-superuser backends. Roles with pg_signal_autovacuum_worker can cancel/terminate autovacuum workers.

PostgreSQL 9.6. This was the first predefined role available in PostgreSQL.

relid (regclass), parentrelid (regclass), isleaf (boolean), and level (integer).

No. Session-level advisory locks do not honor transaction semantics. A lock acquired during a transaction that is later rolled back will still be held after the rollback.

NULL. It only returns a value for partitions and partitioned tables.

Bytes. It calculates the difference in bytes between two LSN values (lsn1 - lsn2).

No. It will not move the slot's position backward or beyond the current WAL insert location.

Yes. pg_advisory_unlock_all() is implicitly invoked at session end, releasing all session-level advisory locks.

Superusers and members of the pg_monitor role by default. Access can be granted to others via GRANT.

It returns boolean. The return value only indicates whether the signal was successfully sent, NOT whether the configuration was successfully reloaded. Check pg_file_settings and pg_hba_file_rules views for configuration errors.

Superusers and the index owner.

No. These functions cannot be executed during promotion to primary.

PostgreSQL 15. pg_stop_backup() was introduced in PostgreSQL 8.0 and replaced by pg_backup_stop() in version 15.

Outside the live data directory. They must be written as part of the backup archive but not within the PostgreSQL data directory itself.

CREATE privilege on the tablespace OR membership in the pg_read_all_stats role. Default tablespaces don't require special permissions.

Level 0. The input table has level 0, and child partitions have incrementing levels.

pg_total_relation_size() = pg_table_size() + pg_indexes_size(). The total includes both table and index storage.

SIGHUP. This signal causes the postmaster and all server processes to reload their configuration files.

It returns 0 when: (1) the fastupdate storage parameter is disabled for the GIN index, or (2) the pending list is already empty (already cleaned by VACUUM or previous cleanup).

Recovery will stop at the first matching name encountered. Using duplicate names can cause recovery to stop at an unintended point.

MAINTAIN privilege on the relation OR database ownership.

Normally 2 kB (2048 bytes). TOAST is triggered when a row value exceeds this threshold.

Only information functions. Most recovery control functions like pg_wal_replay_pause() can only execute during recovery. Information functions like pg_is_in_recovery() work in any mode.

Multiple locks on the same resource require multiple unlocks. If you lock the same advisory lock multiple times, you must unlock it the same number of times.

true. By default, pg_promote() waits for the promotion to complete before returning.

NULL. It returns NULL if the value is uncompressed.

'main'. When only one argument is provided, pg_relation_size() returns the size of the main data fork.

The entire file is returned. Both parameters are optional, and omitting them reads the complete file.

No. It only adds new collations; it does not remove obsolete ones.

No. Attempting to copy an invalidated slot will fail with an error.

true. By default, pg_backup_stop() waits for WAL to be archived when archiving is enabled.

It fails with an error. Once a transaction has exported any snapshots, it cannot be prepared with PREPARE TRANSACTION. The error message is: 'ERROR: cannot PREPARE a transaction that has exported snapshots'.

NULL. It returns NULL rather than raising an error when the origin doesn't exist.

At least 25%. PGLZ will only compress if it can achieve at least a 25% reduction in size.

It returns the current LSN but does nothing. No new WAL file is created if there's been no WAL activity.

No. It returns bytea without encoding validation, unlike pg_read_file() which validates encoding.

Yes. They return NULL if the server was never in recovery or if no WAL has been received/replayed yet.

It returns NULL if the value is un-TOASTed (not stored in the TOAST table) or not stored on disk.

A negative offset is relative to the end of the file. The function uses SEEK_END positioning, allowing you to read from the end of the file backwards.

To the server log only, at LOG level. The output never appears in the client application; it only appears in the server's log files.

Zero (0). Use zero to query the default tablespace.

Yes. File access functions bypass standard database privilege checks, which is why they require superuser permissions by default.

false. When is_local is false, the setting applies to the entire session. When true, it applies only to the current transaction.

name (text), size (bigint), and modification (timestamp with time zone).

PostgreSQL 12.

A record with three columns: lsn (pg_lsn), labelfile (text), and spcmapfile (text).

An integer representing the count of new collations created. It skips collations that already exist.

READ COMMITTED isolation level only. Multiple exports are only allowed in READ COMMITTED transactions.

pg_lsn (Log Sequence Number). This represents the backup's starting WAL location.

There is no difference in how the limit is applied. Both functions check the limit after each transaction, so the actual number of rows returned may exceed upto_nchanges.

No. Files and directories with names beginning with a dot are excluded from the results (except '.' and '..' when include_dot_dirs is true in pg_ls_dir).

A record with two columns: slot_name (name) and lsn (pg_lsn).

The timestamp when the commit or abort WAL record was generated on the primary server. This is the transaction time on the primary, not when it was replayed on the standby.

pglz (historical, integrated in all versions) and lz4 (since PostgreSQL 14).

The four valid fork values are: 'main' (the main data fork), 'fsm' (Free Space Map), 'vm' (Visibility Map), and 'init' (initialization fork for unlogged tables/indexes).

Yes. The inherited (boolean) parameter is required.

NULL. When missing_ok is supplied and is true, current_setting() returns NULL instead of throwing an error.

Superusers and the index owner.

Temporary synced slots cannot be used for logical decoding. They must be dropped after promoting the standby to primary.

It returns false and issues a SQL warning. The function does not raise an error.

The valid units are: 'bytes', 'B', 'kB', 'MB', 'GB', 'TB', and 'PB'.

When true, it includes '.' and '..' in the results. By default (false), these directories are excluded.

When true, it enables decoding of prepared transactions in two-phase commit. When false (default), prepared transactions are not decoded.

It displays them as the next-lowest unit. For example, 9216 bytes is displayed as '9216 bytes' instead of converting to kB.

CONNECT privilege on the database OR membership in the pg_read_all_stats role.

Milliseconds. The timeout is specified in milliseconds.

None. LZ4 only requires that the compressed data be no larger than uncompressed data (no size increase).

No, except for certain information functions. Most backup control functions like pg_backup_start() cannot execute during recovery.

A bigint representing the number of pages removed from the pending list.

No. Exported snapshots are only available for import until the exporting transaction ends.

pg_logical_slot_get_changes() consumes the changes (advances the slot position), making them unavailable for future reads. pg_logical_slot_peek_changes() returns changes without consuming them, allowing the same changes to be retrieved again.

PLAIN (no compression or out-of-line storage), EXTENDED (allows both compression and out-of-line storage), EXTERNAL (allows out-of-line storage but not compression), and MAIN (allows compression but not out-of-line storage).

No. The flush parameter has no effect when transactional is true.

Until the end of the current transaction

0.1 (10%)

The number of live table rows fetched by simple index scans using this index

none (function statistics tracking is disabled by default)

More than 200,050 dead rows ((1,000,000 × 0.2) + 50)

No, log_statement_stats cannot be enabled simultaneously with any of the per-module options

The number of queries canceled due to conflicts with recovery on standby servers (primarily relevant on replicas)

on (enabled by default, required by the autovacuum daemon)

The cumulative number of deadlocks detected in this database

No, SQL-language functions that are simple enough to be inlined will not be tracked, regardless of the track_functions setting

on (enabled by default)

No, it can be changed without restarting (does not require restart)

The backend is not waiting on anything and is actively executing its query

temp_files tracks the number of temporary files created by queries, and temp_bytes tracks the total amount of data written to temporary files (in bytes). These are cumulative counters over the database lifetime

Total time spent executing the function in milliseconds, including time spent in other functions that it calls

The number of live table rows fetched by index scans (both simple and bitmap)

client backend, autovacuum launcher, autovacuum worker, background writer, checkpointer, walwriter, archiver, logical replication launcher, and background worker

(blks_hit / (blks_hit + blks_read)) × 100

The timestamp when statistics were last reset for that object or database

off (all disabled by default)

The external file might grow unmanageably large, and pg_stat_statements may choose to discard query texts

The number of rows inserted since the table was last vacuumed

pg_stat_reset_single_table_counters(oid) → void

pg_stat_reset_shared()

1MB (1048576 bytes)

That backends are writing buffers themselves, suggesting a need for more shared_buffers or more aggressive background writer configuration

50

sent_lsn (WAL sent to replica), write_lsn (WAL written to OS on replica), flush_lsn (WAL flushed to disk on replica), and replay_lsn (WAL replayed and visible to queries on replica)

When the update does not modify any columns referenced by the table's indexes (excluding summarizing indexes) and there is sufficient free space on the page containing the old row

1024 bytes

cache

pg_stat_io provides detailed I/O statistics by backend type, I/O object, and I/O context, including timing for reads, writes, extends, and fsyncs

Activity, BufferPin, Client, Extension, IO, IPC, Lock, LWLock, and Timeout

WAL that has been flushed to permanent storage on the replica but not yet replayed (e.g., due to replication conflicts). When replayed, these changes will become visible

PostgreSQL 17. Before version 17, you had to call pg_stat_reset_shared() individually for each statistics type

none, pl (track procedural-language functions only), or all (track SQL and C language functions too)

The number of disk blocks found in the buffer cache (shared_buffers), avoiding the need to read from disk

tup_returned will increase by one million (rows scanned), but tup_fetched will increase by only one (the final result row)

Only during a clean shutdown. Statistics are retained across server restarts when the server shuts down cleanly

Because idx_tup_fetch can be less if dead or not-yet-committed rows are fetched, if heap fetches are avoided via index-only scans, or if bitmap scans are used (which don't affect idx_tup_fetch)

Always exactly one row, containing data about WAL activity

track_io_timing (for relation I/O timing) or track_wal_io_timing (for WAL I/O timing)

All statistics counters are reset to zero

It allows modules like pg_stat_statements to automatically enable query identifier computation

The number of rows modified (inserted, updated, or deleted) since the table was last analyzed

Up to 1% in testing scenarios, though it can cause significant overhead on some platforms with high-cost timing calls

analyze_count (manual analyze count) and autoanalyze_count (autoanalyze count)

The number of buffers written by the background writer process

PostgreSQL 9.6

The number of index scans initiated on this index

checkpoints_timed counts scheduled checkpoints, checkpoints_req counts requested checkpoints. Ideally checkpoints_timed should be much higher and checkpoints_req near zero

The number of failed attempts to archive WAL files

The cumulative number of transactions that have been committed in this database

Prior to PostgreSQL 10, pg_stat_activity only contained information about normal backend processes (client backends) serving end users

No, it can be changed without restarting (does not require restart)

No, it can be changed without restarting (does not require restart)

Estimated number of dead tuples (bigint type)

Superusers and roles with privileges of the built-in role pg_read_all_stats

auto

They update continuously throughout the transaction and show statistics not yet flushed to shared memory

No, it can be changed without restarting (does not require restart)

Because autovacuum uses insert/update/delete counters to determine when to visit a table, and resetting them delays necessary table maintenance

Just before going idle, but not more frequently than once per PGSTAT_MIN_INTERVAL milliseconds (default 1 second)

1 second (1000 milliseconds) unless altered while building the server

NULL (the columns revert to NULL after a short time, not zero)

Yes, they are independent. A backend in 'active' state may or may not be waiting on some event

SECURITY DEFINER functions that don't properly control search_path can lead to privilege escalation attacks, where malicious users exploit them through custom operators or types to execute code with elevated privileges.

Use ALTER TABLE (not ALTER TRIGGER) with commands like: ALTER TABLE table_name DISABLE TRIGGER trigger_name or ALTER TABLE table_name ENABLE TRIGGER trigger_name.

pg_trigger_depth() returns the current nesting level of triggers. If not invoked from inside a trigger, directly or indirectly, it returns zero.

The maximum is 100 arguments, as defined by FUNC_MAX_ARGS, which is a compile-time constant that applies to all PostgreSQL functions including trigger arguments.

No, BEFORE ROW triggers on INSERT cannot change which partition is the final destination for a new row.

No, TRUNCATE triggers cannot be specified with BEFORE or AFTER on views. They are permitted only on tables and foreign tables.

tgenabled is of type char and controls firing based on session replication role. It can have values: 'O' (origin/local), 'D' (disabled), 'R' (replica), or 'A' (always).

Row-level BEFORE triggers are much cheaper than AFTER triggers when updating many rows, because BEFORE triggers fire immediately while AFTER triggers fire at the end of the statement, requiring PostgreSQL to temporarily store row state information.

Use a WHEN clause with pg_trigger_depth() = 0 or pg_trigger_depth() < 1 to ensure the trigger only fires for top-level operations.

Triggers defined for the same event on the same relation fire in alphabetical order by trigger name.

Yes, the syntax is: ALTER TRIGGER name ON table_name RENAME TO new_name.

When a row-level AFTER trigger is fired, all data changes made by the command are already complete, and are visible to the invoked trigger function.

No, the data change causing the trigger to fire is not visible to SQL commands executed in a row-level BEFORE trigger, because it has not yet occurred.

The REFERENCING clause is only allowed for AFTER triggers on plain tables (not foreign tables or constraint triggers).

BEFORE row-level triggers on partitioned tables were enabled in PostgreSQL 13. Prior versions required creating BEFORE triggers on each partition individually.

CASCADE automatically drops objects that depend on the trigger, and in turn all objects that depend on those objects.

The return value is ignored for row-level AFTER triggers, so they may as well return NULL.

In REPEATABLE READ, a trigger sees the database as of the beginning of the transaction (the start of the first non-transaction-control statement), not as of the current statement.

No, TRUNCATE triggers can only be statement-level triggers, never row-level.

RESTRICT refuses to drop the trigger if any objects depend on it. This is the default behavior.

TG_OP is of type 'text' and specifies the operation: INSERT, UPDATE, DELETE, or TRUNCATE.

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command, even if the value doesn't actually change.

Statement-level BEFORE triggers execute before the statement starts to do anything.

If the table that the trigger is on is a partitioned table, then corresponding clone triggers in the partitions are renamed too.

A trigger function must be declared as a function with no arguments and a return type of 'trigger' (for data change triggers) or 'event_trigger' (for database event triggers).

Creating a row-level trigger on a partitioned table will cause an identical 'clone' trigger to be created on each of its existing partitions, and any partitions created or attached later will automatically have an identical trigger.

Statement-level trigger functions should always return NULL.

Triggers configured as ENABLE ALWAYS will fire regardless of the current replication role (session_replication_role setting).

Statement-level triggers execute once per SQL statement regardless of rows affected, while row-level triggers execute for every row. Statement-level triggers with transition tables can show 7x performance improvement over row-by-row processing.

The execution of an AFTER trigger can be deferred to the end of the transaction, rather than the end of the statement, if it was defined as a constraint trigger.

It disables only user-defined triggers, excluding internally generated constraint triggers such as those used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.

Disabling or enabling internally generated constraint triggers requires superuser privileges.

Row-level BEFORE triggers fire immediately before a particular row is operated on.

Constraint triggers must be AFTER ROW triggers on plain tables (not foreign tables).

For typical OLTP transactions with simple triggers, the overhead is minimal. Tests showed 1.173 ms vs 1.178 ms average transaction latency (0.4% difference) for transactions with 3 simple data checking/filling triggers.

INSTEAD OF triggers fire immediately as each row in the view is identified as needing to be operated on.

In DELETE triggers, only OLD is available (the row being deleted). In INSERT triggers, only NEW is available; OLD is null.

For constraint triggers, evaluation of the WHEN condition is not deferred, but occurs immediately after the row update occurs.

Transition tables for triggers were introduced in PostgreSQL 10.

NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT.

tgnewtable is of type name and stores the REFERENCING clause name for NEW TABLE (transition table).

In UPDATE triggers, both OLD (the row before update) and NEW (the row after update) are available. In INSERT triggers, only NEW is available; OLD is null.

Yes, it is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents by BEFORE UPDATE triggers are not considered when determining if the column was targeted.

There is no functional difference. The keywords FUNCTION and PROCEDURE are equivalent in CREATE TRIGGER syntax, but the use of PROCEDURE is historical and deprecated. Modern code should use EXECUTE FUNCTION.

Row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers).

OLD TABLE may only be specified once, and only for a trigger that can fire on UPDATE or DELETE.

Returning NULL from a row-level BEFORE trigger signals the trigger manager to skip the rest of the operation for that row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row).

It disables all triggers on the table, including both user-defined and internally generated constraint triggers.

No, currently WHEN expressions cannot contain subqueries.

The referenced object must in any case be a function, not a procedure, regardless of whether EXECUTE FUNCTION or EXECUTE PROCEDURE is used.

No, a BEFORE trigger's WHEN condition is not allowed to examine the system columns of the NEW row (such as ctid), because those won't have been set yet.

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]. RESTRICT is the default.

Trigger functions execute with the privileges of the user who created them (SECURITY DEFINER behavior), not the user invoking the trigger.

No, row-level BEFORE and AFTER triggers may only be defined on tables and foreign tables, not views.

tgnargs is of type int2 (smallint) and contains the count of argument strings for the trigger function.

Trigger support on foreign tables was introduced in PostgreSQL 9.4.

BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT (not FOR EACH ROW).

FOR EACH STATEMENT is the default if neither is specified.

Statement-level AFTER triggers fire at the very end of the statement.

Yes, triggers are executed as part of the same transaction as the statement that triggered them, so they inherit the transaction's isolation level.

If a view is automatically updatable, the action is handled by automatically rewriting the statement into an action on the view's base table, so the base table's statement-level triggers are the ones that are fired (not triggers on the view itself).

No, INSTEAD OF triggers may only be defined on views, and only at row level.

None of the changes made by a statement are visible to statement-level BEFORE triggers, because the statement has not yet started.

Foreign tables support row-level BEFORE and AFTER triggers, and statement-level triggers. They do not support INSTEAD OF triggers, constraint triggers, TRUNCATE triggers, or the REFERENCING clause.

When a partition is detached from its parent, its clone triggers are removed.

Yes, SQL commands executed in a row-level BEFORE trigger see the effects of data changes for rows previously processed in the same command.

It returns the text of the currently executing query, as submitted by the client (may contain more than one statement).

The return type is boolean.

It returns a string describing the PostgreSQL server's version, including version number, OS information, compiler information, and architecture (32-bit or 64-bit).

No. This parameter can only be set at server start, so changing it requires a PostgreSQL restart.

It returns the authentication method and identity presented during authentication.

It accepts a table name or OID.

It returns true if the given string is valid input for the specified data type, false otherwise.

It returns NULL when the collation of an expression is undefined or when there are mixed collations in the expression.

The return type is inet.

The return type is regtype.

The return type is text.

Yes. Multiple privilege types can be listed separated by commas (added in PostgreSQL 8.4).

The return type is boolean.

The return type is text.

It returns the name of the sequence associated with a column (or NULL if no sequence is associated).

When column is 0, it returns the full index definition (all columns) rather than information about a specific column position.

The track_activity_query_size parameter controls this. It has a minimum of 100 bytes, maximum of 1048576 bytes (1MB), and a default of 1024 bytes.

The return type is integer.

No. current_user has special syntactic status in SQL and must be called without trailing parentheses.

With NULL typemod, it outputs a 'prettier' representation (e.g., 'character' for bpchar). With typemod -1, it preserves the base type name (e.g., 'bpchar' which would be interpreted as character(1) by the parser).

It is a synonym for current_user (PostgreSQL unifies users and roles into a single kind of entity).

It returns a record with columns: pg_control_version, catalog_version_no, system_identifier, pg_control_last_modified.

It accepts an object OID and optionally a catalog name. The two-parameter form is preferred; the one-parameter form is deprecated.

The return type is record with columns: message (text), detail (text), hint (text), sql_error_code (text).

The return type is boolean.

The return type is text.

When include_implicit is TRUE, it includes implicitly searched system schemas such as pg_catalog. When FALSE, it only returns schemas explicitly defined in the current search path.

session_user is the user who initiated the database connection (the 'real user'). current_user is the user identifier applicable for permission checking (the 'effective user'). Normally they are equal, but current_user can be changed with SET ROLE or during execution of SECURITY DEFINER functions.

It accepts a table OID and a column number (integer).

The return type is regclass.

It accepts two text parameters: table name and column name.

It returns the OID of the data type of the value passed to it, displayed as a type name (regtype format).

The recommended replacement is pg_current_xact_id().

The return type is name.

It accepts an optional text parameter specifying the log format.

It returns NULL if the connection is via a Unix-domain socket.

The function returns 'in progress', 'committed', or 'aborted' to report the commit status of a transaction.

The return type is text.

The parameters are type oid and typemod integer.

The return type is text.

It returns the time when the server configuration files were last loaded.

It returns a record with columns including: checkpoint_lsn, redo_lsn, redo_wal_file, timeline_id, prev_timeline_id, full_page_writes, next_xid, next_oid, next_multixact_id, next_multi_offset, oldest_xid, oldest_xid_dbid, oldest_active_xid, oldest_multi_xid, oldest_multi_dbid, oldest_commit_ts_xid, newest_commit_ts_xid, checkpoint_time.

The return type is setof record with columns: word (text), catcode ("char"), barelabel (boolean), catdesc (text), baredesc (text).

It returns the file system path that the specified tablespace is located in.

It returns NULL if the connection is via a Unix-domain socket.

It returns a string representing the version of Unicode used by PostgreSQL.

It accepts an xid8 parameter (64-bit transaction ID).