certuvia teaches PostgreSQL differently. Every lab starts with a realistic scenario — you discover why the command exists before you touch it. A live Linux environment with real PostgreSQL and four preloaded databases runs in your browser. All 104 labs are available; browse the complete curriculum below.
Design schemas, write queries, and manage data with confidence.
Configure, monitor, back up, and recover PostgreSQL.
Diagnose slow queries, tune indexes, and scale PostgreSQL.
The setup phase is where beginners lose confidence. These labs make the environment feel navigable before a single table is created.
Connect to PostgreSQL, verify your identity, exit gracefully
Navigate databases, schemas, and tables using psql meta-commands
createdb, CREATE DATABASE, encoding, and \c
Create roles, grant permissions, and apply least privilege
DDL decisions are permanent and costly to reverse in production. Make each column choice count.
Choose the right column type — not everything is text or integer
Make invalid data impossible to insert — NOT NULL, UNIQUE, CHECK, DEFAULT
Link tables with REFERENCES — make orphaned rows impossible
Add columns, change types, drop constraints — and know which ones are dangerous
Namespace your tables, set search_path, and lock down public schema access
Fix invalid statuses, stale computed columns, and float rounding in one lab
A query that returns wrong rows is worse than one that returns no rows. Precision over volume.
Return exactly the rows you need — and nothing else
ORDER BY is a correctness tool — LIMIT without it returns random rows
LIKE for simple patterns, tsvector/tsquery for language-aware search
Date arithmetic, OVERLAPS, and querying daterange columns
Make text equality work for humans — case and accent insensitive by default
JOINs are where SQL becomes truly expressive. Watch row counts change — that tells you more than any definition.
Link two tables — and understand exactly which rows disappear
Keep all rows from the left table — then use IS NULL to find the ones with no match
Join a table to itself, then use CTEs to make complex multi-table queries readable
Filter against dynamic values — and learn why NOT IN silently breaks when a NULL is present
COUNT rows, GROUP BY buckets, write data safely, and wrap changes in transactions that either fully commit or fully roll back.
Collapse many rows into one meaningful number — and understand what NULL does to each function
Split a summary across categories — and filter which categories survive
Write data to the database — and use RETURNING to get results back without a second query
Wrap multiple writes in a transaction — either everything commits or nothing does
Import data in bulk, upsert with MERGE, and page through millions of rows without the OFFSET cliff
JSONB for flexible payloads, arrays for multi-valued columns, range types for intervals that cannot overlap, and full-text search for natural language queries — all built into PostgreSQL without extra dependencies.
Store semi-structured metadata in a jsonb column and query it with path operators, containment, and GIN indexes
Store and query multiple values in a single column — no junction table required for simple membership tests
Model time intervals natively and prevent overlapping bookings with a database-level exclusion constraint
Search prose columns with stemming, ranking, and highlighting — using tsvector, tsquery, and GIN indexes
Understand how a PostgreSQL cluster is laid out on disk, how to read and tune its configuration, control who can connect and how, apply changes safely with reload vs restart, and manage the service with pg_ctl.
Navigate the PGDATA directory tree — where data files, WAL segments, config files, and sockets live
Read, understand, and tune PostgreSQL parameters through pg_settings — and know which changes take effect immediately
Read host-based authentication rules, add a new rule, upgrade an authentication method, and apply both with a zero-downtime reload
Know which configuration changes need a full restart and which take effect immediately — and use pending_restart to verify
Start, stop, reload, and inspect the PostgreSQL server process — and diagnose, fix, and recover from a failed startup
Design a least-privilege role hierarchy, close the PUBLIC-access gap, isolate tenants with row-level security, enforce encrypted connections, and understand what real audit logging requires.
Design a role hierarchy with NOLOGIN group roles, INHERIT login roles, and auditable SET ROLE impersonation
Revoke the PUBLIC-access default, grant precise privileges to the analyst role, and make the fix apply to tables that do not exist yet
Enforce tenant isolation at the database layer with ENABLE ROW LEVEL SECURITY and a USING policy — no application bug can bypass it
Enable TLS, require encryption for an application role, and verify the active connection.
Install the available pgAudit extension and compare its read auditing with log_statement=ddl.
Take logical and physical backups, restore precisely under pressure, migrate roles alongside data, and recover to an exact point in time — every lab ends with a verified, successful restore.
Dump schemas and tables in plain, custom, and directory formats — and understand why -Fc is the default that scales
Restore exactly one table from a custom-format dump without touching anything else — then compare a serial restore against a parallel one
Discover what pg_dump silently leaves behind, migrate roles with pg_dumpall --globals-only, and prove a role-dependent restore only works once globals exist
Take a full streaming physical backup of the cluster, restore it to an alternate data directory, and start it as a second, independent, writable instance
Configure WAL archiving, take a base backup, and recover to the exact second before a destructive statement — then promote the recovered cluster to writable
Query the statistics collector to find hot tables, trace a live lock chain, measure real bloat, tune autovacuum per table, configure logs you can actually search, and read the cache and I/O system PostgreSQL exposes about itself.
Find hot tables with pg_stat_user_tables, measure a real cache hit ratio, and catch a scoping gotcha in counter resets
Trace a live blocking chain with pg_blocking_pids(), then choose between pg_cancel_backend and pg_terminate_backend to resolve it
Measure real bloat with pgstattuple, watch VACUUM reclaim space without shrinking the file, and see why VACUUM FULL is the one that actually does
Read the autovacuum trigger formula from pg_settings, override it for one high-churn table, and watch it fire faster than the cluster default
Configure slow-query logging and csvlog properly, discover pgBadger is not on this box, and build the same report by hand with grep and awk
Look directly inside shared_buffers, catch PostgreSQL's ring-buffer protection for large scans in the act, and see why a bigger cache does not always mean more caching
Break disk I/O down to the exact operation — heap writes, WAL, and VACUUM — instead of one undifferentiated number
Understand exactly what streams over the replication protocol, build a hot standby from scratch, protect it with a replication slot, measure its lag precisely, and migrate a single table live with logical replication.
Read the write-ahead log directly — its current position, how much a workload generates, and which files are safe to remove
Build a real hot standby from scratch with pg_basebackup -R, start it on port 5433, and confirm it streams
Watch a replication slot hold WAL open for a disconnected standby, measure exactly how much is retained, and clean it up safely
Create lag on demand, watch write_lag and replay_lag diverge, then use a deliberate delay as a real disaster-recovery safeguard
Migrate a single table live between two independent clusters with CREATE PUBLICATION and CREATE SUBSCRIPTION — no full standby required
Read VACUUM output line by line, reclaim real disk space safely, survive a connection storm, upgrade a live cluster, and build the runbook that keeps all of it from becoming an emergency again.
Read a real VACUUM VERBOSE output line by line, and watch the visibility map change state before and after
Reclaim real disk space with VACUUM FULL, physically reorder a table with CLUSTER, and watch the AccessExclusiveLock block a second session
Hit this database's real connection ceiling head-on, then learn the tool built to absorb exactly that storm
Run a real pg_upgrade --check, then a real --link upgrade, and verify every database, role, and row survived
Write a bloat-alert query, a maintenance script, and schedule both with real OS-level cron — no pg_cron required
Push real-time notifications without polling, coordinate work with advisory locks, build a job queue with no external broker, audit every DDL statement automatically, query external sources as local tables, and move application logic into the database itself.
Replace a polling loop with a real push model — and see exactly when a listening client actually notices a message
Turn leader election into three lines of SQL, with no external coordination service at all
Dequeue work atomically with no collisions and no external broker — and see exactly how NOWAIT fails differently
Capture every schema change automatically — and discover firsthand that DROP needs a completely different event to see it
Query a genuinely separate cluster and a plain CSV file as if both were local tables — with real filter pushdown, not just convenience syntax
Move twelve round-trips into one transactional procedure — and prove a failed order leaves no partial damage behind
Read EXPLAIN output fluently enough to know exactly why a query is slow — bad row estimates, the wrong join strategy, an unhelpful GUC, or work the planner could parallelize but is not.
Read a real query plan node by node — and reproduce "30 seconds on production, 0.1 seconds locally" with nothing but a cold cache
Watch the planner badly misjudge two correlated columns, then fix it with one CREATE STATISTICS object — and measure exactly how much better the estimate gets
Force the planner's hand between Nested Loop and Hash Join, and measure exactly what the wrong choice actually costs
No extensions can be installed on this server — fix bad plans with the GUCs and query shapes PostgreSQL already ships with
This VM has exactly one CPU. Find out, with real measurements, whether parallel query and JIT compilation still do anything on it.
Indexes are the single highest-leverage performance tool in a relational database. Design them well and most production slowdowns never happen at all.
A query filters on (status, created_at). There is already an index on (created_at, status). It is not being used. Prove why, then fix it.
99% of these rows will never be queried by status again. Index only the 1% that matters, and measure exactly how much smaller that makes it.
An Index Only Scan still touched the table heap once. The index was never the problem — the visibility map was.
LIKE '%word%' cannot use a B-tree index no matter how it is built. GIN indexes a completely different structure — and actually can.
500,000 rows in physical time order. A B-tree index on created_at costs 11MB. A BRIN index costs 24kB — for the same query support.
Two indexes on the same table. One has never been scanned once. The other is real, measured, 33% fragmented. Find both, and fix only the one that matters.
Five low-selectivity columns, queried in every combination. Five B-tree indexes cost 10MB combined. One bloom index covers all of them at 3.1MB.
Solve complex analytical problems inside the database, where the data already lives, instead of shipping rows across the network to solve them in application code.
An org chart traversal loops through 7 levels in application code, one database round trip per level. Replace it with one recursive query.
A 7-day moving average, a cumulative total, and a day-over-day change — all three, plus the raw daily number, without collapsing a single row.
"For each customer, their 3 most recent orders." One approach costs less on paper. The other one is actually faster, measured.
100,000 event payloads stored as JSONB. Query inside them, extract nested values, and index them — all without ever normalizing them into columns.
A check constraint at the application layer is unreliable. Make double-booking a room structurally impossible at the database level instead.
SELECT into Python, then INSERT, then DELETE — three round trips, with a real window where data exists in both places or neither. One writeable CTE closes that window completely.
A rolling average that silently absorbs an extra day across a data gap. A pivot table for the board. An average computed 46x faster from 1% of the data. Three requests, three real tools.
A schema decision made at design time determines whether a database performs the same way at 100 million rows as it did at 100,000. Every lab here is a real pattern that breaks at scale.
A 2-billion-row logs table full-scans on every date-filtered query, despite an index. Partitioning eliminates the irrelevant 99% before a single row is touched.
A common assumption says an unselected large column still silently costs I/O. Checked directly against the real database, that assumption turns out to be wrong — and the real cost lives somewhere more specific.
A table taking thousands of updates per second is generating enormous WAL volume and index bloat. HOT updates are the structural fix — when they can happen at all.
A 90-second aggregation query runs on every page load. A materialized view pre-computes it once — the real challenge is refreshing it without blocking every reader while it happens.
Index I/O and table I/O are competing on the same disk, saturating the queue. Moving indexes to a second volume immediately separates the two workloads.
Configuration tuning is guesswork without a mental model of what each parameter actually controls. This block builds that model against this VM's real, measured hardware — not an imagined 64GB server.
Verify shared_buffers with a restart, then compare disk and memory sorts using real query plans.
A common reference says checkpoint stats live in pg_stat_bgwriter. Checked directly on this PostgreSQL 18 server, that view no longer has them at all.
The curriculum imagines 16 idle CPU cores. This VM has exactly one, confirmed directly. Tune parallel query anyway — and measure the honest result.
A connection sits idle inside an open transaction. A query runs forever. Two sessions lock each other out permanently. Three real problems, three real safety timeouts.
Each lab starts with an incident. You are on-call, the SLA is 30 minutes away, and there is no one else to ask. Go.
A developer shows you a query that takes 2 seconds. A different query, called 50,000 times a minute, might be costing far more in total. pg_stat_statements reveals which one actually matters.
The application is intermittently slow. Several sessions are waiting on locks. Build the real chain, find the one session actually responsible, and terminate only that one.
A slow query only fires under specific production load and cannot be reproduced in testing. auto_explain captures its real execution plan automatically, the moment it actually happens.
Black Friday. A real connection storm arrives. PgBouncer is confirmed absent on this server — find out exactly what PostgreSQL itself does, and does not do, when far too many connections arrive at once.
Four real incidents, back to back, on the same server. A dropped table, a crashed primary, a bloating replication slot, and a transaction ID wraparound check — each one genuinely reproduced, not simulated.
Five production requests arrive this sprint. A junior engineer reaches for a separate service each time — every one of them is solvable inside PostgreSQL, with an extension already available on this VM.
Customer search is broken three different ways: an accent, a typo, and a phonetic variant. Three genuine extensions fix the three real cases — and they compose into one function.
The mobile app needs the 5 nearest cafes, a count within a delivery zone, and a point-in-polygon check. PostGIS is confirmed absent on this server — PostgreSQL's own native geometric types genuinely cover all three.
The finance team needs a geometric mean. The risk team needs a 95th percentile. The sales team wants a hypothetical rank without inserting a row. All three are built-in, or writable, directly in SQL.
A table with 18 months of deletes is mostly empty space. pg_repack would reclaim it online — confirmed absent on this server. What VACUUM FULL genuinely costs instead is demonstrated directly.
A new data warehouse pipeline needs every row change in real time, with no polling and no application code changes. Logical decoding turns the real WAL into a structured change stream.