Benchmarking Time-Series Ingestion Without Misleading Results

What an ingestion benchmark should answer

This guide is for an engineer choosing between candidate databases or designs for telemetry, for example plain PostgreSQL with partitions versus a time-series engine for a portfolio of buildings, or for checking that an existing store will cope when a new site goes live. It covers method only. For the factors that make ingestion fast or slow (batching, indexes, partitioning, cardinality), read time-series database performance; this page is about measuring them honestly.

A useful benchmark answers a specific question with a yes or no and a margin. "How fast is database X?" has no useful answer. "Can this design ingest 20,000 BMS points at their real intervals, plus the backlog from a two-hour gateway outage, while ten dashboards refresh, with every reading durable on commit?" does.

Make the workload realistic

A common source of misleading results is data that is easier than production. Match these properties:

• Series count and cardinality. Use the real number of points and the real tag or label set. Engines that index series can behave differently at 1,000 and at 1,000,000 series; see high-cardinality tags.
• Interval mix. A few fast points and many slow ones produce a different write pattern from every point at the same rate.
• Order. Real data is mostly in time order with some late readings from reconnecting gateways. A perfectly ordered load can flatter engines that handle out-of-order writes less efficiently.
• Duplicates. At-least-once delivery from MQTT QoS 1 or a restarted Kafka consumer resends readings; include them and the de-duplication you will run (for example ON CONFLICT DO NOTHING).
• Values. Compression depends on value patterns. In our expectation (not a sourced or measured finding), constant or smooth synthetic values can compress better than real sensor noise; replay a captured day of real data where you can.
• Size at test time. Measure with the table, indexes and partitions at the size you will hold at full retention, not an almost empty database.
• Payload path. Benchmark the protocol you will use (SQL INSERT, COPY, line protocol, an ingestion connector), through the same network path.

-- bench_load holds the readings in the order the load generator will send them
CREATE TABLE bench_load (
    arrival_seq  bigint PRIMARY KEY,
    point_id     integer NOT NULL,
    ts           timestamptz NOT NULL,
    value        double precision
);

-- 200 points x 10 minutes at 1 s; 2% of readings delayed by 90 seconds
INSERT INTO bench_load
SELECT row_number() OVER (ORDER BY
           g.ts + CASE WHEN (p + extract(epoch FROM g.ts)::int) % 50 = 0
                       THEN interval '90 seconds' ELSE interval '0' END,
           p) AS arrival_seq,
       p, g.ts, 20 + p * 0.1
FROM generate_series(1, 200) AS p
CROSS JOIN generate_series('2026-10-01 00:00+00'::timestamptz,
                           '2026-10-01 00:09:59+00'::timestamptz,
                           interval '1 second') AS g(ts);

-- redeliver every 200th reading at the end, as a restarted consumer would
INSERT INTO bench_load
SELECT 1000000 + row_number() OVER (ORDER BY arrival_seq), point_id, ts, value
FROM bench_load WHERE arrival_seq % 200 = 0;

