INSERT Performance Tuning for TimescaleDB

I have reviewed dozens of TimescaleDB deployments where the team was investigating hardware upgrades, read replica configurations, or connection pooler tuning -- all to address write throughput problems that were caused by single-row INSERT loops. The fix in every case was the same: batch your inserts. No new hardware, no architectural changes, just a different INSERT pattern that delivers 20-50x better throughput on identical infrastructure.

This article covers why single-row inserts are so costly in TimescaleDB, what the alternatives look like, and what other factors quietly degrade write performance.

The Hidden Cost of Single-Row INSERTs

When your application executes INSERT INTO sensor_readings VALUES (...) in a loop, each iteration pays a fixed overhead that has nothing to do with the data:

1. Network round trip between client and server
2. SQL parsing and plan generation
3. Transaction bookkeeping (BEGIN/COMMIT if autocommit)
4. WAL record write for durability
5. Index maintenance for every index on the table
6. Chunk routing -- TimescaleDB determines which chunk owns the row based on timestamp and constraint checks

For a single row, the overhead dominates. The actual data write is a small fraction of the total work. Scale this to 100,000 rows and you are paying the overhead 100,000 times.

Batching amortizes this overhead across many rows. A 1,000-row INSERT resolves chunk routing once, generates one plan, writes one WAL record per page, and makes one network round trip.

What the Benchmarks Show

Against a TimescaleDB hypertable with 1-day chunks:

The jump from single-row to even a modest 50-row batch is dramatic. The jump to COPY is transformative. These are not theoretical maximums -- they reflect real workloads on standard hardware.

Practical Batching

Switching to batched inserts rarely requires major refactoring. Instead of executing one INSERT per loop iteration, accumulate rows in a buffer and flush them as a single multi-row statement:

-- Batch insert with generate_series (example: 1,000 rows)
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;

Every major PostgreSQL client library supports multi-row INSERTs or batch execution natively:

• Go (pgx): CopyFrom() for COPY protocol, or batch queries
• Python (psycopg): executemany() for batches, copy_from() for COPY
• Node.js (pg): parameterized multi-row VALUES syntax

The sweet spot for batch size is 500 to 5,000 rows. Below 500, per-statement overhead is still meaningful. Above 5,000, diminishing returns set in and you start competing with autovacuum for buffer pool space. Default to 1,000 rows per batch unless you have specific benchmarks suggesting otherwise.

COPY: The Fastest Path

When you control the data format -- ETL jobs, backfills, data migrations -- the PostgreSQL COPY protocol is unmatched. COPY skips SQL parsing entirely. The client streams raw tuples in CSV or binary format, and PostgreSQL writes them to heap pages with minimal per-row overhead.

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

COPY FROM STDIN streams over the existing database connection, so no server-side file access is needed. In production, this consistently delivers 50-100x the throughput of single-row inserts.

Three Silent Performance Killers

1. Secondary Indexes

Each index on the hypertable adds B-tree maintenance cost to every inserted row. In controlled testing, adding two secondary indexes reduced insert throughput by 20-40%. The fix: use partial indexes where possible (index only rows matching a WHERE condition), drop unused indexes (check pg_stat_user_indexes for indexes with zero scans), and consider batch-loading patterns that drop and recreate indexes.

2. Inserts Into Compressed Chunks

When your application writes to a chunk that has already been compressed, TimescaleDB transparently decompresses the affected segment, appends the row, and recompresses. This is orders of magnitude slower than writing to an uncompressed chunk.

The prevention is simple: set compress_after to at least one chunk interval so the chunk currently receiving writes is never compressed. Check your situation:

SELECT chunk_name, is_compressed, range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_readings'
ORDER BY range_end DESC
LIMIT 5;

If the newest chunk is compressed and your application is still writing to that time range, increase compress_after immediately.

3. Frequent Chunk Creation

New chunk creation acquires a brief lock that can stall concurrent writers. With reasonable chunk intervals (hours or days), this happens rarely. With overly aggressive intervals (1-minute chunks means 60 creations per hour), it becomes a real contention point.

Rule of thumb: if you are creating more than a few chunks per hour, your interval is too small. Use set_chunk_time_interval() to increase it.

Summary

The write performance hierarchy for TimescaleDB is clear: COPY > batch INSERT > single-row INSERT, with each step delivering a 5-20x improvement over the previous one. Combine batching with proper compression timing, index discipline, and appropriately-sized chunks, and you can achieve throughput levels that would otherwise require significantly more expensive hardware.

Before scaling up your infrastructure, check how you are inserting. The answer is almost always there.