25 PostgreSQL interview questions

Listen for: A strong answer starts by reproducing safely, comparing plan versus actual rows, checking buffer reads, sort/hash spills, changed parameters, stale statistics, and data distribution changes. They should use pg_stat_statements to confirm frequency and impact, not optimize a one-off query blindly. They should propose a small, reversible change and re-test with the same inputs.

Red flag: They immediately add an index without reading the execution plan or validating the workload.

Listen for: Listen for awareness of locks, table rewrites in older versions, application compatibility, and migration sequencing. They should suggest adding the column in a backward-compatible way, backfilling in batches when needed, validating constraints separately, monitoring locks, and coordinating deploy order. Bonus if they distinguish modern PostgreSQL behavior for constant defaults from more expensive changes.

Red flag: They assume every ALTER TABLE is harmless because it worked in development.

Listen for: Good answers discuss query patterns, validation, indexing needs, schema evolution, and reporting. JSONB is useful for flexible, sparse, or rarely filtered attributes, while relational columns fit required fields, joins, constraints, and frequent predicates. They should mention GIN indexes where appropriate and the cost of letting JSONB become an ungoverned data model.

Red flag: They say JSONB is always better because it avoids migrations.

Listen for: They should ask for deadlock logs, involved statements, transaction boundaries, lock wait patterns, and application retry behavior. Strong candidates explain consistent lock ordering, shorter transactions, avoiding user interaction inside transactions, indexing foreign keys, and using appropriate isolation levels. They should know deadlocks are detected and one transaction is canceled, so retries must be safe.

Red flag: They recommend disabling transactions or raising timeouts as the primary fix.

Listen for: Look for keyset pagination using a stable sort key, such as created_at plus id, with a matching index and deterministic ordering. They should explain why large OFFSET values scan and discard rows. Good answers cover UX trade-offs, back/next navigation, handling deleted rows, and making filters align with composite indexes.

Red flag: They only suggest increasing work_mem or adding a generic index on every column.

Listen for: They should compare plans and timings with representative parameters, check shared/local/temp buffers, and review pg_stat_statements over time. Good answers consider write overhead, index size, duplicate or unused indexes, vacuum impact, and whether the planner actually chooses it. They should use realistic data and avoid relying solely on local development results.

Red flag: They judge success only by the CREATE INDEX command completing successfully.

Listen for: A strong answer ties each partial index to specific WHERE clauses and ordering requirements, verifies selectivity, and checks whether predicates match application SQL exactly. They should consider write amplification, HOT updates, bloat, autovacuum pressure, and index maintenance cost. They should also discuss measuring with production-like data and removing redundant indexes.

Red flag: They create partial indexes for every status value without looking at query frequency or update patterns.

Listen for: Listen for triage by risk: age of relfrozenxid, blocked vacuum, long-running transactions, replication slots, and tables with high dead tuples. They should adjust autovacuum settings per table when needed, clear blockers carefully, schedule aggressive vacuum or vacuum freeze, and avoid jumping straight to VACUUM FULL on busy tables without lock planning.

Red flag: They treat VACUUM FULL as the default fix for all bloat during business hours.

Listen for: Good candidates inspect EXPLAIN (ANALYZE, BUFFERS), temp file logs, row estimates, join order, indexes, and statistics. They may use extended statistics for correlated columns, targeted work_mem per session or job, query rewrites, pre-aggregation, or materialized views. They should explain why global work_mem increases can be dangerous with many concurrent queries.

Red flag: They set work_mem very high at the instance level and stop there.

Listen for: Strong answers compare physical replication, logical replication, dump/restore for small databases, and tools such as pglogical or native publications/subscriptions. They should discuss sequence synchronization, DDL handling, replication lag, cutover writes, extensions, collations, permissions, large objects, validation, rollback, and rehearsal. They should treat migration as an operational project, not a single command.

Red flag: They assume logical replication copies every database object and handles cutover automatically.

Listen for: They should understand connection overhead and the difference between server connections and client connections. Strong answers mention transaction pooling limitations around session state, prepared statements, LISTEN/NOTIFY, advisory locks, temp tables, and SET commands. They should align pooling mode with ORM behavior, workload type, and operational needs such as failover and maintenance.

Red flag: They say transaction pooling is always safe because it supports more clients.

Listen for: They should verify indexes on the child foreign key columns, ON DELETE behavior, triggers, cascading depth, row counts, locks, and concurrent activity. Good answers consider batching deletes, soft deletes, partitioning, archival strategy, and measuring plans for referential checks. They should avoid assuming the parent table index is the only relevant index.

Red flag: They drop the foreign key as the first solution without assessing data integrity requirements.

Listen for: Look for a practical baseline: pg_stat_statements, logs with slow statements and lock waits, auto_explain for sampled problem queries, wait events, checkpoint and WAL metrics, replication lag, autovacuum activity, connection counts, and disk usage. They should connect metrics to runbooks and alerts, not collect dashboards no one acts on.

