Storing Matched Traces in PostGIS for Fleet Analytics

The storage topic argues for a partitioned hot store with three indexes and an aged-out cold tier. This page is the PostGIS implementation of the hot half: the DDL, the load path, and the maintenance that keeps a table with a few hundred million rows behaving like a small one.

Everything here assumes matched segment output — one row per contiguous run on a directed edge — because that is the grain most fleet analytics queries want. The same structure applies at fix grain with a larger row count and the same indexes.

Compatibility and Configuration Requirements

The Schema

CREATE TABLE matched_segments (
    segment_id      bigint GENERATED ALWAYS AS IDENTITY,
    vehicle_id      text        NOT NULL,
    operating_date  date        NOT NULL,
    trip_id         uuid        NOT NULL,
    osm_way_id      bigint      NOT NULL,
    direction       smallint    NOT NULL CHECK (direction IN (-1, 1)),
    enter_utc       timestamptz NOT NULL,
    exit_utc        timestamptz NOT NULL CHECK (exit_utc >= enter_utc),
    length_m        double precision NOT NULL CHECK (length_m >= 0),
    mean_speed_kmh  real,
    confidence      real CHECK (confidence BETWEEN 0 AND 1),
    graph_version   char(12)    NOT NULL,
    geom            geometry(LineString, 32631) NOT NULL,
    PRIMARY KEY (operating_date, segment_id)
) PARTITION BY RANGE (operating_date);

The check constraints are cheap and they catch the errors that are hardest to find later. A segment with exit_utc before enter_utc is a sign-flip somewhere upstream; a confidence outside zero and one means two matchers with different conventions have been mixed. Both are silent without the constraint and expensive to unpick a quarter later.

Partitions are created ahead of time by a small job rather than on demand, so a nightly load never fails because tomorrow’s partition does not exist:

CREATE TABLE matched_segments_2026_08 PARTITION OF matched_segments
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

The Three Indexes

CREATE INDEX ON matched_segments_2026_08 USING gist (geom);
CREATE INDEX ON matched_segments_2026_08 (vehicle_id, enter_utc);
CREATE INDEX ON matched_segments_2026_08 (osm_way_id, direction);
CREATE INDEX ON matched_segments_2026_08 USING brin (enter_utc);

Built per partition, not on the parent, so a load into one month never rebuilds anything for the others. The BRIN index is nearly free — a few hundred kilobytes for a month — and handles range scans that the composite index would only serve if the query happened to filter on vehicle first.

Column order in the composite index matters more than it looks. (vehicle_id, enter_utc) serves both “this vehicle in this window” and “this vehicle, everything”; the reverse order serves neither well, because a time-only predicate is already handled by partitioning.

The Load Path

Loading into an indexed partition is several times slower than loading into an empty one and building the indexes afterwards. The pattern that exploits this is load-then-attach:

BEGIN;
CREATE TABLE matched_segments_2026_08_load (LIKE matched_segments INCLUDING DEFAULTS);

COPY matched_segments_2026_08_load
    (vehicle_id, operating_date, trip_id, osm_way_id, direction,
     enter_utc, exit_utc, length_m, mean_speed_kmh, confidence, graph_version, geom)
FROM PROGRAM 'zstd -dc /data/matched/2026-08.csv.zst' WITH (FORMAT csv, HEADER true);

CREATE INDEX ON matched_segments_2026_08_load USING gist (geom);
CREATE INDEX ON matched_segments_2026_08_load (vehicle_id, enter_utc);
CREATE INDEX ON matched_segments_2026_08_load (osm_way_id, direction);

ALTER TABLE matched_segments_2026_08_load
    ADD CONSTRAINT part_range CHECK (operating_date >= '2026-08-01' AND operating_date < '2026-09-01');

ALTER TABLE matched_segments ATTACH PARTITION matched_segments_2026_08_load
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
COMMIT;

ANALYZE matched_segments_2026_08_load;

The explicit CHECK constraint before ATTACH is the detail that makes this fast. Without it, PostgreSQL scans the whole table to verify every row belongs in the partition range; with it, the attach is a catalogue update and completes instantly.

