Postgres is where a lot of “the app is slow” problems actually live. These questions check whether a candidate can reason about indexes, plans and concurrency.
Hiring a PostgreSQL developer is easy. Telling a real one from a convincing résumé is the hard part — and it’s most of what we do. These are grouped by level, because the same question that stretches a junior is a warm-up for a senior.
Junior PostgreSQL interview questions
0–2 years
Fundamentals.
What makes PostgreSQL different from MySQL?
Strong standards compliance, rich types (JSONB, arrays), extensibility, and robust MVCC concurrency; both are solid but differ in features and defaults.
Cannot name a concrete difference.
What is a primary key and a unique constraint?
A primary key uniquely identifies rows and is not null; unique constraints enforce uniqueness on other columns, backed by an index.
Confuses the two or thinks uniqueness is free.
What are common data types you reach for?
Appropriate integer/numeric types, text, timestamptz, boolean, uuid and jsonb; choosing well saves storage and bugs.
Stores everything as text.
What is the difference between timestamp and timestamptz?
timestamptz stores an instant normalised to UTC; timestamp is naive. Use timestamptz to avoid timezone bugs.
Uses naive timestamps and gets timezone bugs.
What is an index, at a basic level?
A structure (usually B-tree) that speeds lookups at the cost of storage and slower writes.
Adds an index to every column reflexively.
What is a foreign key?
A constraint enforcing that a value references an existing row in another table, protecting referential integrity.
Skips foreign keys and ends up with orphaned rows.
What does NULL mean and how do you handle it?
Unknown/absent; comparisons need IS NULL, and it affects aggregates, uniqueness and joins.
Compares with = NULL.
What is JSONB and when do you use it?
A binary JSON type supporting indexing and operators, good for semi-structured data — but not a reason to avoid proper columns.
Puts all data in one jsonb blob.
Mid-level PostgreSQL interview questions
2–5 years
Indexing and transactions.
What index types does Postgres offer beyond B-tree?
GIN (for jsonb/arrays/full-text), GiST, BRIN for huge ordered tables, and hash; choosing the right one matters for the query.
Only knows B-tree and can’t index jsonb.
How do you read an EXPLAIN ANALYZE plan?
Look at scan types (seq vs index), row estimates vs actuals, join methods and costly nodes to find the bottleneck.
Never reads plans and guesses.
What is MVCC and how does it affect you?
Multi-Version Concurrency Control keeps row versions so readers don’t block writers; dead tuples accumulate and need vacuuming.
Unaware why the table bloats over time.
What does VACUUM do and why does it matter?
Reclaims space from dead tuples and updates statistics; autovacuum usually handles it, but heavy write tables may need tuning.
Ignores bloat until performance collapses.
What is a partial and an expression index?
Partial indexes cover a subset of rows (e.g. active only); expression indexes index a computed value so a function query can use it.
Wonders why lower(email) queries don’t use the index.
What are the transaction isolation levels in Postgres?
Read Committed (default), Repeatable Read and Serializable, trading concurrency against anomalies.
Assumes default isolation prevents all anomalies.
How do row-level locks and deadlocks arise?
Concurrent updates to the same rows in different orders; consistent ordering and short transactions reduce deadlocks.
No concept of lock ordering.
What is a CTE and are they always optimisation-safe?
A named subquery for readability/recursion; historically materialised, they can be inlined in newer versions — know your version’s behaviour.
Assumes CTEs are always free.
Senior PostgreSQL interview questions
5+ years
Performance and operations.
How do you diagnose a slow query in production?
pg_stat_statements to find offenders, EXPLAIN (ANALYZE, BUFFERS), checking indexes, statistics and bloat.
Rewrites queries blindly with no measurement.
When and how do you use table partitioning?
For very large tables partitioned by range/list (e.g. time) to prune scans and ease maintenance; it adds planning complexity.
Partitions on a key that doesn’t help pruning.
How do connection pooling and its absence affect Postgres?
Each connection is a process, so many idle connections hurt; a pooler (PgBouncer) is often essential at scale.
Opens thousands of direct connections.
How do you run zero-downtime schema changes?
Add columns/indexes concurrently, avoid long locks, backfill in batches, and use expand/contract for renames.
Runs a blocking ALTER on a huge live table.
How do you scale reads and writes?
Read replicas and caching for reads; partitioning, tuning and careful schema for writes; sharding as a last resort.
Assumes vertical scaling is the only lever.
How do you tune autovacuum for a write-heavy table?
Lower thresholds and increase workers/cost limits for hot tables to keep bloat and transaction wraparound in check.
Leaves defaults and hits wraparound risk.
How do you handle high-concurrency counters or hotspots?
Avoid a single hot row; use techniques like sharded counters or upserts with care to reduce contention.
Serialises all updates through one hot row.
When is Postgres the wrong tool?
For pure key-value at massive scale, wide-column workloads, or when you need a different consistency/scale profile — they can justify the tradeoff.
Treats Postgres as the answer to every data problem.
Build and score a full interview with our free interview scorecard tool, browse the full question hub, or see how we interview engineers.