Red flag: They rely only on CPU and memory graphs from the host or cloud console.

Listen for: Strong senior answers broaden beyond query text: wait events, checkpoints, WAL pressure, fsync latency, autovacuum, lock queues, connection storms, replication feedback, noisy neighbors, and storage behavior. They should form hypotheses, correlate timestamps across logs and metrics, run low-risk experiments, and communicate uncertainty and mitigations to stakeholders during the investigation.

Red flag: They keep tuning the most common query even when evidence points outside query planning.

Listen for: They should begin with access patterns, retention, ingest rate, maintenance windows, uniqueness requirements, and query predicates. Strong answers discuss range partitioning by time, optional subpartitioning, local indexes, partition pruning, detach/drop for retention, avoiding too many partitions, default partitions, and effects on foreign keys, vacuum, backups, and query planning.

Red flag: They propose partitioning because the table is large, without tying it to queries or lifecycle management.

Listen for: Listen for clarity on replication lag and consistency expectations. Good answers include routing recent writes to primary, session stickiness, lag-aware replica selection, explicit consistency tokens, synchronous replication for narrow cases, and user experience trade-offs. They should explain that read replicas are not a transparent scale-out layer for every workload.

Red flag: They promise read replicas will always be current enough without measuring lag or defining guarantees.

Listen for: Strong answers define recovery objectives with each service, then design tested physical backups, WAL archiving, encryption, retention, and restore automation using tools such as pgBackRest, Barman, or WAL-G. They should include point-in-time recovery drills, checksum verification, role and extension handling, replica rebuilds, and documenting who can declare a restore.

Red flag: They say backups are complete because a nightly dump job exists.

Listen for: They should discuss primary keys or replica identity, conflict handling, sequence values, DDL coordination, initial copy load, large transactions, lag monitoring, cutover freeze, dual writes versus replication, validation queries, and rollback. Strong answers also mention version compatibility, extensions, triggers, and operational playbooks for moving one tenant at a time.

Red flag: They treat logical replication as a queue that guarantees application-level correctness by itself.

Listen for: A senior candidate asks what problem is being solved and what evidence supports the change. They should consider version, workload, memory concurrency, checkpoint behavior, autovacuum settings, planner parameters, replication, and managed-service constraints. Strong answers insist on controlled rollout, before/after metrics, rollback steps, and avoiding cargo-cult settings from old blog posts.

Red flag: They approve parameter changes based mainly on rules of thumb like a fixed percentage of RAM.

Listen for: Look for phased migrations, compatibility windows, lock-timeout and statement-timeout defaults, online index builds, constraint validation, backfill workers, schema review, and production rehearsals. Senior answers include ownership: migration guidelines, templates, observability, escalation paths, and pairing with application teams so schema changes are designed with deploy and rollback in mind.

Red flag: They frame migration safety as solely the DBA’s job after developers have already written the change.

Listen for: Strong answers compare database-per-tenant, schema-per-tenant, and shared tables with tenant_id, including isolation, cost of migrations, connection counts, backup/restore granularity, noisy-neighbor risk, and analytics. They should propose guardrails such as row-level security where appropriate, composite indexes including tenant_id, tenant move playbooks, and clear criteria for promoting tenants to dedicated databases.

Red flag: They choose one tenancy model without discussing operations, security boundaries, or future tenant moves.

Listen for: Look for a concrete topology: primary, synchronous or asynchronous standbys based on latency trade-offs, Patroni or a managed equivalent, quorum considerations, fencing, health checks, and tested failover. They should cover connection strings, PgBouncer behavior, DNS or proxy routing, backups separate from replicas, planned switchovers, and split-brain prevention.

Red flag: They equate having one read replica with a complete high-availability design.

Listen for: Good answers discuss append-heavy schema, range partitioning by time, batching writes, COPY when appropriate, indexes aligned to dashboard filters, retention by detach/drop, compression options if using extensions such as TimescaleDB, and rollups or materialized views. They should address autovacuum, WAL volume, late-arriving data, and separating ingestion from expensive analytical reads.

Red flag: They put all data in one unpartitioned table and plan to archive it later with ad hoc deletes.

Listen for: Strong answers separate transactional source of truth from search requirements. They should discuss tsvector, GIN indexes, trigram search, JSONB facets, ranking limits, update costs, and query complexity. They should add OpenSearch, Elasticsearch, or another engine when relevance tuning, scale, language analysis, or independent search availability outweigh consistency and operational simplicity.

Red flag: They choose an external search engine immediately without defining search behavior or synchronization semantics.

Listen for: Listen for option analysis: pg_upgrade with planned downtime, logical replication for lower downtime, managed-service blue/green features where available, and full rehearsals. They should cover extension compatibility, collation changes, query plan regressions, statistics refresh, parameter changes, replication slots, backups, PITR validation, application driver testing, and a rollback or fallback plan.

Red flag: They treat a major upgrade like a minor patch and skip workload testing.