EDB PostgreSQL Associate common mistakes
EDB PostgreSQL Associate · Understand the traps and how to avoid them.
After running initdb, the candidate changes only postgresql.conf (for example setting a password or creating a role) and then reports that remote clients still get 'no pg_hba.conf entry' or 'connection refused', treating the two files as interchangeable.
The candidate conflates two independent layers: listen_addresses in postgresql.conf controls which interfaces the postmaster binds to, while pg_hba.conf controls which hosts/users/databases may authenticate and with which method. A connection can be refused before authentication ever happens.
What to do instead
Diagnose in order: (1) confirm the server is listening with pg_settings for listen_addresses/port and check the log for 'listening on'; (2) read pg_hba.conf entries as host/database/user/address/method and match the client's source IP and database name exactly; (3) reload (pg_reload_conf) after pg_hba.conf edits, since only listen_addresses/port changes need a restart.
The candidate writes WHERE col = NULL or WHERE col <> NULL, or uses COUNT(col) and expects it to include rows where col is NULL, and then argues the row 'should' be there.
NULL represents unknown, so any comparison with NULL yields NULL (not true, not false), and most aggregates skip NULL inputs. The candidate reasons in two-valued logic, as in languages where an unset value is still comparable.
What to do instead
Use IS NULL / IS NOT NULL for null tests, and IS DISTINCT FROM for null-safe inequality. To count rows regardless of nulls use COUNT(*); to count non-null values use COUNT(col) deliberately; to substitute a value use COALESCE(col, default). Remember NOT IN with a NULL in the list never returns rows - use NOT EXISTS instead.
The candidate creates a foreign key, sees the constraint working, and never creates an index on the referencing column, then wonders why deleting a parent row or updating the child is slow.
PostgreSQL indexes the referenced (parent) side automatically because it must enforce uniqueness, but it does not index the referencing (child) column. The candidate assumes symmetry with the primary key side.
What to do instead
Create an index on the referencing column(s) explicitly right after the FK, e.g. CREATE INDEX ON child(parent_id). Verify with a query against pg_indexes or by running EXPLAIN on the parent DELETE and looking for a sequential scan on the child.
The candidate writes SELECT customer_id, product, sum(amount) FROM sales GROUP BY customer_id and is confused that PostgreSQL errors instead of guessing which product to show.
The candidate is used to engines or BI tools that silently pick an arbitrary value for non-aggregated columns. PostgreSQL enforces the SQL rule that every non-aggregated select-list column must appear in GROUP BY (the only exception being when grouping by the table's primary key).
What to do instead
Add the column to GROUP BY if you want one row per combination, or wrap it in an aggregate such as min(product) if you only need a representative value, or move it into a window function (sum(amount) OVER (PARTITION BY customer_id)) if you need per-row detail alongside the total. Read the error text literally - it names the offending column.
The candidate plans point-in-time recovery using only nightly pg_dump files plus the current data directory, and asserts they can restore to an arbitrary timestamp in the middle of the day.
pg_dump produces a single logical snapshot at the moment it runs; it contains no intermediate states and no WAL. Rolling forward requires continuous WAL archiving configured in advance (wal_level, archive_mode, archive_command or archive_library), which cannot be turned on retroactively.
What to do instead
For PITR, establish a physical base backup (pg_basebackup) plus a WAL archive, and restore by placing the backup in the data directory with restore_command and recovery_target_time set in postgresql.conf (or a recovery signal file for older versions). Treat pg_dump as a schema/data export and per-table recovery tool, not a roll-forward mechanism.
The candidate runs GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting, the report still fails with 'permission denied for schema app', and the candidate adds more table grants.
Schema access and object access are separate privileges. Without USAGE on the schema, the role cannot resolve names inside it at all, so table-level grants never come into play. The candidate reads 'permission denied' as a table problem.
What to do instead
Grant both layers: GRANT USAGE ON SCHEMA app TO reporting; then GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting; and for tables created later, also run ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO reporting. Test by connecting as the role with SET ROLE rather than assuming.
The candidate raises work_mem to a large value to speed up sorts, expects overall server memory use to stay bounded, and is surprised when the host runs out of memory under many concurrent sessions.
work_mem is a per-operation limit, not a shared pool. A single complex query can allocate it several times (one per sort or hash node), and every concurrent session does the same, so effective usage multiplies by concurrency and node count.
What to do instead
Size work_mem as (available memory for query work) / (expected concurrent sessions x typical number of sort/hash nodes), set it per session or per role rather than globally, and monitor temp file usage via pg_stat_database.temp_bytes and log_temp_files to find queries that actually need more.
The candidate creates an index on a filtered column, sees a sequential scan in EXPLAIN, and concludes the index is corrupt or that PostgreSQL ignores indexes.
The planner chooses the cheapest plan by cost, and on a small table or a low-selectivity predicate a sequential scan genuinely is cheaper. Costs also depend on statistics, so a table that has never been analyzed gives the planner no basis to prefer the index.
What to do instead
Run ANALYZE (or wait for autovacuum) so pg_statistic reflects the data, then compare EXPLAIN (ANALYZE, BUFFERS) plans rather than the index's existence. Read estimated rows versus actual rows to spot bad estimates; use enable_seqscan=off only as a diagnostic to compare costs, never as a permanent fix.
The candidate sets up logical replication (CREATE PUBLICATION / CREATE SUBSCRIPTION), then expects new columns, new tables, sequence values, and TRUNCATE-like DDL to appear on the subscriber automatically.
Logical replication decodes row changes for published tables only; it does not ship DDL or sequence state. The candidate reasons from physical streaming replication, where a standby is a byte-identical block-level copy of everything.
What to do instead
Remember the split: physical replication copies everything including DDL and is used for HA/read replicas; logical replication sends row-level changes for an explicit table list. Apply DDL manually on the subscriber, add new tables to the publication, and handle sequences separately (logical replication in current releases does not sync them). Check pg_stat_subscription and the subscriber log for apply errors.
The candidate assumes that because synchronous_standby_names is configured, a commit will only wait when the standby happens to be reachable, and is puzzled when the primary stops accepting writes after the standby goes down.
Synchronous replication blocks commit until the named standbys acknowledge. With no timeout configured, the primary waits indefinitely, converting a standby outage into a primary write outage. The candidate believes the setting is advisory rather than a hard gate.
What to do instead
Treat sync replication as a trade-off to be configured deliberately: use synchronous_commit=on (the default) with a small synchronous_standby_names list, set a bounded wait (for example the FIRST n form and an appropriate wal_sender_timeout/monitoring alert), and understand that synchronous_commit=off only relaxes the local flush ordering - it does not by itself make replication asynchronous. Verify state in pg_stat_replication (sync_state, write/flush/replay LSN) before declaring the standby synchronous.