PostgreSQL interview questions and answers

What is PostgreSQL?

PostgreSQL is an open-source object-relational database. It follows the SQL standard closely, is fully ACID, and is known for rich data types such as JSONB, arrays and ranges, strong concurrency through MVCC, and extensions that add features like vector search and geospatial queries.

How is PostgreSQL different from MySQL?

Both are mature relational databases. PostgreSQL has stricter standards compliance, more index types, richer types like JSONB and arrays, transactional DDL and an extension system. MySQL with InnoDB is simpler to run for basic web workloads. Either works for most apps; the team's experience often decides.

What is a schema in PostgreSQL?

A schema is a namespace inside a database that holds tables, views and functions, so two teams can each have an orders table in different schemas. The search_path setting decides which schemas are searched when a name is not qualified; the default is the user's own schema, then public.

CREATE SCHEMA billing;
CREATE TABLE billing.invoices (id bigint PRIMARY KEY);
SET search_path TO billing, public;

What is the difference between SERIAL and an identity column?

SERIAL is an older PostgreSQL shortcut that creates a sequence and a default. An identity column, GENERATED ALWAYS AS IDENTITY, is the SQL-standard way: the sequence belongs to the column, permissions are simpler, and ALWAYS stops accidental manual inserts. Prefer identity columns in new tables.

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

Should you use UUIDs or integers as primary keys?

Integers are smaller and fast to index. UUIDs can be generated anywhere without a round trip and do not reveal row counts. Random UUIDv4 values scatter inserts across the index; time-ordered UUIDv7 values avoid that, and PostgreSQL 18 added a built-in uuidv7() function.

What are PostgreSQL extensions?

Extensions are packages that add types, functions, operators or index methods, installed per database with CREATE EXTENSION. Common ones are pg_stat_statements for query statistics, pg_trgm for fuzzy text search, PostGIS for maps and pgvector for embeddings.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector;

How do you write an upsert in PostgreSQL?

Use INSERT ... ON CONFLICT. Name the unique column or constraint, then either DO NOTHING or DO UPDATE. Inside DO UPDATE, EXCLUDED refers to the row you tried to insert. A unique index on the conflict column is required.

INSERT INTO stock (sku, qty)
VALUES ('A-100', 5)
ON CONFLICT (sku)
DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

What does the RETURNING clause do?

RETURNING sends back columns from rows changed by INSERT, UPDATE or DELETE in the same statement. It saves a second query, for example to get a generated ID or the new value after an update.

INSERT INTO users (email)
VALUES ('a@example.com')
RETURNING id, created_at;

What does DISTINCT ON do?

DISTINCT ON (expression) keeps only the first row for each distinct value, where "first" is decided by ORDER BY. It is a short PostgreSQL way to get the latest row per group, such as each customer's most recent order. ORDER BY must start with the DISTINCT ON expressions.

SELECT DISTINCT ON (customer_id) customer_id, id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

What is a LATERAL join?

LATERAL lets a subquery in FROM refer to columns of tables listed before it, so the subquery runs once per outer row. It is useful for "top N per row" queries, such as the three latest orders for every customer, and for calling set-returning functions per row.

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

How do you query a hierarchy such as an org chart?

Use WITH RECURSIVE. The first part selects the starting rows, UNION ALL adds a part that joins back to the CTE itself, and PostgreSQL repeats it until no new rows appear. Add a depth column or a cycle check so bad data cannot loop forever.

WITH RECURSIVE team AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id, e.name, t.depth + 1
  FROM employees e JOIN team t ON e.manager_id = t.id
)
SELECT * FROM team;

Are CTEs an optimisation fence in PostgreSQL?

Not since PostgreSQL 12. A non-recursive CTE without side effects that is referenced once is now inlined into the main query, so filters can be pushed into it. Write WITH name AS MATERIALIZED to force the old behaviour, or NOT MATERIALIZED to force inlining.

Why is OFFSET pagination slow on large tables, and what is the alternative?

OFFSET 100000 still reads and throws away 100,000 rows, so deep pages get slower. Keyset pagination remembers the last row's sort values and asks for rows after them, which uses an index and stays fast on every page.

