Re: Declarative partitioning in pgAdmin4

Hi Robert

On Tue, May 23, 2017 at 8:09 PM, Robert Eckhardt <reckhardt(at)pivotal(dot)io>
wrote:

>
>
> On Tue, May 23, 2017 at 10:09 AM, Shirley Wang <swang(at)pivotal(dot)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*