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

Check Yourself: Running It in Production

Five scenario questions on pruning, keys on partitioned tables, table rewrites, restore tests and dropping partitions. If you miss more than one, reread the lessons in this section.

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

Sign in to save your progress.

Course Home
1. sales_log is partitioned by month on sold_on. A query filters only on customer_id. What does the plan do?
It uses the default partition only. It scans only the newest partition. It fails with an error. It scans every partition, because there is nothing to prune on.
2. You try to create a primary key on sale_id alone on a table partitioned by sold_on. What happens?
It works after the table is vacuumed. It works, and the key is checked across all partitions. It is refused. A unique constraint on a partitioned table has to include the partition key. It works, but only for the default partition.
3. Which of these two column changes rewrites a big table, so that it blocks all access for a long time?
Both rewrite the table. ADD COLUMN stamp timestamptz NOT NULL DEFAULT clock_timestamp(), because each row needs its own value. ADD COLUMN channel text NOT NULL DEFAULT 'web', because every row is written. Neither rewrites the table, because ALTER TABLE never does.
4. A nightly pg_dump has been written for a year and was never restored. Which check proves the backup works?
Check that the file is large. Read the exit code of pg_dump. Restore it into a new scratch database and compare row counts or checksums with the original. Run pg_restore with the list option.
5. Why is dropping an old partition better than DELETE FROM sales_log WHERE sold_on is older than a date?
The DELETE cannot run on a partitioned table. The DELETE locks the whole database. The drop is a metadata change, and it leaves no dead rows for vacuum to clean. The drop writes a copy of the data first.

About

Five scenario questions on pruning, keys on partitioned tables, table rewrites, restore tests and dropping partitions. If you miss more than one, reread the lessons in this section.

Duration
5 min
Last update
09/23/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.