Time partitions seemingly breaking themselves at random on distributed tables.

[quentin-arzalier]

We've been working with citus distributed and partitionned tables for a few weeks now, and some partitions broke at random on distributed tables. This makes the table unable to run a query without time constraints, or just on the partition :

This does not happen for the neighboring partitions :

And only happens on one of our worker nodes (w1) :

Apparently, the partition randomly lost 8KB of data according to citus_tables and citus_total_relation_size :

This already happened three times (as we know of) on two distinct tables. The broken partitions are January 2025 and November 2027 on the first table and March 2027 on the second table. We know that the partition on the second table broke on March 19th of this year.

We are using the Azure Cosmos DB for PostgreSQL Cluster to host the database.

Any help to recover/repair these partitions are greatly appreciated because our current solution is to drop the partition and regenerate it with create_time_partitions.

[onderkalaci]

We are using the Azure Cosmos DB for PostgreSQL Cluster to host the database.

I think it is best to open a support ticket from the portal

[quentin-arzalier]

We are going through this process as we speak. It seemed to me that opening the issue here as well was a good idea, maybe I am not the only one encountering these issues.

[amolenk]

@quentin-arzalier Did you receive a satisfying explanation from the Azure team? We're also evaluating using Azure Cosmos DB for PostgreSQL Cluster to host the database.

[quentin-arzalier]

@amolenk Hello, we haven't had any response from the Azure team, as we haven't been able to communicate properly with our cloud service provider since then. I have found a workaround for the problem, which is to drop the partition and to create it again with the create_time_partitions command. For now, the broken partitions don't interfere with our data, since we don't process data past the current date. As such, we remain in fear that a non-empty partition will break, losing our data in the process.

To respond to your question about Azure Cosmos DB, we can't really tell today if the issue comes from the cloud service, the postgresql database, or citus itself.

[mulander]

Hi @quentin-arzalier,

I am a technical program manager for the Citus engine at Microsoft. This issue have not reached the engineering team. Could you please reach out to me directly either on Citus slack (https://slack.citusdata.com/) or email adamwolk@microsoft.com. We would like to look into it and unblock you.

[quentin-arzalier]

Hello,

Thank you for reaching out. I'm no longer in office for the day, but I'll make sure to contact you first thing tomorrow morning so we can review as many details as possible.

[quentin-arzalier]

Also, I didn't mention this in my original post, but I found out that the missing 8kB of data from the broken partition is only revealed by citus_total_relation_size, not by citus_table_size, if that helps.

[mulander]

@quentin-arzalier as we discussed on Slack without a proper support ticket we have no way to analyze and diagnose the cluster. Hence Azure support is the proper place to obtain support, not GitHub. By the way, can you clarify who are you referring to "our cloud service provider", is that an intermediary and no support ticket was in fact generated at Azure? Let's continue our further support via these channels.

PS. We will keep this GH issue open until we resolve the case and confirm that a fix in the citus extension is not required. Obviously if you or anybody else can provide reproduction steps here in GitHub then we can independently investigate in public.

[amolenk]

Hi @quentin-arzalier! Did you receive more info from MS about the missing data?

[quentin-arzalier]

Hello @amolenk, As stated by mulander, we have been in touch on slack about a month ago. They managed to find a fix to our broken partitions, which I could post here in detail when I'm back in office. I've been in school for my apprenticeship since then and haven't been able to hear from them, though on our end the data is fixed (for now). They were looking into why our partitions got broken in the first place when I left, and I have no idea where they got.