PostgreSQL Interview Questions and Answers

PostgreSQL interviews test whether you understand how the database behaves under concurrency and scale—not only SELECT syntax.

Beyond generic SQL, senior loops lean on MVCC and vacuum, index type selection, and reading EXPLAIN plans—the topics that separate application CRUD from database engineering.

Many postgresql developer interview questions still touch vacuum and indexes because app code causes bloat and bad plans.

System-design rounds often ask how Postgres fits multi-tenant SaaS or event-sourced architectures—read replicas, connection pooling, and logical replication slots.

Run a local PostgreSQL 16+ instance and practice \d+, \di, EXPLAIN ANALYZE.

Interviewers want trade-off awareness, not fanboy answers.

MySQL/InnoDB also uses MVCC—Postgres interviews go deeper on vacuum, index types, and planner behavior.

PostgreSQL uses a multi-process model (not multi-threaded like some databases):

Data files live under PGDATA (often /var/lib/postgresql/<version>/main on Ubuntu). Each database is a collection of schemas and tables stored in heap files.

Connection cost motivates PgBouncer pooling in production.

A strong answer is:

One backend process per connection—so pooling matters at scale—and WAL makes commits durable and feeds replication.

search_path controls unqualified name resolution—migrations should set explicit schemas to avoid public surprises.

CREATE SCHEMA billing;
CREATE TABLE billing.invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL
);

A strong answer is:

I use schemas to separate domains in one database and qualify names in migrations so search_path never hides the wrong table.

Using timestamp without time zone for global apps is a common interview trap.

A strong answer is:

timestamptz for events, numeric for money, bigint or uuid for keys—I avoid float for currency and document why jsonb is not a default for every column.

CREATE TABLE order_items (
  order_id bigint REFERENCES orders(id) ON DELETE CASCADE,
  sku text NOT NULL,
  qty int CHECK (qty > 0),
  PRIMARY KEY (order_id, sku)
);

ON DELETE actions: CASCADE, SET NULL, RESTRICT, NO ACTION—know ORM defaults.

A strong answer is:

I enforce business rules in CHECK and FK constraints close to data—migrations add indexes on FK columns to avoid slow cascades and joins.

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email citext UNIQUE NOT NULL
);

Sequences can have gaps (rollback, crash)—interviewers check you do not assume gapless IDs.

A strong answer is:

IDENTITY for monotonic numeric PKs in single-database apps; UUID when services generate IDs independently—I never assume sequences are gap-free.

Multi-Version Concurrency Control keeps multiple row versions so readers do not block writers and writers do not block readers.

Each row version has system columns:

A transaction sees a snapshot of committed rows visible at snapshot start. UPDATE creates a new row version; old version becomes a dead tuple until vacuum reclaims space.

This differs from in-place overwrite databases and explains why heavy UPDATE causes bloat.

A strong answer is:

MVCC gives snapshot isolation behavior—reads see a consistent view while writes create new tuple versions; dead versions need vacuum, which is core Postgres DBA knowledge.

Postgres implements:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- consistent reads within this transaction
COMMIT;

Anomalies:

• Read committed allows non-repeatable reads between statements
• Repeatable read blocks phantom inserts in Postgres (stronger than standard RR)
• Serializable may raise serialization failures—apps must retry

A strong answer is:

Default read committed is fine for most OLTP; I use repeatable read or serializable when invariants need stronger guarantees and handle retry on 40001.

Postgres uses row-level locks for UPDATE/DELETE and table-level locks for DDL.

Useful views:

SELECT * FROM pg_locks WHERE NOT granted;
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

ORM sessions left open across HTTP requests are a common production blocker.

A strong answer is:

I keep transactions short, index FK columns, and check pg_stat_activity for lock chains—long open transactions hurt vacuum and throughput.

Deadlock detector picks a victim transaction and aborts it with SQLSTATE 40P01. Application must retry.

Prevention:

• Lock rows in consistent order
• Keep transactions small
• Use advisory locks for application-level ordering

BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- updates
COMMIT;

