Time-Series Database Performance: What Drives Ingest and Query Speed

What "performance" means for a time-series database

Four separate things get called performance, and they pull in different directions:

• Ingest throughput: readings per second the system accepts without falling behind.
• Freshness: how soon after a reading is written it appears in query results and dashboards.
• Query latency: how long typical queries take: latest value, a trend for one point, a roll-up across many points.
• Storage efficiency: bytes per reading on disk, which drives how much history fits and how much is read per query.

A design that maximises one can hurt another. More indexes speed some queries and slow every write; very small partitions limit late-data rewrites but add overhead to every query. The rest of this page goes through the factors in roughly the order a reading meets them. It assumes the basic concepts from the time-series overview.

Batching and the write path

Every write has a fixed cost (a network round trip, parsing, a transaction, a flush of the write-ahead log at commit) plus a per-row cost. Sending readings one at a time makes the fixed cost dominate. The documentation for both relational and purpose-built engines says the same thing in different words:

• PostgreSQL: committing each row separately makes the server do a lot of work per row; turn off autocommit and commit once per batch, or use COPY, which the manual describes as having significantly less overhead than a series of INSERTs for large loads.
• InfluxDB OSS v2: write in batches to reduce network overhead (its documentation gives a recommended batch size in lines of line protocol), sort tags by key, and use the coarsest timestamp precision your data needs rather than the nanosecond default.

In an IoT pipeline the natural place to batch is the ingestion consumer that reads from MQTT or Kafka: collect readings for a short interval or up to a row count, write them in one statement or one COPY, and commit. Batching adds a little latency, so pick the interval with your freshness target in mind. An edge gateway that buffers during outages will also send large batches on reconnect, so the write path needs headroom for bursts, not just the average rate.

Indexes: what each write has to maintain

Every index on a table is updated on every insert. In a relational store the primary key on (point_id, ts) is usually justified because it also enforces uniqueness and serves the main query; each additional B-tree index adds write work and storage. The PostgreSQL manual notes that for a bulk load into a new table it is faster to load first and create indexes afterwards.

Choose index column order to match the filter. A multicolumn B-tree is most efficient when the query constrains its leading columns, so (point_id, ts) serves "one point over a range" well and "all points at one instant" less well. For wide time-window scans across all points, a BRIN index on the timestamp is a much smaller alternative, provided the data is stored in roughly time order.

Purpose-built engines make the same trade-off with different names. InfluxDB OSS v2 indexes tags but not fields, so a filter on a tag is fast and a filter on a field value must scan; deciding what is a tag and what is a field is an indexing decision. TimescaleDB creates a descending time index on a hypertable automatically and creates indexes per chunk.

Partitioning and chunking by time

In our view, time partitioning is the biggest structural factor. When data is split by time and a query carries a time-range predicate, the engine skips the partitions outside the range. PostgreSQL calls this partition pruning, TimescaleDB routes queries to the matching chunks, and QuestDB's optimiser skips partitions outside the query's time range. Partitioning also keeps each partition's indexes smaller, which the PostgreSQL documentation notes makes it more likely that the heavily used parts fit in memory.

The partition size is a tuning decision with costs on both sides:

• Too few, too large: indexes stay large and late or out-of-order data means rewriting more. QuestDB's documentation notes that too few large partitions can hurt out-of-order write performance.
• Too many, too small: PostgreSQL warns of longer planning times and higher memory use; QuestDB notes more file-system overhead because each partition is a directory.

QuestDB publishes a target row count per partition and PostgreSQL gives a rough ceiling of a few thousand partitions when queries prune most of them. Treat any such figure as a starting point to check against your own rates and query windows.

Query shape decides whether pruning happens

Pruning works on the partition column's own value. Wrap that column in a function and the planner can no longer prove which partitions to skip. Both plans below come from the same empty table with three daily partitions (run on PostgreSQL 18.6 in PGlite, which is PostgreSQL compiled to 32-bit WebAssembly; illustrative plan shapes, not a production benchmark, and your server's plans can differ with data and statistics); only the WHERE clause differs:

CREATE TABLE metric (
    point_id  integer NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision
) PARTITION BY RANGE (ts);
CREATE TABLE metric_2026_10_26 PARTITION OF metric FOR VALUES FROM ('2026-10-26 00:00+00') TO ('2026-10-27 00:00+00');
CREATE TABLE metric_2026_10_27 PARTITION OF metric FOR VALUES FROM ('2026-10-27 00:00+00') TO ('2026-10-28 00:00+00');
CREATE TABLE metric_2026_10_28 PARTITION OF metric FOR VALUES FROM ('2026-10-28 00:00+00') TO ('2026-10-29 00:00+00');
CREATE INDEX ON metric (point_id, ts);

EXPLAIN (COSTS OFF)
SELECT avg(value) FROM metric
WHERE point_id = 42
  AND ts >= '2026-10-27 06:00+00' AND ts < '2026-10-27 07:00+00';

-- Aggregate
--   ->  Index Scan using metric_2026_10_27_point_id_ts_idx on metric_2026_10_27 metric
--         Index Cond: ((point_id = 42) AND (ts >= ...) AND (ts < ...))

EXPLAIN (COSTS OFF)
SELECT avg(value) FROM metric
WHERE point_id = 42
  AND date_trunc('hour', ts AT TIME ZONE 'UTC') = '2026-10-27 06:00';

