Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dHu2C-00068o-24 for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 15:35:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dHu2A-0007JM-HA for pgadmin-hackers@arkaria.postgresql.org; Mon, 05 Jun 2017 15:35:10 +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 1dHu29-0007Hj-Ft for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 15:35:09 +0000 Received: from mail-oi0-x235.google.com ([2607:f8b0:4003:c06::235]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dHu24-0006tU-Jj for pgadmin-hackers@postgresql.org; Mon, 05 Jun 2017 15:35:06 +0000 Received: by mail-oi0-x235.google.com with SMTP id l18so162240122oig.2 for ; Mon, 05 Jun 2017 08:35:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pivotal-io.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=rPiNha4/7KKPNzg3JO+bsZDoJL/jKMahgd0smr4BlVk=; b=Nu5zXYCtbC+CijO9RSv7hYMzSl+L9ny+g2mwZOZEZGvpDBKIBGtt/v8fSoVKnizzzf 7f3O1hp4NQgNiZ6RM1JeUW/hds6zFbf1anEM4OShjKW0harGx3Dz/QBJaH37fTjT/1F2 s3s10zvUWbkGySuea33CUMOyNFR8X1a3i65Cs5e4BzisAEUGxstyb4Tq9qRfylfXenB9 PnAC8lFFFsYNiY/DTvEM9b6FmM+p9NvW82abuXk1ch6jQcuWlYeQqBn4r1N29OS53UMz xVyarScRc1JWMTRglaZchLbwqyKGORvG/wIxSg4xqDCvjYoaPw0+IfibB5u0TzGcWjKT FOAw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=rPiNha4/7KKPNzg3JO+bsZDoJL/jKMahgd0smr4BlVk=; b=jSaMSrMDaqTHwxMFpPHEuX0PUAqQfajSzpGHstnhDCilByPkciWM4fbORCPVT6FV61 UEWgIv1WACxaV+LytVaNoIr4N+gYIlmErQAZThe+s2P6OjTwIaqzdwMONqxBzrodj1uL um+plthQpBrXjUFKlh9g832HtwV6oKebg5szGJcZLfaxyIV+gbA3uurmJhOYeCNWvUzp daprhajvMQ9BGFqNavO8XW82B2xVWVk1a0HChpG5JW9qjaunF+mC5JUCQY16OIfpi5ZB kMerzq0q/F/95hryL2EAzFlxhXwPQbpxXCMBiNVENq+M2WHHyDh8t76KopL5txCkJ4nR HVmg== X-Gm-Message-State: AODbwcDaNoCGLhId/RQtMa6nmJZYXber1HaIDNY8XN6jllgNx0e/tuok zPSd1GK/6BgS1fjJjnJV2+Z4FiZ8MEQE X-Received: by 10.202.62.70 with SMTP id l67mr11566206oia.46.1496676902467; Mon, 05 Jun 2017 08:35:02 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Shirley Wang Date: Mon, 05 Jun 2017 15:34:51 +0000 Message-ID: Subject: Re: Declarative partitioning in pgAdmin4 To: Dave Page , Akshay Joshi Cc: pgadmin-hackers , Robert Eckhardt Content-Type: multipart/alternative; boundary="001a113ce09a1d97ad0551383fe4" 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 --001a113ce09a1d97ad0551383fe4 Content-Type: text/plain; charset="UTF-8" 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. 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 > --001a113ce09a1d97ad0551383fe4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi!

Here's a summary of the intervi= ews thus far:

DBAs tend to create a partition stra= tegy based on their experience and some alignment on their end users goals = (analysts, report writers, and app developers). Once that partition strateg= y is created, they are usually forced to iterate on that strategy later bas= ed on feedback from end users of what the usage pattern are.
=
We've identified a couple workflows that are key in part= itioning based on whether they are maintaining a successful strategy or ite= rating to improve the strategy.

One workflow is fo= r rollups, which is for maintaining partitions at different granularities a= s data ages. We've learned that older data is less acted upon than rece= nt 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 g= ranular enough so a single partition is being leveraged too many times. Use= rs need to then reevaluate their strategy and tune partitions.=C2=A0
<= div>
To reevaluate strategies, DBAs ask themselves a few ques= tions
- Is the partition stable?=C2=A0
- Are the querie= s analysts, report writers, and app developers are writing getting the corr= ect data?
- Are the partitions organized in a way that analysts, = report writers, and app developers are able to achieve their goals? (ex. go= als for app developer might be fast query while goal for report writer migh= t be ability to get data so they can turn out reports faster. Goals might b= e conflicting)

There are two needs from DBAs in te= rms 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 chi= ld partitions by adding indexes or other such things, and the other is to r= ecreate the parent table because there is inheritance to consider.=C2=A0

For the former, this can be addressed by enabling us= ers 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 wi= th partition workflow we're working on currently.=C2=A0

<= /div>




On Mon, Jun 5, 2017 at 5:21 AM Dave Page <<= a href=3D"mailto:dpage@pgadmin.org">dpage@pgadmin.org> wrote:
On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi = <akshay.joshi@enterprisedb.com> wrote:
Hi All=C2=A0

F= ollowing are the further implementation updates to support Declarative Part= itioning:
  • 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_Partitio= n.png)= , in this example I have detach two existing partition and create two new p= artitions.
  • Added "Detach Partition" menu to partitions no= de only and user will be able to detach from there as well. Refer (Detach.<= span id=3D"m_6013502630113650474m_8355360353654886953:2uc.3">png) = =C2=A0=C2=A0
That= 9;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> wr= ote:

=C2=A0 = =C2=A0Taking average of two columns is just an example/representation of ex= pression, there is no use case of that. As I am also in learning phase. Bel= ow 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,
        crea=
ted_on     timestamptz not null,
        last_=
logged_on timestamptz not null
    )PARTITION =
BY RANGE ( lower(<=
/span> left( u=
sername, 1 ) ) );
    CREATE TAB=
LE 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<=
/span> key (id), unique (username))
    =
    for values from ('g') to (unbounded);
  • =C2=A0Partition based on country's sale = for each month of an year.
CREATE TABLE public.sales
(
=C2=A0 =C2=A0 country tex= t 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 public.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<= /span> PARTITION OF sales
<= div class=3D"gmail_quote">
=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');
=
INSERT INTO sales VALUES (= 'usa', 30000, '2017-1-10');

=C2=A0 =C2=A0Apart from above there may be N num= ber of use cases that depends on specific requirement of user.=C2=A0
<= /blockquote>

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

Given our new found understanding do you mind if we iterate a bit on the U= I/UX?=C2=A0 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 exper= ience between the definition of the child partitions and the creation of th= e partitions would be optimal.=C2=A0

I'm not s= ure where you are with respect to implementing the UI but I'd love to f= loat some ideas and mock ups past you.=C2=A0

-- Ro= b



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


--
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

B= log: http://pgsna= ke.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com=
The Enterprise PostgreSQL Company
--001a113ce09a1d97ad0551383fe4--