Given the index below, which query does it speed up? CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

SELECT FROM orders WHERE status = 'pending' ORDER BY created at LIMIT 50

SELECT FROM orders WHERE status = 'shipped' ORDER BY created at

SELECT FROM orders ORDER BY created at DESC

SELECT count( ) FROM orders GROUP BY status

With the composite index below, which query cannot make good use of it? CREATE INDEX idx_users_org_created ON users (org_id, created_at);

WHERE created at > '2026-01-01'

WHERE org id = 7 AND created at > '2026-01-01'

WHERE org id IN (3, 7) AND created at < '2026-06-01'

Transaction A runs the following. Between its two SELECTs, transaction B commits an INSERT into orders. What does A’s second SELECT return? BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM orders; -- returns 100 -- ... transaction B inserts 1 row and commits ... SELECT count(*) FROM orders; -- returns ? COMMIT;

100 — the whole transaction uses one snapshot

101 — committed data is visible to every transaction

A serialization error because the data changed underneath transaction A

Indeterminate — it depends whether B commits before or after A's second SELECT

The app prevents lost updates with optimistic locking as below. How do you know someone else modified the row first? -- read: SELECT id, name, version FROM products WHERE id = 7; (version = 3) UPDATE products SET name = 'New name', version = version + 1 WHERE id = 7 AND version = 3;

Check the affected row count — 0 means the version changed

Postgres throws a conflict error when the version does not match

Run SELECT version once more right before the UPDATE to be sure

You must enable SERIALIZABLE for the version column to work

PostgreSQL quiz — 69 questions with answers and explanations

18 Beginner · 51 Intermediate

Try PostgreSQL quiz

All 69 PostgreSQL quiz questions below come with a short explanation, visible right on the page — no sign-in needed. They're grouped by beginner and intermediate levels and 19 subtopics, so you can revise one part at a time. Choose Try PostgreSQL quiz above to take a scored run and review the questions you got wrong.

What the PostgreSQL quiz covers

Indexes10
Operations8
Queries8
MVCC5
Query Planning5
Transactions5
Locks4
Schema Design4
Data Types3
JSONB3
Partitioning3
Performance3
Constraints2
DML1
Full-text Search1
Replication1
Security1
Storage1
Triggers1

Want long-form answers? Read 71 PostgreSQL interview questions with answers — same topic, in more depth.

Beginner level — 18 PostgreSQL questions

Question 1Indexes

Which queries does a B-tree index (the PostgreSQL default) suit?

A.Checking whether an array contains a given element
B.Computing the nearest distance between coordinate points
C.Comparisons with =, <, >, BETWEEN and ORDER BY sorting
D.Full-text search inside long text content

Answer: C

Ordered comparisons: =, <, <=, >, >=, BETWEEN, IN, and also ORDER BY on that column. Because B-tree stores values already sorted, it supports point lookups, range scans, and correctly ordered results with no extra sort.

References

PostgreSQL docs — Index types

Question 2Query Planning

A table has only a few hundred rows and the filtered column is indexed, yet the planner picks a Seq Scan. Why?

A.Reading the table's few pages directly is cheaper than consulting the index and returning to the heap
B.The planner only uses an index when the query has an ORDER BY or a JOIN alongside the filter
C.The index on a small table has faulty statistics so the planner does not trust it
D.The index is not activated because the table is below PostgreSQL's minimum row threshold

Answer: A

Because it is genuinely cheaper. A few-hundred-row table occupies only a few pages; a Seq Scan reads them directly, while an Index Scan must read the index and then go back to the heap for each row — more steps for the same result. A Seq Scan is not a bad sign; it only is one when it shows up on a large table with a highly selective filter.

References

PostgreSQL docs — Planner/optimizer

Question 3Indexes

The users table has a UNIQUE constraint on email. Do you need an extra index for fast lookups by email?

A.Yes — but only once the table exceeds a million rows
B.No — a UNIQUE constraint creates a unique index that serves lookups
C.It depends — only needed when the query uses = rather than IN
D.Yes — a constraint only checks for duplicates, so a separate index is needed for fast lookups

Answer: B

No need. PostgreSQL enforces a UNIQUE constraint by automatically creating a B-tree unique index on that column — and this index serves WHERE email = ... lookups just like any regular index. Creating an extra index on the same column only doubles write and disk cost without making anything faster.

References

PostgreSQL docs — Unique indexes

Question 4MVCC

Thanks to MVCC, what happens when one transaction reads a table while another writes to the same table?

A.The reader immediately sees the writer's uncommitted data
B.The reader must wait for the writer to commit before it can read
C.Both run in parallel and the reader sees the committed version
D.The writer is deferred until every reader closes its connection

Answer: C

Both run concurrently. MVCC (Multi-Version Concurrency Control) keeps multiple versions of each row: the reader sees the version committed as of its snapshot, the writer creates a new version. So in Postgres, reads don't block writes and writes don't block reads — only two writers touching the same row have to wait on each other.

References

PostgreSQL docs — MVCC introduction

Question 5MVCC

When you UPDATE a row in Postgres, what actually happens at the storage layer?

A.It edits the value in place within the page holding the row
B.It creates a new row version and the old one becomes a dead tuple
C.It deletes the old row from disk and inserts the new one in the free space
D.It writes the change to the WAL, with the table only updated at checkpoint

Answer: B

Postgres never modifies a row in place: UPDATE creates a new row version, and the old version is merely marked expired and becomes a dead tuple — occupying disk space until VACUUM cleans it up. DELETE works the same way: the row is only marked, not removed.

References

PostgreSQL docs — Routine vacuuming

Question 6JSONB

In the query below, how do the `->` and `->>` operators differ?

sql

SELECT data->'user'->>'name' AS name
FROM events
WHERE data->'user'->>'name' = 'An';

A.-> accesses one level while ->> can descend several nested levels
B.-> returns JSON while ->> returns text
C.They return the same thing; ->> is legacy syntax kept around
D.-> is for objects while ->> is for arrays

Answer: B

-> returns a jsonb value, ->> returns text. The common pattern: use -> to walk through intermediate levels, and ->> at the final step when you need to compare or display it as a string.

References

PostgreSQL docs — JSON Functions and Operators

Question 7Data Types

Which type should store an event timestamp in a multi-timezone system?

A.text in ISO 8601 format for readability
B.bigint holding seconds since the epoch
C.timestamp with a separate column naming the timezone
D.timestamptz — storing an absolute instant

Answer: D

Use timestamptz. It stores an absolute instant (normalized to UTC) and displays according to the session's TimeZone, so comparisons and sorting are always correct. timestamp without time zone is only suitable for purely local times such as store opening hours.

References

PostgreSQL Docs — Date/Time Types

Question 8Constraints

How does a `UNIQUE` constraint in PostgreSQL treat `NULL` values?

A.NULL is converted to an empty string before the duplicate check
B.Only one row may hold NULL
C.Several rows with NULL are still accepted
D.Inserting NULL into such a column raises an error

Answer: C

By default UNIQUE allows multiple NULL rows, because in SQL two NULLs are not equal. From PostgreSQL 15 you can change this with UNIQUE NULLS NOT DISTINCT to allow only a single NULL row.

References

PostgreSQL Docs — Unique Constraints

Question 9Indexes

Why not create an index on every column of a table?

A.An index only helps on tables under a million rows
B.An index is disabled automatically when the table is updated often
C.Each index slows down writes and takes storage
D.PostgreSQL can only use one index per query

Answer: C

An index is a trade-off of faster reads — slower writes: every write must update all related indexes, and indexes consume extra storage and cache. An index that no query uses is pure cost with no benefit.

References

PostgreSQL Docs — Introduction to Indexes

Question 10Query Planning

What is the difference between `EXPLAIN` and `EXPLAIN ANALYZE`?

A.ANALYZE actually runs the query and returns measured figures
B.EXPLAIN only works with SELECT while ANALYZE works with everything
C.ANALYZE refreshes the table's statistics before planning
D.ANALYZE displays the plan in a more readable form

Answer: A

EXPLAIN only shows the plan along with estimated cost and row counts. EXPLAIN ANALYZE executes the query and then reports actual time and row counts. A large gap between the estimated rows= and actual rows= is the most important diagnostic clue.

References

PostgreSQL Docs — Using EXPLAIN

Question 11JSONB

What is the difference between the `json` and `jsonb` types?

A.jsonb stores a parsed binary form and can be indexed
B.jsonb can only store objects rather than arrays
C.json supports schema constraints while jsonb does not
D.json allows deeper nesting than jsonb

Answer: A

json stores the raw text (preserving whitespace, key order, duplicate keys) and must be re-parsed on every query. jsonb stores a parsed binary form: slightly slower to write, much faster to read, and it can be GIN-indexed. In almost every case you should choose jsonb.

References

PostgreSQL Docs — JSON Types

Question 12MVCC

Why does PostgreSQL need `VACUUM`?

A.To reorder the rows physically by primary key
B.To rebuild every index after a large batch of writes
C.To compress large text values to save space
D.To reclaim the space of dead row versions

Answer: D

Because MVCC doesn't modify rows in place: an UPDATE creates a new version and marks the old one dead. VACUUM reclaims that space, updates statistics, and most importantly freezes transaction ids to prevent wraparound failures.

References

PostgreSQL Docs — Routine Vacuuming

Question 13Queries

What is the difference between `WHERE` and `HAVING`?

A.WHERE is for columns while HAVING is for expressions
B.HAVING is faster because it filters a smaller dataset
C.WHERE can only be used when the query has no GROUP BY
D.WHERE filters before grouping while HAVING filters after

Answer: D

WHERE filters each row before grouping; HAVING filters each group after the aggregate functions are computed. So HAVING COUNT(*) > 5 is valid while WHERE COUNT(*) > 5 is not. Any condition that can go in WHERE should go there because it is cheaper.

References

PostgreSQL Docs — Table Expressions

Question 14Schema Design

Should an order status column use an `enum` type or a lookup table?