Interviewers want you to mention retry with backoff in app code.

A strong answer is:

Postgres detects deadlocks and kills one transaction—I retry idempotent operations and design lock order to prevent cycles.

Write-Ahead Logging: changes go to WAL on disk before heap pages. COMMIT waits for WAL flush (depending on synchronous_commit).

WAL enables PITR, streaming replication, and crash recovery.

A strong answer is:

COMMIT is durable when WAL is flushed—I do not disable synchronous_commit without understanding data-loss risk; WAL is also the replication stream.

CREATE INDEX idx_orders_created ON orders USING brin (created_at);
CREATE INDEX idx_docs_body ON docs USING gin (body jsonb_path_ops);

Wrong index type wastes space and slows writes—interviewers ask you to match operator class to query.

A strong answer is:

B-tree by default; GIN for jsonb @> and full-text; BRIN for append-only timestamps on huge tables—I justify type from the WHERE clause operators.

Composite — multiple columns; leftmost prefix rule applies.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

Partial — indexes subset of rows:

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Smaller index, faster writes, perfect for hot filtered queries.

Covering index (INCLUDE):

CREATE INDEX idx_orders_user_inc ON orders (user_id) INCLUDE (total_cents);

Enables index-only scans when visibility map allows.

A strong answer is:

Partial indexes for skewed filters like status='pending'; composite column order matches equality filters first, then range/sort.

JSONB stores binary JSON with efficient operators.

-- Containment query
SELECT * FROM products
WHERE attrs @> '{"color": "red"}';

CREATE INDEX idx_products_attrs ON products
USING gin (attrs jsonb_path_ops);

Tested containment on PostgreSQL 17:

SELECT '{"tags": ["a","b"]}'::jsonb @> '{"tags": ["a"]}'::jsonb;
-- returns true

Prefer real columns for stable typed fields; JSONB for dynamic attributes.

A strong answer is:

jsonb_path_ops GIN when I only need @> containment; typed columns when the field is stable and filter-heavy.

Indexes speed reads and slow writes (every INSERT/UPDATE maintains index pages).

Skip or delay indexes when:

• Table is tiny (seq scan cheaper)
• Write-heavy with rare reads
• Low selectivity column alone (e.g. boolean flag)
• Wrong type for query pattern

Monitor unused indexes:

SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

A strong answer is:

I index for proven query patterns from pg_stat_statements, drop unused indexes, and accept seq scans on small tables.

Normal CREATE INDEX takes SHARE lock blocking writes. CONCURRENTLY builds without blocking writes—slower, two-pass build.

CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

Failures leave invalid index—check pg_index.indisvalid and REINDEX if needed.

Production migrations on large tables almost always use CONCURRENTLY.

A strong answer is:

CONCURRENTLY for online index adds on live tables—I monitor for invalid indexes and reindex if the build failed mid-flight.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND created_at > now() - interval '30 days';

Compare estimated vs actual rows—large gaps mean stale statistics → ANALYZE.

A strong answer is:

EXPLAIN ANALYZE shows real timings; I look for seq scans on big tables, bad row estimates, and buffer hits versus reads.

Reasons:

Fix patterns: expression index, partial index, increase stats target, rewrite predicate.

A strong answer is:

Seq scan is not always wrong—I check row counts and selectivity; fix stats or add a targeted index when the scan is hot and large.

Extension pg_stat_statements aggregates query stats (calls, total time, mean time, rows).

SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Triage flow:

1. Find top total time queries
2. EXPLAIN (ANALYZE) the normalized query
3. Add index or rewrite SQL
4. Verify with load test

Pair with log_min_duration_statement for raw slow logs.

A strong answer is:

I rank by total_exec_time in pg_stat_statements, EXPLAIN the winner, fix plan or index, and re-measure—not guess from ORM logs alone.

Planner picks join order using table statistics (histograms,ndistinct).

Tips:

• ANALYZE after large data changes
• Join on indexed columns
• Avoid cross joins accidentally in ORMs
• JOIN smaller filtered set first when rewriting manually

