PostgreSQL Interview Questions and Answers

Answer

PostgreSQL is an open-source object-relational database originally developed at UC Berkeley in 1986 and maintained by the PostgreSQL Global Development Group. Versus MySQL, the practical differences in 2026 are: (1) MVCC is implemented at the row level natively in Postgres, readers never block writers and vice versa, without any storage engine selection. (2) Richer type system, native JSONB, arrays, ranges, hstore, ENUM, UUID, INET, plus extensions like PostGIS for geospatial. (3) Strict standards compliance, Postgres rejects invalid dates, divisions by zero, and out-of-range integers where MySQL historically coerced them, which means a bug surfaces at write time instead of producing silently corrupt analytics months later. (4) Stronger DDL transactions, you can wrap `CREATE TABLE`, `ALTER TABLE`, even the metadata for `CREATE INDEX CONCURRENTLY` in a transaction and roll back, which makes deployment scripts safer; MySQL implicitly commits on most DDL. (5) Extensions ecosystem, pg_stat_statements, pg_partman, pgvector, TimescaleDB, Citus, PostGIS all plug in without forking the binary. (6) NOTIFY/LISTEN async messaging baked in, useful for cache invalidation and lightweight queues. MySQL still wins for the very fastest simple-key reads, has wider managed-cloud presence, and InnoDB's clustered-index storage is faster for `SELECT * FROM tbl ORDER BY id LIMIT 1000` than Postgres's heap-with-secondary-indexes layout. But Postgres is the default for new Indian startups in 2026, and Razorpay, Swiggy, Zomato, Zerodha, Cred, Meesho, PhonePe and most YC-backed companies hiring backend engineers treat Postgres fluency as table stakes in the interview loop.

Answer

ACID stands for Atomicity, Consistency, Isolation, Durability. Atomicity, a transaction is all-or-nothing; Postgres uses a write-ahead log (WAL) so a crash mid-transaction rolls back cleanly on recovery, because uncommitted changes are simply discarded when the WAL is replayed up to the last COMMIT record. Consistency, constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY, EXCLUDE) are validated before commit; the database moves from one valid state to another, and complex invariants can be enforced via deferred constraints that check at COMMIT instead of after each statement.

Isolation, concurrent transactions see a consistent snapshot via MVCC, with the strength controlled by the isolation level (READ COMMITTED by default; SERIALIZABLE for the strongest guarantee). Durability, committed data is flushed to disk via fsync on the WAL before COMMIT returns; settings like `synchronous_commit=off` trade a tiny window of durability (a few milliseconds of in-flight transactions) for substantial write throughput, which is fine for analytics writes but never for payments. In a typical Indian fintech (Razorpay-style) flow, durability matters enough that `synchronous_commit=on`, `wal_level=replica`, and at least one synchronous standby in the same region are non-negotiable. Pair this with point-in-time recovery (PITR) using `pg_basebackup` plus archived WAL, you can rewind to any second of the last 7 days if a bad migration corrupts data.

Answer

MVCC means every write creates a new row version (a 'tuple') rather than updating in place. Each tuple carries hidden columns `xmin` (transaction that inserted it) and `xmax` (transaction that deleted/updated it). A transaction sees a tuple only if `xmin` is committed and visible to its snapshot, and `xmax` is not, this snapshot is taken either at statement start (READ COMMITTED) or at transaction start (REPEATABLE READ / SERIALIZABLE).

The practical consequence: readers never block writers, writers never block readers, concurrent OLTP scales beautifully on Postgres. The cost is dead tuples accumulating; VACUUM (and autovacuum) reclaims them by marking the space as reusable, and eventually returning empty pages to the operating system. The big gotcha is 'table bloat', if autovacuum can't keep up, your table grows even when row count doesn't, indexes get full of dead entries, and queries slow down because the planner has to skip past them.

A long-running transaction (e.g. an analytics job holding a transaction open for hours, or a `psql` session somebody forgot to close) blocks vacuum because those old tuples might still be 'visible' to it; this is one of the top causes of production incidents at Indian fintechs running heavy reporting alongside OLTP. Mitigation: alert on `pg_stat_activity.xact_start` older than 10 minutes, and use `idle_in_transaction_session_timeout` to auto-kill abandoned sessions.

Answer

Postgres supports four SQL standard levels but only three behaviours: READ UNCOMMITTED (treated as READ COMMITTED, Postgres never shows uncommitted data), READ COMMITTED (default, each statement sees its own snapshot of committed data, so two SELECTs in the same transaction may see different states if concurrent commits happen between them), REPEATABLE READ (the whole transaction sees a single snapshot taken at the first statement, prevents non-repeatable and phantom reads via 'snapshot isolation'), and SERIALIZABLE (REPEATABLE READ plus runtime conflict detection that aborts transactions which would violate serial execution). Postgres's SERIALIZABLE uses Serializable Snapshot Isolation (SSI), not 2-phase locking, meaning it doesn't block on read, but may raise `serialization_failure` (SQLSTATE 40001) which the application must retry. The typical retry loop wraps the whole transaction in a `while attempts < 3` block and on 40001 sleeps a small randomised backoff before retrying, without that retry logic, you'll see user-facing errors under load.

In banking and payments workloads, the kind Razorpay and Zerodha interviews centre on, SERIALIZABLE on the critical money-movement tables is the expected answer; everywhere else, READ COMMITTED with explicit `SELECT ... FOR UPDATE` on hot rows is the norm. Beware that REPEATABLE READ in Postgres does prevent the standard phantom-read anomaly via snapshot isolation, but does NOT detect 'write skew' between two transactions reading and writing disjoint rows, only SERIALIZABLE does.

-- Setting isolation level per transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE wallet SET balance = balance - 100 WHERE user_id = 42;
UPDATE wallet SET balance = balance + 100 WHERE user_id = 99;
COMMIT;
-- May raise 40001, application must retry the whole transaction

Answer

