Sync engine metadata grows to ~10× the user database

[KayLeung]

The sidecar sync meta DB is 757MB compared to 70MB for the production DB.

I had AI research this, and it suggests custom key-prefix encoding + zlib compression as a fix (e.g. shortening/removing repeated prefixes like sqlitedata_icloud_userModificationTime_).

some quick benchmark( ~70MB DB )

Full DB:

Partial:

For reference, SwiftData compresses its store CKRecord with LZFSE.

I'd like to find a fix upstream, or a hook point that lets us apply this without forking the project.

Thanks.

[mbrandonw]

Hi @KayLeung, thanks for the report. I'm not sure how to square this though:

Compressing just lastKnownServerRecord saves only 4mb, which makes sense since that is only the system fields, and compressing just _lastKnownServerRecordAllFields saves 350mb, which also makes sense since that is the full record. Yet somehow compressing both leads to a savings of 573mb, which is 219mb more than each savings combined.

Further, in the first image, which compression image is being used?

[KayLeung]

@mbrandonw,

the extra comes from the SQLite overhead (see point 4)

I re-test them again. For a open source library, go with no Dict by default is safer.

Drafted with AI assistance and reviewed by me. Every figure comes from a real
run against a real database.

Compress and compact the CloudKit metadatabase

The SQLiteData CloudKit sidecar reaches 11.7× the size of the database it
describes — 832 MB against 71 MB of user data — because both CKRecord
columns are NSKeyedArchiver blobs that are ~81% archiver scaffolding, nearly
identical from record to record. Compacting _lastKnownServerRecordAllFields
and dictionary-compressing both columns takes it to 82.6 MB (9.7%), with no
backfill of existing rows and ~43 µs/record on the sync path; without the dictionary, and with no
new dependency at all, 237.6 MB (27.9%). Every percentage in this document is
against the vacuumed 852.7 MB baseline, because every variant is measured
after a VACUUM too; against the 832.2 MB file as found, 82.6 MB is 9.9%.

Measured 2026-08-28 against a real .metadata-iCloud.*.sqlite sidecar from a
production iOS app: 100,710 synchronized records across 12 record types, one of
which holds 98% of the rows. Table and column names below are placeholders;
sizes, ratios and timings are unmodified measurements.

Contents

1. The problem
2. How it was measured
3. Results
   • If the dictionary is rejected
4. Why the file size does not track the payload
   • The threshold can decide between two codecs
5. Technique 1 — dictionary compression
6. Technique 2 — compaction
7. Technique 3 — key-prefix encoding
8. Design

---

The problem

file	size
the app database (the actual user data)	71.3 MB
its .metadata-iCloud.*.sqlite sidecar	832.2 MB

Nothing is wrong with the schema. sqlitedata_icloud_metadata stores two
NSKeyedArchiver blobs per synchronized record, and both are large relative to
the row they track:

column	short name	what it holds	avg size
lastKnownServerRecord	sys	CKRecord archived with encodeSystemFields(with:) — record ID, zone ID, change tag, timestamps. No field values.	1.9 KB
_lastKnownServerRecordAllFields	allFields	the same record archived with the full encode(with:) — system fields, every field value, and the per-column sqlitedata_icloud_userModificationTime_<key> entries that drive last-writer-wins	6.0 KB

That is 7.9 KB of sync bookkeeping per record, against roughly 700 bytes of
actual record. Of the 832 MB file, 802 MB is these two columns; the rest is
index and page overhead.

How it was measured

Every number in the results table is a real file size, not an estimate. For
each variant the harness:

1. copies the full 832 MB sidecar,
2. rewrites the named blob column(s) row by row,
3. verifies every one of the 100,710 records roundtrips,
4. runs VACUUM,
5. measures the file, the surviving payload per column, and mean encode/decode
   time per record.

Step 4 is why the baseline row reads 852.7 MB against the 832.2 MB file as found:
VACUUM rebuilds the file, and here that grows it, because repacking pushes
slightly more of the oversized blobs onto freshly allocated overflow pages. Since
every variant is vacuumed, 852.7 MB is the only apples-to-apples denominator — but
it does mean the ratios are marginally conservative against the file a user
actually has on disk.

