INSERT Performance Tuning for TimescaleDB

INSERT Performance Tuning for TimescaleDB

Your IoT pipeline inserts 10,000 rows/second but the database is running at 50% CPU with write latency climbing -- the problem isn't hardware, it's how you're inserting. The difference between a single-row INSERT loop and a properly batched COPY pipeline is not a marginal optimization. It is a 50x throughput improvement with the same hardware, the same schema, and the same data.

The Cost of Single-Row Inserts

Every individual INSERT statement carries fixed overhead that has nothing to do with the data itself. The client sends a query over the network. PostgreSQL parses the SQL, plans the execution, opens a transaction (if not already in one), writes a WAL record, updates every index on the table, and sends an acknowledgment back. For a single row, the actual data write is a fraction of the total work. The rest is protocol overhead, WAL synchronization, and round trips.

In a TimescaleDB hypertable, there is additional per-row routing cost. Each INSERT must determine which chunk the row belongs to based on the timestamp, check the chunk's constraint bounds, and route accordingly. For single-row inserts, this routing decision happens 100,000 times for 100,000 rows. For a 1,000-row batch, it happens once per batch execution -- the planner resolves the routing for the entire VALUES list in a single pass.

This overhead is measurable. In controlled benchmarks against a TimescaleDB hypertable with 1-day chunks, the relative performance of different insert methods tells a clear story:

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

Secondary indexes make it worse. Each additional index on the hypertable adds a B-tree maintenance cost to every inserted row. In testing, a hypertable with two secondary indexes showed 20-40% lower insert throughput compared to the same hypertable with only the default time index. Partial indexes help here -- indexing only the rows that match a WHERE condition (like anomaly values above a threshold) reduces the maintenance cost per insert while still supporting targeted queries.

Batched INSERTs: The Practical Sweet Spot

Most application code can switch from single-row inserts to batched inserts with minimal refactoring. Instead of executing one INSERT per row in a loop, accumulate rows and insert them in a single statement with a multi-row VALUES clause or a SELECT from a series:

-- Batch INSERT using generate_series (1,000 rows per 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;

For application-level batching with parameterized queries, most database drivers support multi-row VALUES syntax. In Go with pgx, Python with psycopg, and Node.js with pg, you build a single INSERT with multiple value tuples rather than looping over individual inserts.

The optimal batch size is between 500 and 5,000 rows. Below 500, you're still paying meaningful per-statement overhead. Above 5,000, the returns diminish and you start competing with autovacuum for buffer pool space. The 1,000-row batch is a reasonable default that works well across most workloads.

COPY: Maximum Throughput for Bulk Loads

For ETL pipelines, backfills, and any scenario where you control the data format, the PostgreSQL COPY protocol is the fastest path into TimescaleDB. COPY bypasses the SQL parser entirely -- the client sends raw tuples in a binary or CSV stream, and PostgreSQL writes them directly to heap pages with minimal per-row overhead.

-- COPY from a CSV file
COPY sensor_readings (recorded_at, device_id, temperature)
FROM '/tmp/sensor_data.csv' WITH (FORMAT csv, HEADER true);

From application code, use the COPY FROM STDIN variant, which streams data over the existing connection without requiring a server-side file:

-- COPY FROM STDIN (used by client libraries)
COPY sensor_readings (recorded_at, device_id, temperature)
FROM STDIN WITH (FORMAT csv);

Every major PostgreSQL client library exposes a COPY interface. In pgx (Go), it is conn.CopyFrom(). In psycopg (Python), it is cursor.copy_from() or the newer copy() method. In production, COPY consistently delivers 50-100x the throughput of single-row inserts with lower CPU overhead per row.

Compressed Chunks: The Silent Insert Killer

TimescaleDB compression converts chunk data from row-oriented heap storage into a columnar format. Compressed chunks are read-optimized and dramatically smaller on disk. But inserting into a compressed chunk triggers a transparent decompression-recompression cycle: TimescaleDB must decompress the affected segment, append the new row, and recompress. This is orders of magnitude slower than inserting into an uncompressed chunk.

The fix is straightforward: set your compress_after interval so that the chunk currently receiving inserts is never compressed.

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

If the most recent chunk shows is_compressed = true and your application is still inserting data into that time range, you have a problem. Set compress_after to at least twice your chunk interval to maintain a buffer of uncompressed chunks for active writes.

Chunk Creation Lock Overhead

When an incoming row's timestamp falls outside all existing chunks, TimescaleDB creates a new chunk on the fly. This involves creating a new PostgreSQL table, adding CHECK constraints, and propagating indexes from the parent hypertable. The creation itself takes only a few milliseconds, but it acquires a brief lock that can stall concurrent inserts.

For most workloads with reasonably sized chunk intervals (hours or days), chunk creation is infrequent and the overhead is negligible. It becomes a problem when chunk intervals are too small -- a 1-minute chunk interval means 60 chunk creations per hour, each one a brief contention point for high-throughput writers. The solution is to size your chunk interval appropriately. If you're creating more than a few chunks per hour, your interval is too aggressive.

How myDBA.dev Helps

The myDBA.dev Queries page tracks every INSERT template's mean execution time, call count, and total time. If single-row inserts are dominating your workload, you will see an INSERT template with a high call count and low rows-per-call -- a clear signal to batch. The per-query timing trend makes it straightforward to validate that a batching change actually reduced per-INSERT overhead.

Checklist

Use batched inserts or COPY. Single-row INSERT loops are the most common source of unnecessary write overhead in TimescaleDB. Batch at 1,000 rows per statement for application code. Use COPY for ETL and backfills.

Set compress_after to keep the active chunk uncompressed. Inserting into compressed chunks triggers decompression-recompression and destroys throughput. A compress_after of at least one chunk interval ensures the current chunk stays writable.

Monitor per-INSERT timing. Track your INSERT templates over time. A rising mean execution time on an INSERT template usually means you're hitting compressed chunks, contending with autovacuum, or creating too many chunks. Catch the trend before it becomes an outage.