interviewDeck

Your one-stop platform to prepare, practice and ace your interviews.

Loading your questions…

interviewDeck

All Questions

Filters & tools

PostgreSQL Interview Questions and Answers

30 hand-picked PostgreSQL interview questions with detailed answers. Open the interactive version above to search, filter by difficulty, run code, bookmark questions and track your progress.

What is PostgreSQL and why is it popular?

PostgreSQL is an open-source, object-relational database known for reliability, standards compliance, and rich features. Unlike MySQL, it prioritises SQL standard adherence and extensibility.

Strengths: ACID transactions, advanced indexing (GIN, GiST, BRIN), JSONB, full-text search, CTEs, window functions, extensions (PostGIS), strong concurrency via MVCC.

How does MVCC work in PostgreSQL?

Multi-Version Concurrency Control — readers don't block writers and writers don't block readers. Each row has xmin (inserting transaction) and xmax (deleting/updating transaction). Updates create a new row version; old versions remain until vacuumed.

This enables consistent snapshots without read locks — the foundation of PostgreSQL's concurrency model.

PostgreSQL index types and when to use each.

  • B-tree (default) — equality and range queries on scalars.
  • Hash — equality only; rarely needed (B-tree is fine).
  • GIN — JSONB, arrays, full-text search (contains queries).
  • GiST — geometric data, nearest-neighbor, full-text.
  • BRIN — very large tables with naturally ordered data (timestamps); tiny index size.
  • Partial index — index a subset: WHERE active = true.
  • Composite index — column order matters for multi-column filters.
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
CREATE INDEX idx_data_gin ON docs USING GIN(data);

How do you use EXPLAIN ANALYZE?

EXPLAIN shows the query planner's execution plan without running the query. EXPLAIN ANALYZE actually executes it and shows real timings.

Look for: Seq Scan on large tables (bad — add index), Nested Loop with high row counts, high actual time, Buffers (cache hits vs disk reads).

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND status = 'open';

JSON vs JSONB in PostgreSQL.

JSON — stores exact text, faster to insert, slower to query (re-parsed each time).

JSONB — binary format, decomposed storage, supports indexing (GIN), slightly slower to insert but much faster to query. Use JSONB in almost all cases.

Query with -> (JSON object), ->> (text), @> (contains).

SELECT data->>'name' FROM profiles WHERE data @> '{"active": true}';
CREATE INDEX idx_profile_data ON profiles USING GIN(data);

PostgreSQL isolation levels.

PostgreSQL offers three practical levels (default: Read Committed):

  • Read Committed — each statement sees committed data as of statement start. Prevents dirty reads.
  • Repeatable Read — snapshot at transaction start. Prevents non-repeatable reads. May get serialization failures.
  • Serializable — strictest; prevents phantom reads via SSI (Serializable Snapshot Isolation).

On conflict, Postgres raises 40001 serialization_failure — retry the transaction.

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- queries see consistent snapshot
COMMIT;

What causes deadlocks and how does PostgreSQL handle them?

A deadlock occurs when two transactions wait for each other's locks in a cycle. PostgreSQL's deadlock detector identifies cycles and aborts one transaction (the victim), returning an error.

Prevention: acquire locks in a consistent order, keep transactions short, use appropriate isolation levels, avoid unnecessary locking reads.

What is VACUUM and why is it important?

VACUUM reclaims storage from dead row versions (from UPDATEs/DELETEs) and updates statistics for the query planner. Autovacuum runs this automatically.

Without vacuum: table bloat (wasted disk), transaction ID wraparound risk (catastrophic), stale statistics (bad query plans).

VACUUM FULL rewrites the entire table (locks table) — last resort for severe bloat.

SELECT relname, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

Why use connection pooling with PostgreSQL?

Each Postgres connection spawns a backend process (~5–10MB RAM). Thousands of app connections can exhaust memory. A pooler (PgBouncer, pgpool) maintains a small pool of real connections and multiplexes client requests.

Modes: session (one server conn per client session), transaction (return conn after each transaction — most efficient), statement (most aggressive).

SERIAL vs IDENTITY vs sequences.

All generate auto-incrementing integers:

  • SERIAL — legacy shorthand; creates a sequence + column default.
  • GENERATED ALWAYS AS IDENTITY — SQL standard (preferred since PG 10).
  • Sequences — standalone objects; multiple tables can share one.

