Query plan when data is being copied to children

[igniting]

If I run create_range_partitions and then partition_table_concurrently at a later time, then how is the query plan generated for all the queries that happen in between?
I ran explain on a query and the query plan showed that ti would read data from the child, even though the data migration was not complete.
Ideally all the queries should be executed on parent till the data migration is complete. After migration, query should execute only on children.
Documentation of partition_table_concurrently is not very clear on this.

[funbringer]

hi @igniting,

It looks like you got the wrong idea of function partition_table_concurrently(). Its purpose is to move the rows of parent table to partitions using little to no locks, provided that your partitioning scheme is steady (i.e. you've already created all of your partitions). This is handy in case when you have a big table that would be practically impossible to lock (e.g. you have a web app inserting some stuff into it every N minutes). This means that there is no changes in plan whatsoever, as there's no need to do so.

I ran explain on a query and the query plan showed that ti would read data from the child, even though the data migration was not complete

Exactly, that's the whole point - to have a READ & WRITE access to a table that's being partitioned at the moment. The old style partition_data:=true mode locks the table exclusively.

Ideally all the queries should be executed on parent till the data migration is complete

Not quite, the batch of rows to be moved in a single step is locked by the background worker using the SELECT * FOR UPDATE statement, thus disabling any concurrent changes, so it's safe to assume that we can use both the parent and its partitions at the same time. This operation repeats until there's no rows in the parent or the worker's unable to lock the rows (it will take next attempt after 1 second).

[igniting]

I ran explain on a query and the query plan showed that ti would read data from the child, even though the data migration was not complete

Exactly, that's the whole point - to have a READ & WRITE access to a table that's being partitioned at the moment. The old style partition_data:=true mode locks the table exclusively.

So while partition_table_concurrently is running, all my read queries will give wrong results as they are reading from child table?

[funbringer]

So while partition_table_concurrently is running, all my read queries will give wrong results as they are reading from child table?

I don't get it - how come they will give a wrong result?

[igniting]

Since we have already created the partitions, query plan generator will say to read from children (which I verified is true by running explain). However, we are still in process of moving data into children and they don't contain the complete data. Am I missing something here?

[funbringer]

However, we are still in process of moving data into children and they don't contain the complete data.

That's true. But the purpose of partitioning is to perform queries on parent table to let our magic gather all tables containing data. Queries on children are simply useless, why do we even need to partition table in that case?

Meanwhile, queries on the parent table will have parent table included in query plans, to allow you to see all of your data.

Nonetheless, this is only relevant to concurrent partitioning. If you partition table using the partition_data:=true option, you won't be able to write new data or even see new partitions until the transaction is committed.

[zilder]

When you create partitions with partition_data:=false parameter the query planner builds a plan that contains both parent table and children. Let say you haven't run partition_table_concurrently() yet. In this case all the data would be obtained from parent table since children are empty. When you run partition_table_concurrently() then some part of the data is already moved to children and some is left in parent table. In this case postgres will gather the data from all tables (including parent) and merge it in one result set. After data migration is finished you can use enable_parent(<table_name>, false) to exclude parent table from query plans.

[igniting]

Thanks @zilder, that answers my question!