A.An enum is always better because it saves storage
B.A lookup table when the value set still changes or needs extra attributes
C.text with a CHECK constraint is the best option
D.A lookup table is always better because it has a foreign key constraint

Answer: B

Use a lookup table when the set of values can change, needs attached attributes (display label, ordering, color), or needs operators to add values themselves. Use an enum when the set of values is nearly fixed and you want the constraint at the type level.

References

PostgreSQL Docs — Enumerated Types

Question 15Queries

How do `COUNT(*)` and `COUNT(column)` differ?

A.COUNT(column) skips rows where the value is NULL
B.COUNT(*) is slower because it must read every column
C.COUNT(column) counts only the column's distinct values
D.The two forms always give the same result

Answer: A

COUNT(*) counts every row. COUNT(column) counts only rows where that column is not NULL. This is a neat way to do conditional counts: COUNT(*) FILTER (WHERE status = 1) or COUNT(NULLIF(status, 0)).

References

PostgreSQL Docs — Aggregate Functions

Question 16Schema Design

Should an auto-incrementing key use `GENERATED ALWAYS AS IDENTITY` or `serial`?

A.The whole row is moved to a separate large-object area
B.The value is truncated to fit the page size
C.Values over a threshold are moved to a side table and compressed
D.Writing raises an error asking you to use a different type

Answer: C

GENERATED ALWAYS AS IDENTITY is standard SQL syntax and is recommended. serial is just shorthand that creates a sequence and sets it as the default value, so the sequence exists separately and easily causes trouble with permissions and when dropping the column.

References

PostgreSQL Docs — CREATE TABLE (identity columns)

Question 17Transactions

A statement inside a transaction fails. What happens to the remaining statements in it?

A.Create a separate view for each customer
B.Give each customer their own database user with limited permissions
C.A row-level security policy attached to the table
D.Add a customer filter to every query in the application

Answer: C

The whole transaction enters an aborted state: every subsequent statement returns an error until ROLLBACK. To continue after an error you must set a SAVEPOINT beforehand and do ROLLBACK TO SAVEPOINT when the error occurs.

References

PostgreSQL Docs — SAVEPOINT

Question 18Operations

You need to load 10 million rows from CSV into PostgreSQL. What is fastest?

A.As large as possible, near the machine's total memory
B.Enough to hold the working set, commonly around a quarter of RAM
C.A fixed small value since PostgreSQL relies on the OS cache
D.It does not matter because PostgreSQL adjusts it automatically

Answer: B

Use COPY — it is many times faster than row-by-row INSERT because it skips the parse and plan cost for each statement. For data coming from an application, most drivers support COPY … FROM STDIN to stream it without an intermediate file.

References

PostgreSQL Docs — COPY

Intermediate level — 51 PostgreSQL questions

Question 1DML

What is the `RETURNING` clause in PostgreSQL for?

A.Retrieving data from the rows just inserted, updated or deleted
B.Specifying which columns an update statement will modify
C.Rolling the statement back if the affected row count is unexpected
D.Returning the number of rows the statement affected

Answer: A

Retrieving the data of the very rows just affected, within the same statement. That way you get auto-generated identifiers after an insert, or post-update values, without a second query — faster and free of race conditions.

References

PostgreSQL docs — Data manipulation

Question 2Data Types

In PostgreSQL, is `varchar(255)` faster than `text`?

A.Yes; only a length-limited type can be indexed
B.Yes; the length limit allows fixed allocation so access is faster
C.They are not comparable because the two types serve different purposes
D.No; they store identically, the length limit is merely a constraint

Answer: D

No. In PostgreSQL they use the same storage, and the length limit is just a constraint checked on write — so it even costs a little extra. Which type to choose is a data-constraint decision, not a performance one.

References

PostgreSQL docs — Data types

Question 3Indexes

A jsonb column `attrs` is frequently filtered with `attrs @> '{"brand": "x"}'`. Which index type should you create?

A.A B-tree on the attrs column
B.GIN
C.A BRIN on the attrs column
D.A hash index on the attrs column

Answer: B

GIN (CREATE INDEX ... USING gin (attrs)). GIN splits jsonb into child elements and indexes each part, so the @>, ?, ?& operators can hit the index directly. For the same reason, GIN is also the choice for arrays and full-text search.

References

PostgreSQL docs — jsonb indexing

Question 4Indexes

A 500-million-row log table is append-only by time and mostly filtered by `created_at` ranges. Which index is the most economical yet effective?

A.A GIN on created_at for fast time-range lookups
B.A B-tree on created_at, as the safest default
C.A BRIN on created_at
D.A hash on created_at because the table is so large

Answer: C

BRIN (Block Range Index). BRIN stores only the min/max of each block range instead of every row, so with 500 million rows it takes a few MB while an equivalent B-tree would take tens of GB. Prerequisite: column values must correlate with physical order — a time-based append-only log table naturally satisfies this.

References

PostgreSQL docs — BRIN indexes

Question 5Indexes

Given the index below, which query does it speed up?

sql

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

A.SELECT * FROM orders WHERE status = 'shipped' ORDER BY created_at
B.SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 50
C.SELECT count(*) FROM orders GROUP BY status
D.SELECT * FROM orders ORDER BY created_at DESC

Answer: B

Only queries whose condition covers the index WHERE clause — here status = 'pending'. A partial index makes sense when the app repeatedly queries a small subset: unprocessed orders, non-deleted users, pending jobs. The index is many times smaller than a full-table index and carries no write cost for rows outside the condition.

References

PostgreSQL docs — Partial indexes

Question 6Indexes

With the composite index below, which query cannot make good use of it?

sql

CREATE INDEX idx_users_org_created
  ON users (org_id, created_at);

A.WHERE org_id = 7
B.WHERE org_id = 7 AND created_at > '2026-01-01'
C.WHERE created_at > '2026-01-01'
D.WHERE org_id IN (3, 7) AND created_at < '2026-06-01'

Answer: C

Leftmost prefix rule: a composite index (a, b) sorts by a first, then by b within each a group. A query filtering only on b (created_at) has no entry point — the created_at values are scattered across the whole tree. To filter on created_at alone you need a separate index for it.

References

PostgreSQL docs — Multicolumn indexes

Question 7Indexes

Column `email` has a B-tree index but the query below still does a Seq Scan. Why, and what is the right fix?

sql

SELECT * FROM users
WHERE lower(email) = 'an@example.com';

A.The index stores the original email value, not the lower(email) expression — create an expression index on lower(email)
B.The table has not been VACUUMed so the index is temporarily unusable
C.B-tree does not support text — switch to a hash index so the lower function works
D.Missing double quotes around the function name so the planner ignores the index

Answer: A

The index is built on the raw email value, while the query filters on lower(email) — two different things the planner cannot infer. Fix: CREATE INDEX ON users (lower(email)). This is the general reason behind the "don't wrap the column in a function in WHERE" rule — any function wrapping the column (date_trunc, casts, arithmetic) disables the ordinary index by the same mechanism.

References

PostgreSQL docs — Indexes on expressions

Question 8Indexes· select all that apply

Which statements about the cost of adding an index are true? (select all that apply)

A.The more indexes there are, the faster every SELECT becomes
B.An index only costs something when created and nothing afterwards
C.An index no query uses still slows down writes
D.An index takes its own disk space, separate from the table's
E.Every INSERT/UPDATE/DELETE must also maintain the table's related indexes

Answer: C, D, E

An index is a read/write trade-off: reads get faster, but in return every write must also update every index on the table, plus the extra disk space each index occupies. Redundant indexes (no query uses them) are pure cost — so find and drop them periodically via pg_stat_user_indexes.

References

PostgreSQL docs — Indexes introduction

Question 9Query Planning

After bulk-loading tens of millions of rows into a table, familiar queries suddenly pick bad plans. What should you do first?

A.Raise shared_buffers to hold the newly loaded data
B.Run ANALYZE on the table to refresh the planner's statistics
C.REINDEX every index on the table because the new data fragmented them
D.Add new indexes for the columns in the slow queries

Answer: B

Run ANALYZE that_table. The planner estimates costs based on sample statistics in pg_statistic (row count, value distribution, correlations). After a bulk load, the statistics still describe the old table, so estimates are off by orders of magnitude and the planner picks a bad plan. Telltale sign in EXPLAIN ANALYZE: estimated rows wildly different from actual rows.

References

PostgreSQL docs — ANALYZE

Question 10MVCC

A system disabled autovacuum "to avoid performance impact" and ran fine for weeks. What happens in the long run?

A.Only tables with indexes are affected; tables without indexes are fine
B.Postgres re-enables autovacuum automatically after each server restart
C.The tables bloat, queries slow down, and eventually the DB stops accepting writes
D.Nothing serious as long as there is disk space, since dead tuples only take storage

Answer: C

Three stages: (1) dead tuples accumulate → table and indexes bloat, (2) queries slow down as scans skip over dead tuples and planner statistics grow stale, (3) as the transaction ID approaches the 32-bit limit, Postgres runs emergency vacuum and, if that still isn't enough, refuses writes to prevent wraparound. Disabling autovacuum is a debt that will certainly come due.

References

PostgreSQL docs — The autovacuum daemon

Question 11MVCC

You DELETE 90% of a large table but the file size on disk does not shrink. Why?

A.The DELETE has not been committed so the change has not reached disk
B.You need to run REINDEX to free the deleted data
C.Postgres keeps a copy of the deleted data for later point-in-time recovery
D.VACUUM reclaims the space for internal reuse rather than returning it to the OS

Answer: D

DELETE only marks rows as dead tuples. VACUUM reclaims them and keeps the free space inside the file for reuse — it only returns pages to the OS when empty pages sit contiguously at the end of the file. Free space scattered in the middle stays put: this is called table bloat. To actually shrink the file you need VACUUM FULL or pg_repack.

References

PostgreSQL docs — Vacuuming basics

Question 12Transactions

If you set the isolation level to READ UNCOMMITTED in Postgres, what is the actual behavior?

A.A transaction can read another transaction's uncommitted data
B.It behaves exactly like READ COMMITTED
C.Postgres errors because it does not support this isolation level
D.It only applies to READ ONLY transactions

