Interview Q&A / PostgreSQL

1. Does the column order in a multi-column B-tree index matter?

   Yes. A composite index is sorted by the first column, then the second within it, and so on. It helps queries that filter on a leading prefix of its columns, and it is much less useful if only a later column is filtered.

   BeginnerQuick answer1 min read
2. How do you generate and store UUIDs in PostgreSQL?

   Use the uuid type, which stores 16 bytes, and generate values with the built-in gen_random_uuid() (available without an extension since PostgreSQL 13). Store them as uuid, not as 36-character text.

   BeginnerQuick answer1 min read
3. How do you use a CHECK constraint, and what does it ignore?

   CHECK rejects a row when a boolean expression on its columns is false. It passes when the result is true or NULL, so pair it with NOT NULL when the column must always be present. It cannot see other rows.

   BeginnerQuick answer1 min read
4. How do you use array columns in PostgreSQL, and when are they a bad idea?

   A column can hold an array such as text[], queried with ANY, the contains operator @> and unnest. They suit small, simple lists read together with the row; use a separate table when you need foreign keys, per-item data, or heavy filtering by item.

   BeginnerQuick answer1 min read
5. How does a recursive CTE work? Show one that counts from 1 to 5.

   A recursive CTE has an anchor query that produces the starting rows and a recursive query that refers to the CTE itself, joined with UNION or UNION ALL. PostgreSQL repeats the recursive step on the latest rows until it returns nothing.

   BeginnerQuick answer1 min read
6. How is a window function different from GROUP BY?

   GROUP BY collapses rows into one row per group. A window function computes over a related set of rows but keeps every original row, so you can show a row's value beside its group's total or its rank.

   BeginnerQuick answer1 min read
7. Is it safe to run EXPLAIN ANALYZE on an UPDATE or DELETE?

   No, not without care. EXPLAIN ANALYZE really executes the statement, so the rows are changed. Wrap it in BEGIN and ROLLBACK to inspect the plan and timings without keeping the change.

   BeginnerQuick answer1 min read
8. Is there a performance difference between text and varchar(n) in PostgreSQL?

   No. PostgreSQL stores text, varchar and varchar(n) the same way, and the length limit in varchar(n) is only a check. Use text unless a real business rule needs a maximum length.

   BeginnerQuick answer1 min read
9. What do LAG and LEAD do, and how would you use them to find day-over-day change?

   LAG reads a column from a previous row in the window order and LEAD from a following row. Subtracting LAG from the current value gives the change from the previous row, without a self-join.

   BeginnerQuick answer1 min read
10. What do ON DELETE CASCADE, RESTRICT, SET NULL and NO ACTION mean on a foreign key?

    They decide what happens to child rows when the parent row is deleted. CASCADE deletes the children, SET NULL clears the reference, RESTRICT blocks the delete immediately, and NO ACTION blocks it at the end of the statement (the default).

    BeginnerQuick answer1 min read
11. What do the cost numbers in an EXPLAIN plan actually mean?

    cost=A..B gives the estimated startup cost (before the first row) and total cost, in arbitrary planner units based on page fetches and CPU work. They are for comparing plans, not seconds or milliseconds.

    BeginnerQuick answer1 min read
12. What does SELECT ... FOR UPDATE do in PostgreSQL?

    It locks the selected rows until the transaction ends, so other transactions cannot update, delete or lock them meanwhile. Use it to read a value and change it safely, like a balance or a seat count.

    BeginnerQuick answer1 min read
13. What is a CTE (WITH clause) and why would you use one?

    A common table expression names a subquery at the top of a statement so you can read it in steps and reuse it more than once. It improves readability; since PostgreSQL 12 a simple CTE is usually inlined like a subquery.

    BeginnerQuick answer1 min read
14. What is a deadlock in PostgreSQL and how do you avoid it?

    A deadlock occurs when two transactions each hold a lock the other needs, so neither can proceed. PostgreSQL detects it after deadlock_timeout (1 second by default) and aborts one transaction. Prevent it by locking rows in a consistent order.

    BeginnerQuick answer1 min read
15. What is a partial index in PostgreSQL and when is it useful?

    A partial index has a WHERE clause and indexes only the rows that match it. It is smaller and faster to maintain, and ideal when queries target a small, well-defined slice such as unprocessed jobs or active users.

    BeginnerQuick answer1 min read
16. What is an expression index, and how do you make WHERE lower(email) = 'x' fast?

    An expression index stores the result of a function or expression instead of the raw column. Create an index on lower(email) and queries that use exactly lower(email) can use it; a plain index on email cannot help.

    BeginnerQuick answer1 min read
17. What is the difference between -> and ->> in PostgreSQL JSONB queries?

    -> returns the value as jsonb (so you can keep navigating), while ->> returns it as text. Use ->> when comparing to strings or showing a value, and cast it for numbers.

    BeginnerQuick answer1 min read
18. What is the difference between lock_timeout and statement_timeout?

    lock_timeout cancels a statement that waits too long to acquire a lock. statement_timeout cancels any statement whose total running time exceeds the limit, including time spent working.

    BeginnerQuick answer1 min read
19. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

    All number rows in a window order. ROW_NUMBER gives unique numbers with no ties, RANK gives ties the same number and then skips ahead, and DENSE_RANK gives ties the same number without skipping.

    BeginnerQuick answer1 min read
20. What is the difference between Seq Scan, Index Scan and Bitmap Heap Scan in a PostgreSQL plan?

    Seq Scan reads the whole table, Index Scan follows the index row by row, and Bitmap Heap Scan collects matching row locations from the index first, then reads table pages in physical order. The planner picks by estimated selectivity and cost.

    BeginnerQuick answer1 min read
21. What is the difference between VACUUM and VACUUM FULL?

    VACUUM marks dead space reusable and runs alongside normal reads and writes, but usually does not shrink the file. VACUUM FULL rewrites the table into a new compact file, returning space to the OS, but takes an ACCESS EXCLUSIVE lock for the whole run.

    BeginnerQuick answer1 min read
22. When should a primary key be bigint instead of integer in PostgreSQL?

    integer tops out at about 2.1 billion. If a table could ever reach that many rows over its life, including rows deleted along the way, use bigint from the start, because changing the type later rewrites the table and every foreign key.

    BeginnerQuick answer1 min read
23. Why does WHERE col = NULL return no rows in PostgreSQL, and what should you use instead?

    NULL means unknown, so comparing anything to it with = gives NULL, not true, and WHERE keeps only true rows. Use IS NULL, or IS NOT DISTINCT FROM when you want NULLs treated as equal.

    BeginnerQuick answer1 min read
24. Why should you use numeric instead of float or real for money in PostgreSQL?

    float and real are binary approximations that cannot represent values like 0.1 exactly, so sums drift. numeric stores exact decimal digits, which is what money needs. Many teams store paise as a bigint instead.

    BeginnerQuick answer1 min read
25. Why should you use timestamptz instead of timestamp in PostgreSQL?

    timestamptz stores an absolute moment in time (converted to UTC internally) and shows it in the session time zone. timestamp stores a bare date and clock reading with no zone, so the same value means different moments for different users.

    BeginnerQuick answer1 min read