Skip to Content
SQL Advanced: Queries That Hold Up Under Load
Advanced
0 likes
Courses
SQL Advanced: Queries That Hold Up Under Load

Final Check: SQL Advanced

Fifteen scenario questions across the whole course. If you get twelve or more right, you can read a plan, choose an index, change a live table and prove a restore.

15 min SQLDatabasesTesting and DebuggingPerformance Optimization Advanced
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. EXPLAIN ANALYZE shows Sort Method: external merge Disk: 752kB on a slow report. What does it mean, and what is a reasonable first try?
The sort did not fit in work_mem and wrote temporary files. Try the query with a higher work_mem in your own session. The index was read from disk. Add another index. The table is bloated. Run VACUUM FULL. The plan is wrong. Run ANALYZE.
2. Plan A has a total cost of 442 and plan B has a total cost of 90 for the same query. What can you say?
Plan A is faster, because a higher cost means more work done. Cost is in arbitrary units, so plan B is expected to be cheaper, but the numbers are not seconds. Plan B will finish in 90 milliseconds. The costs cannot be compared, because they come from different plans.
3. Products have a tags array, and the question is which rows contain the tag gift. A B-tree index on tags is not used. Which index fits?
A hash index on the array. GIN, which stores each element with the list of rows that contain it. A partial index with WHERE tags IS NOT NULL. BRIN, which stores minimum and maximum per block.
4. A 500 GB log table is only ever appended to, and queries read one day at a time. Which index is small enough to keep and still useful?
A GIN index on the timestamp column. BRIN, because the column follows the physical order of the table, which you can check as correlation in pg_stats. A B-tree on every column. No index is possible on a table that large.
5. A customer may have many closed carts but only one open cart. Which design enforces that in the database?
A CHECK constraint on status. A unique partial index on customer_id WHERE status = 'open'. A plain UNIQUE on customer_id. A check in the application before inserting.
6. You run CREATE INDEX CONCURRENTLY inside a migration tool that wraps every step in BEGIN. What happens, and what do you do?
It runs, and blocks writes the same way as a plain build. It works after you set lock_timeout. PostgreSQL refuses it, because it cannot run inside a transaction block. Run that step outside a transaction. It runs, but without the concurrent behavior.
7. CREATE UNIQUE INDEX CONCURRENTLY fails on a duplicate value. What is left behind, and what do you do?
A lock that stays until the server restarts. An invalid index that does not serve queries but still costs on every write. Drop it with DROP INDEX CONCURRENTLY and fix the data. Nothing. PostgreSQL removes the failed index. A valid index with a few missing rows.
8. CREATE EXTENSION pg_stat_statements works, but SELECT from the view fails with a message about shared_preload_libraries. What is missing?
You need to run ANALYZE first. You need to be a superuser for every query. The view works only after a million statements. The library has to be listed in shared_preload_libraries, and the server restarted.
9. An index shows idx_scan = 0 in pg_stat_user_indexes. Which is a good reason not to drop it?
Every index with zero scans is needed at least once a year. Zero scans means the index is damaged. DROP INDEX is not allowed on a table with data. It backs a primary key or a unique constraint, which enforces a rule even if no query reads it.
10. Two workers pick jobs with SELECT ... FOR UPDATE SKIP LOCKED. One worker crashes before it finishes. What happens to its jobs?
The transaction rolls back and the lock goes away, so the jobs are still queued and another worker takes them. The jobs are marked done by PostgreSQL. The jobs are lost, because the row lock stays forever. Both workers get the same jobs, because SKIP LOCKED ignores locks.
11. What does an UPDATE do to a row in PostgreSQL, and when is the old version removed?
It changes the row in place, and the old value is gone at once. It deletes the row and inserts it again after the next checkpoint. It writes the new version to a separate undo file. It writes a new row version and marks the old one as replaced. VACUUM removes the old version when no transaction can see it.
12. A table is much bigger than its live data. pg_stat_activity shows a session that has been idle in transaction for two days. What do you do?
Run VACUUM FULL every hour. End that session, and set idle_in_transaction_session_timeout so the server ends such sessions itself. Ignore it, because an idle session uses no resources. Turn autovacuum off to save work.
13. You switch a GIN index from the default class to jsonb_path_ops to save space, and a query WHERE payload -> 'tags' ? 'vip' becomes a sequential scan. Why?
jsonb_path_ops supports the contains operator but not the key-exists operator. jsonb_path_ops is only for text columns. The expression payload -> 'tags' cannot be written. The index needs to be rebuilt after the switch.
14. A new column needs a different value in every row of a very big table, and must end up NOT NULL. Which order avoids a long lock?
Add the column as nullable, fill it in batches, add NOT NULL as NOT VALID, then VALIDATE CONSTRAINT. ALTER TABLE ... ADD COLUMN ... NOT NULL with a function that gives each row its own value. Add the column and update every row in one statement. Create a new table and drop the old one.
15. A restore on a new server fails because roles that own the objects do not exist. Why did the dump not carry them?
The custom format leaves out roles to save space. The dump was taken while users were connected. pg_dump saves one database. Roles belong to the server, so dump them with pg_dumpall --globals-only. Roles can be restored only with pg_basebackup.

About

Fifteen scenario questions across the whole course. If you get twelve or more right, you can read a plan, choose an index, change a live table and prove a restore.

Duration
15 min
Last update
09/26/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.