public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Robert Eckhardt <[email protected]>
Cc: Shirley Wang <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: Declarative partitioning in pgAdmin4
Date: Wed, 24 May 2017 13:05:59 +0530
Message-ID: <CANxoLDegWFzkbUi=8KSL-3cPb0masCjD1HwxaMDhV6fs2uOObw@mail.gmail.com> (raw)
In-Reply-To: <CAAtBm9Xw0qpvqRUb87AoSDdu56iaS8TaoVym3KkBJGjOgLU8cA@mail.gmail.com>
References: <CANxoLDcZND0pjXtrDKRip2xjddzjWiMgY2AMmrqqFE_Yu4+tHw@mail.gmail.com>
<CA+OCxowUuaNRX9jHmEVFpqT7JCbjn6vaxw+JJ6yrvVp69FZscg@mail.gmail.com>
<CAPG3WN5NY-Xsa_+6HUQ3NMU_n7jRgJ8L6rjHfyzSLSHS=zZC0Q@mail.gmail.com>
<CA+OCxoy1v+mq2P4ZL2v7mmyHmjwQmL=v8RR8CSRra_SV96nJDQ@mail.gmail.com>
<CANxoLDeBGRmq_kUUNNySXimzJO2Ebj0aQBdjNM+0JvP3_Yr9Dw@mail.gmail.com>
<CAAtBm9Ve2FX4_jY9tv11UqK2BhNoLn118aeT4y=TieSAovL+AA@mail.gmail.com>
<CA+OCxozkEdTmVUtJEBdHT97EbiUK_+cwW+rv21tuHyxSnN3HOg@mail.gmail.com>
<CAAtBm9UHyp+bkxcyYL+1qb9knps_cdh6N0tvwMy5uY-eVjWcPg@mail.gmail.com>
<CANxoLDdgp46uAZzda+cHBn16YibodXgtyH7O1hp39TKT=cv_ig@mail.gmail.com>
<CA+OCxowpGBLT1q2DzL9VpRG5So8zYssP9SAdd=3Mc6dk8_-p7A@mail.gmail.com>
<CANxoLDdP945GEfzeYaPjO41D4VoRN2kDMVhHZCOqCXWKegSEHw@mail.gmail.com>
<CA+OCxowCzLAFybtfnsay9NB0BFORP5yXiitruxh9tvMoADNKRQ@mail.gmail.com>
<CANxoLDcqudMZ5j-30EcFEL9KpQxyvrMWo0mVrWdg0p6_8e7peQ@mail.gmail.com>
<CA+OCxow5pXNkDxrL1dbWbheJjpSseefSdvUs5tiwx7k5o3vB7Q@mail.gmail.com>
<CANxoLDeNovspn8mm0XuYh+F2ShGotwRCAikU5JY9qF1GgFQ9rg@mail.gmail.com>
<CA+OCxowtH1WJpXA1MKSLrzx_qbKAA36GTEk1t5=3VAS8fegBiA@mail.gmail.com>
<CANxoLDeLHGvz0NxH_MM7dCe0muA8Sxe54V65b18iHTAESzt97g@mail.gmail.com>
<CANxoLDeuXKCqrdNwiBut5m7FKQwzRjbPrqR6wHf8qKqgLDnwgQ@mail.gmail.com>
<CAPG3WN4tiMGoFadBZ9KjB8NfNDVfvDnfUHhS=aya5A0o-jZ3Xw@mail.gmail.com>
<CANxoLDfN_RvNc0AsVCtrDC-03L53crHzE8JZjmxna3f08KWVqw@mail.gmail.com>
<CAPG3WN5QA88fNmY4jZZhBY+HUn2FAKecHuvyFjnq2x_vGu4_0w@mail.gmail.com>
<CANxoLDfjy6sWQVHy5m5Rj1R5_=x_XwPzz6Mndj3xXfnEYpU_zg@mail.gmail.com>
<CAPG3WN7haKwrQzrgVh7JSunGcP9_6wj=_q_C9J-yYgsZbhWmEw@mail.gmail.com>
<CANxoLDeZ-izo=RSaHRnFNaAAQjxhd9-x6stx5FyLYU2ZA3A3vA@mail.gmail.com>
<CAPG3WN6sKefWWYfg9A5=f-QOO9HAsg7krsuQ6FZwvojEuvSjCA@mail.gmail.com>
<CAAtBm9Xw0qpvqRUb87AoSDdu56iaS8TaoVym3KkBJGjOgLU8cA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi Robert
On Tue, May 23, 2017 at 8:09 PM, Robert Eckhardt <[email protected]>
wrote:
>
>
> On Tue, May 23, 2017 at 10:09 AM, Shirley Wang <[email protected]> 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*
view thread (77+ 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]
Subject: Re: Declarative partitioning in pgAdmin4
In-Reply-To: <CANxoLDegWFzkbUi=8KSL-3cPb0masCjD1HwxaMDhV6fs2uOObw@mail.gmail.com>
* 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