On: pg_timeseries: Open-source time-series extension f...

[MuffinFlavored]

    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?

[kbolino → MuffinFlavored]

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

[ants_a → kbolino]

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

[gonzo41 → MuffinFlavored]

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

[darkstar_16 → gonzo41]

And provides helper functions to deal with regular time series tasks

[jmaker → MuffinFlavored]

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