Practical Partitioning in Production with

Рет қаралды 21,297

Күн бұрын

Has your table become too large to handle? Have you thought about chopping it up into smaller pieces that are easier to query and maintain? What if it's in constant use?
An introduction to the problems that can arise and how PostgreSQL's partitioning features can help, followed by a real-world scenario of partitioning an existing huge table on a live system.
Talk from Postgres Vision 2021. Slides: vyruss.org/computing/slides/p...
0:00 Introduction
0:53 What is partitioning?
1:49 Partitioning in PostgreSQL
3:08 Declarative Partitioning
5:06 PostgreSQL limits
6:51 What partitioning can help with
10:48 What partitioning is not
12:03 Dimensioning
14:42 Partitioning method
16:45 Partition Key selection
19:35 Sub-partitioning
21:22 Partitioning by multiple columns
23:03 What Postgres does not do
24:21 Partitioning a live production system
25:11 The situation
26:21 Hidden performance pitfall
30:50 The plan
32:32 Create empty partitioned table & indices
32:48 Create partition for new incoming data
33:08 Attach old table as a partition
39:28 To conclude...

Пікірлер: 45

@NOPerative Жыл бұрын

32TB is one installation of Call of Duty and a few jpegs. LOL Good vid. Data partitioning is serious stuff and a concept that I don't see being addressed frequently, so good stuff.

@giampaolocapelli Жыл бұрын

hello, I enjoyed your talk yesterday at Fosdem 2023 in Bruxelles, thanks for the interesting resources

@cjbarroso Жыл бұрын

great video, very grateful for it, learnt a ton, thank you. Mandatory lecture for anyone wanting to learn about postgres partitioning.

@JimmyAngelakos Жыл бұрын

Thank you so much, I'm glad it was helpful.

@periclessevegnani 2 жыл бұрын

Everyday I fall more in love with Postgres ! Thank you for sharing this.

@JimmyAngelakos 2 жыл бұрын

Thank you!

@jirehla-ab1671 13 күн бұрын

@@JimmyAngelakoswould partitioning hep with a big table thats mostly handling random updates (OLTP) r.g an acccounts tables with many users updating the account balances

@JimmyAngelakos 5 күн бұрын

@@jirehla-ab1671 It depends on the size of the table and what you are trying to do. If you choose a partition key that allows you to perform efficient pruning then it should help with query performance.

@sridharramalingam329 10 ай бұрын

Very well explained and the example provided with details is going to help when handling huge tables for parititioning on an active DB. Thanks for this lecture, much appreciated.

@yousifmobark6981 Жыл бұрын

Really helpful material . Very Thankful for your efforts

@isarwarfp 2 жыл бұрын

Very nice way of execution, Asking questions => Answering them. Good Work :)

@AljazVidmar 9 ай бұрын

Very nice video. The only thing I was missing is some debate around Unique indexes or/and primary keys.

@user-lj6rz6rd2w Жыл бұрын

Wow!! Great explainations :)

@MrMikomi 6 ай бұрын

Great video. Thanks.

@robertyackobian8360 2 жыл бұрын

Very nice. I thought I know everything there is to know and you came with the md.c file..

@sanjaybhatikar 2 жыл бұрын

Very helpful, thank you!

@milequinze 2 жыл бұрын

Excelent, thank you a lot!

@prashantverma6415 10 ай бұрын

buddy i wanted to learn this.. thanks aton... as i once expericend this in kusto query lang..when i was new to kql and never heard of partition

@user--142 Ай бұрын

Thank you!

@Valsiphez Жыл бұрын

Slide 17. I think it should be values from ('2021-06-01', 'AAA') to ('2021-07-01', 'BAA'); as TO is exclusive; unless there is no actual AZZ for location_code

@JimmyAngelakos Жыл бұрын

Thanks for your comment; you are right that it is upper bound exclusive. In this case it doesn't really matter, as this is just meant to show a common misconception due to confusing syntax.

@JobinAugustine 2 жыл бұрын

Hi Jimmy, Great Presentation. But I have a doubt how "Move data to new table at our own pace" works. Because we are assuming that we are deleting from old table. However, that old table is just a partition of the new table now. Since no boundary overlaps are allowed, we cannot have another partition for the new table. So if we delete and insert as you explained in the talk, The data should go to the same old partition. right? Am I missing something here?

