MBTiles Architecture & Limits

MBTiles is a SQLite-backed container format for offline vector and raster tile distribution. Its single-file packaging, mandatory schema, and ACID compliance make it the default output format for Tippecanoe automated generation pipelines, but single-writer serialization, page fragmentation beyond ~100 GB, and a TMS row-ordering convention that diverges from web mapping libraries introduce constraints that require explicit handling in production.

Prerequisites

Before implementing MBTiles in a production pipeline, ensure your environment meets these baseline requirements:

• SQLite 3.7.0+ — required for WAL mode; verify with python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
• Tippecanoe v2.x — for .mbtiles output via -o output.mbtiles
• Input data in EPSG:4326 — Tippecanoe projects internally to EPSG:3857; source must be WGS 84 geographic coordinates
• Tile addressing knowledge — MBTiles uses TMS row ordering (row 0 = south); most mapping clients expect XYZ (row 0 = north); the conversion must be applied at read time
• Conceptual baseline — see Vector Tile Architecture & Format Fundamentals for how MBTiles fits the full GeoJSON → CDN delivery chain

Core Specification: Schema and Mandatory Metadata

MBTiles 1.3 defines two mandatory tables. Every field name and type is normative — deviating from them causes incompatibilities with tile servers such as martin, tileserver-gl, and GDAL-based readers.

Mandatory DDL

CREATE TABLE tiles (
    zoom_level  INTEGER NOT NULL,
    tile_column INTEGER NOT NULL,
    tile_row    INTEGER NOT NULL,
    tile_data   BLOB    NOT NULL,
    UNIQUE(zoom_level, tile_column, tile_row)
);

CREATE UNIQUE INDEX tile_index
    ON tiles (zoom_level, tile_column, tile_row);

CREATE TABLE metadata (
    name  TEXT NOT NULL,
    value TEXT NOT NULL,
    UNIQUE(name)
);

tile_data holds either a gzip-compressed Mapbox Vector Tile (.pbf) for vector tiles, or a PNG/JPEG payload for raster tiles. The composite unique index on (zoom_level, tile_column, tile_row) is mandatory — omitting it causes full-table scans on every tile fetch.

Required Metadata Keys

For vector tilesets the json key must embed a vector_layers array describing each layer’s id, minzoom, maxzoom, and fields object. Omitting it breaks layer inspection in MapLibre GL and pmtiles CLI tooling.

Storage Mechanics

SQLite organises the database as a B-tree page file. The default page size is 4 096 bytes. Large vector tile BLOBs exceeding one page trigger overflow page chains, which increase read amplification for tiles with dense geometry. Setting PRAGMA page_size = 8192 before initial schema creation reduces overflow chains for typical 50–200 KB vector tile payloads.

Hard and Soft Limits

File Size

Tile Count and Zoom Density

A global dataset at zoom 14 has 4 294 967 296 potential tile slots. Even sparse coverage (1–5% fill rate) at that zoom produces 40–200 million rows. The composite index remains efficient for random access, but bulk scan operations (counting tiles per zoom, exporting all tiles at a given zoom) degrade with row counts above ~500 million due to B-tree traversal overhead.

Concurrency: Single-Writer Constraint

SQLite’s default DELETE journal mode serialises all write operations at the file level. In a multiprocessing Tippecanoe pipeline, parallel workers attempting simultaneous INSERT statements produce:

sqlite3.OperationalError: database is locked

Enabling WAL mode allows one writer and multiple concurrent readers, reducing lock contention during bulk generation. For full mitigation strategies — including producer-consumer queues, exponential backoff, and PRAGMA busy_timeout — see Resolving SQLite Locks in Large MBTiles Generation.

TMS vs XYZ Row Ordering

MBTiles stores tile_row in TMS convention: row 0 is at the south of the tile grid. MapLibre GL, Leaflet, and OpenLayers all use XYZ convention, where row 0 is at the north. The conversion is deterministic:

mbtiles_row = (2^zoom_level − 1) − xyz_y

Inserting tiles without this transformation produces a vertically flipped map. The MBTiles spec caps zoom_level at 22; practical vector tile pipelines rarely exceed zoom 18 because tile counts grow as 4^z and geographic precision gain diminishes below ~1 m resolution.

Step-by-Step Implementation

Step 1: Initialise the Database

# Create a new MBTiles file with correct schema and PRAGMA settings
sqlite3 output.mbtiles << 'EOF'
PRAGMA page_size = 8192;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;  -- ~64 MB page cache

CREATE TABLE IF NOT EXISTS tiles (
    zoom_level  INTEGER NOT NULL,
    tile_column INTEGER NOT NULL,
    tile_row    INTEGER NOT NULL,
    tile_data   BLOB    NOT NULL,
    UNIQUE(zoom_level, tile_column, tile_row)
);

