Question 1

Is PostgreSQL a good choice for new projects in 2026?

Accepted Answer

Yes — for nearly every transactional workload, Postgres is the default and the safe answer. Its type system, extension ecosystem (PostGIS, pgvector, TimescaleDB), JSONB support, and reliability story make it the standard at Razorpay, Swiggy, Zerodha, Cred and almost every well-funded Indian startup. Reach for an alternative only when you have a clear specialised need: ClickHouse for analytics at petabyte scale, DynamoDB / Cassandra for global key-value with strict latency SLOs, or Redis for caches and queues.

Question 2

What is the average salary for PostgreSQL developers in India?

Accepted Answer

₹6-20 LPA in 2026 for backend developers with strong Postgres skills as a differentiator. Dedicated database/DBA roles (replication, performance tuning, large-scale partitioning) pay at the upper end — ₹18-30 LPA at fintechs and unicorns. Cities with the densest demand: Bengaluru, Hyderabad, Pune, Gurugram. Razorpay, Swiggy, Zomato, Zerodha, Cred, Meesho, PhonePe and most YC-backed Indian SaaS companies hire Postgres-strong engineers throughout the year.

Question 3

Should I learn PostgreSQL or MongoDB first?

Accepted Answer

Postgres. The ratio of Indian backend job postings in 2026 favors relational SQL skills roughly 4:1 over MongoDB. Postgres's JSONB type covers most use cases where teams reach for Mongo and gives you transactions, joins and constraints for free. Many companies that started on MongoDB in 2018-2022 have migrated significant workloads to Postgres by 2026 — the inverse is rare.

Question 4

Which PostgreSQL version should I target for interviews?

Accepted Answer

Postgres 16 and 17 are the relevant versions in 2026. Postgres 17 (released September 2024) shipped a rewritten memory-efficient VACUUM that dramatically lowers maintenance_work_mem usage on big tables, faster B-tree scans for IN-list and range queries, incremental backups via `pg_basebackup -i`, `pg_createsubscriber` to convert a physical standby into a logical subscriber in place for low-downtime upgrades, and SQL/JSON merge plus JSON_TABLE features. Knowing the Postgres 12 line additions (declarative partitioning maturity, generated columns, JSON path queries) and Postgres 15+ features (`MERGE` statement, `NULLS NOT DISTINCT`) is also valuable because many production clusters at older Indian companies are still on 13/14/15 and migrating up. Interviewers test version-awareness implicitly — referring to features like 'we use logical replication for upgrades' tells them you've operated a real cluster, not just read tutorials.

Question 5

What are the most common PostgreSQL production incidents to know about?

Accepted Answer

(1) Long-running transactions blocking autovacuum, leading to runaway bloat — fix is to alert on `pg_stat_activity.xact_start` older than 10 minutes and set `idle_in_transaction_session_timeout` to auto-kill idle sessions. (2) Connection exhaustion when an app deploys badly and every replica opens a fresh pool — PgBouncer in transaction pooling prevents it. (3) Missing index on a foreign key column making parent UPDATEs and DELETEs minutes-slow. (4) Bigint vs int overflow when SERIAL hits 2.1 billion rows — always use BIGINT for IDs. (5) `SELECT *` retrieving large unused JSONB / TEXT columns and bloating buffer cache, hurting every query on the cluster. (6) A migration that adds a NOT NULL column without DEFAULT to a billion-row table, taking ACCESS EXCLUSIVE lock for hours. (7) Forgetting `CREATE INDEX CONCURRENTLY` on a hot table and locking writes for the duration of the build. Hiring managers love asking how you'd diagnose and prevent each.

Question 6

How do PostgreSQL and pgvector fit into the AI / RAG stack in 2026?

Accepted Answer

pgvector is a Postgres extension that adds a `vector` type and indexes (IVFFlat, HNSW) for similarity search. In 2026, the majority of Indian startups building RAG-backed AI features (legal-tech, ed-tech, customer support) use pgvector instead of a dedicated vector DB — it co-locates embeddings with relational metadata, removes a service to operate, and the recall vs latency story is competitive with Pinecone / Weaviate for sub-100M-vector workloads. Expect questions on HNSW vs IVFFlat trade-offs in AI-adjacent backend interviews.

Question 7

What is PostgreSQL and how does it differ from MySQL?

Accepted 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 — Razorpay, Swiggy, Zomato, Zerodha, Cred, Meesho, PhonePe and most YC-backed companies built on Postgres from day one.

Question 8

What is ACID and how does PostgreSQL guarantee it?

Accepted 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.

Question 9

Explain MVCC (Multi-Version Concurrency Control) in PostgreSQL.

Accepted 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.

Question 10

What are the transaction isolation levels in PostgreSQL?

Accepted 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 use cases (Razorpay, Zerodha), SERIALIZABLE on the critical money-movement tables is common; 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.

Question 11

What's the difference between CHAR, VARCHAR, and TEXT in PostgreSQL?

Accepted 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.

Question 12

What is a PRIMARY KEY and how does it differ from a UNIQUE constraint?

Accepted 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.

Question 13

What is an index and what types does PostgreSQL support?

Accepted 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'.

Question 14

What is the difference between WHERE and HAVING?

Accepted 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.

Question 15

What is JSONB and how is it different from JSON in PostgreSQL?

Accepted 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. In Indian SaaS products that started with MongoDB and migrated to Postgres (a common 2024-2026 pattern at companies like Khatabook, Meesho, and OkCredit), JSONB is the bridge — same flexibility, plus relational guarantees, transactions, and joins on the rest of the schema.

Question 16

What are foreign keys and what does ON DELETE CASCADE do?

Accepted 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)`.

PostgreSQL Interview Questions

What is PostgreSQL and how does it differ from MySQL?

What is ACID and how does PostgreSQL guarantee it?

Explain MVCC (Multi-Version Concurrency Control) in PostgreSQL.

What are the transaction isolation levels in PostgreSQL?

What's the difference between CHAR, VARCHAR, and TEXT in PostgreSQL?

What is a PRIMARY KEY and how does it differ from a UNIQUE constraint?

What is an index and what types does PostgreSQL support?

What is the difference between WHERE and HAVING?

What is JSONB and how is it different from JSON in PostgreSQL?

What are foreign keys and what does ON DELETE CASCADE do?

What is the difference between INNER JOIN, LEFT JOIN, and FULL JOIN?

What does EXPLAIN do and how do you read its output?

When would you use a partial index versus a full index?

What is a covering index (INCLUDE) and when does it help?

How do GIN and GiST indexes work and when do you choose between them?

What is autovacuum and how do you tune it?

Explain table bloat and how to detect and fix it.

How do you query JSONB efficiently?

What are Common Table Expressions (CTEs) and when should you use them?

What are window functions and how do they differ from GROUP BY?

What is a materialized view and when should you use one?

How do you implement row-level locking with SELECT ... FOR UPDATE?

What is the N+1 query problem and how do you avoid it in Postgres?

What is streaming replication versus logical replication in PostgreSQL?

How do you set up high availability with Patroni?

How do you partition a large table in PostgreSQL and what are the trade-offs?

How do you debug a slow query in production safely?

How would you architect a Postgres cluster for a payments system handling 5,000 TPS?

How do Foreign Data Wrappers (FDW) work and when should you use them?

How do lateral joins work and when are they the right tool?

Companies Hiring PostgreSQL

Salary Insights

Frequently Asked Questions