DEV Community: James Blackwood-Sewell

Benchmarking Databases for Real-Time Analytics Applications

James Blackwood-Sewell — Wed, 26 Mar 2025 16:11:45 +0000

Choosing the right database for analytics is hard. With many options available, each is optimized for different use cases.

Some databases are built for real-time analytics in customer-facing applications, where low-latency queries and high-ingest performance are essential. Others are designed for internal BI and reporting and optimized for large-scale aggregations and batch processing. Some databases are general-purpose, handling both transactions and analytics, while others specialize in analytical workloads.

Benchmarks can help—but only if they reflect your actual workload.

Several benchmarks, such as ClickBench, TPC-H, and TPC-DS, evaluate the performance of databases for analytics. However, they are not representative of real-time analytics.

To fill this gap, we’ve created RTABench, a new benchmark to assist developers in evaluating the performance of different databases in real-time analytics scenarios.

Key patterns in real-time analytics include:

Multi-table Joins: Quickly combining data from several tables.
Selective Filtering: Fast lookups for the most recent and specific data.
Pre-aggregated Results: Using pre-calculated materialized views for prompt responses.

While denormalizing data can speed up queries, it complicates management and raises costs. Real-time applications favor normalized schemas and joining data at query time.

Introducing RTABench

To meet the need for real-time analytics workloads, we developed RTABench, a benchmark designed to test databases with these specific requirements. RTABench focuses on essential query patterns such as joins, filtering, and pre-aggregations.

How RTABench Works

RTABench models an order tracking system with normalized tables to mimic real-time analytics applications. It uses around 171 million order records and evaluates databases with 40 different queries, including basic counts, selective filtering, multi-table joins, and pre-aggregated queries.

RTABench categorizes databases into:

General-purpose databases: e.g., PostgreSQL and MySQL.
Real-time analytics databases: Optimized for quick insights, often secondary databases.
Batch analytics databases: Primarily for historical data and excluded from real-time benchmarks.

Benchmark Results: What We Learned

RTABench results are published at rtabench.com. While performance varies based on workload characteristics, this benchmark reveals some interesting insights:

General-purpose databases perform better on RTABench than on ClickBench. That’s expected—RTABench uses a normalized schema similar to real applications, while ClickBench is based on a denormalized dataset optimized for batch analytics.
TimescaleDB is 1.9x faster than ClickHouse on RTABench, even though it’s 6.8x slower on ClickBench. This is likely because TimescaleDB is optimized for real-time analytics applications, which often rely on normalized schemas and selective aggregations, while ClickHouse shines in denormalized, columnar analytics with large-scale aggregations.
Incremental materialized views offer massive speedups. They deliver up to hundreds or even thousands of times faster performance than querying the raw data (from seconds to a few milliseconds), demonstrating their value for real-time analytics. However, among the databases tested, only ClickHouse and TimescaleDB support them.
ClickHouse is the leader in data loading and storage efficiency. It’s 4.8x faster at loading data and uses 1.7x less disk than the next best database.
PostgreSQL was the fastest general-purpose database. The most popular database among developers demonstrates its versatility. With indexing, it’s only 4.1x slower than TimescaleDB on raw queries—but it can’t match the performance of incremental materialized views, which PostgreSQL doesn’t support.

Check out the full results here.

Community contributions to RTABench are encouraged! Whether it's adding new databases, improving existing queries, or making configuration tweaks, we hope you’ll contribute to the repo. All tools, datasets, and results are available on GitHub, with the latest version here.

Speed Up Triggers by 7x With Transition Tables

James Blackwood-Sewell — Thu, 20 Mar 2025 13:13:38 +0000

TimescaleDB 2.18 introduces transition table support for hypertables, a long-requested and upvoted feature (issue from 2019!) that enhances trigger functionality by allowing bulk access to affected rows during INSERT, UPDATE, or DELETE operations.

This sidesteps the inefficiencies of row-by-row trigger processing, making operations that aggregate metadata about devices or batches significantly more efficient: we’ve seen a 7x performance improvement for statement-level triggers with transition tables.

