Re: Declarative partitioning in pgAdmin4

Hi

On Mon, Jun 5, 2017 at 4:34 PM, Shirley Wang <swang(at)pivotal(dot)io> 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 <dpage(at)pgadmin(dot)org> wrote:
>
>> On Fri, Jun 2, 2017 at 9:01 AM, Akshay Joshi <
>> akshay(dot)joshi(at)enterprisedb(dot)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 <reckhardt(at)pivotal(dot)io>
>>> wrote:
>>>
>>>>
>>>>
>>>> On Wed, May 24, 2017 at 3:35 AM, Akshay Joshi <
>>>> akshay(dot)joshi(at)enterprisedb(dot)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(at)postgresql(dot)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