4 ms·
One thing this leaves out is the danger of concurrency issues, which can sometimes be worsened by using an ORM. Writing correct, race-free SQL can be very hard
by jasonhansel 7y ago
One thing this leaves out is the danger of concurrency issues, which can sometimes be worsened by using an ORM. Writing correct, race-free SQL can be very hard (with READ COMMITTED transactions) or require complex retry logic (with SERIALIZABLE). Preventing deadlocks can also be a concern.
ORMs tend to hide the underlying SQL operations, making it even harder to verify whether operations are concurrency safe.
- mumblemumble 7y agoI think that this is one of those spots where, if you want things to be ORM-friendly, you've got to bake it into the data model. A good starting point is to use CR instead of CRUD. Append-only tables with soft deletes aren't necessarily the most performant, but they're naturally less susceptible to race conditions. The lack of destructive modification (under normal operation) also makes it easier to diagnose problems and keep audit trails.
- slim 7y agoisn't soft delete an update?
- stevedewald 7y agoNot necessarily. You can insert a new row with a field named ‘deleted’ or similar set to ‘true’. This is how you would implement soft deletes in an append-only table.
- boublepop 7y agoIt doesn’t have to be. You Can write reads that don’t return entries if a separate deletion entry exists. Can’t say I’ve worked that way in sql, but I have seen that pattern in append only data structures. With for instance a “order fulfilled” entry essentially is a delete operation on an outstanding order entry.
- pocket_cheese 7y agoOne way I get around this is having an append only version of the main table, and then a "most current record" version of the table. My append only table will be a running record of all the data I've loaded unmodified. Then I run a create table as select statement against my append only table to generate my new version of the final table. So in essence, I'm "deleting" the old version of the record by omitting it when I create my new version of the final table.
- chrisjc 7y agoDefinitely an advantage on the other side of your traditional "CRUD" application... The data warehouse and BI stack.
- deleted 7y ago[deleted]
- tkyjonathan 7y agoBatching is the multi-threadedness of databases. Its also important to remember that in databases, you are more often optimising for IO usage than CPU.
- macdice 7y agoRetry logic should not be complex! Model transactions as functions, and call them again with the same inputs.
- paulddraper 7y ago> complex retry logic READ COMMITTED and REPEATABLE READ benefit from retry logic as well, not just SERIALIZABLE. For a long time, I sought to write deadlock free code. But that is very hard. For example in PostgreSQL every UPDATE must be ordered, every DELETE must be ordered. [1] Finally, I did myself a favor and create application-level retires. This is 100% cool so long as (1) your deadlocks aren't so frequent so as to reach a performance problem and (2) the action is "replayable" (e.g. no read-once streams). Fortunately, these are both frequently true. [1] https://dba.stackexchange.com/questions/257587/is-select-for-update-necessary-to-delete-or-update-multiple-rows-without-d https://dba.stackexchange.com/questions/257587/is-select-for...
- taffer 7y agoDo you use SERIALIZABLE in Postgres? If so, what's your experience?