Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dDQqC-0006FG-U4 for pgadmin-hackers@arkaria.postgresql.org; Wed, 24 May 2017 07:36:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dDQqC-0003HH-7r for pgadmin-hackers@arkaria.postgresql.org; Wed, 24 May 2017 07:36:20 +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 1dDQpw-0002QO-I4 for pgadmin-hackers@postgresql.org; Wed, 24 May 2017 07:36:04 +0000 Received: from mail-pg0-x234.google.com ([2607:f8b0:400e:c05::234]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dDQpt-0008SO-Jd for pgadmin-hackers@postgresql.org; Wed, 24 May 2017 07:36:03 +0000 Received: by mail-pg0-x234.google.com with SMTP id x64so61657701pgd.3 for ; Wed, 24 May 2017 00:36:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=xexTKdLNmDyZhlqZY6nVt2MyZOlTbLz3pgUGL3FpvFE=; b=UHT+7VTn4P7bovR5KytH61RC8+goXrKFE2LR9Wy/Vt6PEoOTgw+/6yN5g65zqY04K4 u+URMfsvu1fddWgcr0OY0VMtZuh8PQtfKhW3zzp/tsYNe20+ifMZtBthRVnSxWXsOstA y+8r0JbgggOiPmpRYSSFz6Cp+SItnfK21VVErdfAy+zATvonjVS7E9VOuq0Qg//xi/zX WPf9xX4Q660sTxJdIJizQG4uZnSAaTE4lvJz7FHZFvfJ0si8mb6fPIplpP9TRcbMPZxZ vnzH3o3U7dJFNj5tpQ7bBSMZBVIF2yVnZAWY/66E0GlOTU2EfW/YoJEYgp209pjfZnRg e8Wg== 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=xexTKdLNmDyZhlqZY6nVt2MyZOlTbLz3pgUGL3FpvFE=; b=AjAehLwmKUd1ZNwNVqpZ3n10ClhtmxD9EnTJMWlPKqNu/pzH3sSDTm05ENVGwxFk9Z XyEfUst4veyGNMz6vAVrXERdNDrfQ2QC8dbj25Pa7cixfDu7j7G48dNUi2SSmmjYIK93 eXfJ6NB+bXiF4Mb1kBDIOAEVEHhdjCHdFxoP65d1mJg3d410n/odKXh9opiYWdul14Kf Co4vyp2url3c4sCgUTm1W+2qXOy4pZvBWNNAkVA4yikkrJBLSLV4yUxWeQztnJGsXzQ8 4zRZWJAnUOGmw5gmhJ3b8itxe19LkRw0cV70pM4UzkHr6EGc2I2aOBzxBbzftjAuWbwN 5Log== X-Gm-Message-State: AODbwcBzWTG6vKAtTiviU3Sg9DxANkcqABqEmA3GIKJcxg6JvHjlOvoS XgJQy/sUHhCg43PXG8wD5x7AGaqDA5fu X-Received: by 10.99.127.89 with SMTP id p25mr37147593pgn.92.1495611360419; Wed, 24 May 2017 00:36:00 -0700 (PDT) MIME-Version: 1.0 Received: by 10.100.170.75 with HTTP; Wed, 24 May 2017 00:35:59 -0700 (PDT) In-Reply-To: References: From: Akshay Joshi Date: Wed, 24 May 2017 13:05:59 +0530 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Robert Eckhardt Cc: Shirley Wang , pgadmin-hackers Content-Type: multipart/alternative; boundary="94eb2c1b6cb2dc3a8905504027b5" 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 --94eb2c1b6cb2dc3a8905504027b5 Content-Type: text/plain; charset="UTF-8" Hi Robert On Tue, May 23, 2017 at 8:09 PM, Robert Eckhardt wrote: > > > On Tue, May 23, 2017 at 10:09 AM, Shirley Wang 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* --94eb2c1b6cb2dc3a8905504027b5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Robert

On Tue, May 23, 2017 at 8:09 PM, Robert Eckhardt <reckhardt@pivotal.io> wrote:=


On Tue, May 23, 2017 at 10:= 09 AM, Shirley Wang <swang@pivotal.io> wrote:

It's possible to de= sign for the range and list partitions and know we can achieve success beca= use we understand how users would go through this workflow. Not sure about = expressions.

Maybe to pile on this a bit.=C2=A0

When Shirley a= nd I were discussing the workflows it was obvious when we were looking at &= #39;normal' range or list partition use cases. Generally the only open = question we had about the workflow was whether or not users would be buildi= ng 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.= =C2=A0

We couldn't think of a reason why a use= r would want to take the average of two columns and partition by this deriv= ed 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 exi= sting data.=C2=A0

I assume this was supported for = a specific use case. if you could share that it would be awesome. I guess t= he long and short of it is, we are having a difficult time imagining the wo= rkflow for this feature.

=C2=A0 =C2=A0Taking average of two columns is just an example/represe= ntation of expression, there is no use case of that. As I am also in learni= ng phase. Below are some use case that I can think of:
  • Partitions based on first letter of their= username<= /span>
    CR=
EATE TABLE users (
        id             serial not null,
        username       text not null,
        =
password       text,
        created_on     timestamptz not null,
     =
   last_logged_on timestamptz n=
ot null
    )PARTITION BY RANGE ( lower( left(<=
/span> username, 1 ) <=
span style=3D"color:rgb(102,204,102)">) );
    CREATE =
TABLE users_0
        partition of users (id, primary key (id)=
, unique (user=
name))
        for values from ('=
;a') to ('g');
    CREATE TABLE users_1
        partition of users (id, primary key (id), unique (username))
    <=
/font>    for values from (=
'g') to (unbounded)    ;
    • =
  • =C2=A0Partition based on country's sale for each month of an year.<= /li>
CREATE TABL= E 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 (YEAR FROM saledate)), (extract(MONTH FROM saledate)))

=
CREATE TABLE pub= lic.sale_usa_2017_jan PARTITION OF sales
=C2=A0 =C2=A0 FOR VALUES FROM ('usa', 201= 7, 01) TO ('usa', 2017, 02);
CREATE 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', 2017, 02)= ;
=

INSERT INTO sales VALUES ('india', 10000, '2017-1-= 15');
INSERT INTO sales VALUES ('uk', 20000, '2017-1-08&= #39;);
INSERT INTO sales VALUES ('usa', 30000, '2017-1-10= 9;);

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

-- Ro= b
=C2=A0




--
Akshay Joshi
<= span style=3D"color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px;= border-collapse:collapse">Principal Software Engineer=C2=A0


Phone: +91 20-3058-9= 517
Mobile: +91 976-788-8246
--94eb2c1b6cb2dc3a8905504027b5--