-- Filter early in CTE
WITH recent AS (
  SELECT id FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT u.email, COUNT(*)
FROM recent r
JOIN orders o ON o.id = r.id
JOIN users u ON u.id = o.user_id
GROUP BY u.email;

A strong answer is:

Accurate stats drive join choices—I analyze after bulk loads and ensure join keys are indexed.

OFFSET pagination degrades on large offsets (scans skipped rows):

-- Slow at high page numbers
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

Keyset (seek) pagination:

SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

Requires stable sort key; handle tie-breaker column.

A strong answer is:

Keyset pagination on indexed columns for infinite scroll; OFFSET only for small admin pages.

Use JSONB for querying; JSON only if you need exact text preservation.

SELECT attrs->>'sku' AS sku,
       attrs->'dimensions'->>'width' AS width
FROM products;

A strong answer is:

JSONB for application payloads I filter on; extract hot keys to typed columns when queries stabilize.

PostgreSQL optimizes CTEs (inline or materialize depending on version/planner)—use for readable pipelines.

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS m, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT m, revenue,
       revenue - LAG(revenue) OVER (ORDER BY m) AS mom_delta
FROM monthly;

Window functions: ROW_NUMBER, RANK, LEAD/LAG, SUM() OVER (PARTITION BY …)—see SQL technical guide for more patterns.

A strong answer is:

CTEs clarify multi-step analytics; windows for ranking and period-over-period without self-join explosion.

LATERAL lets subquery reference columns from preceding FROM item—great for top-N per group:

SELECT c.name, r.*
FROM customers c
CROSS JOIN LATERAL (
  SELECT *
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 3
) r;

Alternative to window + filter patterns; planner can use indexes per customer.

A strong answer is:

LATERAL for correlated top-N per group—I reach for it when a subquery needs outer row values.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);

A strong answer is:

I enable extensions deliberately—pg_stat_statements in every prod cluster, pg_trgm or pgvector when product needs demand it.

INSERT INTO inventory (sku, qty)
VALUES ('ABC', 10)
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty;

Requires UNIQUE or PRIMARY KEY on conflict target.

EXCLUDED refers to proposed insert row. Watch lock contention on hot keys.

A strong answer is:

ON CONFLICT for idempotent writes—I define unique constraints explicitly and handle conflict updates atomically.

UPDATE/DELETE leave dead tuples. VACUUM reclaims space for reuse, updates visibility map, prevents transaction ID wraparound.

Autovacuum runs automatically—tune, do not disable.

Long transactions block vacuum cleanup → bloat.

A strong answer is:

Vacuum reclaims MVCC dead tuples and protects against xid wraparound—I tune autovacuum on high-churn tables and kill long idle transactions.

Bloat = heap pages holding dead tuples or sparse space.

Check bloat estimates with pg_stat_user_tables (n_dead_tup) and tools like pgstattuple.

pg_repack rewrites online without VACUUM FULL exclusive lock.

A strong answer is:

Bloat from dead tuples and blocked vacuum—I lower autovacuum thresholds on hot tables and use pg_repack before VACUUM FULL in production.

ANALYZE samples table data to update pg_statistic for the planner.

When to run:

• After large COPY/INSERT
• When EXPLAIN estimates diverge from actual
• Autovacuum analyze handles routine cases

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Higher statistics target improves estimates on skewed columns (costs more analyze time).

A strong answer is:

Bad plans often mean stale stats—I ANALYZE after bulk changes and raise statistics target on skewed filter columns.

Native declarative partitioning (Postgres 10+):

CREATE TABLE measurements (
  logdate date NOT NULL,
  value numeric
) PARTITION BY RANGE (logdate);

CREATE TABLE measurements_2026_06
  PARTITION OF measurements
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Partition pruning skips irrelevant child tables—critical for performance.

A strong answer is:

Range partition time-series by month, attach new partitions ahead of time, and verify pruning in EXPLAIN.

Postgres backends are memory-heavy (~MB each). Apps opening thousands of connections exhaust RAM.

PgBouncer pools connections:

ORMs + serverless need pooling (PgBouncer, RDS Proxy, Supavisor).

A strong answer is:

I pool at PgBouncer transaction mode for web apps—raw Postgres connections do not scale to thousands of lambdas or pods.

Primary streams WAL records to standby; standby replays WAL—physical replication, byte-for-byte copy.

Standbys are hot (read-only) with hot_standby = on.

Monitor replication lag via pg_stat_replication.

A strong answer is:

WAL streaming keeps replica identical to primary—I monitor lag bytes and choose sync vs async based on RPO requirements.

CREATE PUBLICATION orders_pub FOR TABLE orders;
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=primary dbname=app'
  PUBLICATION orders_pub;

Logical slots retain WAL until consumed—lagging subscriber causes WAL bloat on primary (CDC interview topic).

A strong answer is:

Physical for HA failover; logical for selective sync and upgrades—I watch replication slot lag to prevent WAL disk fill on primary.

PITR flow: restore base backup to time T0, replay WAL to target timestamp.

Tools: WAL-G, pgBackRest, cloud automated backups.

Test restores regularly—untested backups are folklore.

A strong answer is:

pg_dump for logical portability; base backup plus WAL archiving for PITR—I schedule restore drills, not just backups.

Options:

Application needs connection targeting to new primary and handling brief errors during failover.

Read replicas offload read scaling; writes still go to primary (unless sharded).

A strong answer is:

HA needs automated failover plus app retry—I use managed HA or Patroni and test promote without losing replication slots blindly.

Postgres roles can be users or groups:

CREATE ROLE app_read NOLOGIN;
GRANT CONNECT ON DATABASE shop TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

Row Level Security (RLS) filters rows per session:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Least privilege: app role without SUPERUSER, CREATEDB, or broad DDL.

A strong answer is:

Separate roles per app with minimal grants; RLS for multi-tenant row isolation when defense in depth matters.

Checklist:

1. pg_stat_statements — new query shape or regressed mean time?
2. EXPLAIN (ANALYZE, BUFFERS) — plan change? seq scan?
3. Statistics — run ANALYZE after migration bulk load
4. Indexes — migration drop index accidentally?
5. Data volume — crossed threshold where plan flips
6. Locks — blocking DDL or long transaction?
7. Connection storm — pool saturation?

Narrate before diving into \d and catalogs.

A strong answer is:

I compare EXPLAIN before and after, check stats and indexes first, then locks—deploys that bulk-load without ANALYZE are a frequent culprit.

For CDC slots (Debezium), lag blocks vacuum on primary tables.

A strong answer is:

I read lag in bytes and replay paused state—kill runaway reporting on replica and fix stalled logical consumers before primary WAL disk fills.

Patterns:

Dangerous: ALTER TABLE … ADD COLUMN DEFAULT on huge table (rewrite history in older versions—know version behavior), blocking ACCESS EXCLUSIVE DDL.

Use Flyway/Liquibase with review for lock modes.

A strong answer is:

Expand-contract with concurrent index builds—I avoid blocking DDL on hot tables and backfill in batches with throttling.

See MongoDB interview questions for document-store depth.

JSONB does not make Postgres a document DB—it lacks Mongo's sharding story out of the box.

A strong answer is:

Postgres for relational integrity and SQL analytics with optional JSONB; Mongo when document model and horizontal scale patterns are central—I do not store everything in jsonb by default.

Checklist:

• MVCC and why vacuum exists
• Isolation levels and deadlock retry
• Index types — B-tree, GIN, BRIN, partial
• EXPLAIN ANALYZE reading
• JSONB operators and GIN ops class
• Autovacuum and bloat story
• Streaming vs logical replication
• Backup / PITR basics
• Connection pooling rationale
• RLS and role grants
• One slow query debug narrative
• SQL technical interviews for live coding
• Spring Boot / Django for ORM layer
• Kafka for CDC slots
• Pandas for analytics downstream

A strong answer is:

I rehearse EXPLAIN on a real query weekly, explain MVCC to a rubber duck, and tie one production story to vacuum, indexes, and replication lag.