Like the support for PostgreSQL indexes in our columnstore, this feature brings TimescaleDB closer to standard PostgreSQL behavior, making integration into existing workflows smoother. More importantly, it unlocks new, previously impractical bulk processing capabilities due to performance constraints.

How Transition Tables Work in PostgreSQL

Transition tables were introduced in PostgreSQL 10 to give AFTER triggers access to all affected rows in a single operation. Normally, when a trigger fires, it operates on each row one at a time—meaning that if a statement inserts, updates, or deletes multiple rows, the trigger function must run once per row, which is very inefficient.

With transition tables, PostgreSQL allows trigger functions to see all modified rows at once in a temporary table-like structure. This means a trigger can process changes in bulk rather than row-by-row. As you can guess, it’s a lot faster!

These transition tables are available in AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers. They are referenced using the REFERENCING NEW TABLE AS or REFERENCING OLD TABLE AS clauses, which let the trigger function treat the affected rows like a normal table inside the function.

Until TimescaleDB 2.18, hypertables lacked support for this feature, which meant that bulk processing of changes had to be handled outside of trigger logic or row by row with a statement-level trigger.

Why Transition Tables Took Us So Long

I’m a developer advocate, not a developer, but I came up against the need for transition tables often enough that I brushed up my C skills and attacked the issue. At first, I assumed there was a deep technical reason why TimescaleDB didn't support transition tables. But as I dug into the code and started working on the initial pull request, it became clear that it wasn’t a technical limitation—it was probably just left out of the original implementation to reduce scope.

Hypertables add complexity compared to regular PostgreSQL tables because of chunking and our time-series planner optimizations. Handling transition tables across all these cases took a lot of testing, but fundamentally, it wasn’t impossible—it just hadn’t been prioritized yet.

I opened the initial PR, but, like many open-source contributions, it took some extra polish to get it production-ready. Mats later picked it up and refined the implementation, making sure it was solid and well-integrated into TimescaleDB. Huge shoutout to Mats for seeing it through to completion!

Use Cases for Transition Tables

Now that we have transition tables, they unlock a bunch of cool use cases for hypertables. Here are a few examples:

Tracking first and last appearances of an entity : Keep a metadata table that records when an entity (e.g., a generator or sensor) first and last appears in the dataset. You could even track the total number of records associated with it and any other metadata your application needs (even the full last state). This can then be used for ultra-fast lookups.
Batch validation and anomaly detection : Flag bad data—like missing values or extreme readings—for further analysis.
Batch tracking: Track the size, timing, and metadata (minimum and maximum timestamps) for each batch insert.

Tracking device metadata

One of the best use cases for transition tables is tracking per-ID metadata, like when a device or sensor first and last appears in the dataset. While this data is available in the main table, maintaining a separate metadata table can speed up finding records in the hypertable, especially when you have many hypertable chunks and all devices aren’t constantly reporting.

The following SQL can be used to set this up.

Table schema

CREATE TABLE power_generation (
    generator_id INTEGER,
    timestamp TIMESTAMPTZ NOT NULL,
    power_output_kw DOUBLE PRECISION,
    voltage DOUBLE PRECISION,
    current DOUBLE PRECISION,
    frequency DOUBLE PRECISION,
    temperature DOUBLE PRECISION
);

SELECT create_hypertable(
    'power_generation', 
    'timestamp',  
    chunk_time_interval=> INTERVAL '1 hour',
    create_default_indexes=>false
);

CREATE INDEX power_generation_generator_id_timestamp_idx
ON power_generation (generator_id, timestamp DESC);

Metadata table

CREATE TABLE generator_metadata (
    generator_id INTEGER PRIMARY KEY,
    first_seen TIMESTAMPTZ,
    last_seen TIMESTAMPTZ
);

Trigger function

CREATE OR REPLACE FUNCTION update_generator_metadata()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO generator_metadata (generator_id, first_seen, last_seen)
    SELECT generator_id, min(timestamp), max(timestamp)
    FROM new_table n
    GROUP BY generator_id
    ON CONFLICT (generator_id) DO UPDATE
    SET last_seen = GREATEST(generator_metadata.last_seen, EXCLUDED.last_seen),
        first_seen = LEAST(generator_metadata.first_seen, EXCLUDED.first_seen);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Trigger statement

