SpatiaLite & GeoPackage Automation with Python

Monitoring GeoPackage File Growth Over Time

Record pagecount, freelistcount and the size of any -wal sidecar alongside the file size in every run's manifest. Bytes on disk alone cannot distinguish a…

Record page_count, freelist_count and the size of any -wal sidecar alongside the file size in every run’s manifest. Bytes on disk alone cannot distinguish a container that gained data from one that deleted rows and kept the pages, or from one whose write-ahead log has simply never been checkpointed — and the three cases call for entirely different responses.

This page belongs to the Data Quality Gates & Monitoring guide.

Why This Matters

On a field device, storage is fixed and a container that grows without bound eventually stops the deployment. The unhelpful part is that file size is an ambiguous signal: SQLite never returns pages to the filesystem on its own, so a container that deleted half its rows is exactly as large as it was, with the freed pages sitting on an internal free list waiting to be reused.

That means a growth alert based on bytes fires for three unrelated situations — real growth, accumulated free pages after deletions, and an unchecked write-ahead log — and only one of them is about the data. Distinguishing them costs two pragmas.

Prerequisites

  • A manifest emitted per run, per How to Write a Data Quality Gate for a Field Dataset
  • sqlite3 on the path — nothing here needs the spatial extension
  • A few runs of history, so a trend exists to look at
  • Knowledge of the device’s storage budget, which is what any threshold should come from

Primary Method

python
# growth.py — the four numbers that describe a container's size honestly
import sqlite3
from pathlib import Path


def size_profile(path: str) -> dict:
    p = Path(path)
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    try:
        page_size = conn.execute("PRAGMA page_size").fetchone()[0]
        page_count = conn.execute("PRAGMA page_count").fetchone()[0]
        freelist = conn.execute("PRAGMA freelist_count").fetchone()[0]
    finally:
        conn.close()

    wal = p.with_name(p.name + "-wal")
    return {
        "bytes_on_disk": p.stat().st_size,
        "page_size": page_size,
        "page_count": page_count,
        "free_pages": freelist,
        "free_bytes": freelist * page_size,
        "live_bytes": (page_count - freelist) * page_size,
        "wal_bytes": wal.stat().st_size if wal.exists() else 0,
    }

live_bytes is the number that answers “is this container growing?”. free_bytes answers “is it fragmenting?”. wal_bytes answers “has anyone checkpointed?”. Watching only the first field of the four gives an alert that cannot tell you which question just changed.

What the bytes on disk are actually made ofA container's size on disk is live pages holding data, free pages left behind by deletions and available for reuse, and separately a write-ahead log sidecar that is not part of the file at all. Growth in live pages means the dataset grew. Growth in free pages means rows were deleted and the space was retained. A large log means nothing has checkpointed. Only the first is about the data.One file, three numberslive pagesthe data — growth here is realfree pagesdeleted, retained, reusable-wal sidecar — a separate file entirelycommitted pages not yet folded in · disappears on checkpointa size alert on the total cannot say which one movedand the three have different responses
Two pragmas separate the first two; a directory listing finds the third.

Step-by-Step Walkthrough

1. Add the profile to the manifest

python
manifest["size"] = size_profile(container_path)

Recording all four numbers costs nothing and makes the history interpretable later. A manifest holding only bytes leaves a future investigation with the same ambiguity this page is about.

2. Compare the right number between runs

python
def growth_report(now: dict, was: dict) -> list[str]:
    notes = []

    live_drift = (now["live_bytes"] - was["live_bytes"]) / max(was["live_bytes"], 1)
    if abs(live_drift) > 0.25:
        notes.append(f"live data moved {live_drift:+.1%}")

    free_frac = now["free_bytes"] / max(now["bytes_on_disk"], 1)
    if free_frac > 0.20:
        notes.append(
            f"{free_frac:.0%} of the file is free pages — a VACUUM would reclaim "
            f"{now['free_bytes'] / 1e6:.1f} MB"
        )

    if now["wal_bytes"] > now["live_bytes"] * 0.10:
        notes.append(
            f"write-ahead log is {now['wal_bytes'] / 1e6:.1f} MB — checkpoint before shipping"
        )

    return notes

Three separate conditions, three separate messages, three different fixes. That is the whole benefit over a single size threshold.

3. Reclaim free pages deliberately, not routinely

VACUUM rewrites the entire container to eliminate free pages, which takes an exclusive lock and time proportional to the file. It is the right response to a large one-off deletion and the wrong response to a container that cycles rows continually, where the free pages will be reused anyway.

bash
# After a bulk delete, before shipping — never during a sync window
sqlite3 field.gpkg "VACUUM;"

One caveat matters for spatial containers: VACUUM may renumber implicit row identifiers, and the R-tree keys entries by those. Rebuild the spatial index afterwards, following How to Automate R-tree Index Rebuilds After Bulk Load.

Three signals, three different responsesGrowth in live pages means the dataset grew, and the response is a capacity decision: prune history, split the container, or accept it. A high free-page fraction means space is retained after deletions, and the response is a vacuum followed by an index rebuild. A large write-ahead log means nothing has checkpointed, and the response is a checkpoint before shipping. Applying the wrong response wastes time and can make things worse.live pages grewa capacity decision — prune history, split, or accept itfree pages are a large shareVACUUM, then rebuild the spatial indexthe -wal sidecar is largecheckpoint before shipping — never a vacuum
Vacuuming a container whose growth is really an unchecked log rewrites the whole file and fixes nothing.

