3 ms·

Sometimes you really do have 100 million like-things. Partitioning can also be thought of a very compressed form of index, most of which are implement as some

by fdr 14y ago

Sometimes you really do have 100 million like-things. Partitioning can also be thought of a very compressed form of index, most of which are implement as some hierarchical data structure. This kind of hack basically is because of a weakness in implementation for this use case. A more cohesive feature to help address this case is under discussion in PostgreSQL, but still nascent. There are notes here: http://wiki.postgresql.org/wiki/Segment_Exclusion The goal is to retain most of the 4GL qualities of SQL while still getting advantages in physical data access, not unlike how regular indexes work today: in their most basic form they make no semantic alteration. To some extent, data reorganization is inevitable: you can just hope to make it incremental, on-line, safe, and convenient.

lutusp 14y ago
> Partitioning can also be thought of a very compressed form of index, most of which are implement as some hierarchical data structure. Yes, fair enough. In such a case, why not use an explicit index like letters of the alphabet, in other words why not reindex the table just once, hierarchically, with something relevant to table searches, in a way that breaks the table up into manageable chunks? I'm only objecting to the idea of a periodic repartition -- it seems inefficient, and according to the linked article, it takes the table out of service for as much as 15 minutes, apparently on a daily basis. To me this sounds hacky and ad-hoc. > To some extent, data reorganization is inevitable ... Yes, but if the solution doesn't address the underlying problem, just the symptom, it will need to be reapplied perpetually.
- fdr 14y ago
  > Yes, fair enough. In such a case, why not use an explicit index like letters of the alphabet, in other words why not reindex the table just once, hierarchically, with something relevant to table searches, in a way that breaks the table up into manageable chunks? In an ideal world where we still only want to physically organize but one copy of data, the criteria for what partitioning would be ideal is dependent on query workload: for example, aggregates over a month (avg, stddev, max, min). In that case, choosing a partitioning metric makes a lot of sense, and reorganizations are seldom. Sometimes people just want to have roughly-equal physical partitioning of the data, but want it more-or-less clustered by some metric (the ideal is typically a sort, but maintaining a strict sorted order may be too punishingly expensive on modification), with each partition being aware of some aggregation of its metric (such as spanning range). In this case, partitions will require re-balancing, and that's roughly the class of solution the post here falls into. See also: index organized tables (not supported in Postgres), and their many, many problems when dealing with data modifications. However, they do perform well for some workloads. > Yes, but if the solution doesn't address the underlying problem, just the symptom, it will need to be reapplied perpetually. Yes. However, this solution composes on tools already available, and are already caveat-loaded enough as-is (if you look at the pg_reorg page, they are quite upfront about those). There will always be a frontier of workarounds to problems that have not been polished, because polishing is probably about one hundred fold as expensive (sometimes more), and achieving the incremental, nice versions without database management system support is not very lucid and quite complicated, and not therefore not an economic choice for many situations.
- pgbanshee 14y ago
  > I'm only objecting to the idea of a periodic repartition -- it seems inefficient, and according to the linked article, it takes the table out of service for as much as 15 minutes, apparently on a daily basis. To me this sounds hacky and ad-hoc. The article was stating that by using pg_reorg to partition, the table will NOT be out of service for 15 minutes

by fdr 14y ago

lutusp 14y ago

> Partitioning can also be thought of a very compressed form of index, most of which are implement as some hierarchical data structure. Yes, fair enough. In such a case, why not use an explicit index like letters of the alphabet, in other words why not reindex the table just once, hierarchically, with something relevant to table searches, in a way that breaks the table up into manageable chunks? I'm only objecting to the idea of a periodic repartition -- it seems inefficient, and according to the linked article, it takes the table out of service for as much as 15 minutes, apparently on a daily basis. To me this sounds hacky and ad-hoc. > To some extent, data reorganization is inevitable ... Yes, but if the solution doesn't address the underlying problem, just the symptom, it will need to be reapplied perpetually.

fdr 14y ago
> Yes, fair enough. In such a case, why not use an explicit index like letters of the alphabet, in other words why not reindex the table just once, hierarchically, with something relevant to table searches, in a way that breaks the table up into manageable chunks? In an ideal world where we still only want to physically organize but one copy of data, the criteria for what partitioning would be ideal is dependent on query workload: for example, aggregates over a month (avg, stddev, max, min). In that case, choosing a partitioning metric makes a lot of sense, and reorganizations are seldom. Sometimes people just want to have roughly-equal physical partitioning of the data, but want it more-or-less clustered by some metric (the ideal is typically a sort, but maintaining a strict sorted order may be too punishingly expensive on modification), with each partition being aware of some aggregation of its metric (such as spanning range). In this case, partitions will require re-balancing, and that's roughly the class of solution the post here falls into. See also: index organized tables (not supported in Postgres), and their many, many problems when dealing with data modifications. However, they do perform well for some workloads. > Yes, but if the solution doesn't address the underlying problem, just the symptom, it will need to be reapplied perpetually. Yes. However, this solution composes on tools already available, and are already caveat-loaded enough as-is (if you look at the pg_reorg page, they are quite upfront about those). There will always be a frontier of workarounds to problems that have not been polished, because polishing is probably about one hundred fold as expensive (sometimes more), and achieving the incremental, nice versions without database management system support is not very lucid and quite complicated, and not therefore not an economic choice for many situations.
pgbanshee 14y ago
> I'm only objecting to the idea of a periodic repartition -- it seems inefficient, and according to the linked article, it takes the table out of service for as much as 15 minutes, apparently on a daily basis. To me this sounds hacky and ad-hoc. The article was stating that by using pg_reorg to partition, the table will NOT be out of service for 15 minutes