Answer: B

Postgres accepts the syntax but treats it exactly like READ COMMITTED — dirty reads never happen in Postgres because MVCC only exposes committed data. The Postgres default is READ COMMITTED; the other two levels are REPEATABLE READ and SERIALIZABLE.

References

PostgreSQL docs — Transaction isolation

Question 13Transactions

Transaction A runs the following. Between its two SELECTs, transaction B commits an INSERT into orders. What does A’s second SELECT return?

sql

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders;  -- returns 100
-- ... transaction B inserts 1 row and commits ...
SELECT count(*) FROM orders;  -- returns ?
COMMIT;

A.Indeterminate — it depends whether B commits before or after A's second SELECT
B.101 — committed data is visible to every transaction
C.100 — the whole transaction uses one snapshot
D.A serialization error because the data changed underneath transaction A

Answer: C

100. Under REPEATABLE READ, the snapshot is taken at the transaction's first statement and used for the whole transaction — B's commit happened after that point, so A doesn't see it. Under READ COMMITTED (the default), each statement takes a fresh snapshot and the second SELECT would return 101.

References

PostgreSQL docs — Repeatable Read

Question 14Transactions

Two requests both read a balance of 100, compute, and write back — one deduction is lost (lost update). Under READ COMMITTED, what is the pessimistic fix?

A.Wrap both the read and the write in one BEGIN...COMMIT
B.Add a UNIQUE constraint on the balance column to block duplicate writes
C.Read with SELECT ... FOR UPDATE before computing
D.Lower the isolation to the weakest level so writes go faster

Answer: C

SELECT ... FOR UPDATE locks the row at read time: the second transaction touching that row must wait for the first to commit before reading — and then reads the new value. The read-compute-write sequence thus becomes serialized per row. A plain BEGIN...COMMIT is not enough, because a plain SELECT locks nothing.

References

PostgreSQL docs — Row-level locks

Question 15Locks

The app prevents lost updates with optimistic locking as below. How do you know someone else modified the row first?

sql

-- read: SELECT id, name, version FROM products WHERE id = 7;  (version = 3)
UPDATE products
SET name = 'New name', version = version + 1
WHERE id = 7 AND version = 3;

A.You must enable SERIALIZABLE for the version column to work
B.Check the affected row count — 0 means the version changed
C.Postgres throws a conflict error when the version does not match
D.Run SELECT version once more right before the UPDATE to be sure

Answer: B

Look at the number of rows affected by the UPDATE: if it is 0, someone bumped the version first and the version = 3 condition no longer matches — the app re-reads the fresh data and decides to retry or notify the user. No error is raised; checking is the application's responsibility.

References

PostgreSQL docs — Concurrency control

Question 16Locks· select all that apply

You see connections stuck in `idle in transaction` for hours in `pg_stat_activity`. Which consequences are real?

A.Data that transaction has written becomes partly visible to other sessions
B.Postgres rolls the transaction back automatically after 5 idle minutes
C.Every lock that transaction took is still held, blocking other statements
D.That connection occupies a slot in the connection pool for nothing
E.VACUUM cannot clean dead tuples older than that transaction's snapshot

Answer: C, D, E

idle in transaction = a transaction has done BEGIN, run statements, then hangs without committing. Three real consequences: (1) it holds every lock it acquired — blocking other queries and even migrations; (2) it blocks VACUUM from cleaning dead tuples older than its snapshot → cluster-wide bloat; (3) it wastes a connection. Prevent it with idle_in_transaction_session_timeout (off by default) and by auditing code for missing commits.

References

PostgreSQL docs — idle_in_transaction_session_timeout

Question 17JSONB

An orders table stores all order data in a JSONB column. When should a field be promoted to its own relational column?

A.When the field can be null, since JSONB cannot represent null
B.No need to extract — a GIN index makes JSONB as fast as an ordinary column
C.When the field is filtered or joined often and needs constraints and foreign keys
D.When the field's structure varies frequently between records

Answer: C

Promote a field to its own column when it is filtered/joined frequently, needs constraints (NOT NULL, foreign key, CHECK), or needs good planner statistics. Keep in JSONB the structured data that changes often and is rarely queried — the two approaches complement each other, they don't exclude each other.

References

PostgreSQL docs — JSON Types

Question 18Partitioning

A logs table is RANGE-partitioned by month on created_at. What does a query filtering the last 7 days gain?

A.Nothing on reads — partitioning only helps writes
B.Each partition is scanned by its own parallel worker so it is always faster
C.The planner only scans the partitions covering that time range (pruning)
D.Data in older partitions is compressed automatically so it reads faster

Answer: C

Partition pruning: a condition on the partition key (created_at) lets the planner drop old monthly partitions from the plan — the query touches only 1–2 partitions instead of the entire history. Indexes are also smaller per partition, so the hot read path is leaner.

References

PostgreSQL docs — Table Partitioning

Question 19Partitioning

You need to remove all logs older than 12 months from a monthly-partitioned table. Best approach?

A.DELETE by the time condition and then run VACUUM FULL
B.DROP (or DETACH) the old monthly partitions
C.UPDATE to mark deleted = true to avoid locking the table
D.TRUNCATE the whole table and reload the last 12 months

Answer: B

DROP the old partitions (or DETACH first if you want to keep them for archive). This is a near-instant metadata operation. Deleting the same amount of data would create millions of dead tuples, bloat WAL, and require vacuum cleanup — the difference is hours versus milliseconds.

References

PostgreSQL docs — Partition Maintenance

Question 20Replication

The app writes an order to the primary then immediately reads from an async streaming replica but does not see the row. Why?

A.The transaction on the primary has not committed so the replica cannot see it
B.The connection pooler is returning a cached result of an old query
C.The replica applies the WAL behind the primary (replication lag)
D.The replica only refreshes its data at each checkpoint

Answer: C

This is replication lag: with async replication, the commit on the primary returns without waiting for the replica to receive the WAL, so reading immediately from the replica can return stale data. This is normal behavior for a read replica architecture, not a bug — the application must be designed around it.

References

PostgreSQL docs — Log-Shipping Standby Servers

Question 21Operations

Why does a serverless app (each function opening its own connection) need a pooler like PgBouncer in front of Postgres?

A.Because Postgres' TLS handshake is slower than other databases'
B.Each connection is a server-side process, costing RAM and fork overhead
C.Because Postgres has a hard 100-connection limit that cannot be raised
D.Because Postgres uses shared threads so many connections contend for locks

Answer: B

Postgres uses a process-per-connection model: each connection forks its own backend process, costing RAM and startup overhead. Thousands of short-lived functions opening/closing connections will exhaust server resources even though most connections are idle. A pooler keeps a small set of real connections to Postgres and shares them across many clients.

References

PgBouncer docs — Features

Question 22Operations

What does this statement do when a row (user_id=42, day='2026-08-14') with views=5 already exists?

sql

INSERT INTO stats (user_id, day, views)
VALUES (42, '2026-08-14', 1)
ON CONFLICT (user_id, day)
DO UPDATE SET views = stats.views + EXCLUDED.views;

A.It skips the INSERT and leaves views=5 (equivalent to DO NOTHING)
B.It overwrites with views=1 because EXCLUDED replaces the whole old row
C.It raises a unique violation and rolls the transaction back
D.It updates the existing row to views=6

Answer: D

UPSERT: the conflict on the unique constraint (user_id, day) triggers the DO UPDATE branch, accumulating views = 5 + 1 = 6. EXCLUDED is the row that was proposed for insertion but rejected; stats.views is the existing value in the table. The whole thing happens atomically in a single statement.

References

PostgreSQL docs — INSERT (ON CONFLICT clause)

Question 23Indexes

Given an index on `(user_id, created_at)`, can a query filtering only on `created_at` use it?

A.Never; the optimizer always ignores this index
B.Yes, but only when the table has fewer than ten thousand rows
C.Yes, because PostgreSQL reorders the index's columns itself
D.Not efficiently, because the leading column is absent from the filter

Answer: D

Not effectively. A multi-column B-tree index is ordered by the leading column first, so it is only useful when the condition includes the leading column (or a prefix of the column list). Filtering on created_at alone needs a separate index on that column, or reorder to (created_at, user_id) if that is the common query pattern.

References

PostgreSQL Docs — Multicolumn Indexes

Question 24Transactions

What is PostgreSQL's default isolation level and what does it prevent?

A.Repeatable Read — the whole transaction sees one fixed snapshot
B.Serializable — transactions behave as if run one after another
C.Read Committed — each statement sees only committed data
D.Read Uncommitted — allowing uncommitted data to be read

Answer: C

The default is Read Committed: each statement sees a snapshot of the data as of the moment that statement began, so there are no dirty reads. But non-repeatable reads and phantom reads can still occur between two statements in the same transaction.

References

PostgreSQL Docs — Transaction Isolation

Question 25Locks

Two transactions update two rows in opposite order and hang. How does PostgreSQL handle it?

A.Both are aborted to guarantee data consistency
B.Both wait until statement_timeout expires and then both fail
C.The later transaction is queued automatically and rerun
D.It detects the deadlock and aborts one transaction

Answer: D

PostgreSQL detects deadlocks itself: after deadlock_timeout (1 second by default), it builds the lock wait graph, finds the cycle, and aborts one transaction with a deadlock detected error. The aborted transaction must be retried by the application.

References

PostgreSQL Docs — Deadlocks

Question 26Query Planning

The planner chooses a Seq Scan despite a suitable index. What is the most likely reason?

A.The query returns most of the rows so a sequential scan is cheaper
B.The query uses parameters so the optimizer cannot plan it
C.The table has not been VACUUMed so the index is not yet effective
D.The index is corrupt so the optimizer skips it automatically

Answer: A

Usually because the query returns a large fraction of rows: an index scan has to jump randomly between the index and the table, while a seq scan reads sequentially and is faster when most of the data is needed. Other causes: stale statistics, a small table, or a type mismatch forcing a cast in the condition.

