EDB PostgreSQL Associate cheat sheet

The postmaster listens on port 5432 by default and forks one backend process per client connection; each backend has its own private memory.

Shared memory holds shared_buffers, WAL buffers, and lock tables; work_mem is allocated per sort or hash operation, not once per session.

Default shared_buffers is 128MB and default work_mem is 4MB; shared_buffers tuning commonly starts near 25% of system RAM.

Background processes include the checkpointer, background writer, WAL writer, autovacuum launcher, and logical replication launcher.

initdb creates the cluster in PGDATA with a chosen encoding (default UTF8) and locale; the data directory can only be started by the same major version.

postgresql.auto.conf is written by ALTER SYSTEM and overrides postgresql.conf; both files are read at startup or reload.

WAL is stored in pg_wal as 16MB segments by default, transaction status is in pg_xact, and per-database files live under base/.

Values wider than roughly 2KB are compressed or moved to TOAST tables so heap rows stay within the 8KB page limit.

MVCC gives each row version xmin/xmax metadata; readers use a snapshot and do not block writers, and writers do not block readers.

Integer types: smallint is 2 bytes, integer 4 bytes, bigint 8 bytes; integer division truncates, so cast to numeric for decimal results.

numeric is exact with user-defined precision and scale; real and double precision are binary floating point and can show rounding error.

text and varchar(n) have identical performance; char(n) pads values with spaces, while varchar(n) enforces a maximum length.

timestamp stores date and time without zone information; timestamptz stores UTC and converts for display using the session TimeZone.

Comparisons with NULL return NULL; WHERE keeps only rows where the predicate is true, so use IS NULL, IS NOT NULL, or COALESCE.

json preserves the exact input text; jsonb stores a decomposed binary form that supports GIN indexing and operators such as @> and ?.

Arrays are declared as type[]; use ARRAY[...] or '{...}' literals, unnest() to expand elements, and array_agg() to build arrays.

Casting uses CAST(expr AS type) or the shorthand expr::type, and the :: operator binds more tightly than most other operators.

boolean has three values true, false, and NULL; AND, OR, and NOT follow three-valued logic, so NULL propagates through expressions.

Constraint types are NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and EXCLUDE; PRIMARY KEY implies NOT NULL plus UNIQUE.

CREATE INDEX defaults to B-tree; other access methods are hash, GiST, SP-GiST, GIN, and BRIN, each supporting different operators.

A partial index includes a WHERE predicate; an expression index indexes a computed value and is used only when the query expression matches.

CREATE INDEX CONCURRENTLY avoids blocking writes but takes longer and cannot run inside a transaction block.

A foreign key requires a unique or primary key constraint on the referenced columns and takes row locks on referenced rows during changes.

Views store a query definition; materialized views store results and are refreshed with REFRESH MATERIALIZED VIEW, optionally CONCURRENTLY.

search_path controls resolution of unqualified names; the first matching schema wins, and pg_catalog is searched implicitly first.

Identity columns (GENERATED ... AS IDENTITY) are the SQL-standard alternative to serial, which creates a sequence plus a column default.

Declarative partitioning supports RANGE, LIST, and HASH; a query filtering on the partition key lets the planner prune nonmatching partitions.

INSERT ... ON CONFLICT (cols) DO NOTHING or DO UPDATE SET ... implements upsert; the conflict target must match a unique or exclusion constraint.

UPDATE ... FROM and DELETE ... USING join other tables into the statement; RETURNING returns affected rows for INSERT, UPDATE, and DELETE.

Join types are INNER, LEFT, RIGHT, FULL OUTER, CROSS, and LATERAL; LATERAL lets the right side reference columns from the left side.

Window functions use OVER (PARTITION BY ... ORDER BY ...) and do not collapse rows; row_number, rank, lag, lead, and aggregates all work as window functions.

WITH defines a common table expression; add RECURSIVE for iteration, and non-recursive CTEs may be inlined unless MATERIALIZED is specified.

GROUP BY groups rows and HAVING filters groups; every selected non-aggregate column must appear in the GROUP BY list.

UNION removes duplicates while UNION ALL keeps them; INTERSECT and EXCEPT return common or distinct rows and are left-associative.

LIMIT/OFFSET or FETCH FIRST n ROWS ONLY restricts output; without ORDER BY the rows returned are not deterministic.

Transaction control uses BEGIN, COMMIT, ROLLBACK, SAVEPOINT, and RELEASE SAVEPOINT; isolation levels are READ COMMITTED (default), REPEATABLE READ, and SERIALIZABLE.