In PostgreSQL the three are stored identically using the variable-length 'varlena' format, there is no performance benefit to picking VARCHAR(50) over TEXT. CHAR(n) is blank-padded to length n on storage and trimmed on display, which wastes space and almost never matches anyone's intent, the only legitimate use is fixed-width legacy interop. VARCHAR(n) and TEXT differ only in that VARCHAR(n) enforces a length check (raising an error if you try to insert longer strings), implemented as an internal CHECK constraint.

The idiomatic choice in 2026 is: use TEXT everywhere, add a CHECK constraint only if length actually matters business-wise (e.g. mobile number 10 chars, GST number 15 chars). This is the opposite of what MySQL teaches, in MySQL CHAR vs VARCHAR can affect row format and storage. Junior candidates almost always trip on this; senior reviewers expect you to know that 'VARCHAR(255)' from MySQL muscle memory is wasted typing in Postgres.

Storage detail: short strings (<126 bytes after the 1-byte length header) are stored inline in the tuple; longer values either go inline up to ~2 KB or get pushed to TOAST (The Oversized-Attribute Storage Technique), a side table where they're compressed and chunked. TOAST is transparent, you query the column normally, but a TEXT column full of 50 KB blobs will silently make every wide SELECT slower because TOAST de-detoasting happens per row.

Answer

PRIMARY KEY = NOT NULL + UNIQUE + the conventional row identifier for foreign-key references, replication identity, and logical replication routing. A table has at most one PRIMARY KEY but can have many UNIQUE constraints. Both create a B-tree index automatically, you do not need to add a separate index for either.

A UNIQUE constraint allows NULL values and (by SQL standard) treats two NULLs as distinct, so multiple rows can have NULL in a UNIQUE column. Postgres 15+ added `NULLS NOT DISTINCT` to opt into MySQL-style behaviour where NULLs are treated as equal, useful when a column genuinely shouldn't repeat even when blank. For new tables in 2026, prefer `id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY` over the older `SERIAL`, IDENTITY is SQL standard, doesn't have the SERIAL sequence-ownership quirks that bite you when restoring backups, and BIGINT avoids the dreaded 'integer overflow at 2.1 billion rows' incident that has hit Indian e-commerce companies more than once during sale weekends. UUID v7 (Postgres 18 will ship `uuidv7()` natively; until then use the `pg_uuidv7` extension) is the other modern choice, sortable like an integer but globally unique, ideal for distributed systems that need to mint IDs in the application layer.

