Hacker News Reader

Comment by MuffinFlavored | Hacker News Reader

Dumb question: why can't I just insert a bunch of rows with a timestamp column and indices? Where does that fall short? At a certain # of rows or something?

What does this let me do that can't be achieved with "regular PostgreSQL without the extension"?

View on HN

I'm with you, I need to read up more on where timeseries could benefit, at work we have a PostgreSQL instance with around 27 billion rows in a single partitioned table, partitioned by week. Goes back to January of 2017 and just contains tons of data coming in from sensors. It's not "fast", but also not ridiculously slow to say e.g. "Give me everything for sensor 29380 in March of 2019".

I guess depends on your needs but I do think I need to investigate timeseries more to see if it'd help us.

Same boat here, but with DSP/SSP bidding statistics. Generating around 1 billion rows a day and still going strong. Single table, partitioned by week. BRIN index on timestamp, normal index on one column.

Postgres is just a beast.

Now give me a plot with the average of all sensors of model=A in region=B, grouped by customer, for the past 3 months, downsampled to 500 points. Assuming 1 sensor reading per minute.

I have no doubt sql can do it without too much trouble, but for a time series this is really an instant operation, even on a small server.

A time series will first find the relevant series and then simply for-loop through all the data. It takes just a handful of milliseconds.

Sql will need to join with other tables, traverse index, load wider columns. And you better have set the correct index first, in your case you also spent extra effort on partitioning tables. Likely you are also using a beefy server.

Adding to your comment, from my perspective (exploration geophysics)

> and just contains tons of data coming in from sensors.

it's also desired to, on the fly, deal with missing sensor data, clearly bad sensor data, identify and smooth spikes in data (weird glitch or actual transient spike of interest), apply a variety of running filters; centred average is basic, parameterised Savitzky–Golay filters provide "beefed up" better than running average handling .. and there are more.

It's not just better access to sequential data that makes a dedicated time series engine desirable, it's the suite of time (and geo spatial) series operations that close the deal.

there are several good articles explaining this, especially on Timescable blog, but in short, without time partitioning and just index, at some given point the performance for reads and writes degrades exponencially.

Time based partitioning.

    CREATE TABLE logs (
        id SERIAL PRIMARY KEY,
        log_time TIMESTAMP NOT NULL,
        message TEXT
    ) PARTITION BY RANGE (log_time);

Why won't this work on stock PostgreSQL?

read the docs, it's not saying that won't work. This extension along with timescale just makes some things more ergonomic.

And provides helper functions to deal with regular time series tasks

That won’t work already because your timestamp isn’t part of your primary key.

I think what's meant here is windowing (partitioning the query) not partitioning the table per se. Though even with this strategy, you must manually create new partitions all the time.

This also isn't typical time-series data, which generally stores numbers. Supposing you had a column "value INTEGER" as well, how do you do something like the following (pseudo-SQL)?

    SELECT AVG(value) AS avg_value FROM logs GROUP BY INTERVAL '5m'

Which should output rows like the following, even if the data were reported much more frequently than every 5 minutes:

    log_time             | avg_value
    2024-05-20T00:00:00Z | 10.3
    2024-05-20T00:05:00Z | 7.8
    2024-05-20T00:10:00Z | 16.1

    SELECT date_bin('5 minutes', log_time, '2000-01-01') log_time,
           AVG(value) avg_value
    FROM logs GROUP BY 1

It will work just fine.

Reply on news.ycombinator.com