For distributed systems, consider UUID or Snowflake IDs instead of sequences.

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL
);

What are recursive CTEs?

A recursive CTE queries hierarchical data (org charts, category trees, bill of materials) using a base case + recursive step:

WITH RECURSIVE tree AS (base SELECT ... UNION ALL recursive SELECT ... FROM tree JOIN ...)

PostgreSQL has excellent CTE support — use them for readable complex queries.

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id FROM emp WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM emp e JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Full-text search in PostgreSQL.

Convert text to tsvector (normalized tokens) and query with tsquery. Operators: @@ (match), to_tsvector(), plainto_tsquery().

Index with GIN for fast search. Supports ranking (ts_rank), highlighting, and multiple languages.

Good for app-level search; for heavy search workloads consider OpenSearch/Elasticsearch alongside Postgres.

SELECT title, ts_rank(search_vec, query) AS rank
FROM articles, plainto_tsquery('english', 'postgres indexing') query
WHERE search_vec @@ query
ORDER BY rank DESC;

PostgreSQL replication basics.

Streaming replication — primary streams WAL (Write-Ahead Log) to standby replicas in real time. Standbys serve read queries (hot standby).

Logical replication — replicates specific tables/changes; useful for upgrades, selective sync, and CDC.

For HA: automatic failover with Patroni, repmgr, or cloud-managed RDS/Cloud SQL.

Table partitioning in PostgreSQL.

Split a large table into smaller partitions by range, list, or hash. The planner prunes irrelevant partitions — queries only scan relevant chunks.

Range partitioning by date is the most common (monthly order partitions). Partitions can be detached, archived, or dropped cheaply.

CREATE TABLE orders (
  id BIGINT, created_at DATE, amount NUMERIC
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

PostgreSQL with Spring Data JPA — common pitfalls.

  • N+1 queries — lazy loading without @EntityGraph or JOIN FETCH causes hundreds of queries.
  • Wrong dialect — use org.hibernate.dialect.PostgreSQLDialect.
  • JSONB mapping — use @JdbcTypeCode(SqlTypes.JSON) (Hibernate 6).
  • Connection pool sizing — HikariCP max pool vs Postgres max_connections.
  • DDL auto — never use create-drop in production; use Flyway/Liquibase.
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.jpa.properties.hibernate.jdbc.batch_size=25

How do you do an UPSERT in PostgreSQL?

INSERT ... ON CONFLICT (unique_column) DO UPDATE SET ... — insert if not exists, update if conflict on a unique constraint/index.

DO NOTHING silently skips duplicates. Essential for idempotent consumers and sync operations.

INSERT INTO inventory (product_id, qty)
VALUES (42, 10)
ON CONFLICT (product_id)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;

How do you diagnose locking issues?

Query pg_locks joined with pg_stat_activity to see who holds and waits for locks. pg_blocking_pids() (PG 14+) returns blocking process IDs.

Common causes: long-running transactions holding row locks, missing indexes causing table-level locks, DDL during peak traffic.

SELECT pid, query, state, wait_event_type
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

PostgreSQL vs MySQL — key differences.

PostgreSQLMySQL
SQL complianceVery highModerate
JSONJSONB (indexable)JSON (less mature indexing)
ConcurrencyMVCC (readers never block writers)InnoDB MVCC (improved but different)
ExtensionsRich (PostGIS, pgvector)Limited
Best forComplex queries, analytics, JSON, geospatialSimple web apps, WordPress, read-heavy

Backup and restore strategies.

  • pg_dump — logical backup (SQL or custom format). Portable, good for small/medium DBs.
  • pg_basebackup — physical backup of data directory. Faster for large DBs; basis for PITR.
  • PITR — Point-in-Time Recovery using WAL archiving; restore to any moment.
  • Managed services — automated backups on RDS, Cloud SQL, Azure.
pg_dump -Fc mydb > mydb.dump
pg_restore -d mydb_restored mydb.dump

Native array support in PostgreSQL.

Postgres supports array columns: INTEGER[], TEXT[], etc. Query with ANY, @> (contains), unnest().

Useful for tags, multi-value attributes without a junction table. Index with GIN for containment queries.

SELECT * FROM posts WHERE 'angular' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['angular', 'typescript'];

Schemas and the search_path.

A schema is a namespace for tables, views, and functions inside a single database (the default is public). The search_path is the ordered list of schemas Postgres checks when you use an unqualified name.

Schemas are how you do multi-tenancy (one schema per tenant) or separate app modules within one database.

CREATE SCHEMA tenant_acme;
SET search_path TO tenant_acme, public;
SELECT * FROM orders;   -- resolves to tenant_acme.orders

SERIAL vs IDENTITY vs UUID for primary keys.

  • SERIAL — legacy auto-increment (a sequence behind the scenes).
  • GENERATED ALWAYS AS IDENTITY — the SQL-standard, preferred replacement: same auto-increment, cleaner ownership/permissions.
  • UUID — globally unique, generatable on the client with no coordination, but larger (16 bytes) and, if random (v4), causes index fragmentation.
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ext_id UUID DEFAULT gen_random_uuid()
);