CREATE TRIGGER generator_metadata_trigger
AFTER INSERT ON power_generation
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION update_generator_metadata();

This implementation ensures the metadata table automatically keeps accurate first_seen and last_seen timestamps for each generator_id.

It can be used to speed up real-time analytics, querying directly for the last record with this SQL:

SELECT *
FROM power_generation
WHERE generator_id = 1
AND timestamp = (SELECT last_record 
                 FROM power_generation_metadata
                 WHERE generator_id = 1);

Performance Considerations

Adding a trigger to track generator metadata is useful, but what about performance? Running row-by-row updates in a trigger is notoriously slow, so let’s compare a row-level trigger, a statement-level trigger (using transition tables), and an import without any trigger.

I used the pgingester command, running it with the following settings:

pgingester -b 10000 binary-copy 
                    --threads 1  
                    -f ~/power_generation_10m.csv

For an import of 10 million records using just a single thread, the results were clear:

A row-level trigger processing each record individually took 370 seconds, achieving only 27,000 rows/sec. This was almost 9x slower than the baseline import.
A statement-level trigger (using transition tables) processed the same data in 53 seconds, handling 187,000 rows/sec—a 1.28x slowdown compared to no trigger but still an order of magnitude faster than row-by-row processing.
A baseline import with no trigger completed in 42 seconds at 239,000 rows/sec.

The takeaway? Row-by-row triggers are effectively unusable at scale, while transition tables offer a reasonable trade-off between functionality and performance.

Conclusion

Transition tables in TimescaleDB 2.18 are a huge step forward for efficient bulk processing in hypertables. They make maintaining ID and device metadata tables much more practical while keeping TimescaleDB more in sync with standard PostgreSQL behavior.

Yes, there’s some overhead compared to not using triggers at all, but statement-level triggers with transition tables are 7x faster than row-by-row processing. If you’ve been avoiding triggers because of performance concerns, this feature makes them viable again.

I’m excited to see how people use this feature—whether for real-time monitoring, metadata tracking, or something completely unexpected. Got a cool use case? Let us know!

If you want to try this performance boost for yourself and supercharge your real-time analytics workloads, the easiest way to get started is to create a free Timescale Cloud account. You can also install TimescaleDB on your machine.

10 psql commands to boost productivity (with examples)

James Blackwood-Sewell — Tue, 11 Mar 2025 12:38:17 +0000

Did you know that despite all the fancy GUI tools available, the humble command-line psql remains the most popular way to interact with PostgreSQL databases?

But let's be honest - psql has a bit of a learning curve. The real magic lies in its meta-commands (or slash commands), which start with a backslash (\). These commands can dramatically improve your workflow, but only if you know they exist!

Let me walk you through my top 10 most-used psql meta-commands that will make your database interactions much smoother.

📚 Jump to the full article
🔧 Install psql on Mac, Ubuntu, Debian, Windows

1. `\d` - Describe Relations

This command shows you all the relations (tables, views, sequences) your current session can access:

tsdb=> \d
         List of relations
 Schema | Name  | Type  |   Owner   
--------+-------+-------+-----------
 public | power | table | tsdbadmin
 public | small | table | tsdbadmin
(2 rows)

🔥 Pro tip: There are many variants of this command:

\dt - Shows only tables
\di - Shows only indexes
\du - Shows roles
\dn - Shows schemas
\l - Shows databases (not \db as you might expect!)

As an added bonus, try \d+ to also see the size of each table, or \l+ for database sizes.

2. `\d` table - Describe a Specific Relation

Need the details of a specific table? Use \d followed by the table name:

tsdb=> \d small
                         Table "public.small"
  Column  |           Type           | Collation | Nullable | Default 
----------+--------------------------+-----------+----------+---------
 ts       | timestamp with time zone |           |          | 
 sensorid | integer                  |           |          | 
 value    | double precision         |           |          | 
Indexes:
    "small_sensorid_ts_idx" btree (sensorid, ts DESC)