4. Bound the write-ahead log

A long-running writer under WAL can leave the log growing for a long time, because automatic checkpointing only happens when a connection does work and the threshold is exceeded. On a container that is mostly read, that can be indefinitely.

sql
-- Fold the log back in and truncate it to zero
PRAGMA wal_checkpoint(TRUNCATE);

Running that on a schedule — at the end of a sync cycle, at shutdown, before an artefact ships — keeps the sidecar bounded without stalling daytime collection.

5. Attribute growth to a layer

When live data really has grown, the next question is which layer. Page-level attribution needs dbstat, which is available in most builds.

sql
-- Bytes per table, where the dbstat virtual table is compiled in
SELECT name, sum(pgsize) AS bytes
FROM dbstat
GROUP BY name
ORDER BY bytes DESC
LIMIT 10;

Where dbstat is unavailable, row counts from the manifest plus an estimate of row width give a usable approximation — and the manifest already has the counts.

Verification

python
# The profile must add up
p = size_profile("field.gpkg")

assert p["live_bytes"] + p["free_bytes"] == p["page_count"] * p["page_size"]
assert p["free_bytes"] <= p["bytes_on_disk"]
assert p["page_count"] * p["page_size"] <= p["bytes_on_disk"] + p["page_size"]

And confirm a vacuum does what the profile predicted, which is the check that validates the whole model:

python
before = size_profile("field.gpkg")
reclaimable = before["free_bytes"]

subprocess.run(["sqlite3", "field.gpkg", "VACUUM;"], check=True)

after = size_profile("field.gpkg")
actual = before["bytes_on_disk"] - after["bytes_on_disk"]

assert after["free_bytes"] < before["free_bytes"]
assert abs(actual - reclaimable) < before["page_size"] * 64, (
    f"predicted {reclaimable} reclaimable, actually recovered {actual}"
)

If those diverge substantially, the free-page count is not the whole story for that container — usually because a large deletion happened between the two measurements.

Reading a growth trend from the manifest historyAcross six nightly runs, live bytes rise gently as the dataset grows, which is expected. Free bytes stay near zero for four runs, then jump after a bulk delete on the fifth and remain until a vacuum. Bytes on disk track the sum and give no indication of which component moved. Only the separated series make the fifth run interpretable.Six nightly runslive bytesfree bytesbytes on diskthe fifth run deleted rows — visible in the middle series, invisible in the bottom one
The bottom series is what a naive size monitor watches, and it is the one that cannot explain what happened.

Alternative Approaches or Edge Cases

auto_vacuum. SQLite can return free pages to the filesystem incrementally, which avoids the exclusive lock a full vacuum takes. It must be enabled before the container has any data, adds a little overhead to every delete, and fragments the file more than a vacuum does — a reasonable trade for a long-lived container on a device, and not for a build artefact that is rebuilt each night.

Page size. A container’s page size is fixed at creation and affects both size and read performance. Larger pages suit geometry-heavy tables, where a single feature may not fit in a small page and spills to overflow pages; the default is a reasonable compromise and is worth revisiting only with a measurement.

Splitting the container. When live growth is genuinely the problem, splitting by area or by time is usually better than pruning. A device that only needs its own region does not benefit from carrying the whole dataset, and per-region containers also make the sync smaller.

Troubleshooting

The file grew and no rows were added

Cause: Free pages from deletions, or an unchecked write-ahead log. Fix: Read the profile rather than the size; the two are distinguishable in one query and have different responses.

VACUUM reclaimed nothing

Cause: freelist_count was already near zero — the space was in live pages. Fix: The container really did grow, and the response is a capacity decision rather than a maintenance one.

Spatial queries return nothing after a VACUUM

Cause: Implicit row identifiers were renumbered and the R-tree still references the old ones. Fix: Rebuild the spatial index after any vacuum on a table without an explicit integer primary key. This is the most common way a routine maintenance operation breaks a container.

Frequently Asked Questions

How often should a container be vacuumed?

Rarely, and only in response to a measurement. A container that cycles rows continually reuses its free pages and gains nothing from a vacuum; one that had a large one-off deletion gains the whole free-page total. Scheduling a vacuum unconditionally spends an exclusive lock and a full file rewrite on a condition that may not exist.

Should the sidecar size be part of the growth alert?

As its own condition, yes. A large log is a real operational problem — it consumes storage and it means a single-file copy of the container would lose data — but it is not growth, and lumping it into a size alert produces a recommendation to vacuum, which does not help. Keep it as a separate check with its own remedy.

What is a reasonable free-page threshold?

Twenty per cent of the file is a defensible starting point for a container that is shipped rather than continually written. For one that cycles rows, a much higher fraction is normal and healthy, because those pages are about to be reused. As with drift tolerances, the number should come from a couple of weeks of observation rather than from a first guess.

Does a tile pyramid change how this should be read?

Substantially, because a pyramid dominates the live-byte figure and never changes once built. A container carrying both tiles and features will show a large, flat live-byte total with the feature growth buried inside it, and a percentage threshold on the total becomes almost impossible to trip. Where both are present, attribute the size per table with dbstat and track the feature layers separately, or the monitor stops being able to see the part that actually moves.