pg_dump produces a consistent logical backup of one database; -Fc is the compressed custom format and -Fd is a directory format allowing parallel -j dumps.

pg_dumpall dumps all databases plus cluster-wide objects such as roles and tablespaces; roles are not included in a pg_dump output.

pg_restore restores custom or directory archives and supports -j parallel jobs, --clean, --data-only, --schema-only, and selective -t table restores.

pg_basebackup takes a physical backup of the whole cluster; -X stream includes the WAL needed to make the backup consistent.

Continuous archiving requires wal_level set to replica or higher, archive_mode = on, and a reliable archive_command.

Point-in-time recovery restores a base backup, sets restore_command, and replays WAL to recovery_target_time, _xid, _lsn, or _name; a recovery.signal file starts recovery.

recovery_target_action controls behavior at the target: pause, promote, or shutdown; default recovery otherwise stops at the end of available WAL.

pg_verifybackup checks a pg_basebackup directory against its manifest, and data checksums must be enabled for full verification.

COPY and \copy move table data to and from files; they are faster than row-by-row INSERT for bulk loads and support CSV format.

Roles are cluster-wide objects; CREATE ROLE ... LOGIN creates a login role, CREATE USER is an alias, and roles can be granted membership in other roles.

GRANT and REVOKE set privileges on databases, schemas, tables, columns, sequences, functions, and types.

ALTER DEFAULT PRIVILEGES defines privileges applied automatically to future objects created by a specified role.

pg_hba.conf lines have the form TYPE DATABASE USER ADDRESS METHOD, are evaluated top-down, and the first matching line wins; reload applies changes.

Authentication methods include trust, password, md5, scram-sha-256, peer, ident, cert, and gss; peer maps the operating-system user to a database role.

password_encryption = scram-sha-256 is the modern default; md5 is deprecated, and the server must hold SCRAM verifiers for SCRAM authentication to succeed.

Row-level security is enabled per table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY; policies come from CREATE POLICY, and owners bypass RLS unless FORCE is set.

Superusers bypass all permission checks; SECURITY DEFINER functions run as their owner, so they should set a safe search_path.

SSL is enabled with ssl = on plus ssl_cert_file and ssl_key_file; the hostssl record in pg_hba.conf requires encrypted connections.

EXPLAIN shows the plan with estimated costs; EXPLAIN ANALYZE executes it and adds actual time, rows, and loops, and BUFFERS adds block I/O counts.

Large gaps between estimated and actual row counts indicate stale statistics; run ANALYZE or raise the column statistics target.

pg_stat_activity shows sessions, state, wait events, and query text; pg_locks shows lock waits and the PIDs blocking them.

pg_stat_statements aggregates normalized queries by calls, total_exec_time, mean_exec_time, rows, and shared block hits and reads.

VACUUM reclaims dead tuple space for reuse; VACUUM FULL rewrites the table and holds an ACCESS EXCLUSIVE lock.

Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times reltuples (default 50 + 0.2 * rows).

Cache hit ratio equals blks_hit divided by (blks_hit + blks_read) from pg_stat_database; a low ratio suggests insufficient shared_buffers or large scans.

Key planner cost parameters are random_page_cost (default 4.0), seq_page_cost (1.0), effective_cache_size, and work_mem for sorts and hashes.

Checkpoints are governed by checkpoint_timeout and max_wal_size; more frequent checkpoints shorten crash recovery but increase I/O.

Streaming replication requires wal_level set to replica or logical, a replication role or pg_hba.conf entry, and primary_conninfo configured on the standby.

Hot standby standbys are read-only and accept queries when hot_standby = on; hot_standby_feedback reduces recovery conflicts on the primary.

synchronous_standby_names selects synchronous standbys, while synchronous_commit controls whether commit waits for local flush, remote flush, or remote apply.

synchronous_standby_names accepts FIRST n and ANY n specifications to define quorum-based confirmation among standbys.

Replication slots retain WAL until the consumer confirms receipt; monitor pg_replication_slots and size max_replication_slots and max_wal_senders accordingly.

Logical replication uses CREATE PUBLICATION on the publisher and CREATE SUBSCRIPTION on the subscriber, and requires wal_level = logical.

Promotion uses pg_ctl promote or SELECT pg_promote(); the standby leaves recovery and begins a new timeline.

Monitor lag with pg_stat_replication columns write_lag, flush_lag, and replay_lag, or with pg_wal_lsn_diff between sent and replayed LSNs.

Tools such as Patroni and repmgr automate failover, leader election, and standby re-registration for high availability.