Query for list of tables modified in the last X hours

Question

I would like to get in a query the list of tables that had modifications (or which have entries appended) in the last X hours, without iteratively querying each table (because there are 1000s of tables). Is there a way to achieve that? Tables are with TIMESTAMP, partitioned by day.

SELECT * FROM table_partitions(‘tableName’) works great, however it requires a table parameter, which again is not very efficient when querying for thousands of tables over the network.

Answer 1

There is a wal_transactions function, which also takes a table name as a parameter. This could be used to keep track of recently modified tables.

There isn’t a global CDC yet. This table represents a ring buffer of recent transactions. It is configurable up to a maximum number of rows, so this would need to be set appropriately based on how frequent the writes are coming in.

I have a write-up from a few months ago for a similar use case.

There are a few paths off the top of my head:

• Poll per-table and balance the load against ingestion/other queries
• Maintain your own table which you insert this information into i.e your own event log. Since QuestDB support high concurrent ingestion speeds, this should work well, but need to be application managed.
• Use some sort of file-watching mechanism to monitor the QuestDB directory for modifications. QuestDB often creates transient files, so this could get complicated with many tables and partitions.