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.
@@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
@@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.
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.
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 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 ?
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
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.
Hey Jimmy, great talk! I have just one question, is it possible to automate this with pg_partman and pg_cron. Create one current partition and one for the next month, if we're partitioning by month and then remove the default partition which pg_partment will create and attach our legacy one? Have you done something like this?
You don't really need pg_partman for anything, or pg_cron for that matter. You can just automate the partition management directly via cron. (I hate dependencies ;) )
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.
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.
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?
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.
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; ?
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.
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
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.
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 ?
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.
![Changing your huge table's data types in production [#FOSDEM 2021] #PostgreSQL devroom #postgres](http://i.ytimg.com/vi/aVty0_qcPzM/mqdefault.jpg)
![Changing your huge table's data types in production [#FOSDEM 2021] #PostgreSQL devroom #postgres](/img/tr.png)
![Don't make these mistakes with #PostgreSQL! [Don't Do This @ #FOSDEM 2023]](http://i.ytimg.com/vi/vxuPW4Ottrk/mqdefault.jpg)
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.
hello, I enjoyed your talk yesterday at Fosdem 2023 in Bruxelles, thanks for the interesting resources
Everyday I fall more in love with Postgres !
Thank you for sharing this.
Thank you!
@@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
@@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.
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.
Very useful. Thank you so much!
great video, very grateful for it, learnt a ton, thank you. Mandatory lecture for anyone wanting to learn about postgres partitioning.
Thank you so much, I'm glad it was helpful.
Very nice video. The only thing I was missing is some debate around Unique indexes or/and primary keys.
Really helpful material .
Very Thankful for your efforts
Very nice way of execution, Asking questions => Answering them.
Good Work :)
Wow!! Great explainations :)
Great video. Thanks.
Very nice. I thought I know everything there is to know and you came with the md.c file..
Very helpful, thank you!
Thank you!
Excelent, thank you a lot!
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?
Start a transaction, detach old table, do the move, reattach with new boundaries.
@@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 ?
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
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.
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
Hey Jimmy, great talk! I have just one question, is it possible to automate this with pg_partman and pg_cron. Create one current partition and one for the next month, if we're partitioning by month and then remove the default partition which pg_partment will create and attach our legacy one? Have you done something like this?
You don't really need pg_partman for anything, or pg_cron for that matter. You can just automate the partition management directly via cron. (I hate dependencies ;) )
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.
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.
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.
@@JimmyAngelakos thankyou
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?
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.
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;
?
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.
Hi, partitioning table make pg_dump work on many jobs or just one job for this table?
Hi, pg_dump is going to treat each partition as a separate table, so it should be able to parallelize fine with multiple jobs.
How would it work in the plan when you rename the table? Doesn't the application try to find the old table name?
The renaming is transparent, you do it all in one transaction and attach the (renamed) old table as a partition of the new one.
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
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.
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 ?
You could copy the table onto a partitioned table on another server with logical replication?
Joke about increasing values range for the gender column is priceless 😂
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 ?
Should not recommend people to rename production table in business hours. They can lose their jobs.
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.
Citus is better