Query for list of tables modified in the last X hours

Community
1

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 internet.

2

Hi @dan40,

We have wal_transactions, which also takes a table name as a parameter: Tracking data changes (CDC) in QuestDB | QuestDB

There isn’t a global CDC yet. This table represents a ringbuffer 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.

We could potentially modify this to take a list of table names, or return globally for all tables. However, this transaction data is stored on a per-table basis, and this is a significant amount of data if you have thousands of tables. For your use case, you would probably prefer a global transaction log.

So there are few paths off the top of my head:

  1. Poll per-table and balance the load against ingestion/other queries
  2. Maintain your own table which you insert this information into i.e your own event log. Since we support high concurrent ingestion speeds, this should work well, but need to be application managed.
  3. Use some sort of file-watching mechanism to monitor the QuestDB directory for modifications. We often create transient files, so this could get complicated with many tables and partitions.
  4. Use an external tool to handle this CDC use case.
1 Like
3

Thank you @nwoolmer, great ideas!