agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Jim Nasby <[email protected]>
To: Josh Berkus <[email protected]>
To: Amit Langote <[email protected]>
To: 'Amit Kapila' <[email protected]>
Cc: 'Robert Haas' <[email protected]>
Cc: 'Andres Freund' <[email protected]>
Cc: 'Alvaro Herrera' <[email protected]>
Cc: 'Bruce Momjian' <[email protected]>
Cc: 'Pg Hackers' <[email protected]>
Subject: Re: On partitioning
Date: Mon, 8 Dec 2014 16:12:34 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CA+TgmobN+DT_=hj3NkXZgSv157-W24YaZ50oFdRbhS493covzg@mail.gmail.com>
<[email protected]>
<CA+TgmoZqndQQQY1U0OBxUOJy1RE_MAawJErghNB7RM8MPgCYDg@mail.gmail.com>
<[email protected]>
<CA+TgmoZ-HJOTPfZcnPOh27881xn4N1QGtY7J4nG+v6tV9cJjNw@mail.gmail.com>
<[email protected]>
<CA+TgmoaU+0EN-XsAT1O+64xrENNJiKPgem+Q1hWzz7a=1MYrDA@mail.gmail.com>
<[email protected]>
<C! AA4eK1KoJyoV0qOd0a8rEj+ [email protected]>
<[email protected]>
<CAA4eK1KJNrfYqHtj+kn-0iaa=wW8tWb6nc1Z42va8ninUH27HA@mail.gmail.com>
<[email protected]>
<WM!d4fe63f5b3f541363b68fa36c69d6db7026b7a7e07e2fa6c4cebb9d49983a1398f16ddac5c7ed59bb45dcb629191c755!@asav-3.01 .com>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-hackers>
On 12/8/14, 12:26 PM, Josh Berkus wrote:
> 4. Creation Locking Problem
> high probability of lock pile-ups whenever a new partition is created on
> demand due to multiple backends trying to create the partition at the
> same time.
> Not Addressed?
Do users actually try and create new partitions during DML? That sounds doomed to failure in pretty much any system...
> 6. Unique Index Problem
> Cannot create a unique index across multiple partitions, which prevents
> the partitioned table from being FK'd.
> Not Addressed
> (but could be addressed in the future)
And would be extremely useful even with simple inheritance, let alone partitioning...
> 9. Hibernate Problem
> When using the trigger method, inserts into the master partition return
> 0, which Hibernate and some other ORMs regard as an insert failure.
> Addressed.
It would be really nice to address this with regular inheritance too...
> 11. Hash Partitioning
> Some users would prefer to partition into a fixed number of
> hash-allocated partitions.
> Not Addressed.
Though, you should be able to do that in either system if you bother to define your own hash in a BEFORE trigger...
> A. COPY/ETL then attach
> In inheritance partitioning, you can easily build a partition outside
> the master and then "attach" it, allowing for minimal disturbance of
> concurrent users. Could be addressed in the future.
How much of the desire for this is because our current "row routing" solutions are very slow? I suspect that's the biggest reason, and hopefully Alvaro's proposal mostly eliminates it.
> B. Catchall Partition
> Many partitioning schemes currently contain a "catchall" partition which
> accepts rows outside of the range of the partitioning scheme, due to bad
> input data. Probably not handled on purpose; Alvaro is proposing that
> we reject these instead, or create the partitions on demand, which is a
> legitimate approach.
>
> C. Asymmetric Partitioning / NULLs in partition column
> This is the classic Active/Inactive By Month setup for partitions.
> Could be addressed via special handling for NULL/infinity in the
> partitioned column.
If we allowed for a "catchall partition" and supported normal inheritance/triggers on that partition then users could continue to do whatever they needed with data that didn't fit the "normal" partitioning pattern.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
view thread (170+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: On partitioning
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox