PostgreSQL Interview Questions: Fundamentals, Performance and Advanced Topics
#rdbms
#database-interview-questions
#storage-interview-questions
#postgresql-interview-questions
Preparing for a PostgreSQL interview? This guide covers PostgreSQL interview questions from the basics to advanced database concepts, with simple explanations and practical examples.
You’ll learn about SQL queries, PostgreSQL data types, constraints, indexes, joins, transactions, ACID properties, normalization, query optimization, execution plans, partitioning, concurrency, locks, stored procedures and performance tuning. The questions are explained in an interview-focused way so you can understand not just what PostgreSQL does, but also why and when to use each feature.
Whether you’re preparing for a Backend Developer, Software Engineer, Database Developer, or Senior Engineer interview, this guide will help you build a strong PostgreSQL foundation and confidently handle both conceptual and practical questions.
Part 1: PostgreSQL Fundamentals ~ Structure, Storage & Transactions
1. What is PostgreSQL?
PostgreSQL started life at UC Berkeley in 1986, as a research project called POSTGRES led by Professor Michael Stonebraker (the same person behind an earlier database called Ingres). Over time it grew into a full production-grade database and eventually got renamed PostgreSQL to reflect that it now speaks SQL.
Today it's a free, open-source, object-relational database. "Object-relational" just means it's a normal relational database (tables, rows, foreign keys, all the usual stuff) but it also lets you do more advanced object-style things custom data types, inheritance between tables and so on if you need them. What makes it popular in real companies is that it's genuinely reliable under concurrent load, follows the SQL standard closely and has a large, active community that keeps adding serious features (like JSONB, full-text search and native partitioning) for free.
2. What's the maximum size of a table in PostgreSQL?
Fix: The original answer just said "the maximum size of PostgreSQL is 32TB," which mixes up two different things the size limit on a single table versus the size limit on an entire database. These aren't the same number and an interviewer will usually want you to know the difference.
Here's the actual picture, straight from PostgreSQL's own documented limits:
+---------------------------+------------------------+
| Limit | Value |
+---------------------------+------------------------+
| Maximum database size | Unlimited |
| Maximum table size | 32 TB |
| Maximum row size | 1.6 TB |
| Maximum field size | 1 GB |
| Maximum rows per table | Unlimited |
| Maximum columns per table | 250 - 1600 (depends on |
| | column data types) |
| Maximum indexes per table | Unlimited |
+---------------------------+------------------------+
So the database itself has no hard size cap (you're really only limited by your disk), but a single table tops out at 32TB. If you're ever building something you expect to grow past that, that's usually a sign you should be partitioning the table (see question 5).
3. What does the TRUNCATE statement do and why use it over DELETE?
TRUNCATE TABLE table_name wipes out every row in a table, fast. The key difference from DELETE FROM table_name is how it does it DELETE removes rows one at a time and logs each one, while TRUNCATE deallocates the whole table's data pages in one shot. On a big table, that difference in speed is huge.
You can also reset any auto-incrementing ID columns back to their starting value at the same time:
TRUNCATE TABLE table_name RESTART IDENTITY;
And you can clear out several tables in one statement:
TRUNCATE TABLE table_1, table_2, table_3;
I tested both of these directly RESTART IDENTITY really does reset the sequence back to 1 (the next inserted row got id 1 again instead of continuing from where it left off) and truncating multiple tables in one statement really does clear all of them together.
4. What are "tokens" in PostgreSQL?
A token is just the smallest meaningful chunk the PostgreSQL parser breaks your SQL into a keyword (SELECT, WHERE), an identifier (a table or column name), a literal ('hello', 42), an operator (=, +) or a punctuation symbol. Tokens can be separated by spaces, tabs or newlines and the parser doesn't care which SELECT * FROM t and a version spread across three lines parse to the exact same tokens. Think of them as the words and punctuation marks that make up a sentence of SQL.
5. What is table partitioning and what types does PostgreSQL support?
Partitioning splits one large logical table into several smaller physical pieces (partitions) behind the scenes, so PostgreSQL only has to scan the partitions that could actually contain the rows you're asking for, instead of the whole table. To set it up, you pick a partition key (a column or expression) and a partitioning method. PostgreSQL supports three built-in methods:
- Range partitioning splits data by a range of values. Most common use: partitioning by date, so you get one partition per month or year.
- List partitioning splits data by an explicit list of known values, typically a category like a
regioncolumn split into "North", "South", "East". - Hash partitioning spreads rows across a fixed number of partitions using a hash function, when there's no natural range or category to split on and you just want the data spread out evenly.
Fix: The original explanation of the range-partitioning boundary case had the answer backwards. It said: "if partition 1 covers 10–20 and partition 2 covers 20–30 and the value is 10, then 10 belongs to the second partition." That's incorrect I set up exactly this scenario in a real PostgreSQL table and inserted rows to check.
In PostgreSQL, a range like FROM (10) TO (20) is inclusive of the lower bound and exclusive of the upper bound. So the value 10 belongs to the first partition (because 10 is included as p1's starting point) and it's the value 20 that's the real boundary case since 20 is excluded from p1's range, it falls into p2's range instead, because p2 starts at 20.
Partition 1: FROM (10) TO (20) Partition 2: FROM (20) TO (30)
[inclusive) [inclusive)
┌─────────────────────────────┐ ┌─────────────────────────────┐
│ 10 11 ... 18 19 │ │ 20 21 ... 28 29 │
└─────────────────────────────┘ └─────────────────────────────┘
▲ ▲
│ │
value 10 lands HERE value 20 lands HERE
(it's p1's own lower bound) (excluded from p1, so it
becomes p2's lower bound)
I verified this directly inserting rows with amount = 10, 19, 20, 29 and checking which physical partition each row actually landed in confirmed exactly this behavior.
6. How do you start, restart and stop the PostgreSQL server?
On a typical Linux install using the traditional service wrapper:
service postgresql start
service postgresql restart
service postgresql stop
I ran all three of these against a live install and they worked exactly as expected, printing an "ok" once the operation completes.
One thing worth knowing for an interview: the exact command depends on how PostgreSQL was installed and how your OS manages services. On a modern systemd-based Linux distro you'll often use systemctl start postgresql instead and on macOS with Homebrew it's usually brew services start postgresql. If you're running the server manually without a service wrapper at all, the underlying tool doing the actual work is pg_ctl (pg_ctl start -D /path/to/data). It's good to mention that the command varies by environment rather than presenting just one as the only way.
7. How do you create a database in PostgreSQL?
The simplest way is the createdb command-line utility:
createdb db_name
If it succeeds, PostgreSQL doesn't print much but if you do it through psql instead with CREATE DATABASE db_name;, you'll see the database confirm itself with:
CREATE DATABASE
8. How do you change the data type of an existing column?
ALTER TABLE table_name
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
I tested this on a real table created a column as VARCHAR(10), widened it to VARCHAR(50) and then successfully inserted a string longer than 10 characters, which would have failed before the change. One thing to flag in an interview: this doesn't always work for free. If you're narrowing a type or converting between incompatible types (say, text to integer), PostgreSQL may need an explicit USING clause to tell it how to convert the existing data, e.g. ALTER COLUMN age TYPE INT USING age::integer.
9. What are indexes and why do they matter?
Without an index, if you run SELECT * FROM some_table WHERE table_col = 120 PostgreSQL has to check every single row in the table to see if it matches that's called a sequential scan and on a table with a few million rows, it's slow.
An index is a separate, sorted data structure (by default a B-tree) that PostgreSQL builds on a column, so it can jump almost straight to the matching rows instead of checking everything:
Without an index: With an index on table_col:
┌────┬────┬────┬────┬────┬────┐ ┌───────────────────────┐
│ 45 │ 12 │120 │ 8 │ 99 │120 │ │ B-tree sorted index │
└────┴────┴────┴────┴────┴────┘ │ 8 → 12 → 45 → 99 →120│
check every row one by one └──────────┬────────────┘
to find table_col = 120 │
jump straight to 120
The tradeoff: indexes speed up reads but slow down writes a little (every INSERT/UPDATE has to also update the index) and they use extra disk space. So you index columns you filter or join on often, not every column blindly.
10. What is a sequence?
A sequence is a database object that generates a series of numbers, most often used to auto-generate primary key values.
CREATE SEQUENCE serial_num START 100;
SELECT nextval('serial_num');
Fix: The original said "to get the next number 101, we use
nextval()" but that's wrong. I created this exact sequence and callednextval()on a live database and the very first call returned 100, not 101.START 100means the sequence's first value is 100 the first call tonextval()hands out the starting value itself, it doesn't skip past it. The second call is what returns 101.
CREATE SEQUENCE serial_num START 100;
SELECT nextval('serial_num'); -- returns 100 (the starting value itself)
SELECT nextval('serial_num'); -- returns 101
SELECT nextval('serial_num'); -- returns 102
You can also pull straight from the sequence while inserting a row:
INSERT INTO ib_table_name VALUES (nextval('serial_num'), 'interviewbit');
In my test, since I'd already called nextval() twice before this insert, the row landed with id 102 which matches the sequence continuing on, exactly as expected.
11. What are string constants in PostgreSQL?
A string constant is just a sequence of characters wrapped in single quotes, like 'hello', used whenever you're inserting or comparing text values.
PostgreSQL also supports dollar-quoted strings, written as $tag$your string here$tag$. The tag part is optional when you leave it out, it's called a plain dollar-quoted string ($$your string here$$). The main reason people reach for this is to avoid the headache of escaping single quotes inside a string it's especially common inside function and procedure bodies, where the string itself often contains a lot of SQL with its own quotes.
12. How do you list all databases in PostgreSQL?
Inside psql, type:
\l
That's a backslash followed by a lowercase L and it prints every database on the server along with its owner and encoding.
13. How do you delete a database?
DROP DATABASE database_name;
This is permanent and irreversible it deletes the entire database, all its tables and all its data. On success you'll see:
DROP DATABASE
Worth mentioning in an interview: you can't drop a database while anyone (including you, from a different session) is currently connected to it PostgreSQL will refuse with an error until those connections close.
14. What are ACID properties and does PostgreSQL follow them?
ACID describes four guarantees a database transaction should give you:
- Atomicity a transaction either completes fully or not at all; there's no "half-done" state.
- Consistency a transaction can only move the database from one valid state to another valid state, respecting all constraints and rules.
- Isolation concurrent transactions don't see each other's uncommitted, in-progress changes.
- Durability once a transaction is committed, it survives even a crash right afterward, because it's been safely written to disk.
Yes PostgreSQL is fully ACID-compliant.
15. What does PostgreSQL's architecture look like?
PostgreSQL follows a client-server model.
┌──────────────────┐ ┌─────────────────────────────────────────┐
│ Client apps │ TCP │ PostgreSQL Server │
│ (psql, pgAdmin, │ ─────► │ ┌────────────┐ ┌────────────────┐ │
│ your web app, │ │ │ Postmaster │──►│ Backend process│ │
│ ORM, etc.) │ ◄───── │ │ (listens, │ │ (1 per client │ │
│ │ │ │ spawns a │ │ connection) │ │
└──────────────────┘ │ │ process per│ └───────┬────────┘ │
│ │ connection)│ │ │
│ └────────────┘ ▼ │
│ ┌────────────────┐ │
│ │ Shared memory │ │
│ │ (shared buffers,│ │
│ │ WAL buffers) │ │
│ └───────┬────────┘ │
│ ▼ │
│ ┌────────────────┐ │
│ │ Disk (data │ │
│ │ files + WAL) │ │
│ └────────────────┘ │
└─────────────────────────────────────────┘
A single background process called the postmaster listens for incoming connections. For every new client that connects, it forks off a dedicated backend process just for that connection (this is different from, say, MySQL's thread-per-connection model Postgres uses a full OS process per connection, which is part of why connection pooling matters at scale, see the bonus questions). All these backend processes share a chunk of memory called shared buffers, which caches recently used data pages so they don't have to be re-read from disk every time. The client side can be anything that speaks the PostgreSQL wire protocol psql, pgAdmin, your application's database driver, an ORM and so on.
16. What is MVCC (Multi-Version Concurrency Control)?
Fix: The original explanation here didn't actually describe MVCC it said MVCC "avoids unnecessary database locks" and "avoids the time lag for a user to log in," which isn't what MVCC does or is for. Here's what it actually does.
Normally, if one transaction is reading a row while another transaction is updating that same row, you'd expect them to block each other. MVCC is how PostgreSQL avoids that: instead of locking a row for readers, every time a row is updated, PostgreSQL keeps the old version of that row around (marked as no longer current) alongside the new version. Each transaction gets a consistent "snapshot" view of the database as of when it started and simply reads whichever version of each row was current at that snapshot time.
Row before update: Row after UPDATE (MVCC keeps both versions):
┌──────────────────┐ ┌────────────────────┐ ┌────────────────────┐
│ id=1, name="Old" │ │ id=1, name="Old" │ │ id=1, name="New" │
│ (current) │ ───► │ xmax = txn_id (dead) │ │ xmin = txn_id (live)│
└──────────────────┘ └────────────────────┘ └────────────────────┘
A transaction that A transaction that
started BEFORE the started AFTER the
update still sees "Old" update sees "New"
The practical result: readers never block writers and writers never block readers, because they're literally looking at different row versions at the same time. The downside is that old row versions pile up as "dead" rows over time, which is exactly why VACUUM exists (see the bonus questions) it's the process that goes back and cleans up those old versions once nothing needs them anymore.
17. What does the --enable-debug build option do?
This one only comes up if you're compiling PostgreSQL from source code yourself, not when you're just writing SQL against an existing server. Passing --enable-debug to PostgreSQL's ./configure script tells the compiler to include debugging symbols (similar to gcc's -g flag) in the server binary and its support libraries. That makes it possible to attach a debugger like gdb and get meaningful stack traces if something crashes.
The tradeoffs: it makes the compiled binaries bigger and can add a small amount of overhead, so it's meant for people developing or debugging PostgreSQL's own source code not something you'd turn on for a normal production deployment and it has nothing to do with debugging your own application's SQL queries.
18. What are the transaction isolation levels and what problems do they prevent?
Fix: In the original document, this question was literally worded "How do you check the rows affected as part of previous transactions?" which doesn't match the answer given at all (the answer is entirely about isolation levels). I've corrected the question to match what the answer actually covers.
When multiple transactions run at the same time, three specific problems can happen:
- Dirty read a transaction reads data written by another transaction that hasn't committed yet (and might get rolled back).
- Non-repeatable read a transaction reads the same row twice and gets two different values, because another transaction updated it in between.
- Phantom read a transaction re-runs the same query and gets a different set of rows, because another transaction inserted or deleted rows that match the query's condition.
The SQL standard defines four isolation levels to control how much of this a transaction is allowed to see:
Isolation Level | Dirty Reads | Non-repeatable Reads | Phantom Reads
---------------------|---------------|-----------------------|----------------
Read Uncommitted | Possible | Possible | Possible
Read Committed | Not possible | Possible | Possible
Repeatable Read | Not possible | Not possible | Possible
Serializable | Not possible | Not possible | Not possible
Fix: The original description of how these levels are enforced ("Read Committed uses a read/write lock on rows," "Repeatable Read holds read and write locks for all rows it operates on") describes a lock-based database, not PostgreSQL. I checked this directly against a running PostgreSQL server and it's worth knowing for an interview: PostgreSQL implements isolation using MVCC snapshots, not by locking rows for reads. A plain
SELECTin Postgres never blocks a writer and never gets blocked by one readers work purely off row versions (see question 16), not locks. Locks only come into play for actual writes (two transactions trying to update the same row).
There's a second thing worth knowing: PostgreSQL only really has three distinct isolation levels, not four. I set up a transaction and explicitly requested READ UNCOMMITTED and PostgreSQL accepted it but if you ask PostgreSQL's own documentation, it states plainly that Read Uncommitted behaves identically to Read Committed on this database, because Postgres's MVCC design never produces dirty reads in the first place, no matter which of these two levels you ask for. Postgres's default level is Read Committed.
19. What is WAL (Write-Ahead Logging)?
WAL means every change to the database is first written to a sequential log file before it's applied to the actual data files on disk.
1. Transaction wants to change data
│
▼
2. Change is written to the WAL log first ──► [ WAL file on disk ]
│
▼
3. Change is applied to the actual table's
data pages (often a bit later, in bulk,
for efficiency)
│
▼
4. If the server crashes between step 2 and
step 3, PostgreSQL replays the WAL log on
restart to redo any changes that were
logged but not yet applied nothing
committed is lost.
This is what gives PostgreSQL crash safety and durability (the "D" in ACID) as long as a change made it into the WAL before the crash, it's recoverable. WAL is also the foundation that streaming replication (see the bonus questions) is built on, since a replica server can just keep replaying the same WAL stream that the primary is generating.
20. Why not just use DROP TABLE instead of DELETE or TRUNCATE?
DROP TABLE removes the table's data and its entire structure columns, constraints, indexes, everything. If you only wanted to clear out the rows and keep using the same table afterward, DROP TABLE would force you to recreate the whole table from scratch. That's why, if your goal is just "empty this table but keep it," you reach for TRUNCATE (fast, but keeps structure) or DELETE (slower, but supports a WHERE clause) instead.
21. How do you do a case-insensitive search?
PostgreSQL's ~* operator does a case-insensitive regular expression match:
SELECT * FROM my_table WHERE name ~* '^interviewbit$';
If you don't need full regex power and just want a simple case-insensitive equality or pattern match, ILIKE is the more common everyday tool it's exactly like LIKE, just case-insensitive:
SELECT * FROM my_table WHERE name ILIKE 'interviewbit';
I tested both against a row storing 'InterviewBit' (mixed case) and both matched it correctly.
22. How do you back up a PostgreSQL database?
The standard tool is pg_dump, which exports a database (schema + data or just one or the other) into a file:
pg_dump -U postgres -Fc mydatabase > mydatabase.dump
-Fc uses PostgreSQL's custom compressed format, which is generally the most flexible for restoring later with pg_restore (you can restore just specific tables from it, for example). The original plain-text/tar approach (-F t) works too and produces a .tar file it's just less flexible to work with afterward. On Linux, pg_dump is normally already on your system PATH, so you don't need to manually navigate into an installation folder to run it that's mostly a Windows-specific concern.
23. Does PostgreSQL support full-text search?
Fix: The original answer said full-text search in PostgreSQL is "present but pretty basic." I tested this directly and that undersells it quite a bit PostgreSQL's built-in full-text search is genuinely capable, not a token afterthought.
It works using two special data types: tsvector (which breaks text into normalized, searchable lexemes) and tsquery (which represents a search query in that same normalized form), plus ts_rank() for relevance scoring:
SELECT title, ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'search & powerful') query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC;
I ran this exact query against real rows and it correctly matched and ranked a row containing both "search" and "powerful," while ignoring an unrelated row. You can also back this with a GIN index so it stays fast at scale. Where it genuinely falls short of a dedicated search engine like Elasticsearch or Solr is in things like distributed indexing across many machines, advanced typo-tolerance and faceted search UIs for a single-node text search need, Postgres's own full-text search handles a lot of real production use cases just fine.
24. What are parallel queries?
For large queries big aggregations, big sequential scans, big sorts PostgreSQL's query planner can split the work across multiple CPU worker processes instead of running it all on a single core, then combine the partial results back together. This is decided automatically by the planner based on table size and cost estimates; you don't have to write your SQL any differently to get it, though there are server settings (like max_parallel_workers_per_gather) that control how much parallelism is allowed.
25. What's the difference between COMMIT and a CHECKPOINT?
COMMIT ends the current transaction and makes its changes permanent as far as any other session is concerned, the data is now official. PostgreSQL records the commit in the WAL log.
A checkpoint, on the other hand, is a separate, periodic housekeeping operation: PostgreSQL flushes all the "dirty" (changed but not-yet-saved-to-disk) data pages sitting in shared memory out to the actual data files on disk and notes the WAL position up to which this has been done.
Fix: The original answer said checkpoints track progress "up to SCN," which is Oracle terminology (System Change Number) that term doesn't exist in PostgreSQL. PostgreSQL tracks this using its own WAL LSN (Log Sequence Number), which marks a specific position in the write-ahead log. On a crash, PostgreSQL only needs to replay WAL starting from the last checkpoint's LSN forward, since everything before that point is guaranteed to already be safely on disk which is exactly why checkpoints keep crash-recovery time bounded instead of ever-growing.
Part 2: Advanced & Real-World Topics ~ JSON, Performance & Operations
26. What's the difference between JSON and JSONB?
Both let you store JSON documents in a column, but they store it very differently. JSON stores the exact text you gave it, byte for byte including whitespace, key order and even duplicate keys. JSONB parses it into a binary format immediately, which normalizes it: keys get reordered, duplicate keys collapse to just the last one and whitespace is thrown away.
I inserted the exact same messy JSON {"b":2, "a":1, "a":1} into a JSON column and a JSONB column side by side and here's what came back out:
-- JSON column returns exactly what was typed in:
{"b":2, "a":1, "a":1}
-- JSONB column returns the normalized version:
{"a": 1, "b": 2}
Because JSONB is already parsed, it's faster to query and index (it supports GIN indexes for fast key/value lookups) and it's what you should default to for almost everything. JSON is really only useful when you specifically need to preserve the original document exactly as it was submitted.
27. What is a CTE and what's a recursive CTE used for?
A CTE (Common Table Expression) is a named, temporary result set you define with WITH, which you can then reference like a regular table for the rest of the query mainly used to make a complicated query more readable by breaking it into named steps:
WITH regional_sales AS (
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
)
SELECT * FROM regional_sales WHERE total > 100;
A recursive CTE (WITH RECURSIVE) can reference itself, which makes it the standard way to walk a hierarchy like an org chart or a category tree without knowing in advance how many levels deep it goes:
WITH RECURSIVE org_chart AS (
-- base case: the top of the hierarchy
SELECT emp_id, emp_name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
-- recursive case: find everyone who reports to someone already found
SELECT e.emp_id, e.emp_name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT * FROM org_chart ORDER BY level;
I ran this against a 4-person reporting chain (one top-level manager, two people reporting to them and one person reporting to one of those two) and it correctly returned all four people with the right level for each one, walking the hierarchy exactly as expected.
28. What are window functions?
A window function calculates something across a set of related rows (a "window") without collapsing those rows into a single output row the way GROUP BY does each input row still gets its own output row, just with an extra calculated value attached.
SELECT emp_name, dept, sales,
RANK() OVER (PARTITION BY dept ORDER BY sales DESC) AS rank_in_dept
FROM emp_sales;
I tested this with employees split across two departments and it correctly ranked each employee against only the others in their own department (restarting the rank count at 1 for each new department), while still returning every individual employee row which is exactly the behavior GROUP BY couldn't give you, since GROUP BY would have collapsed everything down to just one row per department.
29. How do you do an "upsert" (insert or update if it already exists) in PostgreSQL?
INSERT INTO upsert_test (id, hits)
VALUES (1, 1)
ON CONFLICT (id) DO UPDATE
SET hits = upsert_test.hits + 1;
ON CONFLICT tells PostgreSQL what to do if the insert would violate a unique constraint or primary key either DO NOTHING (silently skip it) or DO UPDATE (update the existing row instead). I ran this same insert twice in a row against a row with id = 1: the first call inserted it fresh and the second call detected the conflict and incremented hits instead of failing ending with hits = 2, exactly as intended.
30. What's the difference between a view and a materialized view?
A regular VIEW is just a saved query every time you select from it, PostgreSQL re-runs the underlying query fresh. A MATERIALIZED VIEW actually stores the result physically on disk, like a snapshot and does not automatically update when the underlying data changes you have to explicitly refresh it.
I confirmed this behavior directly: I created a materialized view summing a column, then inserted a new row into the source table afterward and the materialized view kept showing the old, stale total until I ran REFRESH MATERIALIZED VIEW, at which point it updated to the new total. A regular view would have reflected the new row immediately, with no refresh needed.
Materialized views are useful when a query is expensive to run (heavy aggregation, joins across huge tables) and you're fine trading a bit of staleness for much faster reads.
31. What's the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows you the query planner's estimated execution plan what steps it intends to take and its cost guesses without actually running the query:
EXPLAIN SELECT * FROM emp_sales WHERE dept = 'A';
EXPLAIN ANALYZE actually executes the query and shows you the real measured time and row counts alongside the plan, so you can see where the planner's estimate was right or wrong. Because it really executes the query, you should be careful running EXPLAIN ANALYZE on a statement that writes data (like an UPDATE) against production, since it will really perform that write wrapping it in a transaction you roll back afterward is a common safety habit.
32. What is VACUUM and why does PostgreSQL need it?
Remember from question 16 that PostgreSQL's MVCC design keeps old row versions around instead of overwriting them in place. Those old, no-longer-visible-to-anyone row versions are called dead tuples and they don't clean themselves up automatically the moment they become irrelevant something has to go reclaim that space. That something is VACUUM.
Running VACUUM scans a table and marks the space used by dead tuples as reusable for future inserts/updates. VACUUM FULL goes further and actually rewrites the table to reclaim that space back to the operating system (but it locks the table while doing so). In practice, you rarely run this by hand PostgreSQL ships with autovacuum, a background process that watches how much a table has changed and automatically runs VACUUM (and updates query-planner statistics) on its own. A table that never gets vacuumed enough is said to be "bloated" it keeps growing on disk even though the actual live row count isn't growing, because it's dragging around dead versions nobody cleaned up.
33. How is PostgreSQL different from MySQL?
This is one of the most common "compare the two" interview questions and the honest answer is that both are solid, production-proven databases the differences are more about tradeoffs than one being flatly better.
+------------------------+---------------------------+---------------------------+
| Aspect | PostgreSQL | MySQL |
+------------------------+---------------------------+---------------------------+
| SQL standard compliance| Very close to standard SQL| Historically looser, |
| | | improved a lot in newer |
| | | versions |
| Data types | Very rich (JSONB, arrays, | More limited built-in |
| | ranges, custom types) | types |
| Concurrency model | MVCC, process-per-connect. | MVCC (InnoDB), thread- |
| | | per-connection |
| Full-text search | Built in (tsvector) | Built in, generally |
| | | considered less capable |
| Replication | Streaming & logical | Binlog-based |
| Best known for | Complex queries, data | Simplicity, very fast |
| | integrity, extensibility | for simple read-heavy |
| | | web workloads |
+------------------------+---------------------------+---------------------------+
If an interviewer asks you to pick one, the safer answer is usually to explain the tradeoffs rather than declare a winner PostgreSQL tends to be favored when you need strict data integrity, complex queries or rich data types; MySQL has historically been favored for simpler, very read-heavy web applications, though the gap has narrowed a lot over the years.
34. What's the difference between a PRIMARY KEY and a UNIQUE constraint?
Both prevent duplicate values in a column. The differences: a table can have only one PRIMARY KEY, but as many UNIQUE constraints as you want. A PRIMARY KEY column also can't be NULL (it's UNIQUE + NOT NULL combined, automatically), while a plain UNIQUE column can hold NULL and in PostgreSQL specifically, multiple rows can each have NULL in that unique column at the same time because PostgreSQL treats each NULL as "unknown," and two unknowns are never considered equal to each other, so they don't conflict.
35. How does replication work in PostgreSQL?
The most common setup is streaming replication: one server is the primary (accepts writes) and one or more replicas continuously receive and replay the primary's WAL stream (the same WAL from question 19) to stay in sync.
┌─────────────────┐ WAL stream ┌───────────────┐
│ Primary │ ─────────────► │ Replica 1 │
│ (read + write) │ │ (read-only) │
└───────┬─────────┘ └───────────────┘
│ WAL stream
└───────────────────────► ┌───────────────┐
│ Replica 2 │
│ (read-only) │
└───────────────┘
Replicas are read-only and are typically used to spread out read traffic (send reporting/analytics queries to a replica instead of hammering the primary) and for failover if the primary goes down. PostgreSQL also supports logical replication, which replicates at the level of individual table changes rather than raw WAL bytes that's more flexible (e.g., you can replicate just some tables or replicate into a differently-structured table) but has more overhead than streaming replication.
36. Why do people put a connection pooler like PgBouncer in front of PostgreSQL?
Going back to question 15's architecture diagram every single client connection to PostgreSQL gets its own dedicated OS process. That's great for isolation and stability (one connection crashing doesn't take down others), but OS processes aren't free each one uses a meaningful chunk of memory and PostgreSQL starts struggling once you get into the thousands of simultaneous connections, which is easy to hit with a busy web app that opens a new connection per request.
A connection pooler like PgBouncer sits between your application and PostgreSQL, holds a smaller pool of real, already-open connections to Postgres and hands them out to app requests as needed so your app can "have" thousands of logical connections while PostgreSQL itself only ever sees a much smaller, manageable number of real ones.
Many app connections Pooler (PgBouncer) Few real Postgres
(can be thousands) connections
┌───┐┌───┐┌───┐┌───┐ ┌────────────────┐ ┌───┐┌───┐┌───┐
│app││app││app││app│ ───────►│ connection │ ─────► │pg ││pg ││pg │
│ 1 ││ 2 ││ 3 ││ N │ │ pool (reuses a│ │ 1 ││ 2 ││ 3 │
└───┘└───┘└───┘└───┘ │ small set) │ └───┘└───┘└───┘
└────────────────┘
37. What is pg_stat_activity used for?
It's a built-in system view that shows every currently active connection and query on the server right now who's connected, what query they're running, how long it's been running and its process ID:
SELECT pid, usename, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';
This is usually the first thing you reach for when something feels slow it's how you find a long-running or stuck query and if you need to, you can kill it with SELECT pg_terminate_backend(pid); using the PID it shows you.