Roundtrip verification differs by technique, and the difference matters:

• Compression is checked byte-exact: decompress(compress(x)) == x.
• Compaction cannot be, by construction — it discards the archiver's object
  graph. It is checked semantically: the rebuilt CKRecord must have the same
  recordID, recordType, and the same encrypted-value keys and values.

Results

Sorted by file size, largest first. Ties are broken by allFields payload.

variant	file MB	sys MB	allFields MB	dict KB	encode µs	decode µs	vs baseline
baseline (vacuumed)	852.7	194.2	606.5	—	—	—	—
lzfse: both columns	562.3	146.2	355.5	—	37.2	5.8	65.9%
zlib: both columns	524.1	130.6	345.6	—	64.9	13.3	61.5%
compact ¹	443.6	194.2	141.3	—	68.4	73.3	52.0%
compact + key-prefix ²	443.6	194.2	74.7	—	76.4	71.1	52.0%
compact ¹ + lzfse both	440.8	146.2	58.0	—	60.7	37.8	51.7%
compact + key-prefix ² + lzfse both	440.7	146.2	49.9	—	63.7	38.2	51.7%
compact ¹ + zlib both (no dictionary)	237.6	130.6	47.1	—	65.1	43.7	27.9%
compact + key-prefix ² + zlib both (no dictionary)	237.6	130.6	41.2	—	70.1	44.5	27.9%
zlib+dict: both columns	191.4	15.0	117.9	333.0	61.0	9.0	22.4%
compact ¹ + zlib+dict both	82.6	15.0	22.2	288.8	73.9	41.9	9.7%
compact + key-prefix ² + zlib+dict both ← smallest	80.8	15.0	21.2	280.0	81.4	43.0	9.5%

¹ compact — replace NSKeyedArchiver with a type-length-value encoding of
field values only; system fields are recovered from the sys column at decode
time. See Technique 2. That decode step needs both
columns of a row at once, which the harness had and the library's decoding seam
does not — every compact row in the table is contingent on
resolving that.

² key-prefix — not a separate technique, but one flag inside that encoder
(Compact.encode(_:keyPrefix:)). Every record repeats the 38-byte
sqlitedata_icloud_userModificationTime prefix once per column — 19 times in the
sample record. The flag replaces it with a single marker byte, taking a compacted
record from 1421 B to 751 B. It cannot exist without compaction: there is nowhere
to put a marker byte in an NSKeyedArchiver blob. See
Technique 3.

Rows ¹ and ² are therefore the same code path with one boolean flipped, and they
produce the same file size for a reason unrelated to that: see
the next section.

zlib is raw deflate (windowBits: -15) at level 6. Dictionaries are trained
per record type per column — 16 KB for sys, 32 KB for allFields — and the dict
column is their total stored cost.

Single-column compression variants are omitted from the table: all of them
landed at 99.6–99.9% of baseline, for reasons in the next section.

The smallest file costs the most to keep. Adding key-prefix encoding reaches
80.8 MB, 1.8 MB (2.2% relative) below compact + zlib+dict. That is a real
saving, and it is bought with three marker bytes and a special-cased key
namespace that can never change once records have been written with it. See
Technique 3.

If the dictionary is rejected

The dictionary carries real integration cost: a dictionary table, append-only
versioning, and Z_NEED_DICT verification. Every one of those disappears
without it.

Plain zlib is reachable from Compression directly — COMPRESSION_ZLIB is
raw DEFLATE (RFC 1951), no new table, no versioning, and the roundtrip stays
byte-exact. The whole change collapses to compaction plus two
framework calls.

One caveat before anyone re-runs this: the 237.6 MB figure was measured with libz
at level 6, because the harness already had libz linked for the dictionary
variants. Compression does not expose a level — COMPRESSION_ZLIB is fixed at
its own default. Expect a small difference in either direction, and note that the
page-packing margin here is thin: compact + zlib rows sit at a 1921 B median
against a ~2042 B threshold, so a codec that lands a few percent worse loses the
two-rows-per-page win and jumps back toward 440 MB. Re-measure the file size
before relying on this row.

	file	vs baseline	integration
compact + zlib	237.6 MB	27.9%	Compression only
compact + zlib + dictionary	82.6 MB	9.7%	import zlib, dictionary table, versioning

Most of that 155 MB gap is one column: sys stays at 130.6 MB without a
dictionary and drops to 15.0 MB with one. Archived system fields are almost pure
cross-record boilerplate — exactly what a dictionary exploits and what plain
deflate, seeing one record at a time, cannot.

The dictionary is worth 155 MB, a further 2.9× — but 237.6 MB is already a
3.6× reduction, and it is the variant most likely to be accepted upstream
unchanged.

Why the file size does not track the payload

Three results look wrong until you account for SQLite page geometry:

• Compressing sys alone does nothing. Every single-column variant landed at
  99.6–99.9% of baseline. Even crushing sys by 92.3% (194 → 15 MB) moves the
  file by 3.5 MB.
• Compressing both columns beats the sum of each alone. For lzfse: sys alone
  saves 0.7 MB, allFields alone saves 205.9 MB — but both together save
  290.4 MB, an extra 83.8 MB from nowhere.
• Key-prefix encoding halves allFields and changes the file by zero bytes.

All three are one mechanism.

SQLite splits a large row: local bytes stay in the table b-tree leaf, the
surplus goes to an overflow chain, and the split is chosen so the overflow chain
is exactly full. So every byte saved comes out of the leaf-resident part —
and a leaf page holds a whole number of rows.

Leaf-local sizes below are derived from SQLite's split rule; page counts are
measured with dbstat:

	avg row	leaf-local	rows per 4 KB leaf	leaf pages
baseline	7950 B	~3858 B	1	99,881
lzfse sys only	6550 B	~2458 B	1	~100,000
lzfse both	4982 B	~890 B	4	29,161

Compressing sys alone takes the leaf-local part from 3858 to 2458 bytes — still
one row per page. The 48 MB it saved becomes unused space in pages that were
already allocated.

dbstat confirms this directly rather than by arithmetic. Every variant that
leaves sys uncompressed lands on the same page count, whatever its payload:

db-compact          leaf: 100,369 pages, 411.1 MB, avg cells/page 1.00   (avg row 3331 B)
db-compact-prefix   leaf: 100,369 pages, 411.1 MB, avg cells/page 1.00   (avg row 2669 B)

avg cells/page 1.00 is the whole story: one row per leaf page. Two rows need
5338 B against a 4084-byte page, so halving the payload changes nothing. Both
files are 443.6 MB — 100,369 × 4096 = 411 MB of leaves, plus ~32 MB of indexes
and internal pages.

Only when both columns come down does the local part fall under ~1000 bytes
and rows start sharing a leaf. Key-prefix encoding fails the same test: while
sys sits uncompressed at 1.9 KB, halving allFields never crosses the line.

dbstat shows the other half of the story:

	leaf pages	overflow pages
baseline	409.1 MB (99,881)	411.1 MB (100,374)
lzfse both	119.4 MB (29,161)	411.1 MB (100,358) ← unchanged
zlib both	86.5 MB (21,121)	405.9 MB (99,089) ← unchanged
zlib+dict both	159.5 MB (38,940)	0.0 MB (0) ← gone

lzfse and zlib shrink the leaf side 3–5× and remove no overflow pages at all.
That 411 MB chain is a hard floor, which is why neither reaches below ~520 MB
however it is tuned.

Among the compression-only variants, dictionary zlib is the only one that brings
the average row under SQLite's ~4061-byte spill threshold, so rows stop
overflowing and the chain disappears. That cliff, not the compression ratio, is
most of the distance between 524 MB and 191 MB. (Compaction clears the same
threshold by a different route — see the next section.)

The threshold can decide between two codecs

