Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

pg_timeseries: Open-source time-series extension for PostgreSQL

tembo.io

Post reply on HN
Print view View original

31–40 of 84 posts

Re: pg_timeseries: Open-source time-series extension for PostgreSQL

#31

Post by rapsey »

riedel wrote: post #6

>You may already be asking: “why not just power the stack using TimescaleDB?” The Timescale License would restrict our use of features such as compression, incremental materialized views, and bottomless storage. With these missing, we felt that what remained would not provide an adequate basis for our customers’ time-series needs. Therefore, we decided to build our own PostgreSQL-licensed extension. Have been using t…

Databases are a tough business. You're just waiting for open source to eat your lunch.

Re: pg_timeseries: Open-source time-series extension for PostgreSQL

#32

Post by osigurdson »

vjerancrnjak wrote: post #28
osigurdson wrote: post #24

Earlier quoted context omitted.

500 million is very little however. A regular table with a covering index would probably be fine for many use cases with this number of points.

I think you’re not talking about the same thing. There’s an expression related to time series data —- “high churn” and another “active time series”. 500 million active time series is extremely huge. It does not have anything to do with number of data points. Good time series databases can scale to 1M-10M writes per second without a hiccup.

I suppose it means by what is meant by an "observation". Is that an entire time series for a single property or a single point? Nevertheless, the number of points absolutely matters.

A regular Postgres database can give you 50-100K inserts per second and can scale to at least 1B rows with 100K+ individual series without much difficultly. If you know you will need less (or much less) than this, my suggestion is to use a regular table with a covering index. If you need more, use ClickHouse.

Re: pg_timeseries: Open-source time-series extension for PostgreSQL

#33

Post by applied_heat »

paulryanrogers wrote: post #16
nitinreddy88 wrote: post #14

Most of the time-series queries (almost all of them) are aggregated queries. Why not leverage or build top-notch Columnarstore for the same. Everything seems to be there and why there's not first class product like ClickHouse on PG.

Citus, Persona, TimescaleDB?

Victoria metrics as well, they say based on similar structures used in clickhouse

Re: pg_timeseries: Open-source time-series extension for PostgreSQL

#34

Post by nikita »

nitinreddy88 wrote: post #18
paulryanrogers wrote: post #16

Earlier quoted context omitted.

Citus, Persona, TimescaleDB?

Looking at the comparison with Click Benchmark, they are almost pathetic in terms of performance. They cant even handle sub-second aggregation queries for 10M records. Compared that too even duckdb reading from parquet files.

Postgres is missing a proper columnstore implementation. It's a big gap and it's not easy to build.

One solution could be integrating duckdb in a similar way as pgvector. You need to map duckdb storage to Postgres storage and reuse duckdb query processor. I believe it's the fastest way to get Postgres to have competitive columnstores.

Re: pg_timeseries: Open-source time-series extension for PostgreSQL

#36

Post by jmaker »

MuffinFlavored wrote: post #20
gonzo41 wrote: post #17

Earlier quoted context omitted.

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?

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

Re: pg_timeseries: Open-source time-series extension for PostgreSQL

#39

Post by tarasglek »

nikita wrote: post #34
nitinreddy88 wrote: post #18

Earlier quoted context omitted.

Looking at the comparison with Click Benchmark, they are almost pathetic in terms of performance. They cant even handle sub-second aggregation queries for 10M records. Compared that too even duckdb reading from parquet files.

Postgres is missing a proper columnstore implementation. It's a big gap and it's not easy to build. One solution could be integrating duckdb in a similar way as pgvector. You need to map duckdb storage to Postgres storage and reuse duckdb query processor. I believe it's the fastest way to get Postgres to have competitive columnstores.

This sounds interesting. I don't see duck db as a supported extension or mentioned anywhere in your code yet ;)

Is this foreshadowing?

Re: pg_timeseries: Open-source time-series extension for PostgreSQL

#40

Post by anentropic »

nikita wrote: post #34
nitinreddy88 wrote: post #18

Earlier quoted context omitted.

Looking at the comparison with Click Benchmark, they are almost pathetic in terms of performance. They cant even handle sub-second aggregation queries for 10M records. Compared that too even duckdb reading from parquet files.

Postgres is missing a proper columnstore implementation. It's a big gap and it's not easy to build. One solution could be integrating duckdb in a similar way as pgvector. You need to map duckdb storage to Postgres storage and reuse duckdb query processor. I believe it's the fastest way to get Postgres to have competitive columnstores.

Hydra?
Post reply on HN