SELECT id, created_at FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

What is the difference between JSON and JSONB?

JSON stores the text exactly as given, including whitespace, key order and duplicate keys. JSONB stores a parsed binary form: it drops duplicates and formatting, is a little slower to write, but much faster to query and can be indexed. Use JSONB unless you must keep the original text.

How do you query JSONB data?

Use -> to get a JSON value, ->> to get it as text, #>> for a nested path, @> to test containment and ? to test whether a key exists. Cast text results when comparing numbers.

SELECT data->>'name' AS name
FROM events
WHERE data @> '{"type": "signup"}'
  AND (data->>'age')::int > 18;

How do you index a JSONB column?

A GIN index on the whole column speeds up containment (@>) and key-exists queries; the jsonb_path_ops variant is smaller but supports only containment and JSON path matches. If you always filter on one key, a B-tree expression index on that key is smaller and faster.

CREATE INDEX events_data_gin ON events USING gin (data jsonb_path_ops);
CREATE INDEX events_type_idx ON events ((data->>'type'));

How does full-text search work in PostgreSQL?

Text is turned into a tsvector of normalised words, the search into a tsquery, and @@ checks for a match. Store the tsvector in a generated column with a GIN index, and sort results with ts_rank. websearch_to_tsquery accepts search-box style input.

ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX articles_search_idx ON articles USING gin (search);

SELECT id FROM articles
WHERE search @@ websearch_to_tsquery('english', 'postgres index');

How do you speed up LIKE '%term%' searches?

A normal B-tree index cannot help when the pattern starts with a wildcard. The pg_trgm extension splits text into three-letter pieces, and a GIN or GiST trigram index then speeds up LIKE, ILIKE and similarity searches anywhere in the string.

CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%phone%';

Which index types does PostgreSQL have?

B-tree is the default and handles equality, ranges and sorting. Hash handles equality only. GIN suits values containing many items, such as JSONB, arrays and full-text. GiST and SP-GiST suit geometry, ranges and nearest-neighbour search. BRIN stores a summary per block range and is tiny for huge, naturally ordered tables.

What is a partial index?

A partial index covers only rows that match a WHERE condition. It is smaller and faster when queries always target a subset, such as unprocessed jobs, and it can enforce uniqueness for a subset, such as one active subscription per user.

CREATE INDEX jobs_pending_idx ON jobs (created_at) WHERE status = 'pending';
CREATE UNIQUE INDEX one_active_sub ON subscriptions (user_id) WHERE active;

What is an expression index?

An expression index stores the result of an expression instead of a raw column, so a query that filters on the same expression can use it. A common case is case-insensitive email lookup with lower(email); the query must use exactly the same expression.

CREATE INDEX users_email_lower_idx ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

What is an index-only scan and what does INCLUDE do?

In an index-only scan, PostgreSQL answers from the index without reading the table, which needs every selected column in the index and pages marked all-visible by VACUUM. INCLUDE adds extra columns to the index leaf entries without making them part of the key, so more queries qualify.

CREATE INDEX orders_customer_idx ON orders (customer_id) INCLUDE (total, status);

How do you add an index to a busy production table?

Use CREATE INDEX CONCURRENTLY, which builds the index without blocking inserts and updates. It takes longer, cannot run inside a transaction block, and if it fails it leaves an invalid index that you must drop and build again.

CREATE INDEX CONCURRENTLY orders_created_idx ON orders (created_at);

How do you read EXPLAIN ANALYZE output?

EXPLAIN shows the planned steps with estimated costs and rows; ANALYZE actually runs the query and adds real times and row counts. Read it from the innermost nodes outwards, look for the slowest node, and compare estimated with actual rows: a large gap usually means stale statistics. Add BUFFERS to see how much data was read.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

What join methods can the PostgreSQL planner choose?

Nested loop suits small outer inputs with an index on the inner side. Hash join builds a hash table from the smaller input and suits large unsorted sets joined on equality. Merge join walks two inputs sorted on the join key. The planner picks by estimated cost, which depends on statistics.

