Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4

Поиск
Список
Период
Сортировка
От Akshay Joshi
Тема Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Дата
Msg-id CANxoLDegWFzkbUi=8KSL-3cPb0masCjD1HwxaMDhV6fs2uOObw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4  (Robert Eckhardt <reckhardt@pivotal.io>)
Ответы Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4  (Robert Eckhardt <reckhardt@pivotal.io>)
Список pgadmin-hackers
Дерево обсуждения
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 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-9517
Mobile: +91 976-788-8246

В списке pgadmin-hackers по дате отправления:

Предыдущее
От: Akshay Joshi
Дата:
Сообщение: Re: [pgadmin-hackers] Declarative partitioning in pgAdmin4
Следующее
От: Neel Patel
Дата:
Сообщение: Re: [pgadmin-hackers] [pgAdmin4][runtime][patch]: RM#2398 - Proxy notbypassed for embedded server in runtime on Windows