PostgreSQL interview questions

25 questions from the PostgreSQL course, each with the correct answer and an explanation. Try to answer before you open the answer.

PostgreSQL Foundations

1. Which PostgreSQL feature stores semi-structured documents efficiently?

  1. BLOB-only tables
  2. CSV columns
  3. JSONB
  4. Spreadsheet links
Show answer

Answer: JSONB

JSONB is binary, indexable JSON.

Lesson: What PostgreSQL Is

2. Which psql meta-command describes a table's columns and constraints?

  1. \d table_name
  2. \q
  3. \timing
  4. SELECT * FROM information
Show answer

Answer: \d table_name

Backslash commands are psql features.

Lesson: psql and Describing Tables

3. Which type should store prices?

  1. json
  2. float8
  3. text
  4. numeric
Show answer

Answer: numeric

Money needs exact decimals.

Lesson: Choosing Data Types

Tables, Keys and Constraints

4. Which constraint stops an order from referencing a customer that does not exist?

  1. UNIQUE
  2. FOREIGN KEY
  3. CHECK
  4. DEFAULT
Show answer

Answer: FOREIGN KEY

Referential integrity.

Lesson: Constraints in Action

5. What does RETURNING do?

  1. Undoes the statement
  2. Returns columns from the rows affected by the statement
  3. Returns the table definition
  4. Returns the query plan
Show answer

Answer: Returns columns from the rows affected by the statement

Get generated values without another round trip.

Lesson: Identity Columns and RETURNING

6. What does WHERE city <> 'Pune' do with rows where city is NULL?

  1. Treats NULL as an empty string
  2. Includes them
  3. Raises an error
  4. Excludes them, because the comparison is unknown
Show answer

Answer: Excludes them, because the comparison is unknown

Use IS DISTINCT FROM for NULL-aware comparisons.

Lesson: NULL: Unknown, Not Empty

Querying Data

7. Is row order guaranteed without ORDER BY?

  1. No
  2. Yes, by insertion order
  3. Yes, by primary key
  4. Only for small tables
Show answer

Answer: No

Specify the order you need.

Lesson: Filtering, Sorting and Limiting

8. Which join keeps customers who have no orders?

  1. CROSS JOIN
  2. INNER JOIN
  3. LEFT JOIN from customers to orders
  4. A WHERE filter on orders
Show answer

Answer: LEFT JOIN from customers to orders

Left joins keep all left rows.

Lesson: Joins

9. Where do you filter on sum(...) > 1000?

  1. In ORDER BY
  2. In WHERE
  3. In HAVING
  4. In LIMIT
Show answer

Answer: In HAVING

HAVING works on aggregated groups.

Lesson: Aggregation With GROUP BY and HAVING

10. What is a CTE?

  1. A named intermediate result defined with WITH
  2. A type of index
  3. A backup format
  4. A database user
Show answer

Answer: A named intermediate result defined with WITH

Readable, composable queries.

Lesson: CTEs and Subqueries

Window Functions, JSONB and Upserts

11. How do window functions differ from GROUP BY?

  1. They require temporary tables
  2. They delete rows
  3. They only work on JSON
  4. They compute across rows while keeping every row in the result
Show answer

Answer: They compute across rows while keeping every row in the result

Aggregates without collapsing.

Lesson: Window Functions

12. Which operator returns a JSON field as text?

  1. ->
  2. ->>
  3. @>
  4. ||
Show answer

Answer: ->>

-> returns jsonb, ->> returns text.

Lesson: JSONB

13. What does EXCLUDED refer to in ON CONFLICT DO UPDATE?

  1. The deleted row
  2. The row that was proposed for insertion
  3. A table of excluded users
  4. The previous transaction
Show answer

Answer: The row that was proposed for insertion

Combine existing and proposed values.

Lesson: Upserts With ON CONFLICT

EXPLAIN and Indexes

14. What does a Seq Scan in a plan mean?

  1. The table is empty
  2. It uses an index
  3. The query failed
  4. PostgreSQL reads every row of the table
Show answer

Answer: PostgreSQL reads every row of the table

Fine for small tables, costly for selective lookups on big ones.

Lesson: Reading EXPLAIN and B-Tree Indexes

15. Why can a partial index be much smaller?

  1. It only includes rows matching its WHERE condition
  2. It compresses all data
  3. It stores only column names
  4. It skips the primary key
Show answer

Answer: It only includes rows matching its WHERE condition

Index only what queries need.

Lesson: Composite and Partial Indexes

Transactions and Concurrency

17. What happens to a transaction after one of its statements fails?

  1. Later statements still run normally
  2. It commits automatically
  3. It is aborted until you roll back (or roll back to a savepoint)
  4. The database restarts
Show answer

Answer: It is aborted until you roll back (or roll back to a savepoint)

Errors must be handled explicitly.

Lesson: Transactions and Savepoints

18. Under Read Committed, can two SELECTs in one transaction see different data?

  1. Yes, each statement sees the latest committed data
  2. No, never
  3. Only if the table is empty
  4. Only with Serializable
Show answer

Answer: Yes, each statement sees the latest committed data

Each statement gets a new snapshot.

Lesson: Isolation Levels

19. What does SELECT ... FOR UPDATE do?

  1. Makes the query run faster
  2. Updates the rows immediately
  3. Deletes the rows
  4. Locks the selected rows until the transaction ends
Show answer

Answer: Locks the selected rows until the transaction ends

Serialise read-modify-write on a row.

Lesson: Row Locks and Lost Updates

Security and Operations

20. What should an application's database role usually NOT have?

  1. SELECT on its tables
  2. Superuser privileges
  3. INSERT on its tables
  4. A password or other authentication
Show answer

Answer: Superuser privileges

Least privilege limits damage.

Lesson: Roles and Privileges

21. Why connect the application with a non-owner role when using RLS?

  1. Owners cannot read tables
  2. Table owners bypass RLS by default
  3. RLS only works for superusers
  4. It is faster
Show answer

Answer: Table owners bypass RLS by default

Make sure policies actually apply.

Lesson: Row-Level Security

22. What is the most important practice for backups?

  1. Using the largest compression
  2. Never deleting old backups
  3. Storing them only on the database server
  4. Regularly testing restores
Show answer

Answer: Regularly testing restores

A backup is only as good as its restore.

Lesson: Backup and Restore

Maintenance, Migrations and a Checklist

23. What blocks autovacuum from cleaning up dead rows?

  1. Long-running or idle-in-transaction sessions
  2. Too many indexes only
  3. Small tables
  4. Using numeric columns
Show answer

Answer: Long-running or idle-in-transaction sessions

Old snapshots keep old row versions alive.

Lesson: MVCC, VACUUM and Monitoring

24. Why use CREATE INDEX CONCURRENTLY on a busy table?

  1. It creates two indexes
  2. It is always faster to build
  3. It builds the index without blocking writes to the table
  4. It skips validation
Show answer

Answer: It builds the index without blocking writes to the table

Avoid long write locks.

Lesson: Safe Schema Migrations

25. Which item belongs on a PostgreSQL review checklist?

  1. Grant superuser to the application
  2. Use float8 for money
  3. Indexes are checked against real queries with EXPLAIN
  4. Skip foreign keys for speed by default
Show answer

Answer: Indexes are checked against real queries with EXPLAIN

Correct types, constraints and verified plans.

Lesson: A PostgreSQL Review Checklist

← Back to the course