20

MongodDB 5.0 comes with support for time series https://docs.mongodb.com/manual/core/timeseries-collections/

I wonder, what is status with PostgreSQL support for time series?

I could quickly find TimescaleDB https://github.com/timescale/timescaledb,
that is actually open source extension for PostgreSQL.

and detailed PostreSQL usage example from AWS https://aws.amazon.com/blogs/database/designing-high-performance-time-series-data-tables-on-amazon-rds-for-postgresql/

Please comment on this options or give other. Maybe other SQL databases extended to natively support time series.

1 Answer 1

28

Postgres has very good storage for time series data - arrays. I remember, I used it 20 years ago for this purpose. You can transform these data to table with unnest function. The arrays use transparent compression.

Timescaledb use it internally with massive partitioning and with some extending optimizer and planner - so using an arrays is transparent for users. TimescaleDB is very good product, smart solution, and extending Postgres's possibilities very well. Postgres has only generic compressions, but Timescale supports special compression's techniques designed for time series data - that is much more effective. It is really good product.

The work with time series data is usually much simpler than work with general data. Time series data (and queries) has clean characteristic and these data are append only. It's not too hard to write time series database. There are lot of products from of this kind. The benefit of timescale is the fact, that it is extension of Postgres. So if you know Postgres, you know timescaledb. And against other time series databases, it is SQL based - so if you know SQL, you don't need to learn new special language.

Attention - lot of SQL databases supports temporal data, but it is different than time series data.

Sign up to request clarification or add additional context in comments.

4 Comments

For arrays, do you mean exactly these postgresql.org/docs/current/arrays.html ? I have been using PostgreSQL via ORM, so not that familiar with special features.
@PaulVerest - yes, this is very effective class of types - you can store vector or matrix, or vector of composite types
can you please provide links or hints how to create time series with arrays?
@dermoritz - no, you can store timeseries data as an array value - per hour, day, week (depends on data). You can use a function unnest that can transform array to rows. That is all - maybe related part of doc postgresql.org/docs/current/arrays.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.