This gives you columns, data types, constraints, and indexes all in one view.

3. `\e` - Edit Query Buffer

Working on a complex query? Every time you run a query in psql, it goes into your query buffer. The \e command opens your default editor with that buffer loaded so you can edit it properly.

This is a lifesaver for complex queries where one-line edits in the terminal become tedious!

4. `\ef` function - Edit Function

Similar to \e, but this opens your editor with an existing function definition loaded. When you save and exit, it runs the SQL automatically.

Remember to include CREATE OR REPLACE if you're updating an existing function!

5. `\x` - Toggle Expanded Output

This command changes how results are displayed. Instead of columns stretching across your screen, each row becomes a vertical block:

tsdb=> \x
-- Expanded display is on.
tsdb=> select * from power limit 1;
-[ RECORD 1 ]------------------------
ts     | 2024-04-11 03:53:05.76672+00
stream | 1
v01    | 8043.284272117919
v02    | 6896.529731366893
v03    | 460.279921892548
...

This is invaluable when dealing with wide tables or columns containing JSON, geospatial data, or long text.

6. `\timing` - Toggle Command Timing

Enable timing mode to see how long each query takes:

tsdb=> \timing 
-- Timing is on.
tsdb=> select max(v01) from power;
-[ RECORD 1 ]——
—max | 9999.99927364142

Time: 30.635 ms

Just be aware that this includes network round-trip time, which can be significant when connecting to remote databases.

7. `\c` database - Connect to Database

Need to switch databases without disconnecting? Use \c followed by the database name:

tsdb=> \c another_database
You are now connected to database "another_database" as user "tsdbadmin".

8. `\copy` - Perform SQL Copy

PostgreSQL's COPY command is great for bulk loading data, but it runs server-side. The \copy meta-command performs the same function but runs client-side, meaning it can access files on your local machine:

tsdb=> \copy my_table FROM '/path/to/local/file.csv' WITH CSV HEADER;

This is especially useful when working with cloud databases where you can't easily upload files to the server.

9. `\i` file - Read SQL from File

Have a SQL script you want to run? Use \i to execute commands from a file:

tsdb=> \i ./my_script.sql

All commands will run in sequence, and errors will be visible but won't stop execution.

10. `\?` - The PSQL Meta-Command Cheat Sheet

Forgot how a meta-command works? Type \? to see all available commands:

tsdb=> \?

Wrapping Up

While there are many great GUI tools out there (like pgAdmin, DataGrip, or PopSQL), mastering psql meta-commands can make you significantly more productive when working with PostgreSQL.

If you need more quick tips, check our cheat sheet for interacting with your PostgreSQL database.

What are your favorite psql tricks? Share them in the comments below!

Want more PostgreSQL tips? Follow for more database insights and practical guides—save for later!

8 Reasons Postgres for IoT just makes sense

James Blackwood-Sewell — Wed, 19 Feb 2025 11:52:00 +0000

TL;DR: PostgreSQL solves the IoT data puzzle by unifying time-series and relational data in one database. Engineers can now query rich context alongside sensor readings, while TimescaleDB handles massive data volumes. From location tracking to custom data types, PostgreSQL delivers the reliability that IoT demands—all without juggling multiple databases. 👉 Jump to the full article

IoT generates massive streams of sensor data from connected physical systems. Teams often split this between SQL and NoSQL databases, creating needless complexity. PostgreSQL with TimescaleDB offers a simpler path: one database that handles both time-series and relational data at scale.

1. Scaling PostgreSQL for Sensor Data (With Timescale)

IoT backends need to support high data ingest rates, while writes on PostgreSQL can often slow to a crawl as your dataset grows.

🤯 Did you know you can scale PostgreSQL to ingest millions of rows per second, storing billions of rows, even on a single node with a modest amount of RAM (if your database is architected the right way).

How do we know this? Because we did it. 😎 For one of our features, we scaled PostgreSQL to petabytes of data (and counting), with over 800 billion new records per day. We flew past 100 trillion raw records in a single table recently.

2. A Simplified Stack: One IoT Database vs. Two

TimescaleDB lets PostgreSQL handle both your time-series and relational IoT data in a single database.

