QuestDB partitions

[Maros112358]

Hello,

in QuestDB I need to get the number of partitions, total size, and row count per table. Right now we use the following query to generate the final query:

select concat(
  'select ''',name,''' as table_name,''',
  walEnabled,''' as walEnabled,''',
  partitionBy,''' as partitionBy,count(*) partition_count,sum(numrows) row_count,(sum(disksize)) as size_b from table_partitions(''',
  name,''') group by table_name union all')
from tables()
order by name;

The final query then returns the desired data.
I have not found a way to both generate the final query and then execute it in sql_exporter.
Is there any way to achieve this in sql_exporter? Or is there any way to simplify this process by another query?

[burningalchemist]

Hey @Maros112358, I'd need to try it out on my end to give any recommendations, since my experience with QuestDB is very limited. I also happen to have an ex-colleague currently working for QuestDB, so I might ask him for some hints and summon him here. :)

As far as I can tell, QuestDB supports line protocol, and this is what you do when using select concat(), in other words, generating the statements via SQL. I don't think there's a way to pass these statements directly back to QuestDB with sql_exporter.

At the same time, it could be that you can try to use multiple statements (delimited by ;), and then store the intermediate result somehow and then execute these statements.

[marregui]

hi! https://questdb.io/docs/reference/function/meta/#table_partitions

example:

CREATE TABLE my_table AS (
    SELECT
        rnd_symbol('EURO', 'USD', 'OTHER') symbol,
        rnd_double() * 50.0 price,
        rnd_double() * 20.0 amount,
        to_timestamp('2023-01-01', 'yyyy-MM-dd') + x * 6 * 3600 * 100000L timestamp
    FROM long_sequence(700)
), INDEX(symbol capacity 32) TIMESTAMP(timestamp) PARTITION BY WEEK;

table_partitions('my_table');

you can also use it with filters:

table_partitions('sqlalchemy_raw') where name = 'default'

in fact the above is just a shortcut for:

SELECT * FROM table_partitions('sqlalchemy_raw') WHERE name = 'default'

you may like to join the slack channel https://slack.questdb.io/