The sharpest case is compact + lzfse against compact + zlib. Their average
rows are 15% apart; their files differ by 46%:

	avg row	cells/page	leaf pages	unused	file
compact	3331 B	1.00	100,369	64.3 MB	443.6 MB
compact + lzfse	2027 B	1.01	99,680	192.8 MB	440.8 MB
compact + zlib	1764 B	2.01	50,192	17.0 MB	237.6 MB

Two rows share a 4084-byte leaf page only if each is under ~2042 B, counting the
non-blob columns and cell overhead. The distribution, not the average, decides
it:

	median row	rows under ~2042 B
compact + lzfse	2183 B	1.4%
compact + zlib	1921 B	100%

zlib puts every row under the line and packs two per page. lzfse leaves 98.6% of
them above it, allocates twice the pages, and wastes 192.8 MB inside them —
ending up barely better than no compression at all.

The codec ranking is therefore not stable across datasets. On records
slightly larger than these, both codecs would miss the boundary and land together
near 440 MB. Measure the file, not the payload.

One consequence: the win is threshold-shaped and does not extrapolate. An app
whose rows already sit below the spill threshold gets the leaf-packing win only.

Technique 1 — dictionary compression

Both blobs are NSKeyedArchiver plists whose content is ~95% identical from row
to row. That redundancy is across records, not within one — which is exactly
what a generic codec cannot exploit and a preset dictionary can:

codec	sys	allFields
lzfse	−25%	−41%
zlib	−33%	−43%
zlib + 32 KB dictionary	−92%	−81%

Dictionary zlib is also faster than plain zlib — 61.0 vs 64.9 µs encode,
9.0 vs 13.3 µs decode — because matching against the dictionary leaves far less
literal data to Huffman-code.

lzfse is the obvious default, and it is the wrong one here. It is Apple's
recommended general-purpose codec and the usual reach for blob compression on
these platforms — but that recommendation is for opaque, one-off payloads, where
there is no cross-record redundancy for a dictionary to exploit. This table is the
opposite case: lzfse is faster per call (37.2/5.8 µs vs 61.0/9.0) but ~3× worse on
compressed size, and size is what this technique is for.

It is also worse than plain zlib for a reason that has nothing to do with
compression: paired with compaction it produces a 440.8 MB file against zlib's
237.6 MB, because its slightly larger records miss SQLite's page-packing
boundary (see above). And
COMPRESSION_ZLIB is available from the same Compression framework, so
choosing lzfse buys no integration simplicity in exchange.

lzfse cannot take a dictionary. Compression exposes no dictionary API. The
obvious workaround — compress dictionary ‖ payload and strip the prefix —
measures 93–142%, i.e. worse than not compressing, because
compression_encode_buffer emits the dictionary into its output. libz is
required — but reaching it costs nothing: the Apple SDKs vend zlib as a Swift
module, so a plain import zlib exposes z_stream, deflateSetDictionary and
inflateSetDictionary directly. Verified compiling against both the macOS SDK and arm64-apple-ios17.0.

Level barely matters. Level 9 buys 0.04 pp over level 6 for double the CPU;
level 1 costs 0.6 pp. Use level 6.

Dictionary size matters more, and has a floor and a ceiling. A dictionary
smaller than one record cannot help — 4 KB against 6 KB allFields records gave
24%, versus 4.7% at 8 KB. And 32 KB is the ceiling, because deflate's window
means deflateSetDictionary only ever uses the last 32 KB.

Construction matters more than either knob. Packing whole record archives and
stopping before the budget is exceeded gave 4.4%. Taking a byte suffix of a
concatenation — which truncates mid-record — gave 15.7% on the same data.

Technique 2 — compaction

A typical allFields blob is 6075 bytes, of which only 1169 are strings. The rest
is archiver scaffolding:

bplist00,  $archiver = NSKeyedArchiver,  $version = 100000
$top          48 keys — AllPCSKeyIDs, BaseToken, ChainPrivateKey,
                        ChainProtectionData, ConflictLoserEtags, … mostly $null
