Why should you care?

PostgresVerse IoT fleet sends sensor readings every second — partition by day and BRIN keeps queries fast on billions of rows.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE sensor_readings (
  device_id int NOT NULL,
  recorded_at timestamptz NOT NULL,
  temperature numeric(5,2),
  PRIMARY KEY (device_id, recorded_at)
) PARTITION BY RANGE (recorded_at);

CREATE TABLE sensor_readings_2026_07
  PARTITION OF sensor_readings
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE INDEX ON sensor_readings_2026_07 USING BRIN (recorded_at);

What happened?

• Partition pruning skips old months.
• BRIN on recorded_at suits append-only time order.
• Primary key includes time for uniqueness per device per timestamp.

Practice next

1. Create parent and one monthly partition.
2. COPY sample readings into July partition.
3. Query one day range; EXPLAIN shows partition scan only.
4. DROP old partition instead of DELETE millions of rows.
5. Aggregate hourly AVG with date_trunc GROUP BY.

Remember

Partition by time for prune and retention. BRIN fits sequential timestamps. TimescaleDB optional for hypertables.

PostgresVerse fleet metrics

Logistics company queries truck temperature last 24h from 2B row table under 2s.

Outcome: Compliance reports automated; cold-chain alerts fire on anomalies.