Adempiere in Postgres-XC

[egonzalez.ergio]

We are testing Postgres-XC Cluster with ADempiere to manage big loads of
data.

Does someone have experience with Postgres-XC cluster and ADempiere?

We are trying to change the database structure to fit the different types
of table distribution in the cluster.

---

--- Timeo Danaos et dona ferentes ---

[mason_s]

I was with the Postgres-XC project at its inception and currently work on Postgres-XL.

I have not used ADempiere, but some general rules:

• If the table is fairly static, DISTRIBUTE BY REPLICATION.

• If write heavy, DISTRIBUTE BY HASH(keycol);

• Distribute child tables with their parent when possible. That is, if there is a customer table and orders table, if orders has a foreign key to customer, distribute both by customer_id.

This last one gets a bit tricky however. What do we do if there is customer-orders-lineitem? We can make a decision to either distribute customer-orders together, or orders-lineitem together. In this case, I would analyze the statements and frequency in which they are used in the application and see which two are used together more frequently.

An alternative to this would be to consider a bit of denormalization and pull customer_id into lineitem and change all of the SQL statements to use it. The XC/XL planner is smart enough to push down joins to a single node and keep transactions limited to one node if possible. A second database schema will make maintaining your project more difficult, so it may make sense to try to avoid this if possible initially, test, and then consider such changes if performance is still unsatisfactory (does ADempiere have a performance test utility?).

I hope that helps.