CREATE TABLE IF NOT EXISTS metadata (
    name  TEXT NOT NULL,
    value TEXT NOT NULL,
    UNIQUE(name)
);

INSERT OR REPLACE INTO metadata VALUES
    ('name',        'my-tileset'),
    ('format',      'pbf'),
    ('minzoom',     '0'),
    ('maxzoom',     '14'),
    ('bounds',      '-180,-85.05,180,85.05'),
    ('center',      '0,0,3'),
    ('type',        'overlay'),
    ('description', 'Road network z0-z14');
EOF

Verify: PRAGMA page_size; returns 8192 and PRAGMA journal_mode; returns wal.

Step 2: Generate Tiles with Tippecanoe

Tippecanoe writes directly to .mbtiles using its own internal SQLite writer with correct TMS row ordering. For geometry simplification and attribute filtering settings applied at generation time:

tippecanoe \
  --output=output.mbtiles \
  --force \
  --minimum-zoom=0 \
  --maximum-zoom=14 \
  --drop-densest-as-needed \
  --extend-zooms-if-still-dropping \
  --layer=roads \
  --attribute-type=highway:string \
  --no-tile-size-limit \
  input.geojson

Verify: after completion, check sqlite3 output.mbtiles "SELECT COUNT(*) FROM tiles;" returns a non-zero row count.

Step 3: Batch Insert via Python (Custom Pipelines)

When building custom generation scripts rather than using Tippecanoe, batch inserts inside explicit transactions deliver 5–10× higher throughput than autocommit mode:

import sqlite3
import gzip

