Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        PostgreSQL backend interview scenarios

        Explain the database behavior before proposing SQL. These original interview questions focus on the choices a backend engineer needs to defend, with a follow-up case for each answer.

        Checked 14 November 2026

        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.

        Sources

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.