26 ms·

I totally agree with all the points here. Maybe Postgres needs pluggable storage engines [0]. I read Postgres 10 might have this, but looks like it will miss

by crudbug 10y ago

I totally agree with all the points here. Maybe Postgres needs pluggable storage engines [0]. I read Postgres 10 might have this, but looks like it will miss the deadline. [0] https://www.pgcon.org/2016/schedule/events/920.en.html https://www.pgcon.org/2016/schedule/events/920.en.html

jeltz 10y ago
Pluggable storage will definitely miss PostgreSQL 10 since the feature freeze is later this week.
- anarazel 10y ago
  And there's not even a credible proof-of-concept patch.
jnordwick 10y ago
Good time series performance is more than just using column-based storage. You also need a query language to take advantage of this and the ordering guarantees it gives you. SQL while it has tried to reinvent itself, is a very poor language for querying TS databases.
- akulkarni 10y ago
  From personal experience, not sure I'd agree with that statement. SQL may be limiting for some time-series use cases, but for others it's quite rich and powerful. I won't pretend that SQL solves everyone's time-series problems, but we've found that it goes pretty far. That said, we may have to get a little creative to support some specific use cases (e.g., bitemporal modeling). Still TBD. Also, I agree that SQL isn't for everyone (the popularity of PromQL is evidence to that). But a lot of people have been using SQL for a while (personally, since 1999), and there is a rich ecosystem (clients, tools, visualization tools, etc) built around it.
  - jnordwick 10y ago
    It most definitely is.. LEAD and LAG are about all you get, and they are painfully slow. SQL was made to be order agnostic, and attempts to make it most order-aware don't quite work. A good time series database is build on table order and lets you exploit it. SQL is abysmal for any time series work. And temporal and bitemporal databases (despite the name) are orthogonal to the aggregation and windowing issues that make time series difficult in SQL or a row-oriented database. The are just a couple of timestamps and where clauses to support point-in-time queries. Maybe this is why so many time series databases fail. People making them often don't seem to fundamentally understand the issues, Very few, such as Kx and KDB, seem to understand them.

by crudbug 10y ago

jeltz 10y ago

Pluggable storage will definitely miss PostgreSQL 10 since the feature freeze is later this week.

anarazel 10y ago
And there's not even a credible proof-of-concept patch.

jnordwick 10y ago

Good time series performance is more than just using column-based storage. You also need a query language to take advantage of this and the ordering guarantees it gives you. SQL while it has tried to reinvent itself, is a very poor language for querying TS databases.

akulkarni 10y ago
From personal experience, not sure I'd agree with that statement. SQL may be limiting for some time-series use cases, but for others it's quite rich and powerful. I won't pretend that SQL solves everyone's time-series problems, but we've found that it goes pretty far. That said, we may have to get a little creative to support some specific use cases (e.g., bitemporal modeling). Still TBD. Also, I agree that SQL isn't for everyone (the popularity of PromQL is evidence to that). But a lot of people have been using SQL for a while (personally, since 1999), and there is a rich ecosystem (clients, tools, visualization tools, etc) built around it.
- jnordwick 10y ago
  It most definitely is.. LEAD and LAG are about all you get, and they are painfully slow. SQL was made to be order agnostic, and attempts to make it most order-aware don't quite work. A good time series database is build on table order and lets you exploit it. SQL is abysmal for any time series work. And temporal and bitemporal databases (despite the name) are orthogonal to the aggregation and windowing issues that make time series difficult in SQL or a row-oriented database. The are just a couple of timestamps and where clauses to support point-in-time queries. Maybe this is why so many time series databases fail. People making them often don't seem to fundamentally understand the issues, Very few, such as Kx and KDB, seem to understand them.