def open_mbtiles(db_path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA synchronous=NORMAL;")
    conn.execute("PRAGMA cache_size=-64000;")
    conn.execute("PRAGMA busy_timeout=5000;")
    return conn

def insert_tiles(conn: sqlite3.Connection, tiles: list[tuple]) -> None:
    """
    tiles: list of (zoom_level, tile_column, tile_row_tms, tile_data_bytes)
    tile_row must already be in TMS convention (row-flipped from XYZ).
    tile_data must be gzip-compressed pbf.
    """
    BATCH = 2000
    for i in range(0, len(tiles), BATCH):
        batch = tiles[i : i + BATCH]
        conn.execute("BEGIN IMMEDIATE;")
        conn.executemany(
            "INSERT OR REPLACE INTO tiles "
            "(zoom_level, tile_column, tile_row, tile_data) VALUES (?,?,?,?)",
            batch,
        )
        conn.commit()

def xyz_to_tms_row(zoom: int, xyz_y: int) -> int:
    return (2 ** zoom - 1) - xyz_y

def compress_pbf(raw_pbf: bytes) -> bytes:
    return gzip.compress(raw_pbf, compresslevel=6)

Verify: after inserting a batch, PRAGMA integrity_check; on the database should return ok.

Step 4: Inject the vector_layers JSON Metadata Key

MapLibre GL requires the json metadata key to enumerate layer names and attribute schemas. Generate it programmatically after tile generation completes:

import json, sqlite3

def set_vector_layers_metadata(db_path: str, layers: list[dict]) -> None:
    """
    layers: [{"id": "roads", "minzoom": 0, "maxzoom": 14,
               "fields": {"highway": "String", "name": "String"}}]
    """
    payload = json.dumps({"vector_layers": layers})
    conn = sqlite3.connect(db_path)
    conn.execute(
        "INSERT OR REPLACE INTO metadata (name, value) VALUES ('json', ?)",
        (payload,),
    )
    conn.commit()
    conn.close()

Verify: sqlite3 output.mbtiles "SELECT value FROM metadata WHERE name='json';" returns a parseable JSON string.

Step 5: Validate and Checkpoint

import sqlite3

REQUIRED_METADATA = {"name", "format", "minzoom", "maxzoom", "bounds", "center"}

def validate_mbtiles(db_path: str) -> dict:
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    cur.execute("SELECT name FROM metadata;")
    found = {r[0] for r in cur.fetchall()}
    missing = REQUIRED_METADATA - found
    if missing:
        raise ValueError(f"Missing required metadata keys: {missing}")

    cur.execute("SELECT COUNT(*) FROM tiles;")
    tile_count = cur.fetchone()[0]
    if tile_count == 0:
        raise ValueError("Database contains zero tiles")

    cur.execute("PRAGMA integrity_check;")
    result = cur.fetchone()[0]
    if result != "ok":
        raise ValueError(f"Integrity check failed: {result}")

    # Checkpoint WAL before distributing the file
    cur.execute("PRAGMA wal_checkpoint(TRUNCATE);")

    conn.close()
    return {"tile_count": tile_count, "missing_keys": list(missing)}

After wal_checkpoint(TRUNCATE), the -wal and -shm sidecar files are removed or zeroed, leaving a self-contained .mbtiles archive safe to copy or upload.

Optimization Knobs

Integration with Adjacent Pipeline Stages

Upstream: Tippecanoe Output → MBTiles

Tippecanoe’s --output flag writes a fully initialised MBTiles file including schema, indexes, and required metadata. The --force flag overwrites an existing file cleanly. For incremental builds — appending new tiles to an existing container — use --output-to-directory to write individual .pbf files, then merge with a custom Python writer using the batch pattern above.

For zoom level optimisation decisions that affect tile density and file size, consult the zoom optimisation workflow before running Tippecanoe.

Downstream: MBTiles → Tile Server → CDN

martin (a Rust tile server) reads MBTiles directly, handles the TMS→XYZ row-flip transparently, and exposes a TileJSON endpoint:

martin output.mbtiles --listen-addresses 127.0.0.1:3000
# TileJSON available at: http://127.0.0.1:3000/output

For CDN delivery without a running tile server, convert to PMTiles to enable direct HTTP range-request serving from object storage:

pmtiles convert output.mbtiles output.pmtiles

The PMTiles format eliminates SQLite overhead and WAL management entirely, making it preferable for static or infrequently updated global datasets served from S3, GCS, or Cloudflare R2.

Troubleshooting

1. sqlite3.OperationalError: database is locked

Cause: Multiple processes or threads attempting concurrent writes without serialisation.

Diagnosis:

lsof output.mbtiles | grep -v COMMAND
# shows all processes holding file descriptors

Fix: Enable WAL mode and route all writes through a single coordinator thread. See Resolving SQLite Locks in Large MBTiles Generation for a production-ready queue-based writer.

2. Vertically Flipped Map in MapLibre GL

Cause: Tiles inserted with XYZ y values instead of TMS row values.

Diagnosis:

-- Compare expected max row at zoom 10 (should be 1023 for full coverage)
SELECT zoom_level, MAX(tile_row) FROM tiles WHERE zoom_level=10;
-- If MAX(tile_row) equals xyz_y max (1023) the row is already in TMS
-- If tiles appear south-of-equator when they should be north, the flip was not applied

Fix: Apply mbtiles_row = (2^zoom - 1) - xyz_y before insertion. For an existing corrupted file, run an UPDATE migration:

UPDATE tiles
SET tile_row = (CAST(pow(2, zoom_level) AS INTEGER) - 1) - tile_row;

3. Missing vector_layers Causes Empty Layer Picker

Cause: The json metadata key is absent or malformed.

Diagnosis:

sqlite3 output.mbtiles "SELECT value FROM metadata WHERE name='json';" | python3 -m json.tool

Fix: Insert a valid json metadata value using set_vector_layers_metadata() from Step 4 above.

4. VACUUM Stalls Serving Process

Cause: VACUUM rewrites the entire database file and holds an exclusive lock, blocking all reads.

Fix: Never run VACUUM on a live-serving database. Schedule it in a maintenance window on a replica copy, or avoid fragmentation altogether by using --auto-vacuum=INCREMENTAL at database creation time:

PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- reclaim 100 pages at a time during idle periods

5. WAL File Grows Beyond 1 GB

Cause: Long-running writers with no reader checkpoints, or PRAGMA wal_autocheckpoint set to 0.

Diagnosis:

ls -lh output.mbtiles-wal

Fix:

PRAGMA wal_autocheckpoint = 1000;  -- restore default
PRAGMA wal_checkpoint(TRUNCATE);   -- force immediate full checkpoint

When to Migrate Beyond MBTiles

MBTiles is the right choice for local offline caching, rapid prototyping, and tile archives under ~50 GB with infrequent updates. Evaluate migration when:

• Tile count exceeds 500 million and scan-based operations (zoom-level exports, diff generation) become slow
• File size approaches 100 GB and VACUUM maintenance windows are no longer acceptable
• Multi-region concurrent read serving is required without a dedicated tile server process
• Static datasets need direct CDN delivery from object storage without a proxy layer

Typical migration targets:

Child Pages

• Resolving SQLite Locks in Large MBTiles Generation — production-ready WAL configuration, producer-consumer queue architecture, and exponential backoff patterns for concurrent tile insertion at scale.
• Merging MBTiles Files with tile-join — combining regional builds and overlays into one tileset, re-filtering layers, and re-checking the 500 KB per-tile budget without regenerating from source.

Parent: Vector Tile Architecture & Format Fundamentals

Related

• PMTiles Specification Deep Dive — binary layout, HTTP range-request serving, and when to prefer PMTiles over an SQLite-backed container for CDN delivery.
• Vector vs Raster Tile Tradeoffs — format selection criteria covering compression ratios, client-side rendering cost, and offline distribution patterns.
• Zoom Level Optimization Strategies — calculating min/max zoom ranges to control tile count, file size, and the practical upper bound on MBTiles container growth.
• Essential Tippecanoe Flags for Production Builds — the flags that directly control MBTiles output path, layer naming, and tile size budgets.