Message-ID: <54862252.3070405@BlueTreble.com>
Date: Mon, 8 Dec 2014 16:12:34 -0600
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>, Amit Langote
 <Langote_Amit_f8@lab.ntt.co.jp>, 'Amit Kapila' <amit.kapila16@gmail.com>
CC: 'Robert Haas' <robertmhaas@gmail.com>, 'Andres Freund'
 <andres@2ndquadrant.com>, 'Alvaro Herrera' <alvherre@2ndquadrant.com>, 'Bruce
 Momjian' <bruce@momjian.us>, 'Pg Hackers' <pgsql-hackers@postgresql.org>
Subject: Re: On partitioning
References: <20140829155607.GF7705@eldon.alvh.no-ip.org>
	<053501cffa30$ecdf8700$c69e9500$@lab.ntt.co.jp>
	<CA+TgmobN+DT_=hj3NkXZgSv157-W24YaZ50oFdRbhS493covzg@mail.gmail.com>
	<066501cffd52$3d919390$b8b4bab0$@lab.ntt.co.jp>
	<CA+TgmoZqndQQQY1U0OBxUOJy1RE_MAawJErghNB7RM8MPgCYDg@mail.gmail.com>
	<032601d00471$ea6f4280$bf4dc780$@lab.ntt.co.jp>
	<CA+TgmoZ-HJOTPfZcnPOh27881xn4N1QGtY7J4nG+v6tV9cJjNw@mail.gmail.com>
	<04da01d00917$2dde1010$899a3030$@lab.ntt.co.jp>
	<CA+TgmoaU+0EN-XsAT1O+64xrENNJiKPgem+Q1hWzz7a=1MYrDA@mail.gmail.com>
	<015b01d00eab$56d37700$047a6500$@lab.ntt.co.jp>	<C!
 AA4eK1KoJyoV0qOd0a8rEj+	iTiAb1ki0pVz5S8Zoi98pCKrDhg@mail.gmail.com>
	<035801d012a8$458f0170$d0ad0450$@lab.ntt.co.jp>
 <CAA4eK1KJNrfYqHtj+kn-0iaa=wW8tWb6nc1Z42va8ninUH27HA@mail.gmail.com>
 <001001d012b7$a81380f0$f83a82d0$@lab.ntt.co.jp>
 <WM!d4fe63f5b3f541363b68fa36c69d6db7026b7a7e07e2fa6c4cebb9d49983a1398f16ddac5c7ed59bb45dcb629191c755!@asav-3.01
 .com> <5485ED3C.7020206@agliodbs.com>
In-Reply-To: <5485ED3C.7020206@agliodbs.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers