Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dHuCY-0007Wr-QH for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 15:45:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dHuCY-0005VQ-CL for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 15:45:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dHuCI-00056h-W0 for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 15:45:39 +0000 Received: from mail-it0-x233.google.com ([2607:f8b0:4001:c0b::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dHuCF-0007AF-F6 for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 15:45:37 +0000 Received: by mail-it0-x233.google.com with SMTP id m62so65614587itc.0 for ; Mon, 05 Jun 2017 08:45:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin-org.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=lJcQZe12THBXw1qdE6sqAIoUsdK3p4gKBs7QIKSpCpo=; b=CJ8F8QaqbcoODFULGDXulzPONJIH0+IUtv5YWAiARFRDAu7F5qjcQa5vPn/Ts5lOH9 8uQbJ0G4syd7Nmr3I90IFPl8+J8gxDf7YlTU+ZjvU+PhGddkbWABm3FS+tkbRd9PGS1x T//VHKJPzBOzyfajv9hK4goR9hqa//WQznqjhFHlEt3vmd32bKRmo5NjWq1lrmSvqzT9 XEvv7elH85edfMDCgmaaikIGNMdIL8ADVc47Fb6VH4CDCsvZ62HraMTtFp7P09xOsSwi HYZxEVVF8UD+is4LHSe5QX9gAC/ULcnHL6G0zzEgQsSssDpEr0w9+xl36O6CcoqC+Z0i 6/sQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=lJcQZe12THBXw1qdE6sqAIoUsdK3p4gKBs7QIKSpCpo=; b=sux+ycT4ZvWay0gq783HE9jfMLPzcg+fwb9Sit9LvSjz1Yi49FUCOkbnGl+rq32x/F gvxh59JFzD2mm2zLMb5+GrqvEcjXURTs9Z6k6tB9DbI3yg6K+q3TPPsrsED+tncwblbj tgop6ZjSHvA/m8WdO9Z+sh5ctWIJ06URXIcRzNRNoGERhzqTCag4MyNfU8Np79EeUd3v w3VDYgQf3CU003L5CI3xOt0DxhABUMlYpcoJwe8k1nE5Da69rXXMPMO5TaVaBdlVPp5/ NRRlUtVWg93FVq8CH9uRMSnCSHGw4TUij6JTtdF7SCLUvfbk+S5b2+/UgQGbktgPsiER F1Og== X-Gm-Message-State: AODbwcAFpsqF6fE8N6JW3XakDqhTu6Jbws4Xb7b6m3HpyxyN6/Eex8DZ BRManDCXbRq42e1p+YT7qDrVXmi7K2E5 X-Received: by 10.107.51.14 with SMTP id z14mr19927090ioz.84.1496677534163; Mon, 05 Jun 2017 08:45:34 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.174.147 with HTTP; Mon, 5 Jun 2017 08:45:32 -0700 (PDT) In-Reply-To: References: From: Dave Page Date: Mon, 5 Jun 2017 16:45:32 +0100 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Shirley Wang Cc: Akshay Joshi , pgadmin-hackers , Robert Eckhardt Content-Type: multipart/alternative; boundary="001a113a8bc8c47a810551386405" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgadmin-hackers Precedence: bulk Sender: pgadmin-hackers-owner@postgresql.org --001a113a8bc8c47a810551386405 Content-Type: text/plain; charset="UTF-8" Hi On Mon, Jun 5, 2017 at 4:34 PM, Shirley Wang wrote: > Hi! > > Here's a summary of the interviews thus far: > > DBAs tend to create a partition strategy based on their experience and > some alignment on their end users goals (analysts, report writers, and app > developers). Once that partition strategy is created, they are usually > forced to iterate on that strategy later based on feedback from end users > of what the usage pattern are. > > We've identified a couple workflows that are key in partitioning based on > whether they are maintaining a successful strategy or iterating to improve > the strategy. > > One workflow is for rollups, which is for maintaining partitions at > different granularities as data ages. We've learned that older data is less > acted upon than recent data so users group together older data for viewing > purposes. The other workflow is for splits, which when users discover that > the data isn't granular enough so a single partition is being leveraged too > many times. Users need to then reevaluate their strategy and tune > partitions. > > To reevaluate strategies, DBAs ask themselves a few questions > - Is the partition stable? > - Are the queries analysts, report writers, and app developers are writing > getting the correct data? > - Are the partitions organized in a way that analysts, report writers, and > app developers are able to achieve their goals? (ex. goals for app > developer might be fast query while goal for report writer might be ability > to get data so they can turn out reports faster. Goals might be conflicting) > > There are two needs from DBAs in terms of tuning partitioning strategies > (there are more but addressing these two will provide the most value to > users). One is to modify one or more child partitions by adding indexes or > other such things, and the other is to recreate the parent table because > there is inheritance to consider. > The former is what I was bleating about when I said we needed to expose partitions to the user. The latter isn't relevant - declarative partitioning in Postgres doesn't use inheritance. So... it sounds like we're on the right lines :-) > > For the former, this can be addressed by enabling users to modify one or > more child partitions at the same time. For the latter, that is a workflow > that might be addressed outside of the create table with partition workflow > we're working on currently. > > > > > > On Mon, Jun 5, 2017 at 5:21 AM Dave Page wrote: > >> On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi < >> akshay.joshi@enterprisedb.com> wrote: >> >>> Hi All >>> >>> Following are the further implementation updates to support Declarative >>> Partitioning: >>> >>> - Show all the existing partitions of the parent table in Partitions >>> tab (Refer Existing_Partitions.png) >>> - Ability to create N partitions and detach existing partitions. >>> Refer (Create_Detach_Partition.png), in this example I have detach >>> two existing partition and create two new partitions. >>> - Added "Detach Partition" menu to partitions node only and user >>> will be able to detach from there as well. Refer (Detach.png) >>> >>> That's looking good to me :-) >> >> >> >>> >>> >>> On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt >>> wrote: >>> >>>> >>>> >>>> On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi < >>>> akshay.joshi@enterprisedb.com> wrote: >>>> >>>>> >>>>> 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. >>>>> >>>> >>>> Thank you for the example, you are absolutely correct and we were >>>> confused. >>>> >>>> Given our new found understanding do you mind if we iterate a bit on >>>> the UI/UX? What we were suggesting with the daily/monthly/yearly drop down >>>> was a specific example of an expression. Given that fact that doesn't seem >>>> to be required in an MVP, however, I do think a more interactive experience >>>> between the definition of the child partitions and the creation of the >>>> partitions would be optimal. >>>> >>>> I'm not sure where you are with respect to implementing the UI but I'd >>>> love to float some ideas and mock ups past you. >>>> >>>> -- Rob >>>> >>> >>> >>> >>> -- >>> *Akshay Joshi* >>> *Principal Software Engineer * >>> >>> >>> >>> *Phone: +91 20-3058-9517 <+91%2020%203058%209517>Mobile: +91 >>> 976-788-8246 <+91%2097678%2088246>* >>> >>> >>> -- >>> Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgadmin-hackers >>> >>> >> >> >> -- >> Dave Page >> >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --001a113a8bc8c47a810551386405 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Mon, Jun 5, 2017 at 4:34 PM, Shirley Wang <swang@pivotal.io>= ; wrote:
Hi!
=
Here's a summary of the interviews thus far:
<= br>
DBAs tend to create a partition strategy based on their exper= ience and some alignment on their end users goals (analysts, report writers= , and app developers). Once that partition strategy is created, they are us= ually forced to iterate on that strategy later based on feedback from end u= sers of what the usage pattern are.

We've = identified a couple workflows that are key in partitioning based on whether= they are maintaining a successful strategy or iterating to improve the str= ategy.

One workflow is for rollups, which is for m= aintaining partitions at different granularities as data ages. We've le= arned that older data is less acted upon than recent data so users group to= gether older data for viewing purposes. The other workflow is for splits, w= hich when users discover that the data isn't granular enough so a singl= e partition is being leveraged too many times. Users need to then reevaluat= e their strategy and tune partitions.=C2=A0

To ree= valuate strategies, DBAs ask themselves a few questions
- Is the = partition stable?=C2=A0
- Are the queries analysts, report writer= s, and app developers are writing getting the correct data?
- Are= the partitions organized in a way that analysts, report writers, and app d= evelopers are able to achieve their goals? (ex. goals for app developer mig= ht be fast query while goal for report writer might be ability to get data = so they can turn out reports faster. Goals might be conflicting)
=
There are two needs from DBAs in terms of tuning partitionin= g strategies (there are more but addressing these two will provide the most= value to users). One is to modify one or more child partitions by adding i= ndexes or other such things, and the other is to recreate the parent table = because there is inheritance to consider.=C2=A0

The former is what I was bleating about when I said we nee= ded to expose partitions to the user. The latter isn't relevant - decla= rative partitioning in Postgres doesn't use inheritance.

=
So... it sounds like we're on the right lines :-)
= =C2=A0

=
For the former, this can be addressed by enabling users to modify one = or more child partitions at the same time. For the latter, that is a workfl= ow that might be addressed outside of the create table with partition workf= low we're working on currently.=C2=A0





On Mon, Jun 5, 2017 at 5= :21 AM Dave Page <dpage@pgadmin.org> wrote:
<= div dir=3D"ltr">
On Fr= i, Jun 2, 2017 at 9:01 AM, Akshay Joshi <akshay.joshi@enterpri= sedb.com> wrote:
Hi All=C2=A0

Following are the further imple= mentation updates to support Declarative Partitioning:
  • Sh= ow all the existing partitions of the parent table in Partitions tab (Refer= Existing_Partitions.png)
  • Ability to create N parti= tions and detach existing partitions. Refer (Create_Detach_Partition.png), in this example I have detach two existing partition and cr= eate two new partitions.
  • Added "Detach Partition" menu to= partitions node only and user will be able to detach from there as well. R= efer (Detach.png) =C2=A0=C2=A0
That's looking good to me :-)

=C2=A0


On Wed, May 24, 2017 at 8:00= PM, Robert Eckhardt <reckhardt@pivotal.io> wrote:


On Wed, May 24, 2017 at 3:35 AM= , Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:

= =C2=A0 =C2=A0Taking average of two columns is just an example/representatio= n of expression, there is no use case of that. As I am also in learning pha= se. Below are some use case that I can think of:
  • Partitions based=
 on first letter of their 
    CREATE TABLE users =
(
        id      =
       serial not null,
        username       text not nul=
l,
        password       text,
        created_on     timestamptz not null,
        last_logged_on timestamptz not null
    )PARTITION BY RANGE ( lower( left( username, 1 ) ) );
    <=
/font>    CREATE TABLE users_0
    =
        partition of users (id, primary=
 key (id), unique (username))
        for value=
s from =
('a') to ('=
;g');
    CREATE TABLE users_1
        partition of users (id, primary key (id), unique (username))
        for values from ('g') to (unbounded=
);
    <= /li>
  • =C2=A0Partition based on country's sale for each month of an ye= ar.
CREATE = TABLE public.sales
(
=C2=A0 =C2=A0 country text NOT NULL,
=
=C2=A0 = =C2=A0 sales bigint NOT NULL,
=C2=A0 =C2=A0 saledate date
=
) PARTITION BY RANGE (country, (extract (YE= AR FROM saledate= )), (extract(MONTH FROM saledate)))

<= div class=3D"gmail_quote">
CREATE TABLE public.sale_usa_2017_jan PARTITION OF sales
=
=C2=A0 =C2=A0 FO= R VALUES FROM ('usa', 2017, 01) TO ('usa', 2017, 02);
=
CR= EATE TABLE public.sale_= india_2017_jan PARTITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('india',= 2017, 01) TO ('india', 2017, 02);
CREATE TABLE public.sale_uk_2017_jan PARTITION OF sales
=
= =C2=A0 =C2=A0 FOR VALUES FROM ('uk', 2017, 01) TO ('uk', 20= 17, 02);

INSERT INTO sales VALUES ('india', 10000, = 9;2017-1-15');
INSERT INTO sales VALUES ('uk', 20000, '2= 017-1-08');
INSERT INTO sales VALUES ('usa', 30000, '201= 7-1-10');

=C2= =A0 =C2=A0Apart from above there may be N number of use cases that depends = on specific requirement of user.=C2=A0

Thank you for the example, you are absolutely correct and we = were confused.=C2=A0

Given our new found understan= ding do you mind if we iterate a bit on the UI/UX?=C2=A0 What we were sugge= sting with the daily/monthly/yearly drop down was a specific example of an = expression. Given that fact that doesn't seem to be required in an MVP,= however, I do think a more interactive experience between the definition o= f the child partitions and the creation of the partitions would be optimal.= =C2=A0

I'm not sure where you are with respect= to implementing the UI but I'd love to float some ideas and mock ups p= ast you.=C2=A0

-- Rob



-= -
Akshay Joshi<= /span>
Principal Software Engineer=C2=A0


--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-ha= ckers

=



--
Dave Page

Blog: http://pgsnake.blogspot.com
Twitter:= @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
Th= e Enterprise PostgreSQL Company



--
=
Dave Page=
Blog: http://= pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprised= b.com
The Enterprise PostgreSQL Company
--001a113a8bc8c47a810551386405--