Constraints and missing values
A column has CHECK (hours >= 0). Can it contain null? Yes. A check passes when its expression is true or null. Add NOT NULL when every row must have a value. Do not replace an unknown value with zero just to satisfy an application display.
For a follow-up, decide whether a foreign key alone requires a related record for every row. A nullable foreign-key column can still contain null. Practice constraints and keys with an invalid insert, rather than only inspecting valid rows.
Indexes and query shape
An API lists open orders for one account, newest first. What would you inspect before adding an index? Start with the predicates, sort order, row counts and workload. A partial multicolumn index can be useful when it matches the query, but its predicate, column order and write cost need an explanation.
For a follow-up, ask why a sequential scan appears on a small table after the index is created. The planner can choose that plan because it estimates a lower cost. Compare estimated and actual rows in EXPLAIN ANALYZE instead of claiming that every sequential scan is a defect.
Time boundaries and grouping
Count each account’s orders for October, with stable ordering for ties. A half-open interval avoids assuming the last representable timestamp of the month. This original example treats the reporting month as UTC; a business using a local calendar needs explicit time-zone boundaries.
SELECT account_id, COUNT(*) AS orders
FROM orders
WHERE submitted_at >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
AND submitted_at < TIMESTAMPTZ '2026-11-01 00:00:00+00'
GROUP BY account_id
ORDER BY orders DESC, account_id;
A follow-up can add order items. Explain how joining to items changes the grain and can multiply the order count. Repair the query at the required grain in joins and grain.
Isolation and retries
Can two reads inside one PostgreSQL Read Committed transaction return different committed values? Yes. Each statement gets a snapshot at its start. Repeatable Read keeps a transaction snapshot, but an application still needs to handle supported failure cases.
For a follow-up, distinguish Repeatable Read from Serializable. PostgreSQL Repeatable Read prevents phantom reads but can permit serialization anomalies. Serializable transactions can fail and require a whole-transaction retry. The isolation lesson uses a labeled simulator because PGlite has one connection.
Locks and deadlocks
Two transactions update the same pair of rows in opposite orders. What can happen? Each can hold a row lock that the other needs, producing a deadlock. PostgreSQL detects it and aborts one transaction. Keep transactions short and acquire related locks in a consistent order; applications must still handle a retry.
A lock wait alone is not a deadlock.
Explain the waiting relationship before choosing a fix in deadlocks and retries.
Roles and row policies
Why can a row-security test pass under the application role but expose all rows under the table owner? Owners normally bypass row policies. Superusers and roles with BYPASSRLS also bypass them. Test the intended role, its table grants and the policy together.
Use row-level security for checked scenarios. For syntax practice before an interview, use the exercise guide and the course. These are original job-skill questions, not questions from a hiring company’s test.
For an interactive round, choose a PostgreSQL interview.