EDB PostgreSQL Professional cheat sheet

MVCC basics: each row version carries xmin (creating transaction) and xmax (deleting transaction); a tuple is visible when xmin is committed and xmax is not yet committed, aborted, or absent.

VACUUM removes dead tuples but does not return space to the OS; VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock and shrinks the file. Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples.

Transaction ID wraparound: the counter is 32-bit; VACUUM FREEZE sets old xids to FrozenTransactionId. Monitor age(datfrozenxid) against autovacuum_freeze_max_age (default 200 million) and act before 2^31.

pg_ctl start/stop/reload/status; pg_ctl reload sends SIGHUP to re-read postgresql.conf and pg_hba.conf. pg_ctl promote is used for a standby. systemd units are typically postgresql-.

postgresql.conf key parameters: shared_buffers, work_mem, maintenance_work_mem, wal_buffers, max_connections, listen_addresses, port, log_destination, logging_collector.

pg_hba.conf is read top-down, first matching line wins; rules have type (local/host/hostssl/hostnossl), database, user, address, and auth method (trust, peer, md5, scram-sha-256, cert, gss).

psql meta-commands: \l databases, \c connect, \d describe, \dn schemas, \df functions, \du roles, \x expanded output, \timing, \e edit, \copy for client-side copy, \i include file.

CREATE ROLE ... LOGIN PASSWORD '...' creates a login role; GRANT role TO user makes membership; ALTER ROLE ... SET parameter sets per-role defaults; REVOKE ALL ON SCHEMA public FROM PUBLIC hardens the schema.

pg_dump (logical) supports -Fc custom format for pg_restore, -Fd directory for parallel jobs, -Fp plain SQL; pg_dumpall includes roles and tablespaces. Logical dumps do not capture WAL, so PITR requires physical base backups.

EXPLAIN (ANALYZE, BUFFERS) shows actual rows, loops, and buffer hits/reads; compare estimated vs actual rows to detect stale stats or bad selectivity. Use EXPLAIN (ANALYZE, FORMAT JSON) for tooling.

Planner cost model: seq_page_cost (1.0 default), random_page_cost (4.0 on HDD, ~1.1 on SSD), cpu_tuple_cost (0.01), cpu_index_tuple_cost (0.005), cpu_operator_cost (0.0025). Lower random_page_cost favors index scans.

Join strategies: nested loop (small outer, indexed inner), hash join (large unsorted inputs, needs work_mem), merge join (pre-sorted inputs, no extra memory). The planner picks the cheapest total cost.

work_mem applies per sort/hash node, not per session; a query with many hash nodes can consume work_mem times nodes times parallel workers. Monitor temp file usage via pg_stat_database.temp_bytes.

Index types: B-tree for equality/range, GIN for arrays/jsonb/full-text, GiST for geometric and range types, BRIN for large naturally ordered tables, hash for equality only. Partial and expression indexes shrink size and speed writes.

pg_stat_statements aggregates query text, calls, total/mean time, rows, shared_blks_hit/read; requires shared_preload_libraries = 'pg_stat_statements' and CREATE EXTENSION. Reset with pg_stat_statements_reset().

Statistics: ANALYZE updates pg_statistic; default_statistics_target (100) controls sample size per column; ALTER TABLE ... ALTER COLUMN ... SET STATISTICS raises it for skewed columns; extended statistics (CREATE STATISTICS) capture multi-column correlation.

Lock contention: pg_locks joined to pg_stat_activity reveals blockers; pg_blocking_pids(pid) returns blockers. lock_timeout and deadlock_timeout bound waits; deadlocks are auto-detected and one transaction is aborted.

Parallel query: max_parallel_workers_per_gather (default 2), max_parallel_workers, min_parallel_table_scan_size. Parallel plans are not used for writes; they require the table to exceed the size threshold.

Streaming replication: primary sets wal_level = replica (or logical), max_wal_senders > 0, and a replication slot or archive; standby uses primary_conninfo and restore_command/standby.signal (PG12+) to enter recovery.

Synchronous replication: synchronous_standby_names = 'FIRST 1 (s1, s2)' or 'ANY 1 (...)'; synchronous_commit = on/remote_write/remote_apply/local/off controls durability vs latency.

Replication slots (pg_create_physical_replication_slot) prevent WAL removal until the standby consumes it; monitor pg_replication_slots and pg_stat_replication (sent_lsn, write_lsn, flush_lsn, replay_lsn, lag).

pg_basebackup -D dir -Ft -z -Xs -P -R creates a physical base backup and writes standby config (-R) and streams WAL (-X stream). Use --checkpoint=fast for immediate start.

PITR: set archive_mode = on, archive_command or archive_library; restore with restore_command and recovery_target_time/name/xid/immediate in postgresql.conf or recovery.conf (pre-PG12).

pg_rewind resynchronizes a former primary after failover using the new primary's timeline; requires wal_log_hints = on or data checksums. It is faster than a full base backup for large clusters.

pgBackRest: repo1-path, repo1-retention-full, stanza-create, backup --type=full/incr/diff, restore with --type=time and --target; supports parallel and delta restore. barman is an alternative backup manager.

