Update: I must admit that the example I’ve chosen is not the clearest because it really strongly suggests the solution with table partitioning. I’ve updated text a little bit to make it more clear that the deletion condition could be ad-hoc, to focus on replication.
Well, for a generic problem, there is a generic solution. Solving this problem in a specific implementation may take different approaches for different engines and datasets. If using MSSQL, the SWITCH command may be an option; in Oracle, there is an ALTER TABLE...MOVE. if the number of rows to delete is actually bigger than the ones to keep, creating a new table may help; At least in Pgsql, temporary dropping indexes will also improve performance on extensive delete clauses; There is also the possibility of using a WITH clause to perform a mass deletion, that will accelerate the process if the delete condition is arbitrarily complex, and probably many other options I'm not aware of to perform this task :)
A pattern I use often is to encapsulate read operations on tables on views; this allows the definition of the underlying tables to be changed as desired, including migrating them to partitioned tables, point them to new tables or event to different schemas or bd's. At some point, the deletion capability needs to be also aligned with the client software implementation.
I try to make each post somewhat open-ended: there are always multiple possible follow-ups that together allow better understanding of the design space, especially in terms of concerns and driving forces.
Here of course the thing is not only about optimizing deletion, but more importantly about the idea of propagation delay, that could then be applied to updating and inserting data, for example; or about dealing with the lagging replicas; or about reducing the unintended atomicity; or about backpressure in distributed database systems.
Different techniques that you describe are certainly interesting, but I'm trying to make it less dependent on specific database server functionality (which, of course, sometimes is very interesting and novel).
I'm sorry if my initial comment came off as a bit snarky, that was not the intention. I did enjoy your post and found it insightful. However, you cannot discuss atomicity and replication (or basically say "table will be locked during delete") without discussing specific database server functionality. Picking up on that specific example, on PG table lock is easily avoided by rewriting it as "DELETE FROM tblFoobar
WHERE id in (SELECT id from tblFoobar WHERE created_at < ‘2018-01-01’);", or by using the previous mentioned WITH approach;
When discussing replication, there is a huge difference between the behavior of any DELETE statement with logical vs physical replication, so its just not "on a cluster, these are the problems". Each dbms has a specific behavior, so it is not possible to mention the underlying complexity of a given statement/operation without going into these details.
In the end, I don't think record deletion should be approached from a database perspective, but from an application perspective. Your application should be designed to perform the required deletions and make sure consistency is met - as an example, what happens when you delete a row that is being also read in a transaction with read uncommitted? What happens if another application thread is reading the same record (or just finished it and is doing something with the data) while you are deleting it? What happens on a clustered application when you delete a row? How is row visibility propagated throughout the different nodes? In which standard replication modes? How can you avoid stale reads on a slave? How can you avoid a dirty/stale read on a master? All of these points/issues are intimately tied with your application design and even your tech stack (orm's, etc), not the database by itself. Sure, in the end, there are commands being executed on the db, and depending on the dbms, engine, replication setup, consistency level, etc. they may execute faster or slower. But the db behaviour in each of these scenarios is usually pretty well documented, and extensively tested for race conditions and stale reads and whatnot. Client applications deleting data usually aren't.
> Picking up on that specific example, on PG table lock is easily avoided by rewriting it as "DELETE FROM tblFoobar
> WHERE id in (SELECT id from tblFoobar WHERE created_at < ‘2018-01-01’);",
wow, that's some news for me. I must admit that last time I did serious work with Postgresql was in 2004 or something. I'm not sure if I have a clear picture of why it avoids the locking problem.
Does it execute the subquery completely, unlock the index and then execute DELETE? I kinda thought that it would do both subquery and DELETE in lockstep? Is this behavour guaranteed or it can change across major versions?
It sets the lock mode for the DELETE operation to a more granular mode (in this case, row locking). Also, SELECT FOR UPDATE before a DELETE will probably also work, as well as setting locking modes explicitly, or using a WITH expression (generates a temp table).
1.create partitioned table with the desired expiring criteria;2. Drop partitions as needed.
There is a hidden caveat here, at least with MySQL. You can't relate to partitioned tables with foreign keys.
Update: I must admit that the example I’ve chosen is not the clearest because it really strongly suggests the solution with table partitioning. I’ve updated text a little bit to make it more clear that the deletion condition could be ad-hoc, to focus on replication.
Thank you!
Yes, certainly, it would work in many case. At the same time, with the "create partitioned table" we're back on square one.
Also, you wouldn't create a partitioned table on every ad-hoc criteria you may need at some point.
Well, for a generic problem, there is a generic solution. Solving this problem in a specific implementation may take different approaches for different engines and datasets. If using MSSQL, the SWITCH command may be an option; in Oracle, there is an ALTER TABLE...MOVE. if the number of rows to delete is actually bigger than the ones to keep, creating a new table may help; At least in Pgsql, temporary dropping indexes will also improve performance on extensive delete clauses; There is also the possibility of using a WITH clause to perform a mass deletion, that will accelerate the process if the delete condition is arbitrarily complex, and probably many other options I'm not aware of to perform this task :)
A pattern I use often is to encapsulate read operations on tables on views; this allows the definition of the underlying tables to be changed as desired, including migrating them to partitioned tables, point them to new tables or event to different schemas or bd's. At some point, the deletion capability needs to be also aligned with the client software implementation.
I try to make each post somewhat open-ended: there are always multiple possible follow-ups that together allow better understanding of the design space, especially in terms of concerns and driving forces.
Here of course the thing is not only about optimizing deletion, but more importantly about the idea of propagation delay, that could then be applied to updating and inserting data, for example; or about dealing with the lagging replicas; or about reducing the unintended atomicity; or about backpressure in distributed database systems.
Different techniques that you describe are certainly interesting, but I'm trying to make it less dependent on specific database server functionality (which, of course, sometimes is very interesting and novel).
I'm sorry if my initial comment came off as a bit snarky, that was not the intention. I did enjoy your post and found it insightful. However, you cannot discuss atomicity and replication (or basically say "table will be locked during delete") without discussing specific database server functionality. Picking up on that specific example, on PG table lock is easily avoided by rewriting it as "DELETE FROM tblFoobar
WHERE id in (SELECT id from tblFoobar WHERE created_at < ‘2018-01-01’);", or by using the previous mentioned WITH approach;
When discussing replication, there is a huge difference between the behavior of any DELETE statement with logical vs physical replication, so its just not "on a cluster, these are the problems". Each dbms has a specific behavior, so it is not possible to mention the underlying complexity of a given statement/operation without going into these details.
In the end, I don't think record deletion should be approached from a database perspective, but from an application perspective. Your application should be designed to perform the required deletions and make sure consistency is met - as an example, what happens when you delete a row that is being also read in a transaction with read uncommitted? What happens if another application thread is reading the same record (or just finished it and is doing something with the data) while you are deleting it? What happens on a clustered application when you delete a row? How is row visibility propagated throughout the different nodes? In which standard replication modes? How can you avoid stale reads on a slave? How can you avoid a dirty/stale read on a master? All of these points/issues are intimately tied with your application design and even your tech stack (orm's, etc), not the database by itself. Sure, in the end, there are commands being executed on the db, and depending on the dbms, engine, replication setup, consistency level, etc. they may execute faster or slower. But the db behaviour in each of these scenarios is usually pretty well documented, and extensively tested for race conditions and stale reads and whatnot. Client applications deleting data usually aren't.
> Picking up on that specific example, on PG table lock is easily avoided by rewriting it as "DELETE FROM tblFoobar
> WHERE id in (SELECT id from tblFoobar WHERE created_at < ‘2018-01-01’);",
wow, that's some news for me. I must admit that last time I did serious work with Postgresql was in 2004 or something. I'm not sure if I have a clear picture of why it avoids the locking problem.
Does it execute the subquery completely, unlock the index and then execute DELETE? I kinda thought that it would do both subquery and DELETE in lockstep? Is this behavour guaranteed or it can change across major versions?
Thank you,
It sets the lock mode for the DELETE operation to a more granular mode (in this case, row locking). Also, SELECT FOR UPDATE before a DELETE will probably also work, as well as setting locking modes explicitly, or using a WITH expression (generates a temp table).
There are a couple of posts on StackOverflow about this (such as https://stackoverflow.com/questions/27007196/avoiding-postgresql-deadlocks-when-performing-bulk-update-and-delete-operations), but it all comes down to managing the granularity of your locks.
On the compatibility note, it should be supported on every "modern" (>8.x) version, as it is a reflection of the available locking policies.