-- Modern Postgres 17 pattern
CREATE TABLE orders (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_number  TEXT NOT NULL UNIQUE,
  user_id       BIGINT NOT NULL REFERENCES users(id),
  amount_paise  BIGINT NOT NULL CHECK (amount_paise >= 0),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

Answer

An index is an auxiliary data structure that lets the planner find rows without scanning the whole table, typically reducing a query from O(N) sequential scan to O(log N) lookup. Postgres supports six index types: B-tree (default, equality and range on scalar columns, sorted output), Hash (equality only, useful only in narrow cases since Postgres 10 made them WAL-logged and therefore crash-safe), GIN (Generalized Inverted Index, for arrays, JSONB, full-text search via tsvector), GiST (Generalized Search Tree, for geometric/PostGIS, range types, exclusion constraints, nearest-neighbour), SP-GiST (space-partitioned, good for non-balanced data like IP prefix lookups and quadtrees), and BRIN (Block Range Index, tiny indexes on huge tables with naturally clustered data like append-only time-series). Picking the right type is one of the most common senior-level questions: B-tree for `WHERE created_at > X`, GIN for `WHERE tags @> ARRAY['fast']`, BRIN for a 500 GB events table where `created_at` correlates with insert order so it sits at 0.01% of the heap size.

The wrong choice is usually a B-tree on everything. Two other dimensions matter independently of type: partial (`WHERE` clause restricts what's indexed) and covering (`INCLUDE` adds non-key columns to enable index-only scans). And a meta-rule: every index slows down writes, so the goal is the smallest set of indexes that supports your read workload, not 'an index on every queryable column'.

Answer

WHERE filters rows BEFORE aggregation; HAVING filters groups AFTER aggregation. You cannot use aggregate functions (COUNT, SUM, AVG, MAX, MIN) in WHERE, you must use HAVING. Order of execution in a SELECT is conceptually: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

The classic interview trap: 'find users with more than 5 orders', beginners write `WHERE COUNT(orders) > 5` which is a syntax error; the answer uses HAVING because the COUNT can only be computed after grouping. Performance note: filter as much as possible in WHERE before grouping, because aggregation is expensive, every row WHERE drops is a row GROUP BY doesn't have to bucket and HAVING doesn't have to consider. Anti-pattern that even mid-level candidates fall into: `WHERE category = 'X' GROUP BY ...

HAVING category = 'X'`, the HAVING clause is redundant once WHERE has done its job. Use HAVING strictly for predicates that involve aggregates.

-- Users who placed more than 5 orders in March 2026
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-03-01' AND o.created_at < '2026-04-01'  -- pre-aggregate filter
GROUP BY u.id, u.email
HAVING COUNT(o.id) > 5;

Answer

Postgres has two JSON types: JSON (stored as exact text, preserves whitespace and key order, slow to query because every read re-parses) and JSONB (stored as a parsed binary format, drops insignificant whitespace, deduplicates keys, supports indexing, and is faster on every operation except initial INSERT). For anything you'll query, use JSONB; use JSON only when you need byte-for-byte round-trip preservation (audit logs of incoming API payloads, for instance). JSONB supports operators `->` (get field as JSONB, preserving structure), `->>` (get field as text), `@>` (left contains right), `<@` (left is contained in right), `?` (key exists at top level), `?|` (any of these keys exist), `?&` (all of these keys exist), `#>` and `#>>` (path-based access), and the SQL/JSON path operators `@?` and `@@` for JSONPath queries (12+).

You can index JSONB with GIN, either the default opclass (supports `@>`, `?`, `?|`, `?&` operators) or `jsonb_path_ops` which is smaller and faster but only supports `@>`. There's also the option of an expression B-tree index on `(data->>'field')` for high-cardinality top-level keys you query by equality. Document-store-first-then-relational is a well-worn arc in Indian SaaS: teams prototype fast on a schemaless store, then want transactions, joins and constraints once real money and real reporting depend on the data. JSONB is the bridge, and interviewers probe exactly that migration story, same flexibility as a document store, plus relational guarantees, transactions, and joins on the rest of the schema.

Answer

A foreign key constrains values in a column to match a primary/unique key in another table, Postgres validates the constraint on every INSERT and UPDATE on the child, and on every DELETE/UPDATE of the parent's key. ON DELETE controls what happens when the referenced row is deleted: NO ACTION (default, raise an error if dependent rows exist, checked at end of transaction so deferrable), RESTRICT (same as NO ACTION but checked immediately, not deferrable), CASCADE (delete the dependent rows too, recursively), SET NULL (clear the FK column on the child, requires the column to be NULL-able), SET DEFAULT (set it to the column default, requires the default value to itself satisfy the FK). CASCADE is convenient but dangerous in production, deleting a user can silently delete millions of related rows across half a dozen tables, with each cascade potentially triggering more cascades.

Best practice in 2026: prefer soft deletes (a `deleted_at TIMESTAMPTZ` column on every business entity) and use NO ACTION / RESTRICT so accidental deletes fail loudly during code review and testing. Performance gotcha: a foreign key column SHOULD be indexed on the child side, Postgres does NOT auto-create that index (unlike SQL Server). Every parent DELETE/UPDATE on the referenced key has to scan the child table to verify no orphans would be created; without an index that's a Seq Scan, making each parent delete take seconds on a large child table. This is one of the most common 'why is this query suddenly slow' incidents, somebody added a `REFERENCES users(id)` to a table and didn't add `CREATE INDEX ON child(user_id)`.

Answer

INNER JOIN returns rows where the join condition matches on both sides. LEFT JOIN returns all rows from the left table, with NULLs on the right where there is no match, useful for 'all users and their orders, including users without orders'. RIGHT JOIN is the mirror image and is rarely seen in production code because LEFT JOIN reads more naturally.

FULL JOIN (also called FULL OUTER JOIN) returns all rows from both sides, with NULLs filling unmatched columns on either side, useful for reconciliation queries comparing two datasets. CROSS JOIN returns the Cartesian product, every left row paired with every right row, no condition; size grows as left.rows × right.rows so use with care. The most common interview mistake is filtering a LEFT JOIN'd right table in WHERE: `LEFT JOIN orders o ...

WHERE o.amount > 100` silently turns it into an INNER JOIN because NULL > 100 is false (and NULL filters out). The fix is to move the filter into the ON clause (`LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 100`) or use COALESCE in WHERE (`WHERE COALESCE(o.amount, 0) > 100`). Postgres's planner picks one of three execution strategies: Nested Loop (good for small driving sides with indexed lookups on the other), Hash Join (good for one small side that fits in `work_mem`), or Merge Join (good for already-sorted inputs from an index). EXPLAIN shows which one, if Postgres picks a Hash Join with a 'Hash Batches' value greater than 1, your `work_mem` is too small for the hash table and it's spilling to disk.

-- WRONG: turns LEFT JOIN into INNER JOIN
SELECT u.email, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.amount > 100;  -- users without orders are filtered out!

-- RIGHT: filter inside ON
SELECT u.email, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 100;

Answer

EXPLAIN shows the query plan the planner intends to execute, without running it, so it's safe on any query. EXPLAIN ANALYZE actually runs the query and shows real timings, note that for INSERT/UPDATE/DELETE this commits changes unless wrapped in a transaction that rolls back. EXPLAIN (ANALYZE, BUFFERS) is the production-debug standard, adding I/O information (shared hit means buffer cache hit, shared read means disk read; the ratio tells you whether your working set fits in memory).

Plan nodes you'll see: Seq Scan (full table read, fine for small tables, disaster on big ones), Index Scan (B-tree lookup then heap fetch per row), Index Only Scan (covered by the index, no heap fetch, fastest read pattern), Bitmap Heap Scan (combine multiple indexes via OR/AND of bitmaps, then a single heap pass), Nested Loop / Hash Join / Merge Join, Sort, Aggregate, Hash Aggregate. Read top-down for narrative, but cost flows bottom-up, children execute first, parents combine. Two numbers to watch: 'rows' (planner's estimate) versus 'actual rows', if they differ by orders of magnitude, statistics are stale (run `ANALYZE`), or the query has parameter-correlated filters the planner can't model (consider `CREATE STATISTICS` on the correlated columns, available 10+).

A 10ms query in dev that's 10s in prod almost always shows a Seq Scan on a million-row table, missing index, basically. The other diagnostic gold is loops × rows for inner nodes of a nested loop, 'Index Scan, rows=1, loops=10000' means 10000 round trips through the index, which is usually fine; but 'rows=500, loops=10000' on the inner side is a sign you've picked the wrong join order.

Answer

A partial index covers only rows matching a WHERE clause, making it dramatically smaller and faster to maintain than a full index. The classic use case: most rows of a column are NULL or one common value, and you only query the rare ones. Examples: `CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'PENDING'`, if 99% of orders are COMPLETED, the index might be 1% the size of the full one, and INSERTs to COMPLETED rows skip the index entirely.

Same for `WHERE deleted_at IS NULL` on a soft-delete table, every active-row query qualifies, and the index excludes archived data, so the index stays small even after years of soft-deletes accumulate. A partial index can also enforce conditional uniqueness: `CREATE UNIQUE INDEX one_active_session ON sessions(user_id) WHERE revoked_at IS NULL` allows multiple revoked rows per user but only one active one, far cleaner than enforcing this in application code. The planner uses a partial index only when the query's WHERE clause logically implies the index predicate.

The query must include `status = 'PENDING'` (or a stricter condition like `status = 'PENDING' AND created_at > X`) verbatim; the planner is not very smart about deriving implications, so use the same literal predicate. Verify with `EXPLAIN` that the partial index is actually picked, a common bug is shipping the index but writing the query as `status IN ('PENDING', 'PROCESSING')` against an index whose predicate is just `status = 'PENDING'`, which won't match. In Indian fintechs, partial indexes are how you scale a 100-million-row orders table without bloating disk and memory with indexes on every status value.

-- Only index unfulfilled orders
CREATE INDEX idx_orders_pending
  ON orders(created_at)
  WHERE status IN ('PENDING','PROCESSING');

-- Conditional uniqueness: at most one active session per user
CREATE UNIQUE INDEX one_active_session
  ON sessions(user_id)
  WHERE revoked_at IS NULL;

Answer

A covering index lets a query be answered entirely from the index, with no visit to the heap (the actual table file). In Postgres 11+, you create one with the INCLUDE clause: `CREATE INDEX idx ON orders(user_id) INCLUDE (amount_paise, status)`. The keys (user_id) are searchable and sorted; INCLUDE columns are stored alongside in the index leaf but not part of the search key, so they don't affect index ordering or uniqueness, and you can include columns of types that aren't B-tree-indexable.

The planner picks an 'Index Only Scan' when the visibility map says all relevant pages have no in-progress writes, meaning the index is sufficient without heap re-check. If the visibility map is stale (recently updated table that hasn't been vacuumed), you'll see 'Heap Fetches: N' in EXPLAIN ANALYZE, and you lose the speed advantage; this is why aggressive autovacuum on heavily-updated tables matters even for read performance. Covering indexes shine for hot read paths where you select a small fixed set of columns by a key, e.g. an API endpoint that returns `(amount, status)` for an order, or a dashboard query for `(rating, review_count)` per merchant.

Realistic gain: a covering index turns a 10 ms query (index lookup + heap fetch for 100 rows) into a 1 ms query, meaningful on a hot endpoint hit 1000 times a second. The trade-off: every INCLUDE column inflates the index, slowing INSERT/UPDATE and consuming buffer cache. Don't over-include, pick the 2-3 columns the hot endpoint actually reads.

CREATE INDEX idx_orders_user_summary
  ON orders(user_id)
  INCLUDE (amount_paise, status);

EXPLAIN ANALYZE
SELECT amount_paise, status FROM orders WHERE user_id = 42;
-- Index Only Scan using idx_orders_user_summary  (no Heap Fetch)

Answer

GIN (Generalized Inverted Index) maps each token/element to the list of rows containing it, like a search-engine inverted index. Internally it's a B-tree of keys; each leaf has a posting list (or posting tree for very common keys) of TIDs pointing at heap rows. It's the right choice for 'find rows containing X' queries: JSONB containment (`@>`), array element checks (`tags @> ARRAY['fast']`), and full-text search with tsvector.

Reads are very fast, the inverted layout matches the access pattern exactly, but writes are slow because every changed token requires an index update. GIN has a 'pending list' (`fastupdate=on`, default true) that batches updates, vacuumed lazily, improves write throughput substantially but means queries occasionally have to scan that pending list before the main index. For write-heavy JSONB tables, tune `gin_pending_list_limit` and consider scheduling explicit `pg_gin_clean_pending_list()` calls during low-traffic windows.

GiST (Generalized Search Tree) is a tree of bounding shapes, designed for nearest-neighbour and overlap queries. It's the right choice for geometric / PostGIS data (`ST_DWithin`, `ST_Intersects`), range types (`tstzrange &&` for 'overlaps in time'), and exclusion constraints (e.g. 'no overlapping bookings for the same room' using `EXCLUDE USING GIST (room_id WITH =, booking_period WITH &&)`). GIN beats GiST for static / read-heavy text and JSONB; GiST wins for spatial / range / write-heavy. SP-GiST is the third related type, partitioned space, good for IP-prefix and phone-number-prefix lookups where the data is naturally hierarchical and non-overlapping.

Answer

Autovacuum is a background worker that reclaims storage from dead tuples (left behind by UPDATEs and DELETEs under MVCC) and updates planner statistics via ANALYZE. The default config is aggressive enough for small workloads but falls behind on busy tables, leading to bloat, slower queries, and eventually a forced anti-wraparound vacuum that locks heavily. Three knobs to tune: `autovacuum_vacuum_scale_factor` (default 0.2 = vacuum when 20% of rows are dead, drop to 0.05 or even 0.02 for hot OLTP tables), `autovacuum_vacuum_cost_limit` (default 200, raise to 1000-2000 on SSD-backed servers; the default was written for spinning disks and is way too cautious in 2026), and `autovacuum_naptime` (default 1min, fine for most).

For hot tables, set per-table overrides rather than globalising: `ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 2000)`. Also raise `autovacuum_max_workers` from default 3 to 6-8 on busy clusters so multiple tables can vacuum concurrently. In Postgres 17, the memory-efficient TID storage in VACUUM means it scans much faster on large tables (the old representation used 6 bytes per dead TID; the new one is variable-length with massive savings on dense bloat); you can raise `maintenance_work_mem` to 1-2 GB safely on a modern server.

Monitor `pg_stat_user_tables.n_dead_tup`, the `last_autovacuum` and `last_autoanalyze` columns, and the `autovacuum_count` to confirm it's running and keeping up. If `n_dead_tup` keeps growing and `last_autovacuum` is recent, autovacuum is finishing but not finishing fast enough, increase cost_limit and workers. If `last_autovacuum` is hours old on a busy table, autovacuum is starving for resources elsewhere or being blocked by a long transaction.

Answer

Bloat is the gap between the storage a table actually uses and the storage its live rows would require if packed tightly. It accumulates because UPDATE creates a new tuple version and the old one only frees up after VACUUM, and even then, the heap doesn't shrink, the space is just available for reuse by future inserts/updates. A hot table with high UPDATE churn (counters, status flags, last-seen timestamps) can easily run at 50-80% bloat, doubling the buffer cache pressure and disk reads for the same logical workload.

Index bloat is the parallel problem: dead index entries don't get removed during VACUUM unless you run `VACUUM (INDEX_CLEANUP ON)`, and even then, B-tree pages don't always merge so the index can stay big. Detect it with the `pgstattuple` extension (`SELECT * FROM pgstattuple('orders')` for the heap; `SELECT * FROM pgstatindex('orders_pkey')` for indexes) or by checking `pg_stat_user_tables.n_dead_tup` and comparing `pg_total_relation_size` to the expected size given row count × average row width. Fixes, in order of impact and operational safety: (1) Tune autovacuum to be aggressive on that specific table, usually solves the future-bloat problem without further intervention. (2) For existing bloat, `REINDEX CONCURRENTLY index_name` rebuilds an index online (12+); a huge improvement over the old `REINDEX` that took ACCESS EXCLUSIVE locks. (3) Use `pg_repack` (open source extension), rebuilds the entire table online without long locks; the standard tool used in production at Indian fintechs. It takes a brief lock at the end to swap. (4) `VACUUM FULL`, takes an ACCESS EXCLUSIVE lock blocking all reads and writes; offline maintenance windows only. (5) Redesign, split hot-updated columns into a separate one-to-one table to localise the churn, leaving the wider main table mostly insert-only.

-- Detect bloat
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');

-- Production-safe fix (no long locks)
-- $ pg_repack -d mydb -t orders

-- Last resort (locks table)
VACUUM FULL orders;

Answer

Two access patterns: extract-as-text vs containment. `data->>'email'` extracts a top-level key as text and lets you filter or index on it, `CREATE INDEX ON users ((data->>'email'))` builds an expression B-tree usable for equality. `data @> '{"role":"admin"}'` checks containment and uses a GIN index on the whole column. Pick by query shape: known-key equality goes through an expression B-tree (smaller, faster, supports range, sorted output); 'does this document contain this sub-document' goes through GIN. For 'either key A or key B exists', GIN with the default `jsonb_ops` opclass supports `?`, `?|`, `?&`.

For containment-only workloads, `jsonb_path_ops` is 30-40% smaller and faster, strictly better when you don't need key-existence operators, which is most cases. The Postgres 12+ SQL/JSON path language adds another tool: `data @? '$.items[*] ? (@.price > 100)'` evaluates a JSONPath expression and uses a GIN-backed jsonb_path_ops index. Anti-pattern: don't `WHERE data->'meta'->'flags'->>'active' = 'true'` on every row, that's a Seq Scan, because no GIN index entry has been generated for that specific path.

Either lift that path into a generated column (`active BOOLEAN GENERATED ALWAYS AS ((data->'meta'->'flags'->>'active')::boolean) STORED`) with its own B-tree index, or restructure the JSONB so the flag lives at the top level where containment indexing helps. Schema-design rule of thumb: anything you filter on a million-row table should not be buried three levels deep inside a JSONB blob.

-- Containment query with GIN
CREATE INDEX idx_users_data_gin
  ON users USING GIN (data jsonb_path_ops);

SELECT * FROM users WHERE data @> '{"role":"admin"}';

-- Top-level key extraction with B-tree expression index
CREATE INDEX idx_users_email_btree
  ON users ((data->>'email'));

SELECT * FROM users WHERE data->>'email' = 'a@b.c';

Answer

A CTE is a named subquery you reference in the main query, `WITH recent_orders AS (...) SELECT ... FROM recent_orders`.

Two use cases dominate: (1) readability, split a 4-level nested query into clearly named steps, often more legible than nested subqueries or complex joins, (2) recursive queries, `WITH RECURSIVE` walks tree/graph structures like product categories, comment threads, org charts, friend-of-friend graphs. Big planner change in Postgres 12+: non-recursive CTEs are now inlined by default (the planner treats them like subqueries and can push predicates through), giving them the same performance as the equivalent subquery. Before 12, every CTE was an 'optimisation fence', materialised even when avoidable, so an old gotcha was wrapping a fast subquery in a CTE and making it slower.

If you need the old behaviour (materialise the intermediate result, perhaps to avoid recomputing an expensive scan that's referenced multiple times in the main query), use `WITH cte AS MATERIALIZED (...)`; to force inlining, `AS NOT MATERIALIZED`. Writable CTEs are another power feature: `WITH inserted AS (INSERT INTO orders ... RETURNING id) UPDATE summary SET ...

FROM inserted` performs both writes in a single statement and a single transaction. Recursive example below traverses a categories tree to find all descendants of a node, common in Indian e-commerce category hierarchies. Recursive performance tip: every iteration is appended to a working table; for deep or wide trees, consider denormalising with a closure table.

-- Recursive: all descendants of a category
WITH RECURSIVE descendants AS (
  SELECT id, name, parent_id
  FROM categories
  WHERE id = 42
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM categories c
  JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

Answer

A window function computes a value across a 'window' of rows related to the current row, WITHOUT collapsing rows the way GROUP BY does. The result is a per-row computation that has access to other rows in its 'window', typically a partition. Syntax: `func(...)

OVER (PARTITION BY col ORDER BY col [frame])`. Common functions: ROW_NUMBER (unique sequential), RANK (with ties skipping ranks), DENSE_RANK (no skipping), NTILE (bucket rows into N tiles), LAG/LEAD (previous/next row), FIRST_VALUE/LAST_VALUE/NTH_VALUE, plus standard aggregates SUM/AVG/COUNT/MIN/MAX used as windowed aggregates. Classic use cases: 'top 3 orders per user' (filter on `ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) <= 3`), 'running total of revenue per day' (SUM windowed over an ORDER BY), 'change vs previous month' with LAG, '7-day moving average' with a `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` frame clause.

The frame clause is the under-appreciated power feature, it can be ROWS (physical rows), RANGE (logical based on the ORDER BY value), or GROUPS (peer groups). Performance note: window functions need a sort within each partition; an index on `(partition_col, order_col)` lets the planner skip that sort, sometimes turning a 5-second query into 50 ms. In the merchant-dashboard problems Razorpay and Swiggy interviews like to pose, 'order amount as % of monthly total' or 'rank vs other merchants', window functions replace what would otherwise be a self-join with terrible performance characteristics.

-- Top 3 orders per user, with their running total
SELECT user_id, id AS order_id, amount_paise,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount_paise DESC) AS rank_in_user,
       SUM(amount_paise) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM orders;

Answer

A regular VIEW is a stored SELECT, re-executed every time you query it; it's just a saved query, not a stored result. A MATERIALIZED VIEW caches the result on disk and only refreshes when you say so with `REFRESH MATERIALIZED VIEW name`. Use them for: expensive aggregations a dashboard hits often (daily revenue per merchant, top-10 products, weekly active users), denormalised joins of multiple slow tables, and any query where data freshness within minutes is acceptable.

With `REFRESH MATERIALIZED VIEW CONCURRENTLY`, you can refresh without blocking SELECTs, provided the view has a UNIQUE index that lets the refresh figure out which rows changed. Without CONCURRENTLY, the refresh takes an ACCESS EXCLUSIVE lock and blocks readers for the duration. Trade-offs: storage cost (you're caching the result), refresh cost (full re-execution unless you script incremental refreshes via triggers), and staleness window.

For Indian fintech dashboards that hit 'last 24h revenue' a thousand times a minute, a materialized view refreshed every 5 minutes is 1000x cheaper than re-running the aggregation each time. For sub-second freshness, pair it with an incremental refresh via triggers (update the matview row on every change to the underlying tables) or a tool like pg_ivm (Incremental View Maintenance, Postgres 16+). For workloads where strict consistency matters, consider regular tables maintained by triggers or a streaming-aggregation tool like Materialize / TimescaleDB continuous aggregates instead, gives you sub-second freshness without re-engineering refresh logic.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT DATE_TRUNC('day', created_at) AS day, SUM(amount_paise) AS revenue_paise
FROM orders
WHERE status = 'COMPLETED'
GROUP BY 1;

CREATE UNIQUE INDEX ON daily_revenue (day);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Answer

`SELECT ... FOR UPDATE` takes a row-level write lock on the returned rows, other transactions can read them under MVCC, but any `UPDATE`/`DELETE` or competing `FOR UPDATE` will block until you COMMIT or ROLLBACK. This is the right tool for the read-modify-write pattern: check current state, decide, write.

Without it, two concurrent transactions read the same balance, both decide it's sufficient for a 100-rupee debit, both write balance = (old - 100), and you end up with a double-spend, the lost-update anomaly. Variants: `FOR NO KEY UPDATE` (weaker, doesn't block foreign key validation checks against this row, and is the lock SQL UPDATE takes by default when it doesn't change the PK), `FOR SHARE` (allow other FOR SHARE readers but no writers, useful for read-then-validate patterns), `FOR KEY SHARE` (the weakest, used by FK validation), `FOR UPDATE SKIP LOCKED` (skip locked rows entirely, the basis of any work-queue table in Postgres, used by Sidekiq-pg, river, graphile-worker, gue, etc.), and `FOR UPDATE NOWAIT` (raise an error immediately instead of waiting, good for user-facing operations where you'd rather show 'try again' than hang). At Razorpay-style wallet-debit code, every balance update wraps the read in `SELECT ...

