3 ms·
This is unfortunate. Once a table is partitioned and assuming new records are inserted over time, it's inevitable that some partitions will grow faster than oth
by lutusp 14y ago
This is unfortunate. Once a table is partitioned and assuming new records are inserted over time, it's inevitable that some partitions will grow faster than others, and the advantage of partitioning is eroded by unequal-sized partitions.
So for a growing database this solution is really a stopgap measure, one that will eventually have to be repeated.
It seems to me that a hierarchical system has advantages over a huge single-level table of millions of records, for example one keyed by letters of the alphabet. But 100 million records in one flat table? Surely there's an organizing scheme that naturally produces faster access without resorting to artificially splitting up up the data.
To me, the fact that partitioning speeds up access means it's time to, not split up a gigantic table (something that may have to be done repeatedly), but rethink the database design.
- pgbanshee 14y ago"one that will eventually have to be repeated" That's the point. With pg_reorg, you can easily partition tables on a daily basis without degradation to performance or sharding.
- lutusp 14y ago>> "one that will eventually have to be repeated" > That's the point. With pg_reorg, you can easily partition tables on a daily basis ... With all respect, partitioning active tables on a daily basis sounds like a very inefficient way to get around a problem with how the database is structured. It would be like pouring water into a car's radiator every day instead of fixing the problem at the source. To me, partitioning a table that logically should be a single table, and that to users appears to be a single table, needs a different solution, something less hacky, like rethinking the basic database scheme. Reasonable people may differ.
- fdr 14y agoSometimes 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.