How do you find the slowest queries in a running PostgreSQL database?

Enable the pg_stat_statements extension, which groups queries by shape and records calls, total time and mean time. Sort by total execution time to find what costs most overall, then run EXPLAIN ANALYZE on the top entries. pg_stat_activity shows what is running right now.

SELECT query, calls, round(total_exec_time) AS total_ms, round(mean_exec_time) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Does PostgreSQL index foreign keys automatically?

No. It indexes primary keys and unique constraints, but not the referencing column of a foreign key. Without that index, joins on it are slower and every delete or key update on the parent table must scan the child table, so add the index yourself.

What is MVCC in PostgreSQL?

Multi-version concurrency control keeps several versions of a row. Each version records which transaction created it and which deleted it, and every query sees a consistent snapshot. Readers never block writers and writers never block readers; only two writers on the same row wait for each other.

Why does an UPDATE in PostgreSQL create table bloat?

An UPDATE does not overwrite the row. It writes a new row version and marks the old one dead, because other transactions may still need it. Until VACUUM reclaims dead versions, they take space, and heavily updated tables grow larger than their live data.

What is the difference between VACUUM, VACUUM FULL and ANALYZE?

VACUUM marks dead row versions as reusable space and updates the visibility map, without blocking normal reads and writes. VACUUM FULL rewrites the whole table to return space to the operating system, but locks the table for the duration. ANALYZE refreshes the statistics the planner uses.

What is autovacuum and when do you tune it?

Autovacuum is a background process that runs VACUUM and ANALYZE when enough rows have changed. Defaults are scaled to table size, so very large or very busy tables can wait too long. Tune per table with a lower scale factor, and never turn it off.

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

What is transaction ID wraparound?

PostgreSQL transaction IDs are 32-bit, so they eventually wrap around. VACUUM "freezes" old rows so they stay visible after the counter wraps. If freezing falls too far behind, PostgreSQL forces aggressive vacuums and, at the limit, stops accepting writes to protect the data.

Which isolation levels does PostgreSQL support?

Read Committed is the default: each statement sees data committed before it began. Repeatable Read gives the whole transaction one snapshot and, in PostgreSQL, also prevents phantom rows. Serializable adds checks so results match some serial order. The higher two can fail with a serialization error that the app must retry.

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

How do you build a job queue on PostgreSQL safely?

Each worker selects pending rows with FOR UPDATE SKIP LOCKED inside a transaction. Rows already locked by another worker are skipped instead of waited on, so workers never take the same job and never block each other.

BEGIN;
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- process, then UPDATE jobs SET status = 'done' ...
COMMIT;

What are advisory locks?

Advisory locks are application-defined locks on a number you choose, not on a row or table. PostgreSQL only tracks them; your code decides what they mean, for example "only one worker runs the nightly report". They can be held for a session or released automatically at the end of a transaction.

SELECT pg_try_advisory_xact_lock(42);  -- true if this session got the lock

How does PostgreSQL handle deadlocks?

When a lock wait lasts longer than deadlock_timeout (one second by default), PostgreSQL checks for a cycle. If it finds one, it cancels one of the transactions with a deadlock error so the others continue. Prevent them by locking rows in a consistent order and keeping transactions short.

What is the write-ahead log (WAL)?

Every change is first written to the WAL, a sequential log, and flushed at commit before the data files are updated. After a crash, PostgreSQL replays the WAL to restore committed work. The same log feeds streaming replication and point-in-time recovery.

What is the difference between streaming and logical replication?

Streaming replication ships the WAL to a standby, which becomes an exact byte-for-byte copy of the whole cluster on the same major version and can serve read-only queries. Logical replication sends row changes for chosen tables through publications and subscriptions, works across major versions, but does not copy schema changes.

How do you back up PostgreSQL and restore to a point in time?

pg_dump makes a logical backup of one database, which is portable but slow to restore for big data. For point-in-time recovery, take a physical base backup with pg_basebackup and archive WAL continuously; you can then restore the base backup and replay WAL up to a chosen moment.