FOR UPDATE` on the wallet row to serialise debits against that specific user. Two interview gotchas: (a) `FOR UPDATE` does NOT prevent inserts of new rows that match your WHERE clause, that's the phantom-read problem, which only SERIALIZABLE or explicit range locks solve. (b) The lock is held until COMMIT/ROLLBACK, so the duration of your transaction is the duration of the lock, never hold it across an external API call.

-- Safe wallet debit
BEGIN;
SELECT balance INTO v_balance FROM wallets WHERE user_id = 42 FOR UPDATE;
IF v_balance < 100 THEN RAISE EXCEPTION 'insufficient funds'; END IF;
UPDATE wallets SET balance = balance - 100 WHERE user_id = 42;
COMMIT;

-- Work queue pattern: pull a job nobody else has
UPDATE jobs SET status = 'PROCESSING'
WHERE id = (SELECT id FROM jobs WHERE status='QUEUED' ORDER BY id
            FOR UPDATE SKIP LOCKED LIMIT 1)
RETURNING *;

Answer

N+1 is when you fetch N parent rows and then run a follow-up query for each one, total N+1 queries instead of 1 or 2. Classic ORM case: `User.find_all()` then iterating users and accessing `user.orders` lazily, where each access fires its own SELECT. With 1000 users, that's 1001 round-trips, at 1ms per query in the same datacenter, that's 1 second of latency the database is barely involved in (each query takes microseconds; the network round-trip dominates).

Fixes: (1) Eager loading, Prisma `include`, SQLAlchemy `selectinload`/`joinedload`, Django `prefetch_related`, Sequelize `include`. Translates to either a single JOIN query or a parent-then-IN-list pair, both far better than per-row queries. (2) Explicit JOIN in raw SQL when you want full control. (3) For deeper nesting (parent → child → grandchild), fetch each level as a separate query and assemble in app code, often faster than a multi-level JOIN because Postgres doesn't have to re-emit parent columns on every child row, and the network payload is smaller. (4) DataLoader-style batching in GraphQL servers, collects requests during a tick and issues a single IN-list lookup. Detection: install `pg_stat_statements`, look for the same query template with very high `calls` count and modest `mean_exec_time`, that's the smoking gun.

APM tools like Datadog, New Relic, or even just `auto_explain` with `log_min_duration` will surface them too. In Indian SaaS apps and admin dashboards, N+1 is by far the most common production performance bug, typically an inner-page render that fires hundreds of queries because someone wrote `posts.forEach(p => fetchAuthor(p.author_id))`.

Answer

Streaming (physical) replication ships WAL records byte-for-byte from primary to replicas. Replicas are exact physical copies, every page identical, only the entire cluster is replicated, all-or-nothing, and they're read-only until promoted. It's fast, simple, and the basis of high availability.

Configure with `primary_conninfo`, optional replication slots to prevent the primary from recycling WAL the replica still needs, and `hot_standby = on` to allow reads on the replica. Logical replication ships logical change records (INSERT row {...}, UPDATE row {...}) decoded from WAL via a publication/subscription model (`CREATE PUBLICATION` on the source, `CREATE SUBSCRIPTION` on the target). You replicate specific tables, you can replicate to a different Postgres major version, and you can use it for zero-downtime upgrades, cross-region selective replication, and feeding downstream consumers (CDC into Kafka via Debezium, into a warehouse like Snowflake or ClickHouse).

Trade-offs: streaming is what you want for HA / read replicas; logical for upgrades, partial replication, and CDC. Logical has gotchas, DDL is NOT replicated (you run it on both sides), large transactions are buffered and can lag, and sequences don't replicate values automatically. Postgres 16 added bidirectional logical replication for the multi-master use case, and Postgres 17 added `pg_createsubscriber` to convert a physical standby into a logical subscriber in place, a big deal for upgrade workflows. Indian fintechs that ran Postgres 13 in 2023 and need to move to 16/17 in 2026 almost always use logical replication for the cutover: spin up the new version as a logical subscriber, let it catch up, switch traffic, decommission old.

Answer

Patroni is an HA template that uses a distributed consensus store (etcd, Consul, or ZooKeeper) to decide which Postgres node is the leader at any time. Architecture: 3 Postgres nodes (one primary, two streaming replicas) + a 3-node etcd cluster + HAProxy or pgbouncer routing traffic to the current leader. Each Patroni agent runs as a sidecar to Postgres, owns the Postgres process lifecycle (start/stop/promote/demote), and updates the DCS with health and lag information every couple of seconds.

On primary failure, Patroni elects a new leader through the DCS (raft consensus via etcd), promotes the most up-to-date replica, fences the old leader so it can't accept writes if it comes back (the classic split-brain risk, Patroni achieves this by writing the leader identity to etcd with a TTL; a deposed leader sees the lease expire and steps down), and the load balancer's health check (Patroni exposes `/master` and `/replica` REST endpoints) flips traffic to the new leader, failover typically takes 10-30 seconds end to end. Key configurations: `synchronous_mode_strict` (no acknowledgement = no commit; you trade availability for zero data loss), `maximum_lag_on_failover` (don't promote a too-far-behind replica, usually a few MB of WAL), `master_start_timeout` (how long to wait for a healthy primary before forcing failover), and pgbouncer/HAProxy callbacks that route writes to the new leader. This is the shape Indian fintech HA converges on, 3 nodes across two or three AZs of a single region plus an async cross-region replica for DR, and it is what infrastructure interviews at Zerodha, Razorpay and Cred probe for. Common gotcha: don't let your etcd live on the same nodes as Postgres, co-located resource contention during heavy load will cause etcd to miss heartbeats, Patroni will think the leader is down, and you'll trigger a spurious failover at the worst possible time.

Answer

Postgres 10+ supports declarative partitioning: define a parent table partitioned BY RANGE / LIST / HASH, then create partitions (`PARTITION OF ... FOR VALUES ...`). The big wins: (1) Smaller per-partition indexes, every B-tree fits in cache, queries hitting one partition are 10-100x faster than on a single huge table because the working set is dramatically smaller. (2) Cheap data lifecycle, `DROP TABLE orders_2024_q1` is O(1), versus a slow `DELETE FROM orders WHERE created_at < '2025-01-01'` that has to lock-scan-mark every row and then autovacuum has to clean up the bloat over hours. (3) Partition pruning, the planner skips partitions whose range doesn't match the WHERE clause, so a query for last week's data touches one partition instead of the whole table. (4) Parallel query, different workers can scan different partitions concurrently.

Costs: every query must include the partition key for pruning to help (an `EXPLAIN` is mandatory before you trust it); foreign keys to a partitioned table aren't supported until 12+ and have caveats; cross-partition uniqueness needs the partition key included in the unique index, so a globally-unique email constraint on a user table partitioned by region needs `UNIQUE (region, email)` instead of just `UNIQUE (email)`. Common shape in Indian e-commerce: orders partitioned by month with `pg_partman` automating partition creation, retention (drop partitions older than N), and analytic table creation. For 17.x, partition pruning at execution time (not just plan time) handles parameterised queries from prepared statements correctly, important for ORM-generated queries where Postgres makes a generic plan.

Don't pre-emptively partition under 50-100 GB; you're adding operational complexity (more files, more autovacuum work, more index objects, more failure modes around partition creation) for no perf win. The threshold rule of thumb: when the working set stops fitting in shared_buffers and queries start hitting disk, that's the moment partitioning starts paying off.

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  user_id BIGINT NOT NULL,
  amount_paise BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_01 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026_02 PARTITION OF orders
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Pruning kicks in only when the WHERE includes the partition key
EXPLAIN SELECT * FROM orders
  WHERE created_at >= '2026-02-15' AND created_at < '2026-02-20';

Answer

The systematic workflow: (1) Identify the query, `pg_stat_statements` ranks queries by total_exec_time; pick the heaviest one whose latency is anomalous, and cross-reference with `pg_stat_activity` if it's currently running. (2) Reproduce with realistic parameters, bound placeholder values matter enormously; the planner picks different plans for selective vs unselective parameters via custom-plan vs generic-plan switching after 5 executions, so a slow case may only appear with specific values. (3) Run `EXPLAIN (ANALYZE, BUFFERS, SETTINGS)` in a session that mimics production GUCs and statistics, and only on a read-replica or off-peak window if the query is expensive, because EXPLAIN ANALYZE actually executes the query. (4) Compare 'rows' (planner estimate) vs 'actual rows', divergence means stale statistics (run `ANALYZE table` and re-check), or a query whose selectivity the planner can't model (consider extended statistics with `CREATE STATISTICS ON (col_a, col_b) FROM tbl` for correlated columns, available 10+). (5) Look for the slow node, usually a Seq Scan on a million-plus-row table, a Sort on a huge intermediate result that spills to disk (you'll see 'Sort Method: external merge Disk:' in the output), or a Nested Loop with the wrong driving side (large outer, small inner, should be the inverse). (6) Try fixes in order: add the right index (often a partial or covering index), rewrite the join order or join type, lift a CTE into a JOIN if MATERIALIZED was forcing a bad plan, add a partial / covering / expression index. (7) Validate with `EXPLAIN ANALYZE` before and after, and confirm the plan in production with `pg_stat_statements` once deployed, sometimes a 'fix' that worked in dev fails in prod because of different data shape. Be paranoid about lock contention, slow on its own and slow under load are different problems; check `pg_stat_activity.wait_event` for waiting transactions, and `pg_locks` if the slowness is intermittent. The `auto_explain` extension can log slow query plans automatically, invaluable for catching transient slowness you can't reproduce on demand.

Answer

5,000 transactions per second with strict consistency is the Razorpay-scale problem in 2026. Stack: (1) Primary write node sized for the workload, modern NVMe-backed instance, 32-64 vCPU, 256 GB RAM, WAL on a separate disk/array from data so fsyncs on commit don't contend with random reads. (2) Synchronous standby in the same region for zero-data-loss failover, asynchronous standbys for reads and cross-region DR. With `synchronous_standby_names = 'ANY 1 (sb1, sb2)'`, the primary waits for any one of the two standbys to ack, keeps you running if a single standby is slow without sacrificing durability. (3) PgBouncer in transaction-pooling mode in front, application connection pools shouldn't exceed worker count, PgBouncer multiplexes thousands of app connections into ~100 Postgres backends. Without PgBouncer, every microservice replica opens its own pool and you exhaust `max_connections` long before you exhaust the database itself. (4) Partition the ledger table by month and the events table by day to keep indexes hot, hot partitions fit in shared_buffers, cold partitions sit on disk; pruning means most queries touch only one partition. (5) Set up Patroni-managed failover with etcd; HAProxy or pgbouncer in front routes writes to the leader by querying the Patroni REST API. (6) Use `synchronous_commit=on` everywhere on money-movement tables, `off` is acceptable for analytics-only schemas where losing a few seconds of writes on crash is fine. (7) Outbox pattern for downstream propagation, write the event row in the same transaction as the business change, separate worker tails it via logical replication into Kafka.

This avoids the dual-write problem where a payment succeeds in Postgres but the Kafka publish fails. (8) Observability: pg_stat_statements, auto_explain on slow queries, query latency p95/p99 SLOs alerted via Grafana / SigNoz / Datadog, and pgwatch2 / pgmetrics for cluster health. Bottlenecks at this scale are almost always (a) lock contention on a small set of hot rows, use SKIP LOCKED queues, splay hot counters into per-hour or per-day rows that aggregate later, or (b) connection storms during traffic spikes, PgBouncer is non-negotiable, and you may need a second PgBouncer layer in front of the first for connection-storm absorption. Test failover under load monthly, failover that works in staging doesn't always work in prod.

Answer

FDW lets Postgres query external data sources as if they were local tables. `postgres_fdw` queries another Postgres instance; `oracle_fdw`, `mysql_fdw`, `mongo_fdw`, `tds_fdw` (MS SQL), `parquet_fdw` (Parquet files on S3), `cstore_fdw` and `clickhousedb_fdw` extend the reach. Mechanism: a foreign server (connection definition), user mapping (credentials per local user), and foreign tables (schema mapping that declares columns). On query, Postgres pushes down what it can, WHERE predicates, ORDER BY, JOINs between two tables on the SAME foreign server, aggregates in 9.6+, LIMIT in 14+.

Pushdown is the difference between a usable FDW and a disaster: without it, Postgres pulls the entire foreign table back over the network and filters locally; with it, the remote system does the filtering and only the matching rows cross the network. Verify with `EXPLAIN VERBOSE` that you see the remote SQL the planner intends to send, look for 'Remote SQL:' in the output. Use cases: cross-region cluster federation (read a warehouse from your OLTP without ETL latency), data migration (gradual cutover via dual-writing during a Oracle-to-Postgres or MySQL-to-Postgres switch), legacy integration (read from a MySQL system you can't yet decommission), and even querying Parquet files directly to avoid a separate analytics warehouse for small datasets.

Gotchas: planner has no remote statistics by default, run `ANALYZE` on foreign tables so the planner knows row counts and column distributions; transactions don't span foreign servers reliably (two-phase commit support is partial and not enabled by default), so treat cross-FDW writes as best-effort, not ACID. Connection pooling on the foreign side matters, every concurrent query opens a connection, so put PgBouncer in front of the remote Postgres too. Indian companies migrating off Oracle to Postgres routinely use `oracle_fdw` to read both stacks during a multi-quarter cutover, slowly moving each table's write path to Postgres while reads federate via FDW.

Answer

A LATERAL join lets the right side of a join reference columns from the left side, like a `for-each` loop in SQL. Without LATERAL, a subquery in FROM can only reference outer tables in its own outer query, not the current row of the join, it would raise 'invalid reference to FROM-clause entry'. The canonical use case: 'for each parent row, get the top-N children'.

Without LATERAL, you need a window function over the entire child table and then filter, Postgres has to materialise rankings for every row before filtering, which is fine for hundreds of parents but disastrous for hundreds of thousands. With LATERAL, the planner can re-enter the child query once per parent with its own index lookup, often 100x faster for selective parents. Other use cases: invoking set-returning functions like `jsonb_array_elements` against a column (`SELECT u.id, e.value FROM users u, LATERAL jsonb_array_elements(u.events) e`, expands every user's events into rows), `unnest()` to explode an array column, `regexp_split_to_table` for tokenising, and generic 'subquery per row' patterns where the inner query has its own ORDER BY / LIMIT.

Beware: LATERAL with `LEFT JOIN LATERAL` is what you need when the right side might return zero rows, without LEFT, parents with no matching children disappear from the result, which is rarely what you want for 'parents with top-N children, including parents with none'. The planner handles LATERAL well when the inner query has an index on the correlation column; without one, you're back to a per-row Seq Scan. For the 'top 3 orders per restaurant' dashboard query that Swiggy and Zomato interviews love to set, LATERAL is the idiom, substantially faster than the equivalent window-function-then-filter pattern.

-- Top 3 most expensive orders per user, fast version
SELECT u.id, u.email, o.id AS order_id, o.amount_paise
FROM users u
CROSS JOIN LATERAL (
  SELECT id, amount_paise
  FROM orders
  WHERE user_id = u.id
  ORDER BY amount_paise DESC
  LIMIT 3
) o;