TimescaleDB Ordered Append: Optimize for Time-Ordered Inserts (2026)

TimescaleDB’s ordered append optimization is fundamentally about making your writes faster by helping the database avoid random disk seeks when you’re inserting data that’s already sorted by time.

Let’s look at a typical scenario. Imagine you’re ingesting sensor data, and each batch of data arrives with timestamps that are chronologically ordered.

-- Sample data insertion
INSERT INTO sensor_data (time, device_id, temperature) VALUES
('2023-10-27 10:00:00.000000', 'device_A', 25.5),
('2023-10-27 10:00:01.000000', 'device_B', 26.1),
('2023-10-27 10:00:02.000000', 'device_A', 25.6);

Normally, when data is inserted into a PostgreSQL table, the database might need to perform random writes to disk. If you’re inserting data into a hypertable that’s partitioned by time, and your inserts are not time-ordered, TimescaleDB has to figure out which chunk (time partition) each row belongs to and write it there. This can involve multiple disk seeks, which are slow.

However, if your incoming data is already ordered by time, TimescaleDB can leverage an optimization called "ordered append." When it detects that new data is being appended to the latest time chunk and is chronologically ordered, it can perform sequential writes. This is much faster because the disk head doesn’t have to jump around. It’s like reading a book page by page versus flipping randomly.

To enable this, you primarily need to ensure two things:

1. Your inserts are actually time-ordered. This is a property of your application’s data generation and ingestion pipeline.
2. TimescaleDB is configured to recognize and utilize this.

The key configuration parameter is timescaledb.max_chunk_time_interval. This setting defines the maximum duration of a single time chunk. When you insert data, TimescaleDB checks if the new data falls within the bounds of the latest existing chunk. If it does, and the data is ordered, it can perform the ordered append. If the data spans across multiple chunks or is out of order, it falls back to the standard (slower) insertion mechanism.

Let’s say you have a hypertable sensor_data partitioned by time. If you set timescaledb.max_chunk_time_interval to '1 day', TimescaleDB will aim to create chunks that are at most one day long.

-- Example hypertable creation
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    device_id TEXT NOT NULL,
    temperature NUMERIC
);

SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => interval '1 day');

With this setup, if you insert data where all timestamps are within the current day, and they are ordered chronologically, TimescaleDB will try to use ordered append.

How to verify it’s working:

You can check the TimescaleDB logs for messages indicating ordered append. You’ll often see messages like INFO: ordered append: chunk <chunk_id>.

You can also observe performance. If your inserts are consistently fast and you’re seeing high write throughput when inserting time-ordered data into the latest chunk, it’s a good sign.

What if it’s not working?

1. Data is not time-ordered: This is the most common reason. Your ingestion pipeline might be interleaving data from different devices or sources, or there might be clock skew issues. The fix is to ensure your application always sends data in strict chronological order.
2. timescaledb.max_chunk_time_interval is too small: If your max_chunk_time_interval is set to, say, 1 hour, but your application inserts data in batches that span multiple hours at once, the ordered append might not trigger for the entire batch. You might need to increase max_chunk_time_interval to match or exceed the typical duration of your insert batches.
   • Diagnosis: Check SHOW timescaledb.max_chunk_time_interval;
   • Fix: ALTER SYSTEM SET timescaledb.max_chunk_time_interval = '1 day'; (Restart required)
   • Why: A larger interval allows more room for ordered data within a single chunk’s time boundary, increasing the chances of the optimization kicking in.
3. Inserts are not targeting the latest chunk: If you’re inserting historical data that falls into older, already-closed chunks, ordered append won’t apply. The optimization is specifically for appending to the current or next time interval.
   • Diagnosis: Examine the timestamps of your inserted data and compare them with the time ranges of your existing chunks using \d+ <chunk_name> in psql or querying timescaledb_information.chunks.
   • Fix: Ensure your application logic prioritizes inserting current data and only inserts historical data when absolutely necessary, understanding it will be slower.
4. High contention on the target chunk: While less common, if many parallel inserts are trying to write to the exact same time interval concurrently and out of order, it can interfere. However, ordered append is designed to handle multiple writers to the latest chunk if they are ordered.
   • Diagnosis: Monitor pg_stat_activity for numerous backend processes inserting into the same hypertable.
   • Fix: If possible, stagger inserts slightly or ensure your application’s ordering is robust.
5. timescaledb.compress_chunk_after_interval conflicts: If a chunk is very small and gets compressed quickly, it might disrupt the ordered append flow for subsequent data if the compression interval is very short and overlaps with new inserts.
   • Diagnosis: Check timescaledb_information.hypertables for compress_chunk_after_interval.
   • Fix: Ensure compress_chunk_after_interval is set to a value that doesn’t overlap with your typical insert cadence. For example, if you insert daily, don’t set compression to 1 hour.
6. Buffer bloat or WAL issues: Extreme write loads can sometimes saturate the WAL or hit buffer limits, indirectly impacting the efficiency of any write operation, including ordered appends.
   • Diagnosis: Monitor pg_stat_bgwriter and pg_wal_lsn_diff or pg_wal_lsn_segments.
   • Fix: Tune wal_buffers, checkpoint_timeout, max_wal_size, and bgwriter_delay.

The next thing you’ll likely encounter after optimizing inserts is the performance of queries that span across many chunks.