Table partitions for all tables in the QuestDB

Is your feature request related to a problem?

I need to get information about partitions for all tables in QuestDB.

Describe the solution you'd like.

I'd like to have a function like table_partitions, for example all_tables_partitions, which returns information about partitions for all tables in the database. For example:

all_tables_partitions()

Alternative can be a function tables_partitions to take array of tables string[] as an argument and returning information about partitions for each table in the array. For example:

tables_partitions(['table_1', 'table_2', 'table_3'])

or

tables_partitions(SELECT name FROM tables())

Describe alternatives you've considered.

Right now, I get the list of tables and use concat with union all to create a 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 looks like this:

select 'table_1' as table_name,'false' as walEnabled,'DAY' as partitionBy,count(*) partition_count,sum(numrows) row_count,(sum(disksize)) as size_b from table_partitions('table_1') group by table_name union all
select 'table_2' as table_name,'false' as walEnabled,'DAY' as partitionBy,count(*) partition_count,sum(numrows) row_count,(sum(disksize)) as size_b from table_partitions('table_2') group by table_name union all
select 'table_3' as table_name,'false' as walEnabled,'DAY' as partitionBy,count(*) partition_count,sum(numrows) row_count,(sum(disksize)) as size_b from table_partitions('table_3') group by table_name

I think this is too tedious and complicated.

Additional context.

No response

This seems really interesting and maybe something I can handle. I am a first timer and would really be grateful for some guidance. Please let me take this on. Took me some time but I have managed to run the source code locally. I am really looking forward to make some contributions here. Thank you in advance :).

Hi @Maros112358 , Can you explain the classes we should be working on to implement this feat.

Hey @Maros112358 , wondering, what is the shape that you intend to have of this? a sql function? or just a code function somewhere?

Hello @agsti, I am not sure if I understand your question. Would you mind to elaborate?

@Maros112358 Sure, I mean, do you intend to do something like SELECT * from tables_partitions(['table_1', 'table_2', 'table_3']) ? or just call it from java code?

@agsti Thank you. I want to use as a sql function in query.

@Maros112358 Gotcha, then: Do we want to keep the same schema as table_partitions(<table>) ?

if so

Can I reuse logic from ShowPartitionsRecordCursorFactory.java ?

I think I would create a new AbstractRecordCursorFactory that gives different ShowPartitionsRecordCursors over time. Can I do that?

Otherwise I guess is about creating a sibling to ShowPartitionsRecordCursorFactory.java with the schema of

boolean walEnabled,
string partitionBy,
int partition_count,
int row_count

@agsti I would like this new function to return same columns as function table_partitions, just for all the tables in the database. I am not familiar with the codebase, therefore it's up to you how you implement.

I have decided to modify table_partitions function as I think it requires the least amount of code than the other options and there would be a big overlap of functionality between this and another function. I opened a MR That is not tagged in this issue

MR is quite ready now, maybe I could get this task assigned

Hello @agsti, is there anything necessary to do from my side?

Hi, @Maros112358 is this issue still open, I'd like to work on this

@Maros112358 @puzpuzpuz @nwoolmer for all_Table_partitions() meta function do we have to output the partition stats for tables with isSystem=true as well?

I'd say we should hide system tables by default, just like it's done in, for example, all_tables. If someone wants to get system table partitions and they know the table name, they can use the table_partitions() function.

@puzpuzpuz also do we have to add stats of detached and attachable partitions as well?

@puzpuzpuz also do we have to add stats of detached and attachable partitions as well?

The output rows should be similar to what's returned from table_partitions(), so detached and attachable partitions should be included.

@puzpuzpuz how do we make isAttachable field for a partition as true , what SQL statement should be used?

Try renaming a detached partition with the .attachable suffix, but don't run ALTER TABLE ATTACH PARTITION (see https://questdb.io/docs/reference/sql/alter-table-attach-partition/).

@Maros112358 The query you posted should be resolved in this PR: https://github.com/questdb/questdb/pull/4817

We'll keep the issue open for now, as it'd still be good to have an all_tables_partitions function.

Hello @nwoolmer, thank you very much.