Search code examples
postgresqltime-seriestimescaledb

Have an ordinary table on a PostgreSQL TimescaleDB (timeseries) database

For a project I need two types of tables.

  1. hypertable (which is a special type of table in PostgreSQL (in PostgreSQL TimescaleDB)) for some timeseries records
  2. my ordinary tables which are not timeseries

Can I create a PostgreSQL TimescaleDB and store my ordinary tables on it? Are all the tables a hypertable (time series) on a PostgreSQL TimescaleDB? If no, does it have some overhead if I store my ordinary tables in PostgreSQL TimescaleDB?

If I can, does it have any benefit if I store my ordinary table on a separate ordinary PostgreSQL database?

Solution

  • Can I create a PostgreSQL TimescaleDB and store my ordinary tables on it?

    Absolutely... TimescaleDB is delivered as an extension to PostgreSQL and one of the biggest benefits is that you can use regular PostgreSQL tables alongside the specialist time-series tables. That includes using regular tables in SQL queries with hypertables. Standard SQL works, plus there are some additional functions that Timescale created using PostgreSQL's extensibility features.

    Are all the tables a hypertable (time series) on a PostgreSQL TimescaleDB?

    No, you have to explicitly create a table as a hypertable for it to implement TimescaleDB features. It would be worth checking out the how-to guides in the Timescale docs for full (and up to date) details.

    If no, does it have some overhead if I store my ordinary tables in PostgreSQL TimescaleDB?

    I don't think there's a storage overhead. You might see some performance gains e.g. for data ingest and query performance. This article may help clarify that https://docs.timescale.com/timescaledb/latest/overview/how-does-it-compare/timescaledb-vs-postgres/

    Overall you can think of TimescaleDB as providing additional functionality to 'vanilla' PostgreSQL and so unless there's a reason around application design to separate non-time-series data to a separate database then you aren't obliged to do that.

    One other point, shared by a very experienced member of our Slack community [thank you Chris]:

    To have time-series data and “normal” data (normalized) in one or separate databases for us came down to something like “can we asynchronously replicate the time-series information”? In our case we use two different pg systems, one replicating asynchronously (for TimescaleDB) and one with synchronous replication (for all other data).

    Transparency: I work for Timescale