Failover tools: Patroni (etcd/Consul/ZooKeeper DCS), repmgr, and EDB EFM. A witness/quorum node prevents split-brain; fencing (STONITH) isolates the old primary.

Logical replication: publication on publisher, subscription on subscriber; requires wal_level = logical; does not replicate DDL, sequences, or large objects; used for version upgrades and selective replication.

pg_upgrade performs in-place major-version upgrade with --old-bindir/--new-bindir, --check for dry run, --link for hard-link mode (fast, but old cluster unusable). Run analyze_new_cluster.sh afterward.

pg_dump/pg_restore across versions: dump from old, restore into new. Use --no-owner/--no-privileges for clean restores; pg_restore -j N parallelizes. pg_dumpall --globals-only migrates roles and tablespaces.

Logical replication migration: create schema on target, then CREATE SUBSCRIPTION; allows near-zero downtime cutover but requires matching table structures and does not carry sequences/DDL.

EDB Postgres Advanced Server migration: EDB Migration Toolkit (MTK) and ora2pg convert Oracle DDL, PL/SQL, and data. EDB Postgres Enterprise Manager (PEM) tracks migration progress.

Oracle compatibility in EPAS: EDB Redwood/PL/SQL compatibility, DBMS_OUTPUT, DUAL, ROWNUM, NVL, and package support via edb_redwood_* parameters; not available in community PostgreSQL.

Character sets and collation: set initdb --encoding=UTF8 --locale; migrating from SQL_ASCII requires conversion. Check lc_collate and lc_ctype compatibility before restore.

Extension and version compatibility: verify pg_available_extensions on target; PostGIS, pg_stat_statements, and EDB extensions must be installed at matching versions. Check contrib modules used by application.

Cutover checklist: freeze writes, capture sequences (setval), verify row counts and checksums, re-point connection strings, run smoke tests, keep old cluster read-only for rollback window.

Authentication: scram-sha-256 is the default in PG14+; md5 is legacy; peer uses OS user; cert requires client certificates; gss/sspi for Kerberos/AD. Set password_encryption = scram-sha-256 before creating roles.

TLS: ssl = on, ssl_cert_file, ssl_key_file, ssl_ca_file; ssl_min_protocol_version (TLSv1.2+). Client uses sslmode=verify-full to validate hostname and CA.

Privilege model: roles are cluster-wide, privileges are per-object (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, USAGE, CREATE, CONNECT, EXECUTE). GRANT/REVOKE; default PUBLIC has CONNECT on databases and USAGE on public schema (revoke for hardening).

Row-level security: ALTER TABLE ... ENABLE ROW LEVEL SECURITY; CREATE POLICY ... USING (...) WITH CHECK (...); table owner bypasses RLS unless FORCE ROW LEVEL SECURITY is set.

Auditing: pgaudit extension logs statement, role, and object access; set pgaudit.log = 'read, write, ddl, role'. log_connections, log_disconnections, log_statement = 'ddl' or 'all' provide baseline logging.

EDB Postgres AI Platform security: EDB Postgres Advanced Server supports SELinux, LDAP/AD, Kerberos, and EDB Postgres Enterprise Manager for centralized user management; EDB Audit Manager provides compliance reporting.

Data protection: pgcrypto for column encryption (pgp_sym_encrypt), TDE in EPAS for at-rest encryption, and column-level GRANT for sensitive columns. Avoid storing secrets in plaintext.

Principle of least privilege: create application roles with only needed privileges, use SET ROLE for elevation, and revoke CREATE on public schema from PUBLIC to prevent object hijacking.

EDB Postgres AI Platform combines EDB Postgres Advanced Server, EDB Postgres Extended Server, and EDB Postgres Distributed (PGD) with AI/vector capabilities via pgvector and EDB Postgres AI Database.

EDB Postgres Distributed (PGD): multi-master replication with conflict resolution (last-write-wins, custom), group commit, and commit scopes for consistency; uses bdr.* extension and a proxy (PgBouncer/Harp) for connection routing.

EDB Postgres Enterprise Manager (PEM): monitoring and management console; PEM agent runs on each monitored server; PEM server stores metrics in a dedicated database; alerts and dashboards for performance and capacity.

EDB Backup and Recovery Tool (BART) and EDB Postgres Backup and Recovery (Barman-compatible) manage physical backups; EDB EFM (Enterprise Failover Manager) provides HA with quorum and fencing.

EDB Postgres AI Database supports vector search: CREATE EXTENSION vector; vector columns with HNSW/IVFFlat indexes; distance operators <-> (L2), <#> (inner product), <=> (cosine). EDB adds AI/ML integration and model serving hooks.

Deployment options: EDB Postgres AI Cloud (DBaaS), Kubernetes operator (EDB Postgres for Kubernetes / CloudNativePG), and on-prem. The operator manages failover, backups, and upgrades via CRDs.

EDB Migration Toolkit (MTK) and EDBPlus (edb-psql compatible client) are part of the toolchain; EPAS includes Oracle-compatible packages and edb_redwood_ compatibility settings.

Licensing and support: EDB provides subscription support, security patches, and certified builds; EDB Postgres Extended Server adds features like incremental backup and advanced monitoring beyond community PostgreSQL.