Why do PostgreSQL apps need a connection pooler?

Each PostgreSQL connection is a separate server process that uses memory, so thousands of app connections hurt performance. A pooler like PgBouncer keeps a small set of real connections and shares them. In transaction mode a connection returns to the pool after each transaction, so session-level settings and locks need care.

How does table partitioning work in PostgreSQL?

Declarative partitioning splits a parent table into child tables by RANGE, LIST or HASH on a partition key, such as one partition per month. Queries that filter on the key skip other partitions, and old data can be removed by dropping a partition instead of a slow DELETE. Primary keys must include the partition key.

CREATE TABLE events (
  id bigint, created_at timestamptz NOT NULL, data jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_11 PARTITION OF events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

Which PostgreSQL settings matter most for performance?

shared_buffers is the database's own cache, often about a quarter of RAM to start. work_mem is memory per sort or hash step, and one query can use it several times, so raise it carefully. effective_cache_size tells the planner how much the OS will cache, and maintenance_work_mem speeds up VACUUM and index builds.

How do you change a schema on a large table without downtime?

Set a short lock_timeout so a migration gives up instead of queuing behind long queries. Adding a column with a constant default is instant since PostgreSQL 11. Build indexes CONCURRENTLY, add constraints as NOT VALID and validate them later, and backfill big updates in small batches.

SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_total_positive CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;

What is row-level security and how does it help a multi-tenant app?

Row-level security attaches policies to a table so each query only sees rows the policy allows, for example rows whose tenant_id matches a setting for the current request. It is a second line of defence if the app forgets a WHERE clause. Table owners and superusers bypass it unless you force it.

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

What is a materialized view in PostgreSQL?

A materialized view stores the result of a query as a table, so expensive reports read instantly. It does not update itself; you run REFRESH MATERIALIZED VIEW. Adding CONCURRENTLY keeps it readable during the refresh but needs a unique index on the view.

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

CREATE UNIQUE INDEX ON daily_sales (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

What is pgvector?

pgvector is a PostgreSQL extension that adds a vector column type, distance operators and approximate nearest-neighbour indexes. It lets you store embeddings next to your normal data and run similarity search with SQL, which is why many RAG apps use Postgres as their vector store.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc_id bigint NOT NULL,
  content text NOT NULL,
  embedding vector(1536)
);

How do you run a similarity search with pgvector?

Order by the distance between the stored vector and the query vector and take the top rows. <-> is Euclidean distance, <=> is cosine distance and <#> is negative inner product. Use the operator that matches how your embedding model was trained; cosine is the common choice.

SELECT id, content
FROM chunks
ORDER BY embedding <=> $1
LIMIT 5;

What is the difference between HNSW and IVFFlat indexes in pgvector?

HNSW builds a layered graph: it gives a better speed-to-recall balance and can be built on an empty table, but builds slowly and uses more memory. IVFFlat groups vectors into lists around centres: it builds faster and is smaller, but should be built after data is loaded, and recall depends on how many lists you probe.

CREATE INDEX chunks_embedding_hnsw ON chunks
  USING hnsw (embedding vector_cosine_ops);

SET hnsw.ef_search = 100;  -- higher = better recall, slower

Why can a filtered vector search return fewer rows than LIMIT?

An approximate index returns a fixed number of nearest candidates first, and the WHERE filter is applied afterwards. If few candidates match the filter, fewer rows come back. Fixes are raising ef_search, a partial index or partition per tenant, or the iterative index scans added in newer pgvector versions.

How would you do hybrid search inside PostgreSQL?

Run a full-text query ranked by ts_rank and a vector query ranked by distance, then merge the two ranked lists, often with reciprocal rank fusion, which adds 1 / (k + rank) from each list. Both searches live in one database, so filters and permissions apply to both.

When is pgvector enough and when would you use a dedicated vector database?

pgvector is enough for most apps up to millions of vectors, and you keep one database, transactions and SQL joins. A dedicated vector database makes sense at very large scale, with very high query rates, or when you need features like built-in sharding of vectors. Measure recall and latency on your own data first.