Hi Robert
On Tue, May 23, 2017 at 8:09 PM, Robert Eckhardt <reckha...@pivotal.io>
wrote:
>
>
> On Tue, May 23, 2017 at 10:09 AM, Shirley Wang <sw...@pivotal.io> wrote:
>
>>
>> It's possible to design for the range and list partitions and know we can
>> achieve success because we understand how users would go through this
>> workflow. Not sure about expressions.
>>
>
> Maybe to pile on this a bit.
>
> When Shirley and I were discussing the workflows it was obvious when we
> were looking at 'normal' range or list partition use cases. Generally the
> only open question we had about the workflow was whether or not users would
> be building tables net new or whether they were more likely to have a table
> that was growing too large and therefore needed to create a new partitioned
> table.
>
> We couldn't think of a reason why a user would want to take the average of
> two columns and partition by this derived value. It added to the question
> of why/how a user would consider this as an idea a priori or whether this
> would be an insight given analysis of existing data.
>
> I assume this was supported for a specific use case. if you could share
> that it would be awesome. I guess the long and short of it is, we are
> having a difficult time imagining the workflow for this feature.
>
Taking average of two columns is just an example/representation of
expression, there is no use case of that. As I am also in learning phase.
Below are some use case that I can think of:
-
Partitions based on first letter of their username
CREATE TABLE users (
id serial not null,
username text not null,
password text,
created_on timestamptz not null,
last_logged_on timestamptz not null
)PARTITION BY RANGE ( lower( left( username, 1 ) ) );
CREATE TABLE users_0
partition of users (id, primary key (id), unique (username))
for values from ('a') to ('g');
CREATE TABLE users_1
partition of users (id, primary key (id), unique (username))
for values from ('g') to (unbounded);
- Partition based on country's sale for each month of an year.
CREATE TABLE public.sales
(
country text NOT NULL,
sales bigint NOT NULL,
saledate date
) PARTITION BY RANGE (country, (extract (YEAR FROM saledate)),
(extract(MONTH FROM saledate)))
CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales
FOR VALUES FROM ('usa', 2017, 01) TO ('usa', 2017, 02);
CREATE TABLE public.sale_india_2017_jan PARTITION OF sales
FOR VALUES FROM ('india', 2017, 01) TO ('india', 2017, 02);
CREATE TABLE public.sale_uk_2017_jan PARTITION OF sales
FOR VALUES FROM ('uk', 2017, 01) TO ('uk', 2017, 02);
INSERT INTO sales VALUES ('india', 10000, '2017-1-15');
INSERT INTO sales VALUES ('uk', 20000, '2017-1-08');
INSERT INTO sales VALUES ('usa', 30000, '2017-1-10');
Apart from above there may be N number of use cases that depends on
specific requirement of user.
>
> -- Rob
>
>
>
--
*Akshay Joshi*
*Principal Software Engineer *
*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