This eliminates the complexity of maintaining separate NoSQL and SQL databases with messy glue code between them. For backup scenarios, this is crucial - no more risk of time-series data pointing to missing device metadata because your databases were backed up at different times. From edge computing to large-scale deployments, one unified database makes system design dramatically simpler, echoed by CTO of a unicorn startup:

"Aggressively consolidating our tech stack lets us sharpen our engineering team's focus."

3. More Context for Your IoT Data

A single database for time-series data and metadata does more than just simplify your stack: it also lets you add context to your sensor data. Because what is the point of collecting data if you don’t know what you’re measuring?

With TimescaleDB and PostgreSQL, they can keep their metadata normalized and add the necessary context to their time-series data at query time (via a SQL JOIN). For example, via a query like this:

-- Show me the average temperature by machine, for machines of a 
-- given type on a particular line, by 5 minute intervals
SELECT time_bucket('5 minutes', measurements.time) as five_min, 
    machine_id, avg(temperature)
FROM measurements
  JOIN machines ON measurements.machine_id = machines.id
  JOIN lines ON machines.line_id = lines.id 
WHERE machines.type = 'extruder' AND lines.name = 'production'
  AND measurements.time > now() - interval '36 hours'
GROUP BY five_min, machine_id
ORDER BY five_min, machine_id;

4. Power and Ease of Use

Let’s take a step back and keep in mind the value of an IoT project: to collect, analyze, and act on data to improve utility/efficiencies, reduce downtime/waste, and provide better products and services. In other words, you need more than a data store: you also need an easy way to wring actionable insights out of your data.

This is where SQL comes in. While it’s been quite fashionable in the past several years to denounce SQL and praise NoSQL, the truth is that SQL is quite powerful and is starting to make a comeback (which is one reason why “NoSQL” is now getting “backronymed” to “Not only SQL”).

-- Plot the change in temperature for ceiling sensors 
-- on linear and logarithmic scales, by 10 second intervals
SELECT ten_second,
       temperature / lead(temperature) OVER data AS temperature_rise_linear,
       log(temperature / lead(temperature) OVER data) AS temperature_rise_log
FROM (
  SELECT time_bucket('10 seconds', time) as ten_second,
         last(temperature, time) as temperature
  FROM measurements JOIN sensors ON measurements.sensor_id = sensors.id
  WHERE sensors.type = 'ceiling' AND measurements.time >= '2017-06-01'
  GROUP BY 1
  ORDER BY 1
) sub window data AS (ORDER BY ten_second asc);

💡 SQL has another advantage: people across your organization already know it. You won’t need to train your engineers to learn a new specialized query language (or hire new ones), and non-technical users won’t need to rely as heavily on engineering (and engineering release cycles) to answer questions about the data.

5. Broad Data Type Support (Including JSON)

JSON in a relational database? Eat your heart out, MongoDB.

When you start building your IoT product, you may not know what data you will care about, nor have a specific data schema in mind. Later on, you may need very specific data structures (e.g., arrays).

Fortunately, PostgreSQL supports a broad spectrum of data types. It allows for semi-structured data (via JSON / JSONB support) but also a variety of other data types, including many numeric types, geometric types, arrays, range types, and date/time types.

-- Storing and querying JSON data in PostgreSQL
SELECT time, sensor_id, type, readings
FROM measurements
ORDER BY time DESC, sensor_id LIMIT 50;

      time     | sensor_id | type |             readings             
---------------+-----------+------+----------------------------------
 1499789565000 |       330 |    1 | {"occupancy": 0, "lights": 1}
 1499789565000 |       440 |    2 | {"temperature": 74.0, "humidity": 0.81}
 1499789565000 |       441 |    2 | {"temperature": 72.0, "humidity": 0.78}
 1499789560000 |       330 |    1 | {"occupancy": 1, "lights": 1}
 1499789560000 |       440 |    2 | {"temperature": 73.9, "humidity": 0.81}
 1499789560000 |       441 |    2 | {"temperature": 72.1, "humidity": 0.79}
 1499789555000 |       330 |    1 | {"occupancy": 1, "lights": 1}
 1499789555000 |       440 |    2 | {"temperature": 73.9, "humidity": 0.80}
 1499789555000 |       441 |    2 | {"temperature": 72.1, "humidity": 0.78}