@vyruss000 2 жыл бұрын

Start a transaction, detach old table, do the move, reattach with new boundaries.

@petslavov Жыл бұрын

@@vyruss000 When you do the detach the main table will be locked with Access Exclusive lock - meaning everything will be locked. which means that you have to select veeeery small portions of the table for this to be fast and usable. Am I right ?

@jonzuka9746 7 ай бұрын

Hello! Instead of the mentioned hack, which requires a priviledged operation, a virtual view of the union of the old and new table can be desirable.

@fredericobraga85 Жыл бұрын

Thnaks for the content. I'm not sure I understood the ""Move data to new table at our own pace" . Why are we moving old data to the new partition table? isn't that data already part of the dailytotal_legacy partition?

@JimmyAngelakos Жыл бұрын

Hi, you might not want to leave the old data in one huge partition if there's a need to query it often, so it may need to be moved to smaller partitions.

@VikramKumarpage 11 ай бұрын

Thanks a lot Jimmy Angelakos. I have a question with below scenario One huge table work_history is having composite PK (wid and seq_no) Need to be range partitioned on column work_date. Now after partition , it's PK is composite on (wid , seq_no an work_date) which is creating issue for Referenced tables as few of those doesnot have work_date column. could you please suggest some alternate way, thanks.

@JimmyAngelakos 10 ай бұрын

You can't have UNIQUE constraints that are not part of the partitioning key. Perhaps since it's a history table and presumably gets only inserts with those values filled in from the application, you can drop the uniqueness constraint and have the partitioning key only be work_date. You can rebuild the necessary indexes as non-unique.

@VikramKumarpage 10 ай бұрын

@@JimmyAngelakos thankyou

@Mohit89ist Жыл бұрын

Thanks for the nice tutorial. One query, If i set partition by range(date). And i want to create partition only when i receive data for a particular date. How can i create it on the fly

@JimmyAngelakos 10 ай бұрын

Postgres doesn't support dynamic partition creation out of the box. You can create partitions in advance if you script it though, and you'll only need to do it once a year.

@dulithamahishka807 2 жыл бұрын

How would it work in the plan when you rename the table? Doesn't the application try to find the old table name?

@JimmyAngelakos 2 жыл бұрын

The renaming is transparent, you do it all in one transaction and attach the (renamed) old table as a partition of the new one.

@isarwarfp 2 жыл бұрын

I have little confusion in last step about anonymous query block. Does all that operation would be in single transaction BEGIN; 1. rename 2. create new table and indices 3. create new partition 4. attach old partition 5. anonymous stored procedure COMMIT; ?

@JimmyAngelakos 2 жыл бұрын

Hi Imran, yes, you can use an anonymous code block with DO inside a transaction block, provided you don't use transaction control statements inside the DO block.

@radoslawolech266 Жыл бұрын

Hi, partitioning table make pg_dump work on many jobs or just one job for this table?

@JimmyAngelakos Жыл бұрын

Hi, pg_dump is going to treat each partition as a separate table, so it should be able to parallelize fine with multiple jobs.

@user-nf4nh7jx6t Жыл бұрын

Joke about increasing values range for the gender column is priceless 😂

@jjlouis7762 Жыл бұрын

in a world where you dont want to attach the old table to the new tables using the method you did, Is there an alternative way ?

@JimmyAngelakos 10 ай бұрын

You could copy the table onto a partitioned table on another server with logical replication?

@thachvungoc4254 8 ай бұрын

A great video, i have some questions: 1. If I my query only select by id (or ids in multiple partition table), performance is good? Do You have a real estimate for this problem? 2. My unique key such as "name", cannot use when partition by create_date. how I resolve this problem 3. Have some another table foreign key into my table, if I create new partition table, it will miss some key? 4. I want to partition date with range base quarter, with old data how I add it to partitioned_table with right parititon. In the last step I need to detach before delete ?

@dianad150 29 күн бұрын

Should not recommend people to rename production table in business hours. They can lose their jobs.

@JimmyAngelakos 27 күн бұрын

It seems there may be a misunderstanding here: The customer specifically requested this partitioning operation due to operational concerns, and each step involved was discussed and approved. Just to clarify, nobody is recommending renaming tables in production as something you should generally do. Thanks for your comment.