hypertable | A flexible database focused on performance | Performance Testing 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:

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

Foreign key constraints referencing a hypertable are not supported.

I've tried unique constraints, but it didn't worked out also.

So, the only way I fount to solve this problem is delete foregein key in SpnValues

If there is an index on the column, max can use the index and will become O(1):

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.

Just so this question is answered in case anyone else ends up here:

The issue was a failure to call commit as a method, as @snakecharmerb pointed out. Gord Thompson also provided an alternate method using 'begin' which automatically commits rather than connection which is a 'commit as you go' style transaction.

What is the best way to handle this situation?

I'd consider adopting smaller chunks, so compress and decompress will become faster operations.

Can I bulk load data to compressed hypertables safely?

Timescale is in progress with the implementation of this ability.

Can the compression condition consider only filtered exchange_id columns and I could compress exchange_id I know to be safe by hand?

Maybe try to add_partition?

As I expected, it was a combination of generate_series and window functions. But I didn't expect to have to create my own locf function, I thought LEAD/LAG had options to remember the last known / non-null values.

The following code takes a few known records, and unions them against a generated series of timestamps.

I needed to use DISTINCT ON to purge the generated records that already had known equivalents.

Then I can finally use LEAD for the "next date" and locf_any for the value carried forward.