WITH ordered AS (
    SELECT point_id, ts,
           max(ts) OVER (PARTITION BY point_id ORDER BY arrival_seq
                         ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_ts_before
    FROM bench_load
)
SELECT count(*) AS rows_sent,
       count(DISTINCT point_id) AS series,
       count(*) - (SELECT count(*) FROM (SELECT DISTINCT point_id, ts FROM bench_load) d) AS duplicate_rows,
       round(100.0 * count(*) FILTER (WHERE ts < max_ts_before) / count(*), 2) AS pct_out_of_order
FROM ordered;

 rows_sent | series | duplicate_rows | pct_out_of_order
-----------+--------+----------------+------------------
    120600 |    200 |            600 |             2.48

Reading the profile

The out-of-order share (2.48%) is higher than the 2% of readings deliberately delayed, because the 600 redelivered copies also arrive after newer readings for the same point. That is the point of profiling: the generator's settings and the data it produces are not the same thing. Compare the profile with the same query run on a day of captured production traffic, and record both in the report.

Batch size and number of writers

Batch size is a client setting that vendor documentation singles out, so treat it as a variable to sweep, not a constant to pick once. The PostgreSQL documentation recommends COPY or many rows per transaction over one transaction per row. The vendor reports, in InfluxData's documentation, an optimal batch size of 5,000 lines of line protocol for InfluxDB OSS v2; that is the vendor's guidance for its engine, a starting point for your sweep rather than a value to copy to other products.

• Sweep batch size (for example rows per statement or per COPY) and the number of concurrent writers separately, one variable at a time.
• Use the batch size your real pipeline can produce. A Kafka consumer or MQTT bridge that flushes every second at low point counts cannot send very large batches; a benchmark that does is measuring a different system.
• Record the end-to-end latency the batching adds. Larger batches can raise throughput, and waiting to fill them delays freshness; measure both rather than assuming either.
• TSBS loaders expose batch size and worker count as common flags; report the values used.

Warm-up, duration and repeat runs

The pgbench documentation is blunt about short tests: it is easy to produce meaningless numbers, any test that runs for only a few seconds should not be believed, runs should last at least a few minutes, and some cases need hours to get reproducible numbers. For time-series ingestion the reasons are specific:

• Checkpoints. After each checkpoint, the first change to every data page writes a full page image to WAL, so write volume rises and falls with the checkpoint cycle (by default every 5 minutes or when max_wal_size is reached). A run shorter than several cycles samples only part of it.
• Background work. Autovacuum, compaction, compression jobs and roll-up refreshes all start some time into a run. The pgbench documentation notes that autovacuum can cause unpredictable changes in measured performance; that is real production behaviour and belongs inside the measured window.
• Caches and partitions. A new partition or chunk starts empty and small; the steady state arrives once it holds a realistic amount of data and its indexes no longer fit in memory as easily.

Discard a warm-up period, measure for long enough to cover several cycles of each background process, and repeat the whole run several times. Report the spread between runs, not only the best one. The pgbench documentation also warns that reproducing a run exactly, with the same random seed, can hide performance variability; use fixed seeds for debugging and varied seeds for the final runs.

Durability settings: compare like with like

A result can be fast partly because writes are not durable. In PostgreSQL, turning fsync off can cause unrecoverable data corruption after a power failure or crash, and asynchronous commit (synchronous_commit = off) returns success before the commit is flushed, so the most recent transactions may be lost in a crash. Other engines have their own equivalents (acknowledging a write before it is persisted, or before replicas confirm it). Decide what an acknowledged reading must survive, configure every candidate to that standard, and state it in the report. As a caution of ours rather than a sourced finding: if two results were run with different durability settings, do not compare them as like for like.

Capture the settings of every run in the same way you capture results. The output below is from the embedded PostgreSQL build used for the SQL on this site, which runs with fsync off; a write benchmark on it would say nothing about a production server.

SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('fsync', 'synchronous_commit', 'full_page_writes', 'wal_level',
               'shared_buffers', 'max_wal_size', 'checkpoint_timeout', 'autovacuum')
ORDER BY name;

        name        | setting | unit
--------------------+---------+------
 autovacuum         | on      |
 checkpoint_timeout | 300     | s
 fsync              | off     |
 full_page_writes   | on      |
 max_wal_size       | 1024    | MB
 shared_buffers     | 16384   | 8kB
 synchronous_commit | on      |
 wal_level          | replica |

Run the query mix during ingestion

Ingest-only results describe a system that differs from most production telemetry stores. Dashboards, alarm rules and reports read the same tables while data arrives, compete for I/O and memory, and may block or be blocked by background work. The TSBS README states that it benchmarks bulk load performance and query execution performance and does not currently measure concurrent insert and query performance. If that combination is your production case, you need to add it yourself.

• Run your five most important queries at realistic concurrency while the ingest load runs: latest value per point, a 24-hour trend for one asset, a site roll-up for yesterday, an alarm-rule window.
• Measure freshness: the delay between a reading's timestamp and the moment a query can see it. Insert canary readings with known timestamps and poll for them.
• Report query latency as percentiles over the run (for example median and 99th percentile), measured separately for each query type.

Do not benchmark the load generator

• Pre-generate data. TSBS generates data and queries before the benchmark phases so that results are not affected by generating them on the fly. Do the same, or replay captured traffic from files.
• Watch the client. The pgbench documentation notes that pgbench can itself become the bottleneck with many client sessions, and suggests running it on a different machine with low network latency, or several instances. Monitor client CPU and network; if the client is saturated, the result measures the client.
• Same path as production. Include TLS, the connection pooler and the network hop you will have, or state clearly that you left them out.
• Count what was accepted. Verify row counts after the run. A loader that drops rejected batches can show a high rate for data that never arrived.

Known suites: TSBS and pgbench

Two openly available tools are described here; they are examples, not a ranking or a complete list. Their documentation describes what they do and do not cover:

Reading someone else's benchmark

Before trusting a published result, including a vendor's, check: which versions and settings were used and whether durability matched yours; how many series and what interval mix; whether data arrived in order; how long the run lasted and whether background work ran inside it; whether queries ran during ingest; who chose the configuration of each product. Our caution: treat results measured on different hardware, schemas or durability settings as not comparable like for like. The time-series database selection guide shortlists products on features; your own run settles performance.

What to report

Example plan: choosing a store for a building portfolio

A hypothetical team is choosing between monthly-partitioned PostgreSQL and a time-series engine for 40 buildings. Their plan, written before any run (the durations, outage length, number of batch sizes and number of repeats are illustrative choices, not recommendations):

1. Capture one weekday of real BMS traffic from the existing historian: points, intervals, timestamps and values. Profile it with the SQL above.
2. Scale it to the five-year point count by cloning sites with new point IDs, keeping the interval mix and late-data share.
3. Pre-load both candidates to the full raw retention size, with roll-ups in place.
4. Replay at real speed for six hours with dashboards and the alarm-rule queries running; then simulate a two-hour gateway outage and replay its backlog at pipeline speed.
5. Sweep batch size at three values the ingestion service can actually produce. Keep fsync and synchronous commit on, or the engine's durable-acknowledgement equivalent.
6. Repeat the full run three times per candidate; report every run with the template above.

The outcome is a pass or fail against the criteria plus the margin, not a ranking of products in general.

Limitations

• A benchmark describes one workload on one setup. Changing hardware, schema, version or durability can change the outcome.
• Synthetic data rarely captures every production quirk; keep a replay of real traffic in the suite.
• Cloud instances and shared storage vary from run to run; repeat runs and report the spread.
• Ingestion is one dimension. Our editorial view: operations (backups, upgrades, schema changes), cost and skills can matter as much as a throughput margin. For backups and sizing, see backup and restore and sizing storage for one-second telemetry; for the pipeline side, batch vs stream ingestion.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: pgbench
• PostgreSQL 18 documentation: Asynchronous Commit
• PostgreSQL 18 documentation: Write Ahead Log settings
• PostgreSQL 18 documentation: WAL Configuration
• PostgreSQL 18 documentation: Populating a Database
• Time Series Benchmark Suite (TSBS) README
• InfluxDB OSS v2 documentation: Optimize writes

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.