INSERT Performance Tuning for TimescaleDB

The single most impactful optimization you can make to a TimescaleDB deployment has nothing to do with PostgreSQL configuration parameters, hardware upgrades, or query tuning. It is changing how your application inserts data. A single-row INSERT loop and a batched COPY pipeline operate on the same data with the same schema, yet the throughput difference is routinely 50x. That is not a typo -- fifty times faster, same hardware, same table structure.

If your time-series pipeline is CPU-bound or latency-constrained, start here before touching anything else.

Why Single-Row Inserts Are So Expensive

Every individual INSERT statement carries a fixed overhead that has nothing to do with the data itself:

• The client sends a query over the network
• PostgreSQL parses the SQL text
• The planner builds an execution plan
• A transaction is opened (if not already in one)
• A WAL record is written for durability
• Every index on the table is updated
• An acknowledgment is sent back to the client

For a single row, the actual data write is a tiny fraction of the total work. The rest is protocol overhead, WAL synchronization, and round trips.

TimescaleDB adds another layer: chunk routing. Each INSERT must determine which chunk the row belongs to based on its timestamp, check the chunk's constraint bounds, and route accordingly. For 100,000 single-row inserts, this routing decision happens 100,000 times. For a 1,000-row batch, the planner resolves routing for the entire VALUES list in a single pass.

Throughput by Insert Method

Benchmarked against a TimescaleDB hypertable with 1-day chunks:

Method	Typical Throughput	Relative Speed
Single-row INSERT	5,000 - 15,000 rows/sec	1x (baseline)
Batch INSERT (50 rows)	30,000 - 80,000 rows/sec	~5x
Batch INSERT (1,000 rows)	80,000 - 200,000 rows/sec	~20x
COPY FROM STDIN	300,000 - 1,000,000 rows/sec	~50x

The numbers tell a clear story: even modest batching (50 rows) delivers a 5x improvement. Larger batches continue to improve throughput up to about 5,000 rows, after which diminishing returns set in.

Batched INSERTs: The Practical Default

Most application code can adopt batching with minimal changes. Instead of inserting one row per loop iteration, accumulate rows and send them in a single multi-row VALUES statement:

INSERT INTO sensor_readings (recorded_at, device_id, temperature)
SELECT
    now() - (1000 - n) * INTERVAL '1 second',
    (n % 100 + 1),
    random() * 100
FROM generate_series(1, 1000) AS n;

In application code, every major PostgreSQL driver supports multi-row VALUES or batch execution. Go's pgx has CopyFrom() and batch queries. Python's psycopg has executemany() and copy_from(). Node.js drivers support parameterized multi-row inserts.

The optimal batch size for most workloads falls between 500 and 5,000 rows. Below 500, per-statement overhead is still significant. Above 5,000, returns diminish and you begin competing with autovacuum for buffer pool resources. A 1,000-row batch is a sensible default.

COPY: Maximum Throughput

For ETL pipelines, data migrations, and bulk backfills, the PostgreSQL COPY protocol is the fastest path into TimescaleDB. COPY bypasses the SQL parser entirely -- the client streams raw tuples in binary or CSV format, and PostgreSQL writes them directly to heap pages with minimal per-row overhead.

COPY sensor_readings (recorded_at, device_id, temperature)
FROM STDIN WITH (FORMAT csv);

From application code, use the COPY FROM STDIN variant, which streams data over the existing connection without needing a server-side file. In production, COPY consistently delivers 50-100x the throughput of single-row inserts with lower per-row CPU overhead.

The Index Tax

Secondary indexes impose a per-row maintenance cost that compounds with insert volume. Every B-tree index on the hypertable must be updated for every inserted row. In testing, a hypertable with two secondary indexes showed 20-40% lower insert throughput compared to one with only the default time index.

Mitigation strategies:

• Partial indexes that only cover rows matching a WHERE condition (e.g., anomalous values above a threshold) reduce the maintenance cost per insert while still supporting the queries you need.
• Batch index creation after bulk loads: drop indexes, load data via COPY, then recreate indexes.
• Review which indexes are actually used. pg_stat_user_indexes reveals indexes with zero scans that are consuming write bandwidth for no benefit.

Compressed Chunks: The Silent Killer

TimescaleDB compression converts chunks from row-oriented heap storage to a columnar format. Compressed chunks are smaller and faster to read, but inserting into one triggers a transparent decompression-recompression cycle: decompress the affected segment, append the row, recompress. This is orders of magnitude slower than inserting into an uncompressed chunk.

-- Check if your active chunks are compressed
SELECT chunk_name, is_compressed, range_start, range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_readings'
ORDER BY range_end DESC
LIMIT 5;

If the most recent chunk shows is_compressed = true while your application is still writing to that time range, you need to increase your compress_after interval. Set it to at least one chunk interval (ideally two) to maintain a buffer of uncompressed chunks for active writes.

Chunk Creation Overhead

When an incoming row's timestamp falls outside all existing chunks, TimescaleDB creates a new chunk on the fly -- a new table, CHECK constraints, and propagated indexes. This takes a few milliseconds but acquires a brief lock that can stall concurrent inserts.

For most workloads with chunk intervals of hours or days, chunk creation is infrequent. It becomes a problem when chunk intervals are too aggressive: a 1-minute interval means 60 chunk creations per hour, each one a brief contention point. If you are creating more than a few chunks per hour, increase your chunk interval.

Performance Checklist

1. Switch to batched inserts or COPY. Single-row loops are the most common source of unnecessary write overhead. Batch at 1,000 rows for application code; use COPY for ETL and backfills.

2. Keep the active chunk uncompressed. Set compress_after to at least one chunk interval so the currently-written chunk never triggers decompression-recompression.

3. Audit your indexes. Every secondary index costs write throughput. Drop unused indexes and convert broad indexes to partial indexes where the query pattern allows it.

4. Size chunk intervals appropriately. If you are creating more than a few chunks per hour, the interval is too small. Chunk creation locks compound under high concurrency.

5. Monitor per-INSERT timing. Track your INSERT templates over time. A rising mean execution time usually means you are hitting compressed chunks, contending with autovacuum, or experiencing chunk creation bottlenecks.