Becoming the Database Person
Expert · 20 min read · ▶ live playground · ✦ checkpoint
The database person is not the teammate who memorized the most syntax. It is the teammate who makes unknown schemas safer, explains tradeoffs clearly, and leaves checks behind for the next person.
This course has taught you the language. This lesson is about the practice loop that keeps the skill alive after the course ends: inspect before assuming, prove before changing, and keep learning from the engine in front of you.
Spelunk A Schema Safely
Real databases rarely arrive as tidy course diagrams. You inherit names, shortcuts, old migrations, half-remembered conventions, and tables that matter because payroll or customers depend on them. Safe spelunking starts read-only: inspect the schema, find constraints, count rows, and read plans before proposing changes.
Here is a small legacy-shaped schema. The important move is not the data; it is the sequence of questions.
CREATE TABLE legacy_customers (
customer_id int PRIMARY KEY,
email text UNIQUE NOT NULL,
joined_on date NOT NULL,
status text NOT NULL CHECK (status IN ('active', 'paused', 'closed'))
);
CREATE TABLE legacy_orders (
order_id int PRIMARY KEY,
customer_id int NOT NULL REFERENCES legacy_customers,
placed_on date NOT NULL,
amount numeric(8,2)
);
INSERT INTO legacy_customers (customer_id, email, joined_on, status) VALUES
(101, 'ava@example.com', DATE '2025-12-10', 'active'),
(102, 'ben@example.com', DATE '2026-01-15', 'paused'),
(103, 'chloe@example.com', DATE '2026-02-02', 'active'),
(104, 'diego@example.com', DATE '2026-03-12', 'closed');
INSERT INTO legacy_orders (order_id, customer_id, placed_on, amount) VALUES
(9001, 101, DATE '2026-04-01', 24.50),
(9002, 101, DATE '2026-04-08', 18.00),
(9003, 102, DATE '2026-04-09', 12.75),
(9004, 103, DATE '2026-04-11', 31.20),
(9005, 103, DATE '2026-04-18', NULL),
(9006, 104, DATE '2026-04-19', 9.50);
SELECT t.table_name,
count(c.column_name) AS columns
FROM information_schema.tables t
JOIN information_schema.columns c
ON c.table_schema = t.table_schema
AND c.table_name = t.table_name
WHERE t.table_schema = 'public'
AND t.table_type = 'BASE TABLE'
AND t.table_name LIKE 'legacy_%'
GROUP BY t.table_name
ORDER BY t.table_name;CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 6
table_name | columns
------------------+---------
legacy_customers | 4
legacy_orders | 4
(2 rows)Now read columns as data instead of guessing from a UI screenshot:
SELECT c.table_name,
c.ordinal_position,
c.column_name,
c.data_type,
c.is_nullable
FROM information_schema.columns c
WHERE c.table_schema = 'public'
AND c.table_name LIKE 'legacy_%'
ORDER BY c.table_name, c.ordinal_position; table_name | ordinal_position | column_name | data_type | is_nullable
------------------+------------------+-------------+-----------+-------------
legacy_customers | 1 | customer_id | integer | NO
legacy_customers | 2 | email | text | NO
legacy_customers | 3 | joined_on | date | NO
legacy_customers | 4 | status | text | NO
legacy_orders | 1 | order_id | integer | NO
legacy_orders | 2 | customer_id | integer | NO
legacy_orders | 3 | placed_on | date | NO
legacy_orders | 4 | amount | numeric | YES
(8 rows)The nullable amount is not a footnote. It changes how sum(amount) behaves, how refunds might be represented, and what a test should assert.
Constraints tell you which relationships the database promises:
SELECT tc.table_name,
tc.constraint_type,
kcu.column_name,
ccu.table_name AS references_table,
ccu.column_name AS references_column
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON kcu.constraint_schema = tc.constraint_schema
AND kcu.constraint_name = tc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_schema = tc.constraint_schema
AND ccu.constraint_name = tc.constraint_name
WHERE tc.table_schema = 'public'
AND tc.table_name LIKE 'legacy_%'
AND tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE')
ORDER BY tc.table_name, tc.constraint_type, kcu.column_name; table_name | constraint_type | column_name | references_table | references_column
------------------+-----------------+-------------+------------------+-------------------
legacy_customers | PRIMARY KEY | customer_id | legacy_customers | customer_id
legacy_customers | UNIQUE | email | legacy_customers | email
legacy_orders | FOREIGN KEY | customer_id | legacy_customers | customer_id
legacy_orders | PRIMARY KEY | order_id | legacy_orders | order_id
(4 rows)Read the references_* columns only on the FOREIGN KEY row — that is where they name the other table. For PRIMARY KEY and UNIQUE rows, constraint_column_usage simply reports the constraint's own table, which is why those rows appear to reference themselves.
Count before assuming size:
SELECT 'legacy_customers' AS table_name,
count(*) AS rows_seen
FROM legacy_customers
UNION ALL
SELECT 'legacy_orders' AS table_name,
count(*) AS rows_seen
FROM legacy_orders
ORDER BY table_name; table_name | rows_seen
------------------+-----------
legacy_customers | 4
legacy_orders | 6
(2 rows)Then read a plan before you argue about a query shape:
EXPLAIN (COSTS OFF)
SELECT lc.email,
sum(lo.amount) AS lifetime_value
FROM legacy_customers lc
JOIN legacy_orders lo
ON lo.customer_id = lc.customer_id
WHERE lc.status = 'active'
GROUP BY lc.customer_id, lc.email
ORDER BY lifetime_value DESC, lc.email; QUERY PLAN
-------------------------------------------------------------------
Sort
Sort Key: (sum(lo.amount)) DESC, lc.email
-> GroupAggregate
Group Key: lc.email
-> Sort
Sort Key: lc.email
-> Hash Join
Hash Cond: (lo.customer_id = lc.customer_id)
-> Seq Scan on legacy_orders lo
-> Hash
-> Seq Scan on legacy_customers lc
Filter: (status = 'active'::text)
(12 rows)Do not over-read a tiny fixture plan. The habit is the point: plan first, claim second.
Follow The Engine, Then Contribute Back
PostgreSQL release notes are not trivia. They tell you about new syntax, planner behavior, index changes, observability, compatibility breaks, and operational details that can change how you write or review SQL. Read them with a scratch database open. Reproduce one small example. Save the query and the result.
Community contribution does not start with a heroic patch. A useful bug report with PostgreSQL version, schema, query, expected behavior, actual behavior, and a small reproduction is a contribution. So is improving a team's internal runbook after you debug a migration. So is answering a colleague's question with a tested query instead of a hunch.
The database person brings concise evidence to reviews: "Here is the constraint," "here is the row count," "here is the plan," "here is the assertion that would fail if this assumption breaks."
The Standard Keeps Moving
SQL is not frozen. SQL:2023 includes a native JSON type with dot-notation accessors and SQL/PGQ property-graph queries. Those features point toward where the language is going: relational systems keep absorbing document and graph-shaped work.
Treat standards as direction, not a portability guarantee. Support for SQL:2023 native JSON and SQL/PGQ is still rare and varies by engine. This browser lesson does not promise either feature. The course stays PostgreSQL-first, and your professional habit should be the same one you used all along: verify the target engine before writing production SQL.
That posture keeps you current without making fake tooling promises. Read the standard headline, read your engine's release notes, then test the actual syntax on the database that will run it.
Try the spelunking loop below. The starter code builds the same tiny schema, inspects it, and reads one plan. Use the prompt to run one more catalog query before changing anything.
You now have the loop: inspect, explain, test, and communicate. The capstone asks you to own the data end to end: model it, load it, query it, tune it, secure it, document it, and leave proof behind.
Checkpoint
Answer all three to mark this lesson complete