← Back to the chapter · Next detail →

Technical detail · Storage Design Case Studies

Fleet schema and indexes

The fleet history stores observation and insertion time separately. The current-state table supports the map read path.

CREATE TABLE fleet_points (
    observed_at  TIMESTAMPTZ      NOT NULL,
    inserted_at  TIMESTAMPTZ      NOT NULL DEFAULT now(),
    tenant_id    TEXT             NOT NULL,
    vehicle_id   TEXT             NOT NULL,
    location     GEOGRAPHY(POINT, 4326),
    speed_kmh    NUMERIC(6,2),
    heading_deg  NUMERIC(5,2),
    fuel_pct     NUMERIC(5,2),
    quality      SMALLINT         NOT NULL DEFAULT 100,
    PRIMARY KEY (tenant_id, vehicle_id, observed_at)
);

SELECT create_hypertable(
    'fleet_points',
    by_range('observed_at', INTERVAL '1 day'),
    if_not_exists => TRUE
);

CREATE INDEX fleet_points_vehicle_time_idx
    ON fleet_points (vehicle_id, observed_at DESC);

CREATE INDEX fleet_points_location_idx
    ON fleet_points USING GIST (location);

CREATE TABLE fleet_latest (
    tenant_id    TEXT        NOT NULL,
    vehicle_id   TEXT        NOT NULL,
    observed_at  TIMESTAMPTZ NOT NULL,
    location     GEOGRAPHY(POINT, 4326),
    quality      SMALLINT    NOT NULL,
    PRIMARY KEY (tenant_id, vehicle_id)
);

← Back to the chapter · Next detail →