Generated (computed) columns.

A GENERATED ALWAYS AS (expr) STORED column is computed from other columns in the same row and kept in sync automatically on every write — you can't insert into it directly.

Great for derived values you want to filter, sort, or index on (e.g. a lowercased search column, a total).

CREATE TABLE items (
  price NUMERIC, qty INT,
  total NUMERIC GENERATED ALWAYS AS (price * qty) STORED
);

Materialized views in PostgreSQL.

Unlike a regular view (recomputed on every query), a materialized view stores the result physically — expensive aggregations read instantly. The price is staleness: the data is a snapshot until you REFRESH it.

REFRESH ... CONCURRENTLY avoids locking out readers during the rebuild, but requires a unique index on the view.

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) d, sum(total) rev
FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

Useful PostgreSQL extensions.

CREATE EXTENSION bolts on powerful features:

  • pg_stat_statements — aggregated query performance stats (find slow queries).
  • pgcrypto — hashing/encryption; uuid-ossp / built-in gen_random_uuid() for UUIDs.
  • pg_trgm — trigram fuzzy/similarity search (great with GIN).
  • PostGIS — full geospatial types and queries.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

Triggers and PL/pgSQL.

PL/pgSQL is Postgres's procedural language for functions and triggers. A trigger binds a trigger function to fire BEFORE/AFTER an INSERT/UPDATE/DELETE; the special records NEW and OLD hold the row values.

Common uses: audit logging, maintaining updated_at, and enforcing rules a CHECK can't express.

CREATE FUNCTION touch() RETURNS trigger AS $
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$ LANGUAGE plpgsql;
CREATE TRIGGER t BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION touch();

Bulk loading data with COPY.

COPY streams whole files of rows in or out far faster than row-by-row INSERT — one command, minimal parsing and per-statement overhead. \copy is the psql client-side variant that reads a local file.

For very large loads, drop/disable indexes and triggers first, load, then rebuild — maintaining indexes per row is the bottleneck.

COPY orders (id, total) FROM '/data/orders.csv' WITH (FORMAT csv, HEADER);
-- client side:
\copy orders FROM 'orders.csv' CSV HEADER

Index types: B-tree, GIN, GiST, BRIN, Hash.

  • B-tree — default; equality, ranges, sorting.
  • GIN — multi-valued data: jsonb, arrays, full-text search.
  • GiST — geometric/spatial, ranges, nearest-neighbor.
  • BRIN — tiny index for huge, naturally-ordered tables (e.g. append-only by time).
  • Hash — equality only; rarely worth it over B-tree.
CREATE INDEX idx_doc ON docs USING GIN (data jsonb_path_ops);
CREATE INDEX idx_ts ON events USING BRIN (created_at);

What is a LATERAL join?

LATERAL lets a subquery (or set-returning function) in the FROM clause reference columns from the preceding FROM items — it's evaluated once per outer row, like a correlated subquery you can join to.

The classic use is top-N per group: for each parent, run a small ordered/limited query.

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

LISTEN / NOTIFY — what and when?

Postgres's built-in lightweight pub/sub: NOTIFY channel, 'payload' broadcasts a message (often from a trigger); connected clients that ran LISTEN channel receive it in near real time.

Great for cache invalidation or nudging workers. But it's not a durable queue — a client that's disconnected misses the message.

-- worker:
LISTEN order_created;
-- producer (or trigger):
NOTIFY order_created, '{"id": 42}';