EDB PostgreSQL Professional common mistakes
EDB PostgreSQL Professional · Understand the traps and how to avoid them.
Treating autovacuum as a background concern and leaving default thresholds in place on large, high-churn tables, then blaming slow queries on indexing when the real problem is dead tuples and stale statistics.
Candidates study VACUUM as a maintenance command but never internalize the per-table threshold formula (threshold + scale_factor * reltuples), so they do not realize that a 200-million-row table needs hundreds of thousands of dead tuples before autovacuum fires. They also overlook that autovacuum does not update the visibility map aggressively enough for index-only scans.
What to do instead
Practice computing the trigger point for a given table size, and be ready to state concrete remedies: per-table storage parameters (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, autovacuum_analyze_scale_factor), tuning autovacuum_max_workers and autovacuum_naptime, and monitoring pg_stat_user_tables (n_dead_tup, last_autovacuum, last_analyze) plus pg_stat_progress_vacuum. Explain why index-only scans depend on the visibility map being current.
Reading EXPLAIN output without ANALYZE and comparing estimated versus actual rows only superficially, then proposing an index without diagnosing why the planner misestimated.
Candidates memorize plan node names but do not routinely check the rows= estimate against the actual rows, or the loops multiplication in nested loops. They also forget that EXPLAIN ANALYZE executes the statement, which matters for writes, and that BUFFERS, WAL, and SETTINGS options reveal I/O and configuration effects.
What to do instead
Build the habit of EXPLAIN (ANALYZE, BUFFERS) and of scanning for the node with the largest estimate-to-actual divergence. Know the usual causes of misestimation: correlated columns (extended statistics via CREATE STATISTICS), non-uniform data, functions with poor or default selectivity, and stale stats. Be able to say what a large row misestimate does to join order and memory allocation.
Assuming a streaming standby is a complete disaster recovery solution without verifying replication slots, WAL retention, and whether the standby can actually be promoted with all required WAL present.
Candidates conflate 'replication is streaming' with 'recovery is guaranteed'. They forget that a standby that falls behind can force the primary to retain or recycle WAL depending on slot configuration, and that without a replication slot the primary may remove WAL the standby still needs. They also overlook the failover steps that must be rehearsed.
What to do instead
Be able to explain the trade-off: physical replication slots prevent WAL removal but risk unbounded pg_wal growth if the standby stalls (max_slot_wal_keep_size). Know how to check pg_stat_replication, pg_replication_slots, and the standby's replay lag, and describe a promotion procedure (pg_ctl promote or the EDB failover manager tooling) plus the need to re-point clients and rebuild the old primary.
Designing a backup strategy around pg_dump alone for a large database and not accounting for restore time, consistency across a long dump, or point-in-time recovery requirements.
pg_dump is the tool candidates learn first, so it becomes the default answer. They underestimate that a logical dump of a multi-terabyte database can take hours, that it does not support PITR, and that concurrent DDL during the dump can produce an inconsistent snapshot unless the dump runs in a single transaction.
What to do instead
Match tool to requirement: logical dumps for portability and single-object restore; pg_basebackup plus continuous WAL archiving for PITR; and know that restore time, not backup time, is the metric that matters. Be ready to describe archive_command/archive_library, restore_command, recovery_target_time, and how to verify a backup by actually restoring it.
Planning a major-version migration as a lift-and-shift and only discovering incompatibilities after the cutover, such as removed configuration parameters, changed default behaviors, or extension version mismatches.
Candidates focus on the data copy step and skip the compatibility audit. They miss that pg_upgrade requires matching major-version binaries and compatible extensions, that logical replication requires primary keys or replica identity for updates and deletes, and that some parameters were renamed or removed between versions.
What to do instead
Describe a phased plan: inventory extensions and custom code, run the application test suite against a restored copy on the target version, use pg_upgrade with --check (or logical replication for near-zero downtime), and validate with pg_dump/pg_restore comparison or checksums. Know the difference between pg_upgrade in-place and dump/restore, and when logical replication is the better fit.
Granting privileges at the database or schema level and assuming that covers tables created later, then being surprised when a new table is inaccessible or, conversely, over-granting with superuser to 'fix' it.
Candidates do not distinguish between GRANT on existing objects and default privileges. ALTER DEFAULT PRIVILEGES applies only to objects created by the role that set them, and schema USAGE is separate from object privileges. The temptation to use superuser masks the misunderstanding.
What to do instead
Be able to explain the privilege hierarchy (database CONNECT, schema USAGE/CREATE, table SELECT/INSERT/etc., column-level grants), the role of ALTER DEFAULT PRIVILEGES FOR ROLE ... IN SCHEMA ..., and the difference between a role and a login. Practice reading \dp and pg_class.relacl output, and prefer group roles over per-user grants.
Tuning shared_buffers, work_mem, or maintenance_work_mem by picking numbers from a blog post rather than reasoning from available RAM and workload concurrency.
Candidates memorize '25% of RAM for shared_buffers' without understanding that work_mem is per operation, not per connection, so a high value multiplied by concurrent sorts and hash joins can exhaust memory. They also miss that maintenance_work_mem affects VACUUM, CREATE INDEX, and pg_restore performance.
What to do instead
Be able to reason out loud: shared_buffers as a fraction of RAM, effective_cache_size as an estimate of OS plus database cache for planner cost, work_mem sized against expected concurrent sort/hash nodes, and maintenance_work_mem against maintenance windows. Know the symptoms of undersizing (spills to disk in EXPLAIN, temp file growth) versus oversizing (OOM, swap).
Ignoring connection pooling and treating max_connections as a knob to raise whenever applications report connection errors.
Candidates see 'too many clients' errors and increase max_connections, not realizing each backend consumes memory and that high connection counts cause contention on locks and snapshots. They also do not distinguish session pooling from transaction pooling, which affects prepared statements, session-level settings, and advisory locks.
What to do instead
Explain why an external pooler (PgBouncer in transaction mode) or a built-in pooling feature is preferred, what breaks under transaction pooling (session state, LISTEN/NOTIFY, prepared statements unless configured), and how to monitor pg_stat_activity for idle-in-transaction sessions. Know how to set per-role connection limits and idle_in_transaction_session_timeout.
Reading pg_stat_activity and pg_locks without distinguishing lock waits from I/O waits, and killing sessions before identifying the blocking root cause.
Candidates find one slow session, terminate it, and move on. They miss that pg_blocking_pids() identifies the actual blocker, that a long idle-in-transaction session can hold locks indefinitely, and that terminating the wrong backend can roll back a large transaction and cause a longer outage.
What to do instead
Walk the diagnosis: query pg_stat_activity for state and wait_event_type, use pg_blocking_pids() to find the blocker, inspect pg_locks for granted versus waiting modes, and check for idle-in-transaction. Know the difference between pg_cancel_backend and pg_terminate_backend and their consequences, and set lock_timeout and statement_timeout defensively.
Treating the EDB platform tooling as interchangeable with community PostgreSQL and missing the operational specifics of managed or AI-platform deployments, such as extension availability, backup retention policies, or role-based access differences.
Candidates prepare on vanilla PostgreSQL and assume every feature behaves identically in the EDB distribution or managed service. They do not check which extensions are preinstalled or supported, how the platform exposes monitoring and backup, or where superuser is restricted.
What to do instead
For each operational task, state the community mechanism and then the platform-specific variation: how backups and PITR are configured, how monitoring metrics are exposed, which extensions are available and how they are enabled, and how privileged roles differ. When a platform detail is uncertain, describe how you would verify it in the console or documentation rather than guessing.