Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dHuhN-0002hU-CF for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 16:17:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dHuhM-0002Tf-Lh for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 16:17:44 +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 1dHuh5-000233-VT for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 16:17:28 +0000 Received: from mail-ua0-x22a.google.com ([2607:f8b0:400c:c08::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dHuh1-0007lW-Hy for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 16:17:25 +0000 Received: by mail-ua0-x22a.google.com with SMTP id y4so78581515uay.2 for ; Mon, 05 Jun 2017 09:17:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pivotal-io.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=rncALq/oVdHMED2GGV3aXVGmFz4KsTWyUstLtHGqq+M=; b=pkuuqohipUvm/9Wj5Y8JKuatYxizX7Y/lZJMmKnH7ZhEz6xsFmsGQVmgYi3ArmwGx3 YdUwTFfeeOiIHosC8Ikp1X7iTXJ/3M+g3HUN/cfjci4FXO1fk/7GG7USRC48kUaZWh35 MtjE2jeZboQueXJ+DO+mKXQLKMH9QGI/ovLJwCsIhtDPgdK7YImHLtK4uinYcG1Um7jX ltql1tq4REhFbbKJbAjy786rATtNLAPJTd6HoRueV+0J+V9W4yVC0N7z32TlEWsEnmMm 9hD74kLTp6VdaSdy/rN+c6PeZgojmNqtCZX+76DlP6u2X78E8XzvZsAQa5YY+iCA/MBJ Vajg== 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=rncALq/oVdHMED2GGV3aXVGmFz4KsTWyUstLtHGqq+M=; b=HkygwIQsLdbSeBQJ5271a8aLx2INg+H/B7MWRe+dd53MycS6sWoW/xf0pxue2rEzrg HPRdtJNTxBenZJObsOHmvXcRau3iZd/LvRgpAIg2t6qTgxO5xLdOp2XFzGb+J5RNSL1o hY+plDfrfMZ2+/JjcdvXDm9fS+YqpbqkL2I6IdEtMf4CKRW8sRzJPYGw7VRgLu2zuF87 n4gfk1gbTFrYmVh7WLU1YiFNNTLHFc4+AHNKdL5++uTgkaFINNKag5BHzUyECIb0nP30 0rJ4KLl/H5bT1xQhHDDyKMvy6n70IUmJBBzDuINR+IBcNqpU4prBHsabYzKxLiCZWkyd 1Xew== X-Gm-Message-State: AODbwcBK1e5SdohBaT0MvX5lIlL7HHvZ/0CB+GSWCpu+aRgZRKU7W1Qz UoJJhj5Nlwp9M0wfREiOR+9BeACaSJBW X-Received: by 10.176.26.47 with SMTP id a47mr3329633uai.64.1496679442127; Mon, 05 Jun 2017 09:17:22 -0700 (PDT) MIME-Version: 1.0 Received: by 10.176.4.82 with HTTP; Mon, 5 Jun 2017 09:17:21 -0700 (PDT) In-Reply-To: References: From: Robert Eckhardt Date: Mon, 5 Jun 2017 12:17:21 -0400 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Dave Page Cc: Shirley Wang , Akshay Joshi , pgadmin-hackers Content-Type: multipart/alternative; boundary="f403043651407db398055138d6aa" X-Pg-Spam-Score: -1.9 (-) 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 --f403043651407db398055138d6aa Content-Type: text/plain; charset="UTF-8" On Mon, Jun 5, 2017 at 11:45 AM, Dave Page wrote: > > > 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. > The former is certainly the most interesting. We do need to expose the partitions but only exposing them individually might be a bit overwhelming. What we found was that the number of partitions users have, (given existing means of leveraging partitions) vary from ~100 up to 10k. Basically what we were thinking about was how we can create a workflow/interface that allows users to modify one or more children at once. Furthermore, it would be nice if we could figure out an easy (easy-ish) way for users to identify the one or more partitions that need to be modified. For roll up this pattern seems obvious, identify the n partitions you need/want to combine and then run a job to combine them. For other patterns such as creating indexes and such it requires a bit more thought. Generally users described wanting to treat all of the children like a single table (just like Oracle), however, other users described potentially modifying chunks of partitions differently depending on some criterion. This means that users will need to identify the subset they want to optimize and then ideally be able to act on them all at once. -- Rob > > 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 > --f403043651407db398055138d6aa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On M= on, Jun 5, 2017 at 11:45 AM, Dave Page <dpage@pgadmin.org> w= rote:


Th= e former is what I was bleating about when I said we needed to expose parti= tions to the user. The latter isn't relevant - declarative partitioning= in Postgres doesn't use inheritance.

The former is certainly the most interesting.=C2=A0 = We do need to expose the partitions but only exposing them individually mig= ht be a bit overwhelming. What we found was that the number of partitions u= sers have, (given existing means of leveraging partitions) vary from ~100 u= p to 10k. Basically what we were thinking about was how we can create a wor= kflow/interface that allows users to modify one or more children at once. F= urthermore, it would be nice if we could figure out an easy (easy-ish) way = for users to identify the one or more partitions that need to be modified.= =C2=A0

For roll up this pattern seems obvious, ide= ntify the n partitions you need/want to combine and then run a job to combi= ne them.=C2=A0

For other patterns such as creating= indexes and such it requires a bit more thought. Generally users described= wanting to treat all of the children like a single table (just like Oracle= ), however, other users described potentially modifying chunks of partition= s differently depending on some criterion. This means that users will need = to identify the subset they want to optimize and then ideally be able to ac= t on them all at once.=C2=A0

-- Rob

=



=C2=A0

So... it sounds like we're on t= he 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:
On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi &l= t;akshay= .joshi@enterprisedb.com> wrote:
Hi All=C2=A0

Following are th= e further implementation updates to support Declarative Partitioning:
=
  • Show all the existing partitions of the parent table in Partit= ions 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 t= wo existing partition and create two new partitions.
  • Added "De= tach Partition" menu to partitions node only and user will be able to = detach from there as well. Refer (Detach.png) =C2=A0=C2=A0
That's looking good to me :-)

=C2=A0
<= /div>


On Wed, May 24, 2017 at 8:00 PM, Robert Eckhardt <reckhard= t@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/repr= esentation of expression, there is no use case of that. As I am also in lea= rning phase. Below are some use case that I can think of:
    Partitions based on first letter of th=
eir username
    CREATE TABLE users (
        id             se=
rial 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 (usern=
ame))
        for values from ('=
;a') to ('g');
    CREATE TABLE users_1
        partition of users (id, primary key (id), unique (us=
ername))
        for values <=
span style=3D"color:rgb(153,51,51);font-weight:bold">from (=
9;g') to (unbounded)<=
/span>;
  • =C2=A0Partition based on country&= #39;s sale for each month of an year.
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<= /div>
) PARTITION BY RANGE (country, (extract (YEAR FROM = saledate)), (extract(MONTH FROM saledate= )))

CREATE TABLE public.sale_usa_201= 7_jan PARTITION OF sales
=C2=A0 =C2=A0 FOR V= ALUES FROM ('usa', 2017, 01) TO ('usa', 2017, 02);
CREAT= E TABLE public.sale_india_2017_jan PARTITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('i= ndia', 2017, 01) TO ('india', 2017, 02);
CREATE TABLE public= .sale_uk_2017_jan P= ARTITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('uk', 2017, 01= ) TO ('uk', 2017, 02);

INSERT INTO sales VALUES ('in= dia', 10000, '2017-1-15');
INSERT INTO sales VALUES ('uk= ', 20000, '2017-1-08');
INSERT INTO sales VALUES ('usa&#= 39;, 30000, '2017-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 absolu= tely correct and we were confused.=C2=A0

Given our= new found understanding do you mind if we iterate a bit on the UI/UX?=C2= =A0 What we were suggesting with the daily/monthly/yearly drop down was a s= pecific 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 be= tween the definition of the child partitions and the creation of the partit= ions would be optimal.=C2=A0

I'm not sure wher= e you are with respect to implementing the UI but I'd love to float som= e ideas and mock ups past you.=C2=A0

-- Rob
<= /div>


-= -
Akshay Joshi
Principal Software Engin= eer=C2=A0

<= b>



--
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
<= /div>
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Post= greSQL Company



--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
=
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--f403043651407db398055138d6aa--