El vie., 27 mar. 2020 a las 15:41, Michael Lewis (<mlewis@entrata.com>) escribió:
If you can afford the time, I am not sure the reason for the question. Just run it and be done with it, yes?
I've been working with other RDBMS all of my life and I'm quite new to PG world, and I'm learning to do things when I need to do them so I'm trying to learn them in the right way :D
Also, for what I'm seeing in other projects, this is going to be a problem in most of them (if it's not yet a problem), and it's going to be me the one that solves it so again I'm in the path of learning to do this kind of things in the right way.
A couple of thoughts-
1) That is a big big transaction if you are doing all the cleanup in a single function call. Will this be a production system that is still online for this archiving? Having a plpgsql function that encapsulates the work seems fine, but I would limit the work to a month at a time or something and call the function repeatedly. Get the min month where records exist still, delete everything matching that, return. Rinse, repeat.
Ok, the function provided it's just a first approach. I was planning to add parameters to make dates more flexible.
2) If you are deleting/moving most of the table (91 of 150 million), consider moving only the records you are keeping to a new table, renaming old table, and renaming new table back to original name. Then you can do what you want to shift the data in the old table and delete it.
I was aware of this solution but I've read it's not side effect free. As my tables don't have any kind of FK-PK only the sequences for the serial columns, would this be a safe way to do what I want?
