Data modeling series: 1. The data modeling process · 2. JSON columns, online DDL, data types and views · 3. Case studies: translations, ordering, calendars and reports
A schema never stands still: new attributes every sprint, tables growing to hundreds of millions of rows, views appearing “to keep things tidy”. Afterwards you’ll know when to extend a schema with a JSON column or a real one, how to change it without downtime, which types to pick for strings, numbers, money and time, and where views belong. The original slides target older MySQL, so each piece of advice is re-checked on MySQL 8.4 and PostgreSQL 16 (Docker on Apple Silicon, so read the numbers as ratios).
Read first: part 1 for the table template and its data JSON column, and the database basics post for MySQL’s three ALTER algorithms.
At a glance:
flowchart TD
Q{Attribute used for?} -->|"filter, JOIN, sort, constraint"| COL[Real column]
Q -->|"sparse, display only"| J[JSON]
J --> IX["Index: generated column / GIN"]
COL --> ADD["Add column: INSTANT / metadata"]
ADD -.->|"metadata lock"| ML["Short lock_timeout + retry"]
M["Money: DECIMAL, never FLOAT"]
V[View] --> VG["Joined GROUP BY: computes all"]
V --> SI[security_invoker]
V -.-> MV["Materialized view: fast, maybe stale"]
1. Extending a schema: JSON columns, spare columns and online DDL
Key idea: the slides proposed JSON columns and spare columns because of three limits of old MySQL. MySQL 8 and PostgreSQL removed them all, so now you just add real columns through migrations and keep JSON for the sparse part.
| Old MySQL problem | The slides’ answer | Today |
|---|---|---|
| Adding a column locked the table (downtime) | a data JSON column, so new attributes need no ALTER |
INSTANT (MySQL 8), metadata change (PG 11+) |
| No indexes on JSON expressions | pre-create spare columns c1 VARCHAR(200), c2, …, c6 INT and document what each means |
generated column, expression index, GIN |
| Before MySQL 5.6, adding an index locked the table | pt-online-schema-change or Online DDL INPLACE, LOCK=NONE (from 5.6) |
Online DDL |
Measured on 1 million rows: adding a column with INSTANT takes 3.6 ms, the old COPY way takes 3.56 s because it rewrites the table.
ALTER timings on MySQL 8.4 and PostgreSQL 16 (1 million rows)
| Operation (MySQL 8.4, 1 million rows) | Time |
|---|---|
ADD COLUMN nickname VARCHAR(50) NULL, ALGORITHM=INSTANT |
3.6 ms |
ADD COLUMN loyalty_tier TINYINT NOT NULL DEFAULT 0, ALGORITHM=INSTANT |
2.6 ms |
ADD INDEX (name), ALGORITHM=INPLACE, LOCK=NONE |
0.72 s, writes not blocked |
MODIFY name VARCHAR(200) from VARCHAR(100), INPLACE |
1.2 ms (INSTANT rejected) |
ADD COLUMN c1 VARCHAR(10), ALGORITHM=COPY (the old way) |
3.56 s, rewrites the whole table |
| Operation (PostgreSQL 16, 1 million rows) | Time |
|---|---|
ADD COLUMN loyalty_tier smallint NOT NULL DEFAULT 0 |
0.8 ms (metadata only, since PG 11) |
ADD COLUMN signup_token uuid NOT NULL DEFAULT gen_random_uuid() |
1.0 s (a volatile default forces a full rewrite) |
pt-online-schema-change works in four steps: create a new table, copy the data gradually, keep it in sync with triggers, then rename.
Indexing a JSON field no longer needs spare columns. A generated column is one the database computes from an expression; an expression index indexes the expression’s result directly; GIN is an inverted index that answers “does this JSON contain that key/value”.
-- MySQL 8: virtual generated column + ordinary index
ALTER TABLE app_user
ADD COLUMN referral_code VARCHAR(20)
GENERATED ALWAYS AS (data->>'$.referral_code') VIRTUAL,
ADD INDEX idx_referral (referral_code);
-- A query written against the original expression still uses the index:
EXPLAIN SELECT user_id FROM app_user WHERE data->>'$.referral_code' = 'R4242';
-- type: ref, key: idx_referral, rows: 1 (before: type ALL, rows 992,791)
-- MySQL 8.0.17+: multi-valued index for arrays inside JSON
ALTER TABLE app_user ADD INDEX idx_tags ((CAST(data->'$.tags' AS CHAR(20) ARRAY)));
SELECT COUNT(*) FROM app_user WHERE 't13' MEMBER OF (data->'$.tags'); -- key: idx_tags
-- PostgreSQL: expression index for one key, GIN for containment queries
CREATE INDEX app_user_referral_idx ON app_user ((data->>'referral_code')); -- 21 MB
CREATE INDEX app_user_data_gin ON app_user USING gin (data jsonb_path_ops); -- 37 MB
SELECT count(*) FROM app_user WHERE data @> '{"tags":["t13"]}'; -- 23 ms, 20,000 rows
Online DDL (changing the schema while reads and writes continue) handles adding indexes; PostgreSQL uses CREATE INDEX CONCURRENTLY. External tools such as pt-online-schema-change (trigger-based) and gh-ost (reads the binlog, no triggers) remain useful when an operation only runs as COPY or you need to throttle it. The three ALTER algorithms and expand/contract are in the database basics post.
2. Choosing data types
Key idea: the slides’ three rules still hold: smaller is usually better, simple is good, avoid NULL when you can. Two corrections: the time zone behaviour of TIMESTAMP/DATETIME, and “avoid NULL” doesn’t mean inventing fake values.
| Family | Pick | Note |
|---|---|---|
| Strings | VARCHAR for short, often-read strings; TEXT for logs, comments |
the criterion is length and query pattern, not update frequency as the slides say |
| Integers | the smallest type that fits; BIGINT from day one for ids that may grow |
INT(1) = INT(11), the number is only a display width |
| Money | DECIMAL(p, s) or integers in the smallest unit |
never FLOAT/DOUBLE |
| Time | store UTC, with fractional seconds | MySQL’s TIMESTAMP stops at 2038 |
| NULL | NOT NULL + default for columns that always have a value |
genuinely unknown data stays NULL |
Strings and integers in detail on MySQL and PostgreSQL
Strings in MySQL. VARCHAR uses only the bytes it needs plus 1–2 length bytes. InnoDB may move TEXT/BLOB values to separate overflow pages; they can’t be indexed over their full length (prefix indexes only) and can’t use an index for sorting.
Strings in PostgreSQL are completely different: varchar(n) and text are stored the same way. Values longer than about 2 KB are compressed and moved to TOAST (a side table for large values) automatically; varchar(n) merely adds a length check.
Integers. Changing a primary key type later means migrating the table and every referencing table, so choose BIGINT early. MySQL 8.4 warns Integer display width is deprecated on CREATE TABLE, drops the width from the definition, and INT(1) happily stores 123456789.
Money. IEEE 754, behind FLOAT/DOUBLE, stores the closest base-2 value, so 0.1 has no exact representation. Run on JDK 21:
double d = 0;
BigDecimal b = BigDecimal.ZERO;
for (int i = 0; i < 10; i++) {
d += 0.10;
b = b.add(new BigDecimal("0.10"));
}
// d = 0.9999999999999999, d == 1.0 -> false
// b = 1.00, compared with 1.00 -> true
new BigDecimal(0.1); // 0.1000000000000000055511151231257827021181583404541015625
BigDecimal.valueOf(0.1); // 0.1
new BigDecimal("100.00").divide(new BigDecimal("3"), 2, RoundingMode.HALF_EVEN); // 33.33, times 3 = 99.99
In the database use DECIMAL or integers, as in the flight booking schema. The last line is a reminder: splitting money still needs a rounding rule and a home for the remainder (100.00 split three ways means one person pays 33.34).
Time. The slides compare MySQL’s TIMESTAMP and DATETIME but get the time zone part backwards:
The slides also suggest: store fractional seconds (TIMESTAMP(3)), also store the time zone when a moment is tied to a place (a doctor’s appointment, a departure time), and run the JVM, OS and database in UTC. timestamptz, Instant and DST are covered in dates and times in the backend and storing dates in the database and JSON.
NULL. The slides (following High Performance MySQL) advise against nullable columns because they complicate indexes, statistics and comparisons. But don’t invent fake values ('1970-01-01', -1, an empty string) for unknown data: counts, averages and comparisons silently go wrong. The paid_at of an unpaid order should be NULL.
3. Views: convenient, and easy to be fooled by
Key idea: a view stores only a query; the data stays in the base tables. It’s fast when the database can fold it into your query, and surprisingly slow when it must compute the whole view first (a GROUP BY view that gets joined).
The slides’ example: CREATE VIEW simple_bookings AS SELECT book_ref, date(book_date), total_amount FROM bookings. MySQL runs a view with one of two algorithms:
- MERGE (the default when possible): merges the user’s query with the view definition into one statement on the base tables, so indexes can be used.
- TEMPTABLE: computes the view into a temporary table, then runs the user’s query on it. Required when the view has
GROUP BY,DISTINCT, aggregates,LIMITorUNION. ForcingALGORITHM=MERGEonly earns warning 1354 and MySQL still uses TEMPTABLE.
Older material says TEMPTABLE “loses the indexes”; since MySQL 8.0.22, a condition on the grouping column is pushed inside the view, but a condition coming through a JOIN is not.
View experiments on MySQL 8.4 and PostgreSQL 16
MySQL 8.4, a 500,000-row click table, view v_click_count (GROUP BY user_id):
| Query | Result |
|---|---|
Simple view: SELECT * FROM v_click_simple WHERE user_id = 42 |
MERGE: ref on idx_user, 25 rows |
SELECT * FROM v_click_count WHERE user_id = 42 |
Derived table, but the condition is pushed inside: ref on idx_user, 25 rows |
JOIN app_user (3 users) with v_click_count |
Materializes the whole view: scans 500,000 rows, builds 20,000 groups, 65 ms |
Same query written directly against click |
Index lookups for 3 users, 0.05 ms |
A derived table is the intermediate result of a subquery in FROM. PostgreSQL 16 behaves the same: filtering the view directly takes 0.06 ms, joining it with a high range of author_id values aggregates 1.8 million rows in 242 ms.
A materialized view stores the result and must be refreshed to update. MySQL has none; PostgreSQL does:
CREATE MATERIALIZED VIEW mv_post_count AS
SELECT author_id, count(*) AS cnt FROM post GROUP BY author_id;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_post_count;
-- ERROR: cannot refresh materialized view "public.mv_post_count" concurrently
-- HINT: Create a unique index with no WHERE clause ...
CREATE UNIQUE INDEX ON mv_post_count (author_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_post_count; -- 417 ms, readers not blocked
A plain REFRESH locks out readers for the whole run; CONCURRENTLY doesn’t, but it’s slower because it diffs row by row. The data is stale until the next refresh: fine for reports, not for balances.
The three uses of views from the slides:
- Simplifying complex queries (joins, functions): handy for analysts; in application code I keep queries in the repository, where they’re easy to read and test.
- A security layer: HR sees profiles, accounting sees balances. In PostgreSQL a view runs with its owner’s privileges by default, so picking the mode is a deliberate security decision.
- Backward compatibility: rename the table
usertoaccountand create a viewuseroveraccountso old code keeps working.
Simple views (one table, no aggregation) are updatable in both databases. The slides advise against it; if you do, add WITH CHECK OPTION so nobody can write rows outside the view’s condition.
Testing view privileges and WITH CHECK OPTION on PostgreSQL 16
- Owner’s privileges: a role
hrwith no privilege on theauthortable still read 100,000 rows through the view. WITH (security_invoker = true)(since PG 15): the view checks the caller’s privileges, andhrgetspermission denied.security_barrier: stops user-written functions from peeking at filtered-out rows.WITH CHECK OPTION: a viewv_sg_authorfilteringcountry = 'SG'rejects moving an author toVNthrough the view withnew row violates check option.
Exercises
Exercise 1: Remove spare columns from a legacy table
Task: a legacy customer table uses c1 VARCHAR(200) for a referral code and c5 INT for a loyalty tier. Turn them into properly named columns without downtime.
Solution to Exercise 1: expand/contract in six steps
An outline, not run end to end on real data. Expand/contract: add the new thing alongside the old, move over gradually, and only then remove the old. Why dual writes are needed while old and new versions run side by side in a rolling update is in Deployment strategies.
- Expand:
ALTER TABLE customer ADD COLUMN referral_code VARCHAR(20) NULL, ADD COLUMN loyalty_tier TINYINT NULL, ALGORITHM=INSTANT. - Dual write: the new application version writes both
c1andreferral_code, still reading fromc1. - Backfill in batches:
UPDATE customer SET referral_code = c1, loyalty_tier = c5 WHERE id BETWEEN ? AND ? AND referral_code IS NULL, a few thousand rows per batch, pausing between batches so replicas keep up. - Verify: the count of mismatched rows is zero; add
NOT NULLand indexes if needed. - Switch reads to the new columns; one release cycle later, stop writing
c1andc5. - Contract:
DROP COLUMN c1, DROP COLUMN c5(MySQL 8.0.29+ can do it withINSTANT).
Exercise 2: Fix a JOIN against a counting view
Task: an admin screen shows 3 authors with their post counts by joining author with the view v_post_count (SELECT author_id, count(*) FROM post GROUP BY author_id). On 2 million posts the query takes hundreds of milliseconds. Explain and fix it.
Solution to Exercise 2: LATERAL instead of joining the view (PostgreSQL 16)
-- Before: the view is aggregated almost entirely, then merge-joined
SELECT a.author_id, v.cnt
FROM author a JOIN v_post_count v USING (author_id)
WHERE a.author_id BETWEEN 90000 AND 90002;
-- GroupAggregate (actual rows=90002) <- Index Only Scan on post (actual rows=1800041)
-- Execution Time: 241.796 ms
-- After: count per author with LATERAL
SELECT a.author_id, c.cnt
FROM author a
CROSS JOIN LATERAL (
SELECT count(*) AS cnt FROM post p WHERE p.author_id = a.author_id
) c
WHERE a.author_id BETWEEN 90000 AND 90002;
-- Nested Loop -> Aggregate (loops=3) -> Index Only Scan on post (rows=20, loops=3)
-- Execution Time: 1.049 ms
The author_id BETWEEN ... condition sits on author, so the planner can’t push it into the view’s GROUP BY. It picks a merge join and has to aggregate every author with an id below 90,002.
Writing it with LATERAL (or a correlated subquery in the SELECT list) turns it into 3 lookups in the post (author_id, ...) index. On MySQL 8.4 the equivalent fix is to query click directly and GROUP BY after the join (0.05 ms versus 65 ms in section 3). If the screen needs counts for many authors at once, the denormalized counter from part 1 or a materialized view fits better.
Remember
- Explain why spare columns
c1..c6once made sense and why they’re an anti-pattern now. - Know when an attribute goes into JSON versus a real column, and how to index a JSON field on MySQL and PostgreSQL.
- Explain why an “online” ALTER can still stall a table, and how to prevent it.
- Know which types to use for money and time, and point out what the slides got wrong about
TIMESTAMP/DATETIME. - Explain when a view can use an index and when it’s computed in full; know the privilege trap of PostgreSQL views.
Interview questions
Junior
Does a view store data?
A regular view doesn’t; it stores only the query.
Every read re-runs it against the base tables. A materialized view (PostgreSQL has them, MySQL doesn’t) stores the result and must be refreshed to pick up changes.
Why not store money as FLOAT or DOUBLE?
Because IEEE 754 stores the nearest binary value, so 0.1 can’t be represented exactly.
Adding 0.10 ten times as a double in Java gives 0.9999999999999999. Use DECIMAL in the database and BigDecimal (built from a string or valueOf) in Java, or integers in the smallest currency unit.
How do INT(1) and INT(11) differ?
Not at all in storage or range: both are 4-byte INTs.
The number in parentheses is only a display width, which MySQL 8 has deprecated and drops from the table definition.
Mid
How do TIMESTAMP and DATETIME differ in MySQL?
TIMESTAMP follows the session time_zone and stops at 2038; DATETIME stores the value as given, with no zone.
TIMESTAMP is stored internally as UTC, takes 4 bytes and ends on 2038-01-19. DATETIME takes 5 bytes and goes to year 9999. For data beyond 2038 or deliberate local times, use DATETIME plus a time zone column; for system instants, store UTC.
How do MERGE and TEMPTABLE views differ in MySQL?
MERGE folds the user’s query into the view and runs it on the base tables; TEMPTABLE computes the view into a temporary table first.
MERGE can use indexes. TEMPTABLE is required for GROUP BY, DISTINCT, aggregates, LIMIT and UNION. Since 8.0.22 filters on grouping columns are pushed inside, but conditions coming through a JOIN are not.
How do varchar(n) and text differ in PostgreSQL?
In storage they’re the same; varchar(n) just adds a length check.
Long values are compressed and moved to TOAST automatically in both. That’s unlike MySQL, where TEXT may live off-page, can only get prefix indexes and can’t use an index for sorting.
Senior
When does an attribute go into the JSON column and when does it become a real column?
A real column when it’s used for filtering, joining, sorting or constraints, or most rows have it; JSON for sparse, display-only attributes.
JSON suits attributes that vary by record type. For occasional filters, a generated column or an expression/GIN index; once it becomes a primary filter, promote it. Adding columns is cheap now (INSTANT in MySQL 8, constant defaults in PG 11+), so “avoiding ALTER” is no longer a reason.
An online ALTER still froze production. Why, and how do you prevent it?
Because the ALTER still needs an exclusive metadata lock for a moment, and it has to wait for long transactions touching the table.
While the ALTER waits, every new query queues behind it, so the whole table stalls. Prevent it with a short lock_wait_timeout/lock_timeout for the migration session plus retries, checking for long transactions first, and running during off-peak hours.
What should you watch for when using views as a permission layer in PostgreSQL?
By default a view runs with its owner’s privileges, so a role granted only the view still reads the base table through it.
That’s the mechanism, but also a hole if unintended. Since PG 15, security_invoker = true makes the view check the caller’s privileges. security_barrier prevents user-written functions from seeing filtered rows. For per-row permissions, consider row-level security.
What does REFRESH MATERIALIZED VIEW CONCURRENTLY require and what do you get for it?
It requires a unique index with no WHERE clause on the materialized view; in return, readers aren’t blocked during the refresh.
Without that index PostgreSQL refuses. The cost is a slower refresh because old and new results are diffed row by row. The data is still stale until the next refresh.
Useful phrases for interviews
- “Spare columns made sense when adding a column locked the table; with instant DDL I’d just add a properly named column through a migration.”
- “Sparse attributes go into the JSON column; anything we filter on becomes a real, typed column.”
- “I index the JSON path with a generated column in MySQL, or an expression or GIN index in Postgres.”
- “Even online DDL needs a brief metadata lock, so I set a short lock timeout and retry instead of letting traffic queue up.”
- “Money goes into DECIMAL or integer minor units, never floating point.”
- “In MySQL it’s TIMESTAMP, not DATETIME, that’s converted with the session time zone, and TIMESTAMP stops at 2038.”
- “A view with GROUP BY that gets joined is materialized in full, so I always check the plan of queries on views.”
- “In Postgres a view runs with the owner’s privileges unless it’s created with security_invoker.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.