5 ms·

No, that's not true. When combined with parallelism, ability to place partitions to other hosts and partition-aware algorithms (partition-wise joins/aggregation

No, that's not true. When combined with parallelism, ability to place partitions to other hosts and partition-aware algorithms (partition-wise joins/aggregation etc.) it can be a powerful scalability feature.

arghwhat 8y ago
It is true. However, while partitioning is not load balancing, partitioning can be used to make some form of load distribution. Partitioning primarily deals with isolating related data into small pools so that queries do not need to touch full tables. The purpose of the buckets is to group related data so that a single partition may serve a query (instead of having to touch the entire table), thereby lowering the load from each individual query, not distributing all queries. Load balancing deals with distributing all queries to ensure even node load, not increasing efficiency of queries (which it does not do at all). Data distribution leading to round-robin accesses across nodes/"partitions" is best. This is the exact opposite of partitioning, which tries to group all that data into a single partition. Hash partitioning on serial or uuid might give you round-robin like behavior, but it's not really what partitioning is meant for.
- pgaddict 8y ago
  I'm not really sure what is your point. I was not suggesting partitioning alone is a load-balancing solution, but that partitioning in combination with other features is a powerful scalability feature. Assuming there is a (1) way to keep the partitions withing some size limit automatically, and (2) the partitioning scheme is such that the queries can leverage the partitioning scheme (eliminate most partitions, join tables partitioned in the same way). We don't have (1) yet - at least not in core PostgreSQL, but this part should be doable in an extension until it gets into the core. We do have (2) to a large extent, although I'm sure there are gaps and room for improvement. And when combined with ability to place partitions to other hots, that will be another step. No one is claiming partitioning alone magically solves scalability, but IMHO it's an important building block in doing that (for a large class of applications).
  - arghwhat 8y ago
    My point is that partitioning is the polar opposite of load balancing, and the wrong tool to implement it.

arghwhat 8y ago

It is true. However, while partitioning is not load balancing, partitioning can be used to make some form of load distribution. Partitioning primarily deals with isolating related data into small pools so that queries do not need to touch full tables. The purpose of the buckets is to group related data so that a single partition may serve a query (instead of having to touch the entire table), thereby lowering the load from each individual query, not distributing all queries. Load balancing deals with distributing all queries to ensure even node load, not increasing efficiency of queries (which it does not do at all). Data distribution leading to round-robin accesses across nodes/"partitions" is best. This is the exact opposite of partitioning, which tries to group all that data into a single partition. Hash partitioning on serial or uuid might give you round-robin like behavior, but it's not really what partitioning is meant for.

pgaddict 8y ago
I'm not really sure what is your point. I was not suggesting partitioning alone is a load-balancing solution, but that partitioning in combination with other features is a powerful scalability feature. Assuming there is a (1) way to keep the partitions withing some size limit automatically, and (2) the partitioning scheme is such that the queries can leverage the partitioning scheme (eliminate most partitions, join tables partitioned in the same way). We don't have (1) yet - at least not in core PostgreSQL, but this part should be doable in an extension until it gets into the core. We do have (2) to a large extent, although I'm sure there are gaps and room for improvement. And when combined with ability to place partitions to other hots, that will be another step. No one is claiming partitioning alone magically solves scalability, but IMHO it's an important building block in doing that (for a large class of applications).
- arghwhat 8y ago
  My point is that partitioning is the polar opposite of load balancing, and the wrong tool to implement it.