PostgreSQL DB-administrator tests

These exercises evaluate your ability to design, tune and reason about Postgres DB management

Tooling requirements

• IntelliJ IDEA Ultimate – use the built-in DB client
• Latest stable PostgreSQL (Docker or native).
• Flyway for all schema and data-fix migrations.
• Spring Boot + Kotlin, to run migrations, populate test data and do queries.

---

Test 1 – Column Tetris (storage efficiency)

In this test you will demonstrate why column order and column data type matters.

1. Make an unoptimized table your choice and programatically populate it with a lot of data. Unoptimized means: Too big data type compared to what is needed. For example using bigint as primary key for a countries table
2. Record a Loom showing, in IntelliJ DB client:
   • pg_relation_size, pg_total_relation_size before.
   • The DDL changes (type tweaks, column tetris).
   • pg_total_relation_size after
   • Explain why the size was reduced

---

Test 2 – ERP core schema

Design a minimal but realistic schema for:

• customers
• invoices
• invoice_lines
• payments
• products

Avoid common mistakes

Requirements:

• Sensible PKs and FKs
• Index strategy justified.
• Reasonable constraints on columns

Loom: walkthrough of tables, relationships, defaults, and why you chose them.

---

Test 3 – Query-performance

1. Inefficient: write a query to calculate each customer’s total sales last year by pulling all lines to the client. Meaning you will do the calculation in Kotlin instead of inside the DB server.
2. Efficient: rewrite as a single set-based SQL using SUM, GROUP BY, or a window function.
3. Capture EXPLAIN (ANALYZE, BUFFERS) for both.
4. Loom: show both plans, discuss why the second wins (CPU, I/O, network).

---

Test 4 – Postgres best-practices quiz

Explain briefly on a Loom:

• JSON vs JSONB – when, why, how they’re stored, how they index.
• Partial / expression indexes vs ordinary indexes.
• Autovacuum thresholds and why bloat matters.
• Transaction isolation levels and common locking gotchas.
• Tuning knobs: work_mem, maintenance_work_mem, shared_buffers, effective_cache_size.