-- Aggregate
--   ->  Append
--         ->  Bitmap Heap Scan on metric_2026_10_26 metric_1
--               Recheck Cond: (point_id = 42)
--               Filter: (date_trunc('hour'::text, (ts AT TIME ZONE 'UTC'::text)) = ...)
--         ->  Bitmap Heap Scan on metric_2026_10_27 metric_2
--               ...
--         (and the same for metric_2026_10_28)

Cardinality: how many series you have

Engines that index by series (Prometheus, InfluxDB and others) keep per-series structures in memory and on disk. Every distinct combination of label values is a new series, so a label with unbounded values multiplies the series count. Prometheus's naming guidance warns against labels such as user IDs or email addresses for this reason, and InfluxData's documentation names high series cardinality as a primary driver of high memory use and a common cause of slow reads and writes.

In building and IoT data the usual culprits are a message ID, a raw payload hash or a free-text status stored as a tag or label. Keep identifiers that grow without limit in values (fields, columns) rather than in series identity, and keep the series identity to what you filter and group by: site, asset, point, unit.

A relational table has no series index as such, but the same idea applies to indexes: an index on a column with millions of distinct values is large, and every insert updates it. The article on high-cardinality tags covers detection and fixes.

Query shape

• Bound the time range wherever the question allows, with a plain predicate on the timestamp column (ts >= ... AND ts < ...). As the plans above show, wrapping the column in a function can defeat pruning.
• Filter on indexed identity first: point or tag filters that match the leading index column, or InfluxDB tags rather than fields.
• Ask for what the chart can show. A trend chart 1,000 pixels wide (an illustrative width) cannot display a year of one-minute data; aggregate into buckets in the database, or read from a rolled-up table, instead of shipping raw rows to the client.
• Latest value queries benefit from an index ordered by time within each point, or a small "last value" table updated on ingest.
• Select the columns you need. In columnar storage, each extra column is extra data read.
• Joins to metadata are cheap when the metadata table is small; resolve names to point IDs first, then query readings by ID.

Compression, columnar storage and downsampling

Two further factors change how much data a query has to read at all.

Compression and columnar layout. Several engines keep recent data in a write-friendly row format and convert older data to compressed columns. TimescaleDB's hypercore, for example, writes new data to a rowstore and converts it to a columnstore where analytical queries read fewer bytes, and can skip whole chunks or batches using metadata. The effect on a given workload depends on the data and the query, and the trade-offs (conversion timing, updates to compressed data, which columns to segment by) are the subject of the article on time-series compression and columnar storage.

Downsampling and retention. Queries over months or years should read pre-aggregated data rather than raw readings, and raw data past its useful age should be dropped by partition rather than deleted row by row. How to choose roll-up intervals and retention periods is covered in the article on retention and downsampling; the storage guide shows a working roll-up table in PostgreSQL.

Late and out-of-order data

In our reading of the documentation, these engines are designed around data that arrives in time order, where new rows go to the newest partition and its in-memory structures. Late data from a gateway that was offline lands in older partitions. QuestDB documents that out-of-order data rewrites only the affected partitions and that it may split a partition to avoid rewriting it whole; TimescaleDB's rowstore is designed to accept late-arriving rows and upserts. In plain PostgreSQL, late rows go into the older partition's heap and indexes as normal inserts, but they reduce the physical time ordering that a BRIN index relies on.

Plan for bursts of late data after outages: they can arrive faster than the normal rate and touch partitions that may already be compressed or rolled up.

Hardware and storage

• Memory holds the hot index pages and, in series-indexed engines, the series index. Running out of it can cause a sudden slowdown, and a cardinality increase is one way that happens (our summary of the cardinality guidance above, not a measured result).
• Disk must sustain the write-ahead log, background compaction or compression, and query reads at the same time. Prometheus documents that compaction temporarily needs extra space and recommends leaving headroom below the disk size.
• File system: Prometheus does not support non-POSIX file systems such as NFS for its local storage and recommends a local file system for reliability. Check your engine's requirements before putting it on network storage.
• Single node or cluster: scale-out adds capacity and failure tolerance but also network hops and coordination. Prometheus states that its local storage is limited to a single node and offers remote-storage integrations for more.

Evaluating performance for your own workload

Published benchmarks answer someone else's question. Before choosing a product or a design, build a small test from your own situation:

1. Write down the workload: number of points, sampling intervals, expected late-data share, raw and rolled-up retention, and the five queries that matter most (with their time windows).
2. Generate or replay realistic data, including bursts after a simulated outage and the real label cardinality.
3. Measure ingest rate, freshness, query latency and bytes on disk at the retention size you will actually hold, not on an almost empty database.
4. Run it long enough for background work (compaction, compression, vacuum, roll-ups) to happen during the test.
5. Change one factor at a time: batch size, partition interval, index set, compression settings.

For product-level differences, see TimescaleDB vs InfluxDB, ClickHouse vs PostgreSQL and the time-series database selection guide.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Table Partitioning
• PostgreSQL 18 documentation: Populating a Database
• PostgreSQL 18 documentation: Multicolumn Indexes
• PostgreSQL 18 documentation: BRIN Indexes
• InfluxDB OSS v2 documentation: Optimize writes
• InfluxDB OSS v2 documentation: Resolve high series cardinality
• InfluxDB OSS v2 documentation: Data elements
• Prometheus documentation: Metric and label naming
• Prometheus documentation: Storage
• QuestDB documentation: Time partitions
• Tiger Data documentation: Hypercore
• Tiger Data documentation: Hypertables

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.