An ESP32 sending temperature every minute is not a database benchmark. The real question is where readings arrive, who queries them, how long they remain useful, and what else they must join to: device identity, maintenance tickets, customer accounts or control events. PostgreSQL, InfluxDB and SQLite can all store time-stamped observations, but they solve different operating shapes.
Write down ingest rate, bursts, late arrivals, query windows, retention tiers, tenant boundaries, offline duration and recovery objectives. Estimate bytes per row with indexes and metadata, not only sensor payload. A thousand devices sampled once per minute produce about 1.44 million points per day before retries, derived metrics or indexes. That is a planning input, not proof a specialized database is required.
| Option | Good fit | Trade-offs to own | Typical first choice when... |
|---|---|---|---|
| PostgreSQL, including managed Postgres on Supabase | Telemetry joins device/customer records, operational SQL, transactions, access policies and one application database. | Index/query design, storage growth, vacuum, retention and measured partition maintenance. | Relational context and SQL matter more than a specialized time-series workflow. |
| InfluxDB | Purpose-built ingestion, time-window queries and retention-oriented buckets. | Schema/cardinality discipline, its query and operational model, and version-specific migration choices. | Telemetry dominates and its query model fits the team and deployment. |
| SQLite on an edge gateway | Local durable buffer, offline-first appliance, single-host collector or small installation. | One writer at a time in WAL mode, same-host access, backup/checkpoint handling and later synchronization. | Data must survive a WAN outage locally and one process can own writes. |
Supabase is managed Postgres, not a separate time-series engine. Its extension story is version-sensitive: Supabase currently says TimescaleDB is deprecated on Postgres 17 projects and directs hypertable users toward native partitioning managed with pg_partman. Check your project's Postgres major version and supported migration path before following an old extension tutorial.
A regular table is often enough to begin. A basic schema might use device_id, observed_at (timestamptz), received_at, sequence_no, metric, value, unit and schema_version, with a uniqueness key based on device, time, sequence and metric. Index device and descending observation time only if that matches real dashboard filters.
Keep event time distinct from server receipt time and use UTC-aware timestamps. Define idempotency around the device sequence or event ID. In production, constrain metric/unit combinations, bound payloads and enforce tenant authorization at the API/database boundary. Every extra index costs storage and write work. Partition by time only when measurements show that pruning, bulk retention deletion or operations justify the lifecycle work. PostgreSQL presents partitioning as useful mainly for sufficiently large tables and supported access patterns, not as an automatic speed switch.
The relational advantage is practical: a graph can join a reading to a device's site, customer, calibration revision or service window without copying metadata into every point. Aggregate to the dashboard's requested interval; do not send millions of raw points to a browser and expect its chart library to solve storage design.
InfluxDB distinguishes timestamp, measurement, tags and fields. In its documented 2.x model, tags are indexed and fields are not. Tags suit stable dimensions used in filters; avoid promoting rapidly changing or highly unique values casually because series cardinality matters. A device ID may work as a tag for a bounded fleet, but test expected growth and the deployed version's guidance.
InfluxDB 2.x buckets combine data organization with retention rules. InfluxDB 3 is the current product generation with different architecture and query details. Do not copy a v2 bucket/Flux recipe into v3 without checking compatibility, edition, hosting, authentication, backup/export, client libraries and upgrade path.
On a gateway that loses connectivity, SQLite can commit local batches and forward them when the uplink returns. Write-Ahead Logging lets readers and a writer run concurrently, but permits only one writer at a time. WAL relies on same-host shared memory and is not designed for a network filesystem. Give one ingestion process ownership, batch records in transactions, monitor disk, plan checkpoints and backups, and test power-loss recovery on target hardware. Use a SQLite release containing fixes relevant to your deployment.
On reconnect, the uplink can resend events safely if the cloud endpoint is idempotent. Track a durable local cursor or acknowledgment so a crash cannot silently discard a batch. Define behavior at the disk limit: pause sampling, drop only explicitly low-priority data, or preserve summaries while alerting an operator. Silent overwrite is not a retention policy.
Keep high-resolution raw data only as long as a real use case, applicable rule or incident workflow requires it. Raw vibration for days, hourly summaries for months and maintenance outcomes longer are examples, not universal durations. Preserve aggregation method, sample count, min/max and units so an average does not erase a short alarm. Define late-event windows and whether corrected readings replace earlier values or create an auditable revision.
Replay the same synthetic or authorized workload against candidates. Keep hardware, network, data shape, retention, indexes, batch size and queries comparable. Measure ingest lag, p95 dashboard query time, storage growth, CPU/memory, restart recovery, backup restore time and operating effort. Include hot and cold windows, fleet rollups, out-of-order events and tenant isolation. Publish workload and schema with results; one headline write rate is not a reusable benchmark.
Give each event a stable contract: logical device ID, metric, unit, schema version, event time, receipt time and idempotency key. Temporarily dual-write, compare counts and aggregates by bounded time window, validate retention and access, then switch readers behind a feature flag. Set an end date for dual writes and rollback conditions. Do not let two databases become competing sources of truth by accident.
Choose by data path and query shape: Postgres when relationships and SQL dominate, InfluxDB when its time-series model and operating lifecycle fit, SQLite when one edge host needs dependable local writes during outages. Start with the simplest system that meets measured requirements, make retention and replay explicit, and revisit the choice using real workload data rather than database-brand folklore.