Try it with tables
Series: jsonb migration · Part 4 of 4
- Defining the problem space
- Moving away from nested loops
- TOASTed
- Try it with tables
← Previous partNext part →
I think anyone who reads this will think why go into so much depth with JSONB? Wouldn’t just splitting the data into tables be easier, and probably more efficient?
Let’s see!
Splitting the data into tables
beat_mix (+ ord)
beat_mix_tag (+ ord) []string
beat_mix_mood (+ ord) []string
beat_pattern (+ ord)
beat_pattern_preset (+ ord) []string, only [0] is read
beat_mixer (1:1)
beat_mixer_send (+ ord) [0]=reverb [1]=delay [2]=master
beat_hit (+ ord, + 15 inlined sound columns)
beat_layer (+ ord) first match wins
beat_fx (+ ord) the chain
sample_lookup (flat)
Storage
| size | |
|---|---|
jsonb blob (compressed) |
412 kB |
| flat derived table | 317 kB |
| normalized (3 of the 11 tables) | 3,032 kB |
Three tables cost 7.4× the whole compressed document. Decomposing destroys compression, and Postgres charges ~24 bytes of tuple header per row, which on 322,920 rows adds up.
Most of the time I see normalization as the space efficient option. It’s intersting here because it’s not the case.
Row order
An interesting thing happens if you don’t persist the order (ORDER BY ord).
Sure, this is just a mock example, but it can bite you if you don’t know about it.
From the document, JSONB:
kick |. . . . . . # x X X . . . . # .|
snare |. . . . . x x X . . - . . . . -|
hihat |. . . . . . . - . . - . - x # .|
bass |X . . . X . . . - . . - x . # X|
From the tables, with ORDER BY ord:
kick |. . . . . . # x X X . . . . # .|
snare |. . . . . x x X . . - . . . . -|
hihat |. . . . . . . - . . - . - x # .|
bass |X . . . X . . . - . . - x . # X|
From the tables, without ORDER BY ord:
kick |. . . . . . X x X X . . . . X .|
snare |. . . . . x x X . . - . . . . -|
hihat |. . . . . . . - . . - . - x X .|
bass |X . . . X . . . - . . - x . X X|
Why did # become X? Because our velocity layers are first-match-wins:
"layers": [
{ "min_velocity": 120, "symbol": "#" },
{ "min_velocity": 100, "symbol": "X" },
{ "min_velocity": 64, "symbol": "x" },
{ "min_velocity": 0, "symbol": "-" }
]
Then, a hit at velocity 124 checks 120 first and renders #. However, if you
put 100 first, the same hit renders X.
I’ll just push on this a bit more, to see where this might happen IRL.
UPDATE r_layer SET min_velocity = min_velocity WHERE ...;
Maybe you think it’s a bit convoluted, since I’m assigning it’s own current value
to every row, therefore nothing changes. But the important thing to note, is that
UPDATE in Postgres is kind of delete and then insert. Ergo, each row got rewritten:
-- WITH ORDER BY ord -- WITHOUT
ord | min_velocity ord | min_velocity
-----+-------------- -----+--------------
1 | 120 2 | 100
2 | 100 3 | 64
3 | 64 4 | 0
4 | 0 1 | 120
Whether it’s a no-op update like this, or a real edit, it would reorder the array nevertheless.
In the document, order is preserved. In the table split, now we have to carry an ord
column.
Other scenarios
Let’s say we want to see Which projects use this sample?. This would need us to go four array levels deep. No index would get chosen (remember TOAST), and we would end up scanning all ~hundred entries.
What we could do is have one flat, denormalized table:
CREATE TABLE hits_flat AS
SELECT b.id AS beat_id, b.title, m->>'label' AS mix,
h#>>'{sound,sample_id}' AS sample_id
FROM beats b
CROSS JOIN LATERAL jsonb_array_elements(b.payload->'mixes') m
CROSS JOIN LATERAL jsonb_array_elements(m->'patterns') p
CROSS JOIN LATERAL jsonb_array_elements(p->'hits') h;
CREATE INDEX ON hits_flat (sample_id);
| time | |
|---|---|
| containment over the blob, planner’s choice | ~112 ms |
| full-document GIN, forced | ~3.4 ms |
targeted GIN on the projected sample_id path, forced |
~4.3 ms |
| flat table | ~0.013 ms |
Using this flat table is also convenient. We don’t have to have: ord columns,
foreign keys, joins, we can truncate it, rebuild it from documents,etc.
Comparison
| document + projections | 11 normalised tables | |
|---|---|---|
| tables | 2 (+3 columns) | 11 |
| round trips | 1 | 5 (or one 32× product) |
| storage per project | 729 kB | 3,032 kB (3 of 11) |
| repository code | json.Unmarshal + one INSERT … SELECT |
~280 lines of reassembly |
ORDER BY invariants |
0 | 8 |
| “which projects use this sample?” | 0.013 ms | needs the joins |
| firmware adds a field | nothing | migration + DDL + ingest + backfill |
| byte-exact re-export | free | impossible |
So? Document or table?
For our usecase, it the document is faster and easier to use. We do have to keep in mind a couple of restraints that pop up normally when thinking about a similar situation:
- Can’t design the format and can’t version it
- Hand it back to other actors intact
- Order matters
PostgreSQL 16.15 in Docker, 100 projects averaging 2042 kB, Go 1.26.6. Schema,
generator, render demo and raw EXPLAIN output in the companion repo.