ANALYZE after attaching is not optional. A freshly attached partition has no statistics, and the planner will make poor choices — usually a sequential scan where an index would serve — until autovacuum gets round to it, which on a large table can be hours.

Maintenance That Actually Matters

Three routines keep the store healthy, and none of them needs a maintenance window when the table is partitioned.

Per-partition VACUUM ANALYZE after any bulk change. Autovacuum handles steady-state churn but lags badly after a large delete or an attach. Running it explicitly at the end of a load job costs minutes and prevents hours of poor plans.

Reindex the spatial index on partitions that see deletes. GiST bloats under deletion far more than a B-tree does. REINDEX INDEX CONCURRENTLY on the affected partition is an online operation and takes minutes rather than the hours a whole-table reindex would.

Drop the spatial index before ageing a partition out. A partition about to become a Parquet file does not need a three-gigabyte index, and dropping it first makes the export faster and the disk recovery immediate.

-- Nightly, over partitions touched by the load
VACUUM (ANALYZE) matched_segments_2026_08;

-- Monthly, over partitions that have seen deletes
REINDEX INDEX CONCURRENTLY matched_segments_2026_07_geom_idx;

-- Before ageing out
DROP INDEX matched_segments_2026_02_geom_idx;

A useful habit is to record partition size and index size per month in a small table. The series makes growth visible before it becomes a capacity conversation, and it shows immediately when a schema change has widened rows.

Common Pitfalls Specific to This Technique

Using geography because it sounds more correct. It is more correct for intercontinental distances and materially slower for the metre-scale work fleets do. A projected geometry is accurate to centimetres inside a UTM zone and indexes better.

Indexing the parent table. In declarative partitioning an index on the parent creates one on every partition, including ones you were about to age out. Build per partition and keep the choice explicit.

Forgetting the CHECK before ATTACH. The attach then scans the entire table to prove the constraint, turning an instant catalogue change into a long lock on a busy table.

Storing both geometries in the hot table. The display geometry described in Douglas-Peucker simplification belongs in the tile pipeline, not next to the analytical one where a query can pick it up by mistake.

Concurrency and the Nightly Window

A fleet store is written in bulk overnight and read interactively all day, and the two workloads interfere in ways worth designing around rather than discovering.

Loads should never lock what queries read. The load-then-attach pattern gives this for free: the staging table is invisible until the attach, and the attach takes a brief lock on the parent’s catalogue entry rather than on any data. A load that instead inserts into the live partition holds row locks and inflates the visibility map for the whole run.

Autovacuum needs headroom during the window. A large load leaves a partition with no statistics and a large volume of newly written pages. Explicit VACUUM (ANALYZE) at the end of the job is cheaper than letting autovacuum discover it hours later, competing with the morning’s queries.

Long analytical queries hold snapshots. A query that scans a quarter of history keeps a transaction open for minutes, which delays vacuum cleanup across the whole database. This is the strongest practical argument for the cold tier: the queries that hold long snapshots are exactly the ones that belong in a columnar store instead.

-- Keep the load's lock footprint visible
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle' AND query ILIKE '%matched_segments%';

Sizing the connection budget

Fleet workloads are bursty — a dashboard refresh fans out into a dozen simultaneous queries, and a batch job opens a connection per worker. PostgreSQL’s per-connection memory makes this expensive quickly, and a pooler in transaction mode is close to mandatory once more than a few dozen clients exist.

Size work_mem against the pool’s maximum concurrency rather than against the connection count you hope for. A generous work_mem multiplied by a hundred concurrent sorts is how a machine with ample memory runs out of it during the one hour a week everybody opens the dashboard at once.

Related

• Storing and querying matched trajectories — parent topic and the tiering argument
• Writing matched trajectories to GeoParquet with Polars — where aged partitions go
• Compressing fleet trajectory archives without losing fidelity — shrinking the archive without touching geometry
• Coordinate reference system mapping for fleet data — choosing the SRID this schema fixes
• Trajectory Analysis & Map Matching Techniques — parent section