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 ;) )
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.
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.
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.
