The most surprising thing about modifying TimescaleDB hypertables is that, unlike standard PostgreSQL tables, DDL changes on them don’t necessarily lock the entire table for the duration of the operation.
Let’s see this in action. Imagine you have a hypertable named measurements with a time column and an insert_time column, both TIMESTAMPTZ. You want to add a new location column of type TEXT.
-- Initial hypertable structure
CREATE TABLE measurements (
time TIMESTAMPTZ NOT NULL,
device_id INT NOT NULL,
temperature DOUBLE PRECISION,
insert_time TIMESTAMPTZ DEFAULT now()
);
SELECT create_hypertable('measurements', 'time');
-- Let's add some data
INSERT INTO measurements (time, device_id, temperature) VALUES
('2023-10-26 10:00:00 UTC', 1, 22.5),
('2023-10-26 10:05:00 UTC', 2, 23.1);
Now, to add the location column:
ALTER TABLE measurements ADD COLUMN location TEXT;
If you were watching the pg_stat_activity view, you’d notice that while the ALTER TABLE command is running, there isn’t a single, long-held exclusive lock on the measurements table. Instead, TimescaleDB intelligently distributes this operation across the underlying regular PostgreSQL tables (chunks) that make up the hypertable. For each chunk, it acquires a brief ACCESS EXCLUSIVE lock, performs the ALTER TABLE on that specific chunk, and then releases the lock before moving to the next. This significantly reduces the downtime and impact on concurrent writes and reads, especially for hypertables with many chunks.
The problem this solves is the traditional PostgreSQL ALTER TABLE ADD COLUMN bottleneck. On large tables, this operation can take a very long time, during which the table is inaccessible for writes, and sometimes even reads, depending on the operation. TimescaleDB’s chunking architecture allows it to break this down into smaller, manageable operations on individual chunks.
Internally, TimescaleDB manages a set of regular PostgreSQL tables, called "chunks," that store the actual data for a hypertable. When you perform a DDL operation on a hypertable, TimescaleDB iterates through all the existing chunks. For each chunk, it executes the equivalent DDL statement on that specific PostgreSQL table. This is why the locks are short-lived and granular – they are applied per-chunk, not per-hypertable. New chunks created after the DDL operation will automatically include the new column.
The exact levers you control are the same as standard PostgreSQL DDL, but the behavior is different due to the TimescaleDB layer. For instance, ALTER TABLE ... ADD COLUMN ... DEFAULT ... is still a powerful option. If you add a column with a DEFAULT value, TimescaleDB will apply this default during the chunk-by-chunk operation. For existing data, it will update the rows to include the default value. For new data, the default will be applied as usual. This is crucial for maintaining data consistency.
Consider adding a NOT NULL constraint. This is generally not recommended on a hypertable with existing data that doesn’t satisfy the constraint, as it will fail on the first chunk that contains NULL values for that column. If you are certain all existing data (or you’ve updated it) satisfies the constraint, you can add it. TimescaleDB will attempt to add the constraint to each chunk individually.
One thing that often trips people up is the behavior of ALTER TABLE ... SET DATA TYPE. While the operation is still chunked, it can still be a significant operation per chunk if the data type conversion is complex or requires rewriting data. For instance, converting a TEXT column to an INT on a massive hypertable will still take a considerable amount of time per chunk, even though the hypertable itself remains largely available. The key is that the entire hypertable doesn’t get blocked for the entire duration of the operation across all chunks.
After you’ve successfully added your location column, the next logical step in a migration might be to populate this new column for existing data, or perhaps to add a new index on it.