timescaledb | source time-series SQL database | Time Series Database library

I'm not sure I understood what do you mean by "all affected tables", but to get all hypertables in a single query, you can cast the hypertable name with ::regclass. Example from some playground database with a few random hypertables:

You can get this information about retention policies through the jobs view:

I would use generate_series function.

generate_series(start, stop, step interval)

The third parameter you can write your expect interval. In your case that might be 1 hours

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

Problem

Author of the question made some changes in his Job. After the update he got a "field is immutable" error.

This is caused by the fact that .spec.template field in Job is immutable and can not be updated.

Solution

Delete the old Job and create a new one with necessary changes.

Typically the "time" column used to create the hypertable is not used as a segment by column while setting up compression. segment_by column is a column that has some commonality across the data set. e.g. If we have a table with device readings (device_id, event_timestamp, event_id, reading) the segment by column could be device_id (say you have a few 1000 devices and the device_readings table has data in the order of millions/billions). Note that the data in the segment by column is never stored in compressed form. Only the non segment by columns get compressed.

Good question!

You can do this by calculating a normal continuous aggregate and then doing a window function over it. So, calculate a sum() for each hour and then do sum() as a window function would work.

When you get into more complex aggregates like average or standard deviation or percentile approximation or the like, I'd recommend switching over to some of the two-step aggregates we introduced in the TimescaleDB Toolkit. Specifically, I'd look into the statistical aggregates we recently introduced. They can also do this cumulative sum type thing. (They will only work with DOUBLE PRECISION or things that can be cast to that-ie FLOAT, I'd highly recommend you don't use NUMERIC and instead switch to doubles or floats, doesn't seem like you really need infinite precision calculations here).

You can take a look with some queries I wrote up in this presentation but it might look something like:

Yes! it's possible!

You can try it by using regular SQL. Here is some example:

One of the key advantages of TimescaleDB in comparison to other timeseries products is that timeseries data and relational data can be stored in the same database and then queried and joined together. So, "by design", it is expected that the database with several normal tables and hypertable will perform well. Usual PostgreSQL consideration about tables and other database objects, e.g., how shared memory is going to be affected, applies here.