← 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)
);