mirror of
https://github.com/discourse/discourse.git
synced 2026-08-07 13:19:19 +08:00
a0b7033f31
Part of the uploads rework. The IntermediateDB derives upload ids by
hashing (XXH3-128) because uploads have no natural id, and those ids
end up in the table's PK and in every *upload*_id reference column. I
wanted actual numbers on whether storing the hash as a 16-byte BLOB
instead of the current 24-char base64 TEXT is worth it before building
more on top of the current format.
It is. Full run (5M sources, 20M references, 4-shard merge,
i9-13900H/NVMe):
metric TEXT BLOB delta
source insert rows/s 74675 83442 +11.7%
ref insert rows/s 1244109 1422542 +14.3%
point lookups/s 207467 301823 +45.5%
join s 22.73 21.33 -6.1%
shard merge s 21.44 19.43 -9.3%
db file size 1.7 GB 1.3 GB -22.6%
Nothing gets slower - the gain is pure key width in the PK b-tree and
every reference index, and it bites twice in the shard-merge path
(table + index). The trade-off is ergonomics: ids stop being readable
in query output and ad-hoc SQL needs hex(id) / x'...' literals.
The script mirrors the converter's write path (prepared statements,
batched transactions, same pragmas) and is parameterized via env vars;
results are recorded in RESULTS.md.
|
||
|---|---|---|
| .. | ||
| config/schema | ||
| lib | ||
| scripts | ||
| spec | ||
| .rspec | ||
| Gemfile | ||
| migrations-tooling.gemspec | ||
