Horton

Re: On partitioning

Josh Berkus <josh@agliodbs.com>

View thread View on postgr.es
From: Josh Berkus <josh@agliodbs.com>
To: 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>
Date: 2014-12-09T18:14:01Z
Lists: pgsql-hackers 
On 12/09/2014 12:17 AM, Amit Langote wrote:
>> Now if user wants to define multi-column Partition based on
>> > monthly_salary and annual_salary, how do we want him to
>> > specify the values.  Basically how to distinguish which values
>> > belong to first column key and which one's belong to second
>> > column key.
>> >
> Perhaps you are talking about "syntactic" difficulties that I totally missed in my other reply to this mail?
> 
> Can we represent the same data by rather using a subpartitioning scheme? ISTM, semantics would remain the same.
> 
> ... PARTITION BY (monthly_salary) SUBPARTITION BY (annual_salary)?

... or just use arrays.

PARTITION BY LIST ( monthly_salary, annual_salary )
	PARTITION salary_small VALUES ({[300,400],[5000,6000]})
) ....

... but that begs the question of how partition by list over two columns
(or more) would even work?  You'd need an a*b number of partitions, and
the user would be pretty much certain to miss a few value combinations.
 Maybe we should just restrict list partitioning to a single column for
a first release, and wait and see if people ask for more?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com