💡 Tips on picking data types in Postgres

6. Geospatial Support for Your IoT DataPostgreSQL with You can run many Postgres extensions in TimescaleDB.

One Powerful extension is PostGIS, and it tackles a core IoT challenge: tracking things through space and time. Fleet vehicles, delivery routes, and mobile assets all need both location and temporal data. Instead of juggling separate systems, these extensions turn PostgreSQL into a spatiotemporal database that handles it all - where things are, where they've been, and when.

7. Plenty of Integration Opportunities

Time-series databases need a rich ecosystem of connectors to tools like Kafka, Spark, and Tableau. While newer databases lack this support, PostgreSQL's 30-year head start means TimescaleDB inherits a vast network of ready-to-use integrations. If PostgreSQL connects to it, TimescaleDB does too.

8. Proven Reliability

IoT databases need to be dependable from day one. A factory line can't pause while you debug database issues at 3 AM. Period.

PostgreSQL has spent 30 years running mission-critical systems, and TimescaleDB inherits this stability along with built-in tools for replication and failover.

Try Timescale Today (Free)

There is a lot of noise in the IoT world right now, and deciding what database to use for your IoT project can be hard.

Sometimes, the best option is the boring option: the database that just works.

That’s PostgreSQL, which now (thanks to Timescale) finally scales to handle IoT workloads and optimizes SQL for time-series data.

If you’re building a new IoT project or currently wrestling with a complex IoT stack, choose PostgreSQL—but faster. Create a free Timescale account.

If you need help with anything, feel free to join our Community on Slack.

Calculating Dwell Time in Postgres+TimescaleDB

James Blackwood-Sewell — Thu, 30 Jan 2025 03:52:49 +0000

When you're working with IOT or OT data you often want to look at a series of state transitions for sensors or devices and work out the amount of time spent in each state. Each device can only be in a single state at a time, so when a new state is started, the old one is closed off.

This is often used for billing or metering (think power meters, devices connected to a network, or even a complex state machine) and can be referred to as dwell time or connection time. In this case, the desired output is ( device_id, state, start_time, dwell_time).

Let's consider the following table which tracks states for sensors.

CREATE TABLE testdata
(
    sid INT NOT NULL,
    state VARCHAR(1) NOT NULL,
    ts TIMESTAMPTZ NOT NULL
);

We can populate the table with some test data:

-- Create some test data
INSERT INTO testdata (sid, state, ts) VALUES 
  (1, 'a', '2019-Nov-01 12:01:00'),
  (1, 'a', '2019-Nov-01 12:02:00'),
  (1, 'a', '2019-Nov-01 12:05:00'),
  (1, 'b', '2019-Nov-01 12:10:00'),
  (1, 'b', '2019-Nov-01 12:20:00'),
  (2, 'a', '2019-Nov-01 12:20:00'),
  (2, 'a', '2019-Nov-01 12:22:00'),
  (1, 'a', '2019-Nov-01 13:00:00'),
  (1, 'a', '2019-Nov-01 13:02:00'),
  (1, 'a', '2019-Nov-01 13:06:00'),
  (1, 'a', '2019-Nov-01 13:12:00'),
  (1, 'a', '2019-Nov-01 14:00:00'),
  (1, 'a', '2019-Nov-01 14:12:00'),
  (1, 'a', '2019-Nov-01 14:14:00'),
  (1, 'a', '2019-Nov-01 14:30:00'),
  (1, 'a', '2019-Nov-01 14:35:00');

Now, you can solve this with plain SQL (I'll leave that as a exercise for the reader😉), but if you're using PostgreSQL + TimescaleDB then you can use one of the hyperfunctions which come with the timescaledb-toolkit extension.

The final solution looks like this:

WITH states AS (
  SELECT 
    (state_timeline(state_agg(ts, state))).*,
    sid 
  FROM testdata 
  GROUP BY sid
)
SELECT 
  sid, 
  state, 
  start_time, 
  end_time - start_time dwell_time
FROM states
ORDER BY sid, start_time;

Which gives the following results:

sid     state   start_time              dwell_time
1       a       2019-11-01 12:01:00+00  00:09:00
1       b       2019-11-01 12:10:00+00  00:15:00
1       a       2019-11-01 12:25:00+00  02:10:00
2       a       2019-11-01 12:20:00+00  00:02:00

The magic lies in the state_agg aggregate, which tracks transitions between states within our grouping set (in this case the state column), and also in the state_timeline function, which takes the output of state_agg and creates a timeline of all state changes with the time they were entered and exited.

With that information in hand, it’s easy to work out the dwell_time using end_time - start_time. If you're impressed by this check out the other things hyperfunctions can do!

TimescaleDB in 2024: Making Postgres Faster

James Blackwood-Sewell — Wed, 15 Jan 2025 09:56:20 +0000

If I had to summarize 2024 for Timescale, I’d call it the year of Postgres for AI. From game-changing open-source launches like pgvectorscale (a performance booster for large production vector workloads with PostgreSQL + pgvector) to pgai (which integrates Postgres with LLMs for AI app development), we pushed the boundaries of what developers can achieve with Postgres. But AI wasn’t the only story. TimescaleDB’s time-series capabilities also evolved dramatically during 2024, making Postgres even faster for real-time analytics.

For those of you who don’t know, TimescaleDB is a PostgreSQL extension for high-performance real-time analytics on time series and event data. It is available as an open-source extension or fully managed on Timescale Cloud.

I love TimescaleDB (I was a user well before I worked at Timescale ❤️), so in the spirit of sharing that love, here are my top five TimescaleDB features from 2024 and why you should care (or at least why I do 😅).

1. Chunk-Skipping Indexes on Compressed Hypertables

This feature introduces a way to add min/max indexes on compressed hypertable chunks. The planner uses these to perform chunk exclusion on columns other than the default time-based one.

This can be useful in two main cases:

You’ve got a column that is correlated to the time-based partition in some way, and you’d like to use it efficiently in queries. The classic example is an order ID. As time increases, so does the order ID, so the min/max value will be tightly bound per chunk. Adding chunk skipping would allow you to use order ID in your WHERE clause and have all chunks but the matching one excluded.
You have a column in which you want to search for outliers that don’t occur often. Say you have a temperature column, and you want to find all rows that exceed a temperature threshold. You could enable chunk skipping and only have to scan the chunks that exceed the outlier value in your WHERE clause.

Let’s take a look at how it works:

-- Create the orders hypertable and enable compression
CREATE TABLE orders (ts timestamptz, orderid serial, orderdata JSONB);
SELECT create_hypertable ('orders', by_range ('ts'));
ALTER TABLE orders SET (timescaledb.compress);

-- Insert some data, this will make 52 chunks (one per week)
INSERT INTO orders 
  SELECT generate_series(now() - INTERVAL '1 year', now(), '1 minute');

-- Enable chunk skipping for the orders column then compress all the data
SET timescaledb.enable_chunk_skipping = on;
SELECT enable_chunk_skipping('orders', 'orderid');
SELECT compress_chunk(c) FROM show_chunks('orders') c;

-- Query by orderid alone, this would normally hit all 52 chunks :)
EXPLAIN ANALYZE SELECT * FROM orders WHERE orderid=1;

Custom Scan (DecompressChunk) on _hyper_95_10464_chunk  (cost=0.03..15.60 rows=560000 width=44) (actual time=0.032..0.033 rows=1 loops=1)
  Vectorized Filter: (orderid = 1)
  Rows Removed by Filter: 5605
  ->  Seq Scan on compress_hyper_96_10517_chunk  (cost=0.00..15.60 rows=560 width=116) (actual time=0.003..0.004 rows=6 loops=1)
Planning Time: 1.436 ms
Execution Time: 0.064 ms

-- You can see the query above only hit a single chunk!

As with most new features in TimescaleDB, chunk skipping is disabled by default at launch, so remember to set timescaledb.enable_chunk_skipping to true (either globally or per session) to let the planner know you’re using it. I’d recommend only enabling the feature for queries that you think will use it.

