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

61–70 of 84 posts

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

#61

Post by kbolino »

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?

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

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

#62

Post by rywalker »

matthewmueller wrote: post #35

Would love to use this with RDS!

Tembo CEO here - we are targeting feature parity for Tembo Cloud w/ RDS as soon as possible, would love to have you give Tembo a try sometime, give us feedback :)

Tembo Cloud is standard SaaS offering, and our new Tembo Self Hosted (https://tembo.io/docs/product/software/tembo-self-hosted/ove...) allows you to run the same software that powers our SaaS, but in your own K8s cluster.

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

#63

Post by samaysharma »

wdb wrote: post #48

Would this be a good extension when you want to load balancer log entries (status, response body, headers etc)? I think a columnar database store would be more efficient than normal row-based databases? load balancer log entries could be considered something similar to analytics events.

Yes. Columnar is integrated with pg_timeseries already.

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

#64

Post by ants_a »

kbolino wrote: post #61
MuffinFlavored wrote: post #20

Earlier quoted context omitted.

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?

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 IN…

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

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

#65

Post by klaussilveira »

skibbityboop wrote: post #21
MuffinFlavored wrote: post #15

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"?

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…

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.

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

#66

Post by riedel »

osigurdson wrote: post #24
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…

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.

> 500 million is very little however. A regular table with a covering index would probably be fine for many use.

Totally agree. The problem here was it was some awkwardly designed geospatial measurement dabase (OGC sensor things) and we could not do much about the queries etc forms one ORM logic and alot of postgis stuff. It was great to have something as a drop-in replacement speeding up all the time series queries without much thinking. Actually what was still nagging us was all the locking going on due to the transactional semantics. Time series databases are probably much better at handling queries under constant ingress. We are total amateurs wrt database optimisation, but my guess is that many of those offerings are rather targeting the average use case.

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

#67

Post by pgwhalen »

walr000s wrote: post #57
pgwhalen wrote: post #56

Earlier quoted context omitted.

Why would the number of data points correlate to budget? Perhaps there’s a chance if the business scales with paying users, but that’s unlikely to be true in finance.

At that number of observations, I would assume depth of market data so probably HFT use case. HFT is notoriously expensive to try to compete in

I'm no market data specialist, but that seems like order of magnitude just top-of-book for US equities? The equity options market, at least, is orders of magnitude larger.

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

#68

Post by vegabook »

walr000s wrote: post #57
pgwhalen wrote: post #56

Earlier quoted context omitted.

Why would the number of data points correlate to budget? Perhaps there’s a chance if the business scales with paying users, but that’s unlikely to be true in finance.

At that number of observations, I would assume depth of market data so probably HFT use case. HFT is notoriously expensive to try to compete in

actually, any number of crypto APIs will give you tens of millions of points per day, for free (though that was not our use case).

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

#69

Post by Too »

osigurdson wrote: post #24
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…

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.

That number in itself doesn’t say anything.

What really causes a database to sweat is high cardinality.

When talking about time series, also which fields are indexed and if are you inserting out of order.

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

#70

Post by Too »

skibbityboop wrote: post #21
MuffinFlavored wrote: post #15

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"?

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…

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.

Post reply on HN