On 11/18/20 6:02 AM, Laurenz Albe wrote:
> On Wed, 2020-11-18 at 10:57 +0100, Thomas Kellerer wrote:
>>> No matter how long it takes, this is an excellent argument for
>>> partitioning Very Large Tables: many maintenance tasks are made
>>> *much* easier.
>> The problem is, you can't partition every table as long as Postgres
>> does not support a primary key that is independent of the partitioning key
>> (i.e. until it has "global indexes" as they are called in Oracle)
> I personally hope that we will never have global indexes.
> I am not looking forward to helping customers with the problems that
> they create (long duration of ATTACH/DETACH PARTITION, index fragmentation).
I've been using what Oracle calls "global indexes" for 20 years. They're
super useful when -- for example -- you want to partition a transaction
table by a date field, while the PK is synthetic.
Up until about two years ago, I purged old data every six months. (Then it
was migrated from the legacy RDBMS to Oracle.)
Yes, you've got to drop and rebuild the indices, but that's a small price to
pay for the simplicity of archiving (especially when the indices are built
in parallel).
--
Angular momentum makes the world go 'round.