Range partitioning? Is PostgreSQL centralized?

[nino....@gmail.com]

Hello,

I have chosen Citus as a distributed PostgreSQL for range partitioning.

However, my work involves altering PostgreSQL catalogs such as pg_class and pg_statistic. For this I issue classical SQL updates and inserts for particular partitions. Are the catalogs centralized, or do they require some special attention.

My second question involves the PostgreSQL Query Optimizer.

I mainly use EXPLAIN. Is it adapted to distributed queries? (I wnat to issue an EXPLAIN query over the master (parent) table and get the cost of the distributed one. Is this possible?

Perhaps inadvertently, I couldn't find any relevant information on these issues. I guess they are quite specific.

Thanks,
Nino

[Brian Cloutier]

> Are the catalogs centralized

What do you mean centralized? Could you give a little more detail on what you're trying to do?

Currently just the master node keeps track of all the partitions, and all nodes have the same pg_class and pg_statistic tables you're used to, but modifications to those tables aren't propagated to the other nodes.

> I mainly use EXPLAIN. Is it adapted to distributed queries?

It certainly is! 

--
You received this message because you are subscribed to the Google Groups "citus-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to citus-users...@googlegroups.com.
To post to this group, send email to citus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/citus-users/624cca8f-9862-435e-b9f5-577cefb3956f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[nino....@gmail.com]

Thanks Brian.

What I am basically trying to do is modifying statistics of specific partitions so when I run a distributed query, the optimizer would see and leverage those changes. My concern is whether updating pg_statistic at the master node would propagate those updates to partition-specific catalog entries. As long as this is true, it would get my task done in no time.

Doing it manually, i.e. propagating those updates to other node-specific instances of postgres is of course acceptable as well.

In a nutshell, I'm populating catalogs instead of loading data in order to quickly estimate workload costs. In other words, I'm doing some predictive optimization of partitioning.

Please let me know if I can provide some more detail.

[Brian Cloutier]

> What I am basically trying to do is modifying statistics of specific partitions so when I run a distributed query, the optimizer would see and leverage those changes

Unfortunately the way to do this is to connect manually to the workers and change the statistics on those nodes. Each shard is stored as a separate table so you'd do that just like you normally would on the workers. But since the shards aren't stored as tables on the master there aren't any entries for them in the metadata tables (except the tables citus adds, of course).

--
You received this message because you are subscribed to the Google Groups "citus-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to citus-users...@googlegroups.com.
To post to this group, send email to citus...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/citus-users/99eca696-1b64-4fb7-8c50-254389db3b36%40googlegroups.com.

[nino....@gmail.com]

That's great!

I'll just find the slave worker where the shard is stored at and update its catalogs manually.

Looking forward to using Citus (probably the best implementation I've seen so far), and the most easily configurable one as well.

Thank you for the immediate help and support, Brian!

[Brian Cloutier]

Of course!

Hope the rest of your Citus troubles are so easily resolved :)