Hands-On Labs, Java Build Track and Study Plans

In one line

Knowledge sticks when you break things yourself. Run the PostgreSQL labs (plans, deadlocks, isolation, MVCC, VACUUM, partitioning, backup and restore, replication), build the Java/Spring track (locking, pagination, outbox, idempotency, caching, CDC, read-replica routing, multi-tenancy, zero-downtime migration), and follow a 30-day intensive or 60-day deep plan, finishing with the readiness checklist.

0/5 · 0%

Think of it like this

Learning to swim. Reading about strokes helps a little; getting in the water, swallowing some, and trying again is what works.

Key ideas

01
PostgreSQL labs: generate 10M rows; compare plans before and after indexes; create a slow query and optimise it; create and investigate a deadlock; reproduce each isolation anomaly; watch MVCC versions with pageinspect; bloat a table and vacuum it; partition and drop old data; take a backup and run PITR; set up a replica and measure lag.
02
Performance lab loop: dataset → baseline query → EXPLAIN ANALYZE → add index → benchmark → composite index → benchmark → covering index → benchmark → cache → benchmark; record latency, throughput, CPU, memory, I/O and plan each step.
03
Failure lab: database outage, connection exhaustion, slow query, deadlock, lock contention, replica lag, disk full (in a disposable VM), cache failure, Kafka failure, bad migration. For each, document detection, impact, root cause, mitigation, recovery, prevention.
04
Java/Spring track: CRUD API → transactional order service → optimistic and pessimistic locking → offset and cursor pagination → dynamic filtering with composite indexes → soft delete and audit logging → outbox → idempotency keys → Redis cache and lock → Kafka CDC pipeline → search sync → read-replica routing → multi-tenant schema with RLS → Flyway migrations → a zero-downtime column rename.
05
Readiness checklist: modeling (requirements to entities, keys, normalize, denormalize deliberately); SQL (window functions, recursive queries, optimization); indexing (from access patterns, EXPLAIN ANALYZE); transactions (isolation, races, deadlocks, locking choice); internals (MVCC, WAL, B-tree, LSM, VACUUM); scaling (replication, partitioning, sharding, hot keys); NoSQL (when and why each); production (backup, restore, RPO/RTO, multi-region, migrations, monitoring); security (access, PII, audit, injection); system design (defend trade-offs, handle 10×, handle failure).

Code & diagrams

study-plan.txttext

30-DAY INTENSIVE                          60-DAY DEEP
Days 1-3   fundamentals, ER, keys         Weeks 1-2  modeling: phases 0-3
Days 4-6   normalization, denormalization Weeks 3-4  SQL + transactions: phases 4-5
Days 7-9   SQL, joins, CTEs, windows      Weeks 5-6  PostgreSQL: phases 6-8 + labs
Days 10-12 transactions, isolation, MVCC  Week 7     scaling: phase 9
Days 13-15 indexes, planner, EXPLAIN      Week 8     NoSQL: phase 11
Days 16-18 internals, WAL, VACUUM         Week 9     distributed data: phases 10, 12
Days 19-21 replication, partitioning,     Week 10    production: phases 13-15
           sharding                        Weeks 11-12 interviews: phases 16-17,
Days 22-24 Redis, MongoDB, Cassandra                  20 schema designs,
Days 25-26 Elasticsearch, CDC, outbox                 20 query optimizations,
Days 27-28 HA, DR, security, multi-region             15 architecture problems,
Days 29-30 mock designs, failure drills               10 failure scenarios, 10 timed mocks

lab-setup.shbash

docker run -d --name pg -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:17 \
  -c shared_preload_libraries=pg_stat_statements -c log_lock_waits=on
psql postgresql://postgres:pw@localhost:5432/postgres -c "CREATE EXTENSION pg_stat_statements"
pgbench -i -s 100 postgresql://postgres:pw@localhost:5432/postgres   # ~10M rows in pgbench_accounts

Interview problem

The problem

Plan your preparation for a senior database interview in 4 weeks

You have 4 weeks, 2 hours per weekday and 5 hours at weekends, and you're strong in SQL but weak in internals and distributed data. Build a plan.

When it breaks

Reading without doing

What you see

Concepts feel familiar but the candidate can't read a real plan, reproduce a deadlock, or size a system under interview pressure.

Fix & prevent

Do the labs and timed mocks; explain each result aloud or in writing.

Explain it without notes

Which labs give the most interview value per hour?

Practice

Complete the readiness checklist and mark each area green, amber or red.

Trade-offs

↔
Labs take longer than reading, and they turn recognition into skill you can use under pressure.

Done when you can

I have done the core labs, built the Java track milestones, and every readiness area is green.

Command Palette