Performance gains are pretty data-dependent, but we have seen up to 7x when testing normal situations.

2. Faster Real-Time Continuous Aggregates and Continuous Aggregate Policies

Continuous aggregates (CAggs) are one of my favorite features in TimescaleDB. We already give you lightning-fast analytical queries out of the box, but CAggs take it a step further by taking a SQL query and automatically materializing it in the background. I’m cheating here by squashing two CAgg features into the same item, but I’m sure you can live with that.

Faster real-time continuous aggregates

Real-time continuous aggregates allow the computation of results for newly inserted data on the fly (before the background job has materialized). That data is then immediately contained in queries to the continuous aggregate alongside the already materialized data to give a full, up-to-date snapshot.

In Timescale 2.14.0, we made a change that increased the performance of real-time continuous aggregates by up to 50,000x (that’s not a typo). For those who want the technical details we constifyied our internal cagg_watermark function call, which unlocked plan-time chunk exclusion (as opposed to run-time) and made everything faster (read the link above for the full debrief).

If you haven't tried real-time (or even normal) CAggs, I’d encourage you to give them a go. Check out our newly rewritten GitHub Readme for a quick how-to.

Faster continuous aggregate policies

In the past, continuous aggregates could sometimes be a little I/O hungry when updates flowed in for older chunks. Before version 2.17.0, we would recalculate and rewrite each chunk with changes, but now we use an efficient merge policy to only write the changes to storage. Overall, continuous aggregate policies are lighter, use fewer system resources in the background, and complete faster.

For anyone building dashboards or running real-time analytics with late-arriving data, this is a game-changer.

3. Hypercore Introduction and Enhancements

We’ve had a hybrid row-columnar storage engine for a long time (it powers our compression), but this year we gave it an official name: hypercore. That wasn’t enough for the team, so during the year, they also knocked out some great hypercore improvements. My favorites were:

Optimized defaults : Timescale now recommends the best segment_by and order_by settings based on table configuration and statistics.
Smarter decompression : The query planner can now evaluate more WHERE conditions before decompressing data, reducing unnecessary I/O.
Min/max sparse indexes : Columns with btree indexes can now benefit from min/max sparse indexes after compression. This is similar to chunk skipping but operates on individual batches within chunks.
Vectorized filters : Queries with text equality or LIKE filters are now vectorized for faster execution.
Compressed tuple filtering : Up to 500x faster updates and deletes by avoiding the need to decompress and materialize irrelevant batches.
Faster upserts : Improved upsert performance by more than 300x on compressed data by forcing B-tree index scans.

We also continued our work on vectorization for real-time analytics queries, but that deserves its own mention.

4. SIMD Vectorization for Blazing Fast Analytical Queries

Real-time analytics over columnar (compressed) data got a massive performance boost this year thanks to more work on SIMD (Single Instruction, Multiple Data) vectorization. This lets us take advantage of modern CPUs when processing vectors (or arrays) of values, which hypercore can conveniently provide for each column. If you’re running queries that group by segment_by columns and use aggregate functions like sum, count, avg, min, or max, you’ll notice major speedups.

This is an area we are continuing to invest heavily in, you’ll hear a lot more about this in the coming months. If you’re after more information right now , then have a read of my post on the start of our vectorization journey from last year.

5. Foreign Keys on Hypertables

For years, developers have asked for support for foreign keys on hypertables. I can even remember asking for this shortly after TimescaleDB launched!

This year, we delivered: You can now add foreign keys from regular tables to hypertables, with full support for cascading actions. While it's not going to change the world, it's one of those little developer experience bumps that make your life a little easier.

Wrap-Up

What a year for TimescaleDB! 🚀 If you’re working with time-series data or want to keep enjoying Postgres familiarity but with a significant performance boost, give TimescaleDB a try. With these new features, you can optimize your queries, reduce your storage footprint, and make real-time analytics more efficient. Set it up, test it out, and see how it fits into your projects.

Got questions? Join the Timescale Slack community, where like-minded devs and Timescale team members share their tips and will help you get started.