References

PostgreSQL Docs — Planner Cost Constants

Question 27Queries

What is `INSERT … ON CONFLICT DO UPDATE` used for?

A.Inserting in batches, splitting automatically past a limit
B.Inserting, or updating if it already exists, in one statement
C.Ignoring every error arising during the insert
D.Saving a copy of the old row before overwriting it

Answer: B

This is an upsert: insert a new row, and if it violates a unique constraint, update the existing row. The advantage over a check-then-write at the application layer is atomicity — there's no window for another transaction to slip in between the two steps.

References

PostgreSQL Docs — INSERT

Question 28Queries

You need the latest row per `user_id` from a log table. What is the efficient PostgreSQL approach?

A.ORDER BY created_at DESC LIMIT 1 per user
B.Fetch everything and filter in the application
C.GROUP BY user_id with MAX(created_at) alongside the other columns
D.DISTINCT ON (user_id) with ORDER BY user_id, created_at DESC

Answer: D

SELECT DISTINCT ON (user_id) * FROM logs ORDER BY user_id, created_at DESC — with an index on (user_id, created_at DESC) this is the cleanest and fastest approach. Two equivalent standard-SQL alternatives: the window function ROW_NUMBER(), or a LATERAL JOIN when the users table is small.

References

PostgreSQL Docs — DISTINCT Clause

Question 29Operations

Why should a web application use a connection pooler with PostgreSQL?

A.Because PostgreSQL does not support many simultaneous connections
B.Because the pooler encrypts traffic between the application and the database
C.Because each connection is its own process, costing meaningful memory
D.Because the pooler automatically routes queries to read replicas

Answer: C