$objects     134 entries — 43 strings, 36 {$class: UID, …} wrappers,
                           33 byte blobs, 6 $classname records

total bytes            6075
  string payload       1169
  everything else      4906   object table, CFKeyedArchiverUID refs,
                              $class dicts, offset table

81% of the blob is scaffolding. Every value is a CFKeyedArchiverUID into
$objects. Every NSDate gets its own object plus a $class object. The
CKRecordValueStore is materialized three times, as RecordValues,
OriginalValues and ChangedKeys. And $top's 48 CloudKit system keys are
already stored in the sibling sys column.

Compaction replaces the archiver with a type-length-value encoding: each
field is written as its key, a one-byte type tag, and the value, with lengths
prefixed where they are not fixed. It carries field values only — system fields
come back from sys at decode time.

The same record, compacted:

marker + key                      key                    value
20                                count = 32
fd 06 "amount"                    amount                 double 1234.5
fd 08 "groupId"                   groupId                string "8f2c…a71d" (uuid)
fd 04 "date"                      date                   date   2016-08-25 02:00:00
fd 07 "dayKey"                    dayKey                 int    20160825
fe                                «UMT»                  int    1787650877444818000
ff 06 "amount"                    «UMT»_amount           int    1787650877444818000
 …                                (25 more fields)
fd 09 "updatedAt"                 updatedAt              date   2026-08-25 09:23:16
                                                         total  751 B

13 value fields and 19 per-column modification-time keys. Field names are
placeholders; byte counts are real.

6075 B → 1421 B values-only, → 751 B with key-prefix encoding.

Technique 3 — key-prefix encoding

Three marker bytes carry the key:

marker	meaning
fd	a literal key follows
fe	the bare sqlitedata_icloud_userModificationTime key
ff	that prefix, plus the suffix that follows

So a «UMT»_<column> key costs ff + a length byte + the column name, instead
of repeating the 38-byte prefix in full every time.

It halves the payload and is worth almost nothing. Measured in all three
pairings, it never once paid for itself:

paired with	allFields payload	file
nothing	141.3 → 74.7 MB	443.6 MB → 443.6 MB (no change)
zlib	47.1 → 41.2 MB	237.6 MB → 237.6 MB (no change)
zlib + dictionary	22.2 → 21.2 MB	82.6 MB → 80.8 MB (−1.8 MB)

Uncompressed and with plain zlib the file does not move at all. With the
dictionary it is worth 1.8 MB out of 82.6, because the dictionary already encodes
those repeated prefixes as back-references — the two techniques attack the same
redundancy.

dbstat shows where the removed bytes go. They do not leave the file; they
become holes in pages that were allocated anyway:

	payload (both columns)	leaf pages	unused inside them
compact	335.5 MB	100,369 = 411.1 MB	64.3 MB
compact + key-prefix	268.9 MB	100,369 = 411.1 MB	130.9 MB

Payload falls 66.6 MB and unused space rises by exactly 66.6 MB. With sys still
uncompressed a row is 2669 B, and two of those need 5338 B against a 4084-byte
page, so avg cells/page stays at 1.00. Collecting that 66.6 MB needs the row
under ~2042 B — which is what compressing sys does, and why key-prefix only ever
shows a saving once the dictionary has shrunk sys to ~150 B/record.

It is a permanent format complication for ~2%.

Design

Dictionaries: self-trained, in the metadatabase, append-only

The dictionary is required to decompress — deflate encodes matches as
back-references into it. It is part of the decoder, not a build-time
optimization that can be regenerated later.

A dictionary hardcoded into the library would be wrong, because it is trained on
the byte layout of one app's archived records. Train on device instead, and
store it beside the rows it decodes:

CREATE TABLE sqlitedata_icloud_recordDictionaries (
  recordType TEXT NOT NULL,
  kind       INTEGER NOT NULL,   -- 0 = sys, 1 = allFields
  version    INTEGER NOT NULL,
  dictionary BLOB NOT NULL,
  PRIMARY KEY (recordType, kind, version)
) STRICT;

