I have users table where a row can be marked for future deletion, so I'm looking for the best way to do this.

I've thought of two options so far:

  1. Add a delete_date to the users table.

    Every night a process will query the table and get all users that match delete_date with current date and delete them.

  2. Create a users_to_be_deleted table with the id of the row in users table to be deleted along with a delete_date.

    Every night a process will query users table and join on users_to_be_deleted in order to identify which users should be deleted (matching delete_date with current date), and then delete them.

Basically, Option 1 adds a column that can (and in most cases will) contain NULL marks, while Option 2 has no columns that accept NULLs at all, but will require an additional JOIN.

Considering that most users will not be marked for deletion (an estimate is that only 10-20% will), which option is the most efficient? What are the pros and cons of each option?

  • 1
    This is a perfect chance to use a partial / functional index. Create the field "tobedeleted" etc and index on that as "create index on table (id) where tobedeleted is not null" – Scott Marlowe Sep 8 '17 at 16:12
  • When considering the differences, note that the time to update/insert during the day should be weighed appropriately against an off-hours process; generally, time to complete during business hours is much more significant than whatever amount of time is necessary off-hours. – RDFozz Sep 8 '17 at 16:35
  • I assume that there are no FOREIGN KEY issues here. If I were you, I'd fire a TRIGGER for the ON DELETE step - just delete the user record for the active table and move it to a table called deleted_users with a date and time of deletion as well as the deleting programme operative. – Vérace Sep 8 '17 at 17:47
  • @ScottMarlowe Sounds pretty good, it will make option 1 more appealing. I'll give it a shot. Thank you! – Gerry Sep 8 '17 at 17:59
  • @RDFozz In this scenario both actions (mark to deletion and deleting the row) will be executed during off-hours; in fact, both actions take place within a batch process. – Gerry Sep 8 '17 at 18:01

Normal guiding principles are to,

  1. Not delete data. It's easy to put a boolean flag on the data deleted and then for it to seem like the data is deleted to users, without it really being deleted. Do you really want the data gone forever?
  2. Not create 1:1 relationships. If it's 1:1 just merge it together.
  3. "Match delete_date with current date and delete them" I would suggest storing the delete_queue_date instead. The date that the data entered the queue. That's what matters. The date the row actually gets deleted is a function of that. Generally you don't store f(x) on the table, you store x.
| improve this answer | |
  • So, this principles point to avoid that extra table, that is, option 1. Are there any drawbacks i should be aware of? (BTW, i do want the data deleted forever). – Gerry Sep 8 '17 at 17:58
  • Nope, though you may want to read about table packing with PostgreSQL if you're worried about space. You can also store it in a boolean, and the previous suggestion to use a partial index could be valid too, depending on how often you run the reaper. – Evan Carroll Sep 8 '17 at 18:01
  • 1
    Currently space is not an issue (but, in the future it could be, so ill refer to table packing). How could it be stored as a boolean? I will be great, but a date is needed to identify if the row must be deleted (BTW, at any time the recored could be removed from deletion, if it is done before being deleted). – Gerry Sep 8 '17 at 18:10
  • Oh my bad, that's right you're storing the tz because it has sit in queue for a set amount of time, forgot. Scratch that. Normally, I just store a flag. Different workloads. – Evan Carroll Sep 8 '17 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.