PostgreSQL allocates a separate process per connection, costing a few megabytes of memory and significant setup overhead. A pooler (PgBouncer, or a provider's built-in pooler) keeps a small set of connections ready and reuses them, so thousands of clients share a few dozen real connections.

References

PgBouncer — Features and pooling modes

Question 30Schema Design

For a write-heavy table, should the primary key be `uuid` or `bigint`?

A.uuid, because an auto-incrementing key causes serious contention
B.uuid, because it always gives better write performance than an integer
C.bigint, because uuid cannot be a primary key
D.It depends: bigint is more compact while uuid can be generated client-side

Answer: D

bigint takes 8 bytes and inserts sequentially, so the index stays compact and writes are fast, but it exposes ordering and record counts. uuid takes 16 bytes and can be generated on the client before the write — important for distributed systems. v7 UUIDs eliminate most of the performance downside thanks to their time-based prefix.

References

PostgreSQL Docs — UUID Type

Question 31Queries

From PostgreSQL 12 on, is a CTE (`WITH`) optimized together with the main query?

A.Only when the CTE is declared with the MATERIALIZED keyword
B.Only when the CTE references no indexed table
C.No, a CTE is always materialized into a temporary table
D.Yes, a non-recursive CTE is inlined by default

Answer: D

Since PostgreSQL 12, a non-recursive CTE referenced only once is inlined into the main query, so filter conditions can be pushed down. You can force the behavior with WITH … AS MATERIALIZED or AS NOT MATERIALIZED.

References

PostgreSQL Docs — WITH Queries

Question 32Queries

How does a window function differ from `GROUP BY` when computing group totals?

A.GROUP BY is faster in every case
B.A window function can only compute sums and averages
C.A window function preserves the row count while GROUP BY collapses them
D.A window function cannot be used with ORDER BY

Answer: C

GROUP BY collapses multiple rows into one row per group. A window function computes over a "window" of rows but keeps each row, so you can write queries like "this row's revenue and the total revenue of the month containing it" in a single scan.

References

PostgreSQL Docs — Window Functions

Question 33Performance

What does the `pg_stat_statements` extension provide?

A.A detailed log of each execution with its parameter values
B.The execution plan stored for every query
C.Real-time alerts when a query exceeds a time threshold
D.Aggregated statistics per normalized query shape

Answer: D

It groups queries by their normalized form (stripping parameter values) and aggregates call count, total time, mean time, row count, and cache and disk reads. It is the first tool to reach for when you need to know which queries are consuming resources.

References

PostgreSQL Docs — pg_stat_statements

Question 34Schema Design

What is a generated column (`GENERATED ALWAYS AS … STORED`) used for?

A.Generating a random value for the primary key on insert
B.Marking a column read-only to block updates from the application
C.Precomputing a derived value from other columns and storing it
D.Filling in a value automatically when the column is left empty on insert

Answer: C

A derived column computed by the database from other columns in the same row and stored physically, so it is always consistent and can be indexed. Common use cases: a tsvector column for full-text search, a normalized value for matching, and a line total computed from quantity times unit price.

References

PostgreSQL Docs — Generated Columns

Question 35Performance

How does a materialized view differ from a regular view?

A.It updates itself when the source tables change
B.It stores the result and must be refreshed manually
C.It does not allow indexes on the result
D.It can only hold data from one table

Answer: B

A regular view is just a named query — it re-runs on every read. A materialized view stores the result physically: reads are fast and it can be indexed, but the data is a snapshot from the last refresh and you must call REFRESH MATERIALIZED VIEW.

References

PostgreSQL Docs — Materialized Views

Question 36Partitioning

When should a table be partitioned?

A.When the table is very large and old data must be dropped in bulk
B.When many transactions write to the table concurrently
C.When you need to speed up every query on that table
D.When the table has many columns and each row must shrink

Answer: A

The clearest benefit is data lifecycle management: dropping old data with DROP on a partition instead of DELETEing tens of millions of rows. The second benefit is partition pruning for queries filtering on the partition key. The commonly cited threshold is when a table exceeds a few tens of gigabytes.

References

PostgreSQL Docs — Table Partitioning

Question 37Operations

Where is the most sensible place to set `statement_timeout`?

A.At the cluster level, one value for every connection
B.Do not set it at all so queries always run to completion
C.Per role or per session, differing by workload type
D.Only at the application layer through the driver's timeout

Answer: C

Set it per role: web-serving roles get a short timeout (a few seconds), reporting roles get a long one. ALTER ROLE app_web SET statement_timeout = '5s'. The key point is the timeout must be server-side — cancelling on the client does not stop a running query.

References

PostgreSQL Docs — Client Connection Defaults

Question 38Locks

What are PostgreSQL advisory locks used for?

A.Locking the whole table to block every write
B.Locks by application convention, tied to no data row
C.Locking the schema during a migration to block other DDL
D.Locking a row more strictly than SELECT FOR UPDATE

Answer: B

These are locks agreed on by the application itself, identified by an integer and not tied to any table or row. Typical uses: ensuring only one process runs a scheduled job, or that only one process runs a migration at a time.

References

PostgreSQL Docs — Advisory Locks

Question 39Data Types

When should an array type be used instead of a separate relational table?

A.When elements are frequently added and removed individually
B.When the elements need no independent querying or constraints
C.When the number of elements exceeds a few hundred
D.When the elements' order must be preserved

Answer: B

Use an array when the elements always travel with the parent row and are never queried independently: label lists, permission lists, simple configuration values. If you need joins, foreign key constraints, or extra attributes per element, split them into a separate table.

References

PostgreSQL Docs — Arrays

Question 40Operations

What is the difference between `pg_dump` and a physical backup with WAL archiving?

A.A physical backup can restore onto a different PostgreSQL version
B.A physical backup allows restoring to any point in time
C.pg_dump is faster at every data size
D.pg_dump locks the whole database while it runs

Answer: B

pg_dump exports logical data (SQL statements or a compressed format), can be restored into a different version, and allows selecting individual tables, but it is only a snapshot at run time. Physical backup with WAL archiving allows point-in-time recovery to any moment and is much faster on large databases.

References

PostgreSQL Docs — Continuous Archiving and PITR

Question 41Triggers

When should a trigger be used instead of application-layer logic?

A.When you need to call an external API on every data change
B.When the rule must hold on every write path into the table
C.When you want to reduce how many queries the application sends
D.When the business logic is complex and changes often

Answer: B

Use a trigger for invariants that must hold no matter who writes: change logs for reconciliation, an updated_at column, complex constraint checks across multiple tables. Don't use it for business logic or transformations, and never call external services from inside a trigger.

References

PostgreSQL Docs — Trigger Behavior

Question 42Full-text Search

What are the `tsvector` and `tsquery` types for?

A.It is created automatically on the first write
B.The data is stored in a default collection
C.The write fails because the collection does not exist
D.A warning is issued and the write is skipped

Answer: A

tsvector stores text that has been tokenized and normalized to lexemes along with positions; tsquery represents a search query. The @@ operator matches the two sides, and ts_rank computes a relevance score for ordering results.

References

PostgreSQL Docs — Full Text Search

Question 43Storage

What does PostgreSQL's TOAST mechanism handle?

A.Delete the unnecessary fields from the documents
B.Split the documents into several collections
C.A filter selecting the fields after reading them
D.A projection listing only the needed fields

Answer: D

TOAST handles values that exceed the size that fits in an 8KB page: it compresses first, and if still too large it slices them into chunks stored in a side table. The mechanism is transparent to queries, but it noticeably affects performance when a table has large text or jsonb columns.

References

PostgreSQL Docs — TOAST

Question 44Constraints

You must prevent overlapping bookings for the same room. What is the right tool?

A.Every document in the collection
B.Documents where the field was recently added
C.Documents where the field is present, including those where it is null
D.Documents where the field has a non-null value

Answer: C

Use an exclusion constraint with a range type: EXCLUDE USING gist (room_id WITH =, during WITH &&). The && operator detects overlap, and because the constraint is enforced by the database it holds even when multiple transactions write concurrently.

References

PostgreSQL Docs — Constraints on Ranges

Question 45Query Planning

A query filtering two highly correlated columns (`city` and `country`) gets bad row estimates. What fixes it?

A.Alphabetically by field name for readability
B.Range fields first to narrow the data soonest
C.Equality fields first, then the sort field, then range fields
D.The most selective field first regardless of the query

Answer: C

CREATE STATISTICS stats_name (dependencies) ON city, country FROM addresses and then run ANALYZE. By default the optimizer treats the columns as independent and multiplies their selectivities, so with tightly dependent columns like city and country it underestimates by many times.

References

PostgreSQL Docs — CREATE STATISTICS

Question 46Operations

What is PostgreSQL `LISTEN`/`NOTIFY` suitable for?

A.Only queries that specify no collation
B.Every query on that field regardless of collation
C.Only queries using the same collation
D.Every query, but the result may be wrong

Answer: C

It fits lightweight signalling: notifying that a cache needs refreshing, waking a worker when new work arrives, pushing real-time updates to the UI. Messages are not durable — a process not connected when the notification is sent will miss it.

References

PostgreSQL Docs — NOTIFY

Question 47Queries

How do `WHERE id IN (SELECT ...)` and `WHERE EXISTS (SELECT ...)` differ?

A.Two separate filters combined with $and
B.Add a computed field storing the comparison result
C.$where with a JavaScript function
D.$expr to compare two fields in the same document

Answer: D

Performance-wise, PostgreSQL's optimizer usually turns both into a semi join, so they're equivalent. The real difference is NULL: NOT IN against a subquery set containing NULL never returns any rows, while NOT EXISTS behaves as expected.

References

PostgreSQL Docs — Subquery Expressions

Question 48Security

In PostgreSQL, what is the difference between a role and a user?

A.$inc in one atomic update statement
B.Use a transaction wrapping the read and the write
C.Store each increment as a separate document and total them on read
D.Read the value, add one and write it back

Answer: A

Since PostgreSQL 8.1, CREATE USER is just an alias for CREATE ROLE … LOGIN. A role can contain other roles, giving a group-based permission model: grant privileges to a business role, then assign that role to each login account.

References

PostgreSQL Docs — Database Roles

Question 49Performance

An `orders` table has 100M rows; queries only target `status = pending` (0.1%). Which index fits best?

A.Only the first branch needs an index
B.An index is unnecessary because $or always scans the collection
C.A single compound index covering the fields in every branch is enough
D.Every branch needs an index, otherwise the whole query scans

Answer: D

CREATE INDEX ON orders (created_at) WHERE status = 'pending' — a partial index. It holds only 0.1% of the rows, so it's thousands of times smaller, almost always stays in memory, and only needs updating when a row enters or leaves that status.

References

PostgreSQL Docs — Partial Indexes

Question 50Operations

The app reports "too many connections" at peak. What is the right order of remediation?

A.The limit is applied first and then the result is sorted
B.It always sorts every document before applying the limit
C.It can stop early once it has enough documents from an index
D.The two stages are merged into one index scan every time

Answer: C

Add a connection pooler (PgBouncer in transaction mode) and cap the pool size in each service. Raising max_connections is the wrong reflex: each connection is its own process, so large numbers increase memory consumption and context-switching cost without improving throughput.

References

PostgreSQL Docs — pg_stat_activity

Question 51Queries

Pagination with `OFFSET` gets slower on later pages. How is it fixed?

A.It retries the write until it succeeds
B.It saves failed writes for later replay
C.It retries the write once automatically after a network error
D.It writes to several nodes simultaneously for safety

Answer: C

Switch to cursor pagination (keyset pagination): WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. OFFSET 100000 forces the database to scan and discard 100,000 rows; the comparison condition jumps straight to the position via the index.

References

PostgreSQL Docs — LIMIT and OFFSET

22 more Advanced questions for PostgreSQL

Advanced goes deep on real-world scenarios and the technical pitfalls candidates actually run into. Included with Pro.

Upgrade to Pro

PostgreSQL quiz — 69 questions with answers and explanations

18 Beginner · 51 Intermediate

Try PostgreSQL quiz

What the PostgreSQL quiz covers

Indexes10
Operations8
Queries8
MVCC5
Query Planning5
Transactions5
Locks4
Schema Design4
Data Types3
JSONB3
Partitioning3
Performance3
Constraints2
DML1
Full-text Search1
Replication1
Security1
Storage1
Triggers1

Want long-form answers? Read 71 PostgreSQL interview questions with answers — same topic, in more depth.

Beginner level — 18 PostgreSQL questions

Question 1Indexes

Which queries does a B-tree index (the PostgreSQL default) suit?

A.Checking whether an array contains a given element
B.Computing the nearest distance between coordinate points
C.Comparisons with =, <, >, BETWEEN and ORDER BY sorting
D.Full-text search inside long text content

Answer: C

References

PostgreSQL docs — Index types

Question 2Query Planning

A table has only a few hundred rows and the filtered column is indexed, yet the planner picks a Seq Scan. Why?

A.Reading the table's few pages directly is cheaper than consulting the index and returning to the heap
B.The planner only uses an index when the query has an ORDER BY or a JOIN alongside the filter
C.The index on a small table has faulty statistics so the planner does not trust it
D.The index is not activated because the table is below PostgreSQL's minimum row threshold

Answer: A

References

PostgreSQL docs — Planner/optimizer

Question 3Indexes

The users table has a UNIQUE constraint on email. Do you need an extra index for fast lookups by email?

A.Yes — but only once the table exceeds a million rows
B.No — a UNIQUE constraint creates a unique index that serves lookups
C.It depends — only needed when the query uses = rather than IN
D.Yes — a constraint only checks for duplicates, so a separate index is needed for fast lookups

Answer: B

References

PostgreSQL docs — Unique indexes

Question 4MVCC

Thanks to MVCC, what happens when one transaction reads a table while another writes to the same table?

A.The reader immediately sees the writer's uncommitted data
B.The reader must wait for the writer to commit before it can read
C.Both run in parallel and the reader sees the committed version
D.The writer is deferred until every reader closes its connection

Answer: C

References

PostgreSQL docs — MVCC introduction

Question 5MVCC

When you UPDATE a row in Postgres, what actually happens at the storage layer?

A.It edits the value in place within the page holding the row
B.It creates a new row version and the old one becomes a dead tuple
C.It deletes the old row from disk and inserts the new one in the free space
D.It writes the change to the WAL, with the table only updated at checkpoint

Answer: B

References

PostgreSQL docs — Routine vacuuming

Question 6JSONB

In the query below, how do the `->` and `->>` operators differ?

sql

SELECT data->'user'->>'name' AS name
FROM events
WHERE data->'user'->>'name' = 'An';

A.-> accesses one level while ->> can descend several nested levels
B.-> returns JSON while ->> returns text
C.They return the same thing; ->> is legacy syntax kept around
D.-> is for objects while ->> is for arrays

Answer: B

References

PostgreSQL docs — JSON Functions and Operators

Question 7Data Types

Which type should store an event timestamp in a multi-timezone system?

A.text in ISO 8601 format for readability
B.bigint holding seconds since the epoch
C.timestamp with a separate column naming the timezone
D.timestamptz — storing an absolute instant

Answer: D

References

PostgreSQL Docs — Date/Time Types

Question 8Constraints

How does a `UNIQUE` constraint in PostgreSQL treat `NULL` values?

A.NULL is converted to an empty string before the duplicate check
B.Only one row may hold NULL
C.Several rows with NULL are still accepted
D.Inserting NULL into such a column raises an error

Answer: C

References

PostgreSQL Docs — Unique Constraints

Question 9Indexes

Why not create an index on every column of a table?

A.An index only helps on tables under a million rows
B.An index is disabled automatically when the table is updated often
C.Each index slows down writes and takes storage
D.PostgreSQL can only use one index per query

Answer: C

References

PostgreSQL Docs — Introduction to Indexes

Question 10Query Planning

What is the difference between `EXPLAIN` and `EXPLAIN ANALYZE`?

A.ANALYZE actually runs the query and returns measured figures
B.EXPLAIN only works with SELECT while ANALYZE works with everything
C.ANALYZE refreshes the table's statistics before planning
D.ANALYZE displays the plan in a more readable form

Answer: A

References

PostgreSQL Docs — Using EXPLAIN

Question 11JSONB

What is the difference between the `json` and `jsonb` types?

A.jsonb stores a parsed binary form and can be indexed
B.jsonb can only store objects rather than arrays
C.json supports schema constraints while jsonb does not
D.json allows deeper nesting than jsonb

Answer: A

References

PostgreSQL Docs — JSON Types

Question 12MVCC

Why does PostgreSQL need `VACUUM`?

A.To reorder the rows physically by primary key
B.To rebuild every index after a large batch of writes
C.To compress large text values to save space
D.To reclaim the space of dead row versions

Answer: D

References

PostgreSQL Docs — Routine Vacuuming

Question 13Queries

What is the difference between `WHERE` and `HAVING`?

A.WHERE is for columns while HAVING is for expressions
B.HAVING is faster because it filters a smaller dataset
C.WHERE can only be used when the query has no GROUP BY
D.WHERE filters before grouping while HAVING filters after

Answer: D

References

PostgreSQL Docs — Table Expressions

Question 14Schema Design

Should an order status column use an `enum` type or a lookup table?

A.An enum is always better because it saves storage
B.A lookup table when the value set still changes or needs extra attributes
C.text with a CHECK constraint is the best option
D.A lookup table is always better because it has a foreign key constraint

Answer: B

References

PostgreSQL Docs — Enumerated Types

Question 15Queries

How do `COUNT(*)` and `COUNT(column)` differ?

A.COUNT(column) skips rows where the value is NULL
B.COUNT(*) is slower because it must read every column
C.COUNT(column) counts only the column's distinct values
D.The two forms always give the same result

Answer: A

References

PostgreSQL Docs — Aggregate Functions

Question 16Schema Design

Should an auto-incrementing key use `GENERATED ALWAYS AS IDENTITY` or `serial`?

A.The whole row is moved to a separate large-object area
B.The value is truncated to fit the page size
C.Values over a threshold are moved to a side table and compressed
D.Writing raises an error asking you to use a different type

Answer: C

References

PostgreSQL Docs — CREATE TABLE (identity columns)

Question 17Transactions

A statement inside a transaction fails. What happens to the remaining statements in it?

A.Create a separate view for each customer
B.Give each customer their own database user with limited permissions
C.A row-level security policy attached to the table
D.Add a customer filter to every query in the application

Answer: C

References

PostgreSQL Docs — SAVEPOINT

Question 18Operations

You need to load 10 million rows from CSV into PostgreSQL. What is fastest?

A.As large as possible, near the machine's total memory
B.Enough to hold the working set, commonly around a quarter of RAM
C.A fixed small value since PostgreSQL relies on the OS cache
D.It does not matter because PostgreSQL adjusts it automatically

Answer: B

References

PostgreSQL Docs — COPY

Intermediate level — 51 PostgreSQL questions

Question 1DML

What is the `RETURNING` clause in PostgreSQL for?

A.Retrieving data from the rows just inserted, updated or deleted
B.Specifying which columns an update statement will modify
C.Rolling the statement back if the affected row count is unexpected
D.Returning the number of rows the statement affected

Answer: A

References

PostgreSQL docs — Data manipulation

Question 2Data Types

In PostgreSQL, is `varchar(255)` faster than `text`?

A.Yes; only a length-limited type can be indexed
B.Yes; the length limit allows fixed allocation so access is faster
C.They are not comparable because the two types serve different purposes
D.No; they store identically, the length limit is merely a constraint

Answer: D

References

PostgreSQL docs — Data types

Question 3Indexes

A jsonb column `attrs` is frequently filtered with `attrs @> '{"brand": "x"}'`. Which index type should you create?

A.A B-tree on the attrs column
B.GIN
C.A BRIN on the attrs column
D.A hash index on the attrs column

Answer: B

References

PostgreSQL docs — jsonb indexing

Question 4Indexes

A 500-million-row log table is append-only by time and mostly filtered by `created_at` ranges. Which index is the most economical yet effective?

A.A GIN on created_at for fast time-range lookups
B.A B-tree on created_at, as the safest default
C.A BRIN on created_at
D.A hash on created_at because the table is so large

Answer: C

References

PostgreSQL docs — BRIN indexes

Question 5Indexes

Given the index below, which query does it speed up?

sql

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

A.SELECT * FROM orders WHERE status = 'shipped' ORDER BY created_at
B.SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 50
C.SELECT count(*) FROM orders GROUP BY status
D.SELECT * FROM orders ORDER BY created_at DESC

Answer: B

References

PostgreSQL docs — Partial indexes

Question 6Indexes

With the composite index below, which query cannot make good use of it?

sql

CREATE INDEX idx_users_org_created
  ON users (org_id, created_at);

A.WHERE org_id = 7
B.WHERE org_id = 7 AND created_at > '2026-01-01'
C.WHERE created_at > '2026-01-01'
D.WHERE org_id IN (3, 7) AND created_at < '2026-06-01'

Answer: C

References

PostgreSQL docs — Multicolumn indexes

Question 7Indexes

Column `email` has a B-tree index but the query below still does a Seq Scan. Why, and what is the right fix?

sql

SELECT * FROM users
WHERE lower(email) = 'an@example.com';

A.The index stores the original email value, not the lower(email) expression — create an expression index on lower(email)
B.The table has not been VACUUMed so the index is temporarily unusable
C.B-tree does not support text — switch to a hash index so the lower function works
D.Missing double quotes around the function name so the planner ignores the index

Answer: A

References

PostgreSQL docs — Indexes on expressions

Question 8Indexes· select all that apply

Which statements about the cost of adding an index are true? (select all that apply)

A.The more indexes there are, the faster every SELECT becomes
B.An index only costs something when created and nothing afterwards
C.An index no query uses still slows down writes
D.An index takes its own disk space, separate from the table's
E.Every INSERT/UPDATE/DELETE must also maintain the table's related indexes

Answer: C, D, E

References

PostgreSQL docs — Indexes introduction

Question 9Query Planning

After bulk-loading tens of millions of rows into a table, familiar queries suddenly pick bad plans. What should you do first?

A.Raise shared_buffers to hold the newly loaded data
B.Run ANALYZE on the table to refresh the planner's statistics
C.REINDEX every index on the table because the new data fragmented them
D.Add new indexes for the columns in the slow queries

Answer: B

References

PostgreSQL docs — ANALYZE

Question 10MVCC

A system disabled autovacuum "to avoid performance impact" and ran fine for weeks. What happens in the long run?

A.Only tables with indexes are affected; tables without indexes are fine
B.Postgres re-enables autovacuum automatically after each server restart
C.The tables bloat, queries slow down, and eventually the DB stops accepting writes
D.Nothing serious as long as there is disk space, since dead tuples only take storage

Answer: C

References

PostgreSQL docs — The autovacuum daemon

Question 11MVCC

You DELETE 90% of a large table but the file size on disk does not shrink. Why?

A.The DELETE has not been committed so the change has not reached disk
B.You need to run REINDEX to free the deleted data
C.Postgres keeps a copy of the deleted data for later point-in-time recovery
D.VACUUM reclaims the space for internal reuse rather than returning it to the OS

Answer: D

References

PostgreSQL docs — Vacuuming basics

Question 12Transactions

If you set the isolation level to READ UNCOMMITTED in Postgres, what is the actual behavior?

A.A transaction can read another transaction's uncommitted data
B.It behaves exactly like READ COMMITTED
C.Postgres errors because it does not support this isolation level
D.It only applies to READ ONLY transactions

Answer: B

References

PostgreSQL docs — Transaction isolation

Question 13Transactions

Transaction A runs the following. Between its two SELECTs, transaction B commits an INSERT into orders. What does A’s second SELECT return?

sql

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders;  -- returns 100
-- ... transaction B inserts 1 row and commits ...
SELECT count(*) FROM orders;  -- returns ?
COMMIT;

A.Indeterminate — it depends whether B commits before or after A's second SELECT
B.101 — committed data is visible to every transaction
C.100 — the whole transaction uses one snapshot
D.A serialization error because the data changed underneath transaction A

Answer: C

References

PostgreSQL docs — Repeatable Read

Question 14Transactions

Two requests both read a balance of 100, compute, and write back — one deduction is lost (lost update). Under READ COMMITTED, what is the pessimistic fix?

A.Wrap both the read and the write in one BEGIN...COMMIT
B.Add a UNIQUE constraint on the balance column to block duplicate writes
C.Read with SELECT ... FOR UPDATE before computing
D.Lower the isolation to the weakest level so writes go faster

Answer: C

References

PostgreSQL docs — Row-level locks

Question 15Locks

The app prevents lost updates with optimistic locking as below. How do you know someone else modified the row first?

sql

-- read: SELECT id, name, version FROM products WHERE id = 7;  (version = 3)
UPDATE products
SET name = 'New name', version = version + 1
WHERE id = 7 AND version = 3;

A.You must enable SERIALIZABLE for the version column to work
B.Check the affected row count — 0 means the version changed
C.Postgres throws a conflict error when the version does not match
D.Run SELECT version once more right before the UPDATE to be sure

Answer: B

References

PostgreSQL docs — Concurrency control

Question 16Locks· select all that apply

You see connections stuck in `idle in transaction` for hours in `pg_stat_activity`. Which consequences are real?

A.Data that transaction has written becomes partly visible to other sessions
B.Postgres rolls the transaction back automatically after 5 idle minutes
C.Every lock that transaction took is still held, blocking other statements
D.That connection occupies a slot in the connection pool for nothing
E.VACUUM cannot clean dead tuples older than that transaction's snapshot

Answer: C, D, E

References

PostgreSQL docs — idle_in_transaction_session_timeout

Question 17JSONB

An orders table stores all order data in a JSONB column. When should a field be promoted to its own relational column?

A.When the field can be null, since JSONB cannot represent null
B.No need to extract — a GIN index makes JSONB as fast as an ordinary column
C.When the field is filtered or joined often and needs constraints and foreign keys
D.When the field's structure varies frequently between records

Answer: C

References

PostgreSQL docs — JSON Types

Question 18Partitioning

A logs table is RANGE-partitioned by month on created_at. What does a query filtering the last 7 days gain?

A.Nothing on reads — partitioning only helps writes
B.Each partition is scanned by its own parallel worker so it is always faster
C.The planner only scans the partitions covering that time range (pruning)
D.Data in older partitions is compressed automatically so it reads faster

Answer: C

References

PostgreSQL docs — Table Partitioning

Question 19Partitioning

You need to remove all logs older than 12 months from a monthly-partitioned table. Best approach?

A.DELETE by the time condition and then run VACUUM FULL
B.DROP (or DETACH) the old monthly partitions
C.UPDATE to mark deleted = true to avoid locking the table
D.TRUNCATE the whole table and reload the last 12 months

Answer: B

References

PostgreSQL docs — Partition Maintenance

Question 20Replication

The app writes an order to the primary then immediately reads from an async streaming replica but does not see the row. Why?

A.The transaction on the primary has not committed so the replica cannot see it
B.The connection pooler is returning a cached result of an old query
C.The replica applies the WAL behind the primary (replication lag)
D.The replica only refreshes its data at each checkpoint

Answer: C

References

PostgreSQL docs — Log-Shipping Standby Servers

Question 21Operations

Why does a serverless app (each function opening its own connection) need a pooler like PgBouncer in front of Postgres?

A.Because Postgres' TLS handshake is slower than other databases'
B.Each connection is a server-side process, costing RAM and fork overhead
C.Because Postgres has a hard 100-connection limit that cannot be raised
D.Because Postgres uses shared threads so many connections contend for locks

Answer: B

References

PgBouncer docs — Features

Question 22Operations

What does this statement do when a row (user_id=42, day='2026-08-14') with views=5 already exists?

sql

INSERT INTO stats (user_id, day, views)
VALUES (42, '2026-08-14', 1)
ON CONFLICT (user_id, day)
DO UPDATE SET views = stats.views + EXCLUDED.views;

A.It skips the INSERT and leaves views=5 (equivalent to DO NOTHING)
B.It overwrites with views=1 because EXCLUDED replaces the whole old row
C.It raises a unique violation and rolls the transaction back
D.It updates the existing row to views=6

Answer: D

References

PostgreSQL docs — INSERT (ON CONFLICT clause)

Question 23Indexes

Given an index on `(user_id, created_at)`, can a query filtering only on `created_at` use it?

A.Never; the optimizer always ignores this index
B.Yes, but only when the table has fewer than ten thousand rows
C.Yes, because PostgreSQL reorders the index's columns itself
D.Not efficiently, because the leading column is absent from the filter

Answer: D

References

PostgreSQL Docs — Multicolumn Indexes

Question 24Transactions

What is PostgreSQL's default isolation level and what does it prevent?

A.Repeatable Read — the whole transaction sees one fixed snapshot
B.Serializable — transactions behave as if run one after another
C.Read Committed — each statement sees only committed data
D.Read Uncommitted — allowing uncommitted data to be read

Answer: C

References

PostgreSQL Docs — Transaction Isolation

Question 25Locks

Two transactions update two rows in opposite order and hang. How does PostgreSQL handle it?

A.Both are aborted to guarantee data consistency
B.Both wait until statement_timeout expires and then both fail
C.The later transaction is queued automatically and rerun
D.It detects the deadlock and aborts one transaction

Answer: D

References

PostgreSQL Docs — Deadlocks

Question 26Query Planning

The planner chooses a Seq Scan despite a suitable index. What is the most likely reason?

A.The query returns most of the rows so a sequential scan is cheaper
B.The query uses parameters so the optimizer cannot plan it
C.The table has not been VACUUMed so the index is not yet effective
D.The index is corrupt so the optimizer skips it automatically

Answer: A

References

PostgreSQL Docs — Planner Cost Constants

Question 27Queries

What is `INSERT … ON CONFLICT DO UPDATE` used for?

A.Inserting in batches, splitting automatically past a limit
B.Inserting, or updating if it already exists, in one statement
C.Ignoring every error arising during the insert
D.Saving a copy of the old row before overwriting it

Answer: B

References

PostgreSQL Docs — INSERT

Question 28Queries

You need the latest row per `user_id` from a log table. What is the efficient PostgreSQL approach?

A.ORDER BY created_at DESC LIMIT 1 per user
B.Fetch everything and filter in the application
C.GROUP BY user_id with MAX(created_at) alongside the other columns
D.DISTINCT ON (user_id) with ORDER BY user_id, created_at DESC

Answer: D

References

PostgreSQL Docs — DISTINCT Clause

Question 29Operations

Why should a web application use a connection pooler with PostgreSQL?

A.Because PostgreSQL does not support many simultaneous connections
B.Because the pooler encrypts traffic between the application and the database
C.Because each connection is its own process, costing meaningful memory
D.Because the pooler automatically routes queries to read replicas

Answer: C

References

PgBouncer — Features and pooling modes

Question 30Schema Design

For a write-heavy table, should the primary key be `uuid` or `bigint`?

A.uuid, because an auto-incrementing key causes serious contention
B.uuid, because it always gives better write performance than an integer
C.bigint, because uuid cannot be a primary key
D.It depends: bigint is more compact while uuid can be generated client-side

Answer: D

References

PostgreSQL Docs — UUID Type

Question 31Queries

From PostgreSQL 12 on, is a CTE (`WITH`) optimized together with the main query?

A.Only when the CTE is declared with the MATERIALIZED keyword
B.Only when the CTE references no indexed table
C.No, a CTE is always materialized into a temporary table
D.Yes, a non-recursive CTE is inlined by default

Answer: D

References

PostgreSQL Docs — WITH Queries

Question 32Queries

How does a window function differ from `GROUP BY` when computing group totals?

A.GROUP BY is faster in every case
B.A window function can only compute sums and averages
C.A window function preserves the row count while GROUP BY collapses them
D.A window function cannot be used with ORDER BY

Answer: C

References

PostgreSQL Docs — Window Functions

Question 33Performance

What does the `pg_stat_statements` extension provide?

A.A detailed log of each execution with its parameter values
B.The execution plan stored for every query
C.Real-time alerts when a query exceeds a time threshold
D.Aggregated statistics per normalized query shape

Answer: D

References

PostgreSQL Docs — pg_stat_statements

Question 34Schema Design

What is a generated column (`GENERATED ALWAYS AS … STORED`) used for?

A.Generating a random value for the primary key on insert
B.Marking a column read-only to block updates from the application
C.Precomputing a derived value from other columns and storing it
D.Filling in a value automatically when the column is left empty on insert

Answer: C

References

PostgreSQL Docs — Generated Columns

Question 35Performance

How does a materialized view differ from a regular view?

A.It updates itself when the source tables change
B.It stores the result and must be refreshed manually
C.It does not allow indexes on the result
D.It can only hold data from one table

Answer: B

References

PostgreSQL Docs — Materialized Views

Question 36Partitioning

When should a table be partitioned?

A.When the table is very large and old data must be dropped in bulk
B.When many transactions write to the table concurrently
C.When you need to speed up every query on that table
D.When the table has many columns and each row must shrink

Answer: A

References

PostgreSQL Docs — Table Partitioning

Question 37Operations

Where is the most sensible place to set `statement_timeout`?

A.At the cluster level, one value for every connection
B.Do not set it at all so queries always run to completion
C.Per role or per session, differing by workload type
D.Only at the application layer through the driver's timeout

Answer: C

References

PostgreSQL Docs — Client Connection Defaults

Question 38Locks

What are PostgreSQL advisory locks used for?

A.Locking the whole table to block every write
B.Locks by application convention, tied to no data row
C.Locking the schema during a migration to block other DDL
D.Locking a row more strictly than SELECT FOR UPDATE

Answer: B

References

PostgreSQL Docs — Advisory Locks

Question 39Data Types

When should an array type be used instead of a separate relational table?

A.When elements are frequently added and removed individually
B.When the elements need no independent querying or constraints
C.When the number of elements exceeds a few hundred
D.When the elements' order must be preserved

Answer: B

References

PostgreSQL Docs — Arrays

Question 40Operations

What is the difference between `pg_dump` and a physical backup with WAL archiving?

A.A physical backup can restore onto a different PostgreSQL version
B.A physical backup allows restoring to any point in time
C.pg_dump is faster at every data size
D.pg_dump locks the whole database while it runs

Answer: B

References

PostgreSQL Docs — Continuous Archiving and PITR

Question 41Triggers

When should a trigger be used instead of application-layer logic?

A.When you need to call an external API on every data change
B.When the rule must hold on every write path into the table
C.When you want to reduce how many queries the application sends
D.When the business logic is complex and changes often

Answer: B

References

PostgreSQL Docs — Trigger Behavior

Question 42Full-text Search

What are the `tsvector` and `tsquery` types for?

A.It is created automatically on the first write
B.The data is stored in a default collection
C.The write fails because the collection does not exist
D.A warning is issued and the write is skipped

Answer: A

References

PostgreSQL Docs — Full Text Search

Question 43Storage

What does PostgreSQL's TOAST mechanism handle?

A.Delete the unnecessary fields from the documents
B.Split the documents into several collections
C.A filter selecting the fields after reading them
D.A projection listing only the needed fields

Answer: D

References

PostgreSQL Docs — TOAST

Question 44Constraints

You must prevent overlapping bookings for the same room. What is the right tool?

A.Every document in the collection
B.Documents where the field was recently added
C.Documents where the field is present, including those where it is null
D.Documents where the field has a non-null value

Answer: C

References

PostgreSQL Docs — Constraints on Ranges

Question 45Query Planning

A query filtering two highly correlated columns (`city` and `country`) gets bad row estimates. What fixes it?

A.Alphabetically by field name for readability
B.Range fields first to narrow the data soonest
C.Equality fields first, then the sort field, then range fields
D.The most selective field first regardless of the query

Answer: C

References

PostgreSQL Docs — CREATE STATISTICS

Question 46Operations

What is PostgreSQL `LISTEN`/`NOTIFY` suitable for?

A.Only queries that specify no collation
B.Every query on that field regardless of collation
C.Only queries using the same collation
D.Every query, but the result may be wrong

Answer: C

References

PostgreSQL Docs — NOTIFY

Question 47Queries

How do `WHERE id IN (SELECT ...)` and `WHERE EXISTS (SELECT ...)` differ?

A.Two separate filters combined with $and
B.Add a computed field storing the comparison result
C.$where with a JavaScript function
D.$expr to compare two fields in the same document

Answer: D

References

PostgreSQL Docs — Subquery Expressions

Question 48Security

In PostgreSQL, what is the difference between a role and a user?

A.$inc in one atomic update statement
B.Use a transaction wrapping the read and the write
C.Store each increment as a separate document and total them on read
D.Read the value, add one and write it back

Answer: A

References

PostgreSQL Docs — Database Roles

Question 49Performance

An `orders` table has 100M rows; queries only target `status = pending` (0.1%). Which index fits best?

A.Only the first branch needs an index
B.An index is unnecessary because $or always scans the collection
C.A single compound index covering the fields in every branch is enough
D.Every branch needs an index, otherwise the whole query scans

Answer: D

References

PostgreSQL Docs — Partial Indexes

Question 50Operations

The app reports "too many connections" at peak. What is the right order of remediation?

A.The limit is applied first and then the result is sorted
B.It always sorts every document before applying the limit
C.It can stop early once it has enough documents from an index
D.The two stages are merged into one index scan every time

Answer: C

References

PostgreSQL Docs — pg_stat_activity

Question 51Queries

Pagination with `OFFSET` gets slower on later pages. How is it fixed?

A.It retries the write until it succeeds
B.It saves failed writes for later replay
C.It retries the write once automatically after a network error
D.It writes to several nodes simultaneously for safety

Answer: C

References

PostgreSQL Docs — LIMIT and OFFSET

22 more Advanced questions for PostgreSQL

Advanced goes deep on real-world scenarios and the technical pitfalls candidates actually run into. Included with Pro.

Upgrade to Pro

Beginner level — 18 PostgreSQL questions

Which queries does a B-tree index (the PostgreSQL default) suit?

A table has only a few hundred rows and the filtered column is indexed, yet the planner picks a Seq Scan. Why?

The users table has a UNIQUE constraint on email. Do you need an extra index for fast lookups by email?

Thanks to MVCC, what happens when one transaction reads a table while another writes to the same table?

When you UPDATE a row in Postgres, what actually happens at the storage layer?

In the query below, how do the -> and ->> operators differ?

Which type should store an event timestamp in a multi-timezone system?

How does a UNIQUE constraint in PostgreSQL treat NULL values?

Why not create an index on every column of a table?

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

What is the difference between the json and jsonb types?

Why does PostgreSQL need VACUUM?

What is the difference between WHERE and HAVING?

Should an order status column use an enum type or a lookup table?

How do COUNT(*) and COUNT(column) differ?

Should an auto-incrementing key use GENERATED ALWAYS AS IDENTITY or serial?

A statement inside a transaction fails. What happens to the remaining statements in it?

You need to load 10 million rows from CSV into PostgreSQL. What is fastest?

Intermediate level — 51 PostgreSQL questions

What is the RETURNING clause in PostgreSQL for?

In PostgreSQL, is varchar(255) faster than text?

A jsonb column attrs is frequently filtered with attrs @> '{"brand": "x"}'. Which index type should you create?

A 500-million-row log table is append-only by time and mostly filtered by created_at ranges. Which index is the most economical yet effective?

Given the index below, which query does it speed up?

With the composite index below, which query cannot make good use of it?

Column email has a B-tree index but the query below still does a Seq Scan. Why, and what is the right fix?

Which statements about the cost of adding an index are true? (select all that apply)

After bulk-loading tens of millions of rows into a table, familiar queries suddenly pick bad plans. What should you do first?

A system disabled autovacuum "to avoid performance impact" and ran fine for weeks. What happens in the long run?

You DELETE 90% of a large table but the file size on disk does not shrink. Why?

If you set the isolation level to READ UNCOMMITTED in Postgres, what is the actual behavior?

Transaction A runs the following. Between its two SELECTs, transaction B commits an INSERT into orders. What does A’s second SELECT return?

Two requests both read a balance of 100, compute, and write back — one deduction is lost (lost update). Under READ COMMITTED, what is the pessimistic fix?

The app prevents lost updates with optimistic locking as below. How do you know someone else modified the row first?

You see connections stuck in idle in transaction for hours in pg_stat_activity. Which consequences are real?

An orders table stores all order data in a JSONB column. When should a field be promoted to its own relational column?

A logs table is RANGE-partitioned by month on created_at. What does a query filtering the last 7 days gain?

You need to remove all logs older than 12 months from a monthly-partitioned table. Best approach?

The app writes an order to the primary then immediately reads from an async streaming replica but does not see the row. Why?

Why does a serverless app (each function opening its own connection) need a pooler like PgBouncer in front of Postgres?

What does this statement do when a row (user_id=42, day='2026-08-14') with views=5 already exists?

Given an index on (user_id, created_at), can a query filtering only on created_at use it?

What is PostgreSQL's default isolation level and what does it prevent?

Two transactions update two rows in opposite order and hang. How does PostgreSQL handle it?

The planner chooses a Seq Scan despite a suitable index. What is the most likely reason?

What is INSERT … ON CONFLICT DO UPDATE used for?

You need the latest row per user_id from a log table. What is the efficient PostgreSQL approach?

Why should a web application use a connection pooler with PostgreSQL?

For a write-heavy table, should the primary key be uuid or bigint?

From PostgreSQL 12 on, is a CTE (WITH) optimized together with the main query?

How does a window function differ from GROUP BY when computing group totals?

What does the pg_stat_statements extension provide?

What is a generated column (GENERATED ALWAYS AS … STORED) used for?

How does a materialized view differ from a regular view?

When should a table be partitioned?

Where is the most sensible place to set statement_timeout?

What are PostgreSQL advisory locks used for?

When should an array type be used instead of a separate relational table?

What is the difference between pg_dump and a physical backup with WAL archiving?

When should a trigger be used instead of application-layer logic?

What are the tsvector and tsquery types for?

What does PostgreSQL's TOAST mechanism handle?

You must prevent overlapping bookings for the same room. What is the right tool?

A query filtering two highly correlated columns (city and country) gets bad row estimates. What fixes it?

What is PostgreSQL LISTEN/NOTIFY suitable for?

How do WHERE id IN (SELECT ...) and WHERE EXISTS (SELECT ...) differ?

In PostgreSQL, what is the difference between a role and a user?

An orders table has 100M rows; queries only target status = pending (0.1%). Which index fits best?

The app reports "too many connections" at peak. What is the right order of remediation?

Pagination with OFFSET gets slower on later pages. How is it fixed?

22 more Advanced questions for PostgreSQL

Beginner level — 18 PostgreSQL questions

Which queries does a B-tree index (the PostgreSQL default) suit?

A table has only a few hundred rows and the filtered column is indexed, yet the planner picks a Seq Scan. Why?

The users table has a UNIQUE constraint on email. Do you need an extra index for fast lookups by email?

Thanks to MVCC, what happens when one transaction reads a table while another writes to the same table?

When you UPDATE a row in Postgres, what actually happens at the storage layer?

In the query below, how do the -> and ->> operators differ?

Which type should store an event timestamp in a multi-timezone system?

In the query below, how do the `->` and `->>` operators differ?

How does a `UNIQUE` constraint in PostgreSQL treat `NULL` values?

What is the difference between `EXPLAIN` and `EXPLAIN ANALYZE`?

What is the difference between the `json` and `jsonb` types?

Why does PostgreSQL need `VACUUM`?

What is the difference between `WHERE` and `HAVING`?

Should an order status column use an `enum` type or a lookup table?

How do `COUNT(*)` and `COUNT(column)` differ?

Should an auto-incrementing key use `GENERATED ALWAYS AS IDENTITY` or `serial`?

What is the `RETURNING` clause in PostgreSQL for?

In PostgreSQL, is `varchar(255)` faster than `text`?

A jsonb column `attrs` is frequently filtered with `attrs @> '{"brand": "x"}'`. Which index type should you create?

A 500-million-row log table is append-only by time and mostly filtered by `created_at` ranges. Which index is the most economical yet effective?

Column `email` has a B-tree index but the query below still does a Seq Scan. Why, and what is the right fix?

You see connections stuck in `idle in transaction` for hours in `pg_stat_activity`. Which consequences are real?

Given an index on `(user_id, created_at)`, can a query filtering only on `created_at` use it?

What is `INSERT … ON CONFLICT DO UPDATE` used for?

You need the latest row per `user_id` from a log table. What is the efficient PostgreSQL approach?

For a write-heavy table, should the primary key be `uuid` or `bigint`?

From PostgreSQL 12 on, is a CTE (`WITH`) optimized together with the main query?

How does a window function differ from `GROUP BY` when computing group totals?

What does the `pg_stat_statements` extension provide?

What is a generated column (`GENERATED ALWAYS AS … STORED`) used for?

Where is the most sensible place to set `statement_timeout`?

What is the difference between `pg_dump` and a physical backup with WAL archiving?

What are the `tsvector` and `tsquery` types for?

A query filtering two highly correlated columns (`city` and `country`) gets bad row estimates. What fixes it?

What is PostgreSQL `LISTEN`/`NOTIFY` suitable for?

How do `WHERE id IN (SELECT ...)` and `WHERE EXISTS (SELECT ...)` differ?

An `orders` table has 100M rows; queries only target `status = pending` (0.1%). Which index fits best?

Pagination with `OFFSET` gets slower on later pages. How is it fixed?

In the query below, how do the `->` and `->>` operators differ?

How does a `UNIQUE` constraint in PostgreSQL treat `NULL` values?

What is the difference between `EXPLAIN` and `EXPLAIN ANALYZE`?

What is the difference between the `json` and `jsonb` types?

Why does PostgreSQL need `VACUUM`?

What is the difference between `WHERE` and `HAVING`?

Should an order status column use an `enum` type or a lookup table?

How do `COUNT(*)` and `COUNT(column)` differ?

Should an auto-incrementing key use `GENERATED ALWAYS AS IDENTITY` or `serial`?

What is the `RETURNING` clause in PostgreSQL for?

In PostgreSQL, is `varchar(255)` faster than `text`?

A jsonb column `attrs` is frequently filtered with `attrs @> '{"brand": "x"}'`. Which index type should you create?

A 500-million-row log table is append-only by time and mostly filtered by `created_at` ranges. Which index is the most economical yet effective?

Column `email` has a B-tree index but the query below still does a Seq Scan. Why, and what is the right fix?

You see connections stuck in `idle in transaction` for hours in `pg_stat_activity`. Which consequences are real?

Given an index on `(user_id, created_at)`, can a query filtering only on `created_at` use it?

What is `INSERT … ON CONFLICT DO UPDATE` used for?

You need the latest row per `user_id` from a log table. What is the efficient PostgreSQL approach?

For a write-heavy table, should the primary key be `uuid` or `bigint`?

From PostgreSQL 12 on, is a CTE (`WITH`) optimized together with the main query?

How does a window function differ from `GROUP BY` when computing group totals?

What does the `pg_stat_statements` extension provide?

What is a generated column (`GENERATED ALWAYS AS … STORED`) used for?

Where is the most sensible place to set `statement_timeout`?

What is the difference between `pg_dump` and a physical backup with WAL archiving?

What are the `tsvector` and `tsquery` types for?

A query filtering two highly correlated columns (`city` and `country`) gets bad row estimates. What fixes it?

What is PostgreSQL `LISTEN`/`NOTIFY` suitable for?

How do `WHERE id IN (SELECT ...)` and `WHERE EXISTS (SELECT ...)` differ?

An `orders` table has 100M rows; queries only target `status = pending` (0.1%). Which index fits best?

Pagination with `OFFSET` gets slower on later pages. How is it fixed?