Each blob carries a 4-byte header — magic | flags | dictVersion — naming the
dictionary that decodes it. Writes use the newest version; reads use the
version named in the header. A dictionary row is never updated and never
deleted.

Keeping dictionaries in the same file as the rows they decode — not a shipped
asset, not a sibling file — keeps them atomic through backup, restore, and
file-level copies.

The first ~60 records of a record type sync uncompressed. Once that many exist,
v1 is minted from them and used for everything written afterwards.

Behaviour under schema change

Neither technique needs existing rows rewritten, but for different reasons.

change	dictionary	type-length-value encoding
add a column	ratio degrades slightly; mint v2 when you care	new key appears inline, nothing to do
rename a column	ratio degrades; v2	old rows carry the old key, new rows the new
remove a column	ratio degrades; v2	key simply absent
new record type	uncompressed until trained	works from the first record

The dictionary is trained on archived CKRecord bytes, not on the SQLite schema
— the schema appears only indirectly, as column names inside archived field
keys.

The type-length-value encoding is self-describing: the keys in the blob are the
schema, so an old blob and a new blob decode with the same code. If ratio drifts
far enough to matter, mint v2 and leave v1 in place; old rows migrate naturally
as CloudKit returns fresh server records.

Neither is app-specific. The only constant in the encoding is
sqlitedata_icloud_userModificationTime, which is SQLiteData's own
String.sqliteDataCloudKitSchemaName — not an app's table name.

The gap that must close before this ships

Value type coverage. The prototype encoder handles NSNumber, String,
Date, Data and nil, then falls through to String(describing:), which is
silently lossy.

Scanning all 100,710 records in this sidecar:

rows 100710   null sys 352   null allFields 352
  2283921  NSNumber
   598286  String
   298444  Date

Only three types — no Data, no CKAsset. That is why it roundtripped cleanly
here, and it is not evidence that it is safe. SQLiteData's
CKRecord.setBytes(_:forKey:at:) writes any BLOB column as a CKAsset, and
CKReference, CLLocation and list types are all legal CKRecordValues. Any
app with a blob column would hit the fallback and corrupt that field.

The fix is a per-record escape rather than exhaustive type handling: if any value
is not representable, store the original archive unchanged and clear the
"compacted" header flag. Mixed encodings in one column, decided per row, resolved
by the header.

[mbrandonw]

Hi @KayLeung, I cannot take the time to read 3,500 words of AI gobbledygook. This wall of text has only detracted from the point you are trying to make because no one is going to take the time to read all of this. And there's a chance this is a real issue to deal with, but this discussion is close to be relegated to the AI nonsense pile.

I highly recommend you rewrite the above post in your own words, and simply answer the few questions I had:

Compressing just lastKnownServerRecord saves only 4mb, which makes sense since that is only the system fields, and compressing just _lastKnownServerRecordAllFields saves 350mb, which also makes sense since that is the full record. Yet somehow compressing both leads to a savings of 573mb, which is 219mb more than each savings combined.

Further, in the first image, which compression image is being used?

Should hopefully only take a few sentences to do.

[KayLeung]

Hi @mbrandonw,

In short: Acutal Usage VS Disk Usage. Average records are 4kb+ (which saves as 8kb on disk, sqlite is 4kb pages). Now they are below 4kb, which cuts the size in half.

The last two columns show the actual usage. While this change saved 100MB+ in actual usage, the overall disk usage remained almost the same. This is because most of the lastKnownServerRecord data was already under 4kb.

Sad and Fact, AI understands this library better than me. I can only verify the direction is real. I've performed real test, real compression agaist the database, not AI guess the result.

This is not a random AI PR anyway.

[KayLeung]

My primary concern is disk usage, as taking up too much storage is a common reason users uninstall our app.

Anyway, another point that worries me: iCloud storage usage is about 4x higher than SwiftData just to save the exact same data. It reached 2.3GB in iCloud. I will try to figure out it....but I hit apple quota limits......