11 ms·

Views are a simple solution to this problem. Pretty much all moderns RDBMSs support updatable views, so creating views over your tables with a simple WHERE del

• nousermane 4y ago
  > (...updatable view...) WHERE deleted_at IS NULL This is the way. Also, save record creation timestamp, and you can have very flexible "time-machine" selects/views of your table essentially for free.
• firloop 4y ago
  Views can really bite you performance wise, at least with Postgres. If you add a WHERE against a query on a view, Postgres (edit: often) won't merge in your queries' predicates with the predicates of the view, often leading to large table scans.
  • dafelst 4y ago
    IIRC Postgres has supported predicate push down on trivial views like this for over a decade now, and possibly even more complex views these days (I haven't kept up with the latest greatest changes).
    • firloop 4y ago
      Postgres can do it, you're correct, but in my experience it rarely happens with any view that's even slightly non-trivial even on recent versions of Postgres. Most views with a join break predicate pushdown. It greatly reduces the usecases of views in practice.
      • sarchertech 4y ago
        I haven’t had any problems with this at all and I’ve been using joins in my views for years. Are you using CTEs in your views?
        • deleted 4y ago
          [deleted]
        • tomnipotent 4y ago
          Postgres 12 fixed the CTE issue.
        • bavell 4y ago
          Postgres docs on CTEs: https://www.postgresql.org/docs/current/queries-with.html https://www.postgresql.org/docs/current/queries-with.html
  • deleted 4y ago
    [deleted]
  • AdrianB1 4y ago
    There is no impact with views in MS SQL. You can also have indexed views and filtered indexes, so you can have even better performance.
• pbardea 4y ago
  > assuming the deletes are done appropriately This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships, I need to re-defined these relationship between objects at the application layer. This gets more and more difficult as relationships between objects increase. If the goal is to keep a history of all records for compliance reasons or "just in case", I tend to prefer a CDC stream into a separate historical system of record.
  • kukx 4y ago
    Is not there any attempt to improve the soft deletion at the engine/SQL level? I can see it as a possible feature request.
    • smallnamespace 4y ago
      If you're using PostgreSQL, you can implement cascading soft-deletes yourself. The information schema table holds all foreign key relationships, so one can write a generic procedure that cascades through the fkey graph to soft-delete rows in any related tables.
      • BeefySwain 4y ago
        Could someone take a stab at an example of what this would look like? Sounds really interesting.
        • smallnamespace 4y ago
          Here's a toy implementation: https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2 https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2
        • NoInkling 4y ago
          Here's an interesting approach using rules: https://evilmartians.com/chronicles/soft-deletion-with-postgresql-but-with-logic-on-the-database https://evilmartians.com/chronicles/soft-deletion-with-postg...
• jaydub 4y ago
  Based on my experience, I like the author's approach since it makes things pretty clear-cut and optimized the storage in the core table (in my experience as well, deletes happen frequently and the soft deletes are rarely touched). In large, row-oriented tables that that storage can add up and even with views/materialized views there's a cost to using/maintaining those as well.
• airstrike 4y ago
  Views are such a powerful concept I’m honestly disheartened by how hard it is to use, replicate or leverage that functionality outside of dropping straight into the db shell
  • quickthrower2 4y ago
    See http://materialize.com http://materialize.com
• OJFord 4y ago
  I had the same reaction to the 'code leakage' section, but 'foreign keys'? You can't reference a view; so you either don't use them (fks) or they point at the underlying table and you have the problem described. You could have views that say 'thing I have a foreign key to is not deleted' of course, but that sort of seems like 'code leakage' again, just in SQL this time.
• vlunkr 4y ago
  That doesn't solve the foreign key problem. You can still easily have a reference to a record that is "deleted"
  • yen223 4y ago
    Also in Postgres, you cannot have a foreign key constraint that references a view, not even a materialised view. I'm with the author on this one. Any soft delete logic does have a tendency to bleed into other systems and make your systems more complicated, for very little gain.
• zozbot234 4y ago
  Views can be used to implement pretty much any kind of automated inference, reasoning, rules etc. on the "raw" table data. The example of filtering out deleted records is just one of the simplest. That one single feature can easily transform a simple DB platform into a fully-featured knowledge base system, easily usable to support even complex reasoning tasks.
• semiquaver 4y ago
  The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantially more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps. This is merely annoying when dealing with regular views because recreating even a large number of views is fast, but can be catastrophic if you have any matviews in your table dependency tree. A matview can easily turn what should be an instantaneous DDL operation into a partial outage while the matview is being regenerated. (this is all postgres specific, it may be untrue for other systems)
  • smallnamespace 4y ago
    To avoid an outage, have you tried fronting the matview with an additional view to allow hot-swapping?
  • enepture 4y ago
    As an FYI using a tool like DBT solves this problem. As someone who was not a data engineer I was not familiar, there were tools like this
    • cpursley 4y ago
      I have this problem. What is DBT?
      • banashark 4y ago
        I believe this: https://docs.getdbt.com/docs/introduction https://docs.getdbt.com/docs/introduction
    • teej 4y ago
      dbt is great, but I'm not sure it's appropriate to manage objects in a production transactional database. It's designed for analytical, columnar databases.
    • PaulMest 4y ago
• chrisshroba 4y ago
  How would a view solve the foreign key issue? Are you suggesting coding specific deletion triggers into the view such that appropriate foreign keys are "cascade" deleted when a row in the view is deleted?
  • jermaustin1 4y ago
    because when you are getting a child record through a join (in the view), the parent will never have deleted_at set. Say I have a simple view `select * from foo join bar on foo.foo_id = bar.foo_id where foo.deleted_at is null` I never have to worry about deleting from bar, because I should never grab a child when the parent is 'deleted'.
    • thehappypm 4y ago
      Assuming that the joins are always joined against the views, not the raw tables. This introduces an opportunity for a mistake.
• aeyes 4y ago
  At least in Postgres, having a huge amount of "dead" data in large tables is problematic because vacuum always has to read the full data set. Even with conditional indexes where you exclude deleted data you take a significant performance hit reading dead blocks because there is no way to quickly vacuum them. You accumulate hours of bloat until your vacuum finishes. You can't beat a separate insert only archive table which you never have to vacuum.
  • layer8 4y ago
    Shouldn’t partitioning help with that? (I have no experience with Postgres.)
    • tomnipotent 4y ago
      Only if those partitions are on separate storage, otherwise you have the same number of dead tuples/dirty pages.
      • klysm 4y ago
        Not sure that is true, Postgres can rule our entire partitions and scan less stuff I think?
        • tomnipotent 4y ago
          Doesn't help with writes. More dead tuples can mean more pages scanned for the same amount of data.
  • anarazel 4y ago
    Vacuum does not have to read the full data set every time. The visibility map tracks, on a block level, whether all rows in a page are known to be visible to everyone (starting in 8.4 or such) and whether the page is "frozen", i.e., does not contain visibility information that might need vacuuming (starting in 9.6 IIRC). However, indexes do currently have to be scanned as a whole. But that's only done by autovacuum if there's enough row versions for that to be worth it (in recent versions).
• 5e92cb50239222b 4y ago
  This is one of those situations where a good ORM can simplify things greatly. For example, with EF Core you can add a global filter which will filter out soft-deleted rows in all queries automatically (unless you add .IgnoreQueryFilters()). It couples nicely with some hackery which turns removes into soft-deletes. You can remove objects as usual and they get soft-deleted in the database. I've used this in a few projects and it's fantastic. https://docs.microsoft.com/en-us/ef/core/querying/filters https://docs.microsoft.com/en-us/ef/core/querying/filters https://www.thereformedprogrammer.net/ef-core-in-depth-soft-deleting-data-with-global-query-filters/ https://www.thereformedprogrammer.net/ef-core-in-depth-soft-...
• layer8 4y ago
  How does this help with foreign keys? Normally you can’t have foreign keys referencing a view. I agree that one should make use of RDBMS capabilities. A check constraint may be practical instead of (or in addition to) the foreign-key constraint.
• SoftTalker 4y ago
  A problem (unless something has changed, my context is Oracle from some time ago) is that NULL values are not indexed. So the "WHERE deleted_at IS NULL" could trigger a full table scan. It can also cause row migration when the NULL value is eventually filled in. Unless you explicitly need the deleted date, it's probably better to use a non-nullable Y/N for this.
  • remram 4y ago
    It seems Oracle does it although there is a special syntax to opt-in. That seems wild. I am not aware of another DBMS having that limitation though.
    • deleted 4y ago
      [deleted]
• ibejoeb 4y ago
  > developers underutilize the capabilities of the massively advanced database engines So true. There are so many amazing, powerful features in all of the major players. Also: updatable views are amazing. With query rewriting (whatever you vendor calls it) you can affect some truly material changes to the system without any changes to the client applications. An example would be implementing temporal relations.
• coding123 4y ago
  I was going to chime in with this. thanks. One issue with views however is that a lot of these features require more and more nuanced knowledge of RDBMSes where these days unless you have a veteran architect, most of the team just knows the various library/tooling that interacts with "a variety of databases" so there is often less effort to go deeper.
  • quickthrower2 4y ago
    Where is this anti-fb culture? Is it a startup thing? Everywhere I have worked people know a decent amount about their data store. Not architects, just mid devs and higher.
    • Falkon1313 4y ago
      I've seen so much torturously complex code to coerce an ORM or query builder to generate a query that would've been simple just to write. Because "that's the way it's done" - Spend 5 minutes writing a query and then 5+ hours trying to figure out the convoluted code necessary to coerce something to generate that same query. Of course, there are times when query builders are necessary to create the queries dynamically depending on parameters. But it's all too common to see them used and abused even for static queries. People are just plain afraid of writing SQL. Or they think it's 'best practice' to use an ORM or query builder and using actual SQL is somehow 'wrong'. Even people who know a decent amount about the database do it anyway because "that's the way that it's supposed to be done" or "that's 'best practice'".
• 7crow 4y ago
  > there are fairly well developed techniques for keeping DB and app states, logic and schemas aligned via migrations and partitioning and whatnot. Hi, <1 yr experience swe here. Would HN mind unpacking "whatnot" with specific names of some these techniques?
• rodw 4y ago
  Seriously. That "Downsides: Code leakage" point is nonsensical. ``` CREATE OR REPLACE VIEW active_customer AS SELECT * FROM customer WHERE deleted_at IS NULL OR deleted_at <= NOW() ; ``` There, I fixed it. Just use `active_customer` instead of `customer ... deleted_at IS NULL`. In fact, since the deleted_at column is a timestamp, the original "leakage" query: ``` SELECT * FROM customer WHERE id = @id AND deleted_at IS NULL; ``` is actually broken. A non-null `deleted_at` timestamp that's in the future implies the record hasn't been deleted yet, right? I've often had junior devs assert that views are some kind of code smell, but these sorts of "canned query/filter that you want to apply very very often" seem like the perfect use case for a view to me. It's DRY, and the fact that your standard "query" is in the database" means you can change it more readily than trying to make sure you hit all the points it might be embedded in the application code. > I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code against Early-ish in the JDBC days a senior dev I was working with at the time (as a junior dev myself) made a pretty good case that "the database is part of the application" that's always stuck with me. Full database independence via software level abstractions is a pretty silly goal outside of library code. If you have a service that makes extensive use of the database, don't throw away the database features in the interest of some abstract "we could swap out oracle with mysql without changing anything" objective. If you want it to be generic, use the SQL standard, but don't be afraid to have a few db-specific bits in the app code if that's a subsystem you might replace once a decade or something. I blame the DBA/Dev divide for a lot of this. A lot of the impedance between these layers is social/procedural. If you can change the DB as easily as the code, there's a lot less fear of using the right tool for the specific job.
  • madisp 4y ago
    I'd argue that the simple `deleted_at IS NULL` check is not broken - unless your product / domain specifically allows and requires scheduled future deletions adding such logic can easily introduce bugs. For example, you could to get the comparison flipped by accident, and if it's only in one place out of many that bug could go unnoticed for a while.
    • doctor_eval 4y ago
• dimgl 4y ago
  100% this. And yes, with inner joins it also solves the relationship issues.
• moggers 4y ago
  I'm glad to see this thread. I've been mulling over this exact issue of deleted_at code leakage with a naive soft-delete implementation. My immediate thought was to use views, so its nice to see this is not yet another case of me using crack-brain ideas out of inexperience. What's an appropriate naming convention? Should I do it universally and put transparent views in front of all my tables so I don't have to refactor my code to point to new views whenever I do suddenly need to put in a model constraint that isn't 1:1 with my data layer? Is a transparent view a no-op in terms of perf? if it matters, this is being done in Postgres I will probably make my constraints partial over the not-deleted records, particularly for unique constraints used for upserts. Am I about to footgun myself? Is it even necessary with the new uniqueness behavior with NULLs being implemented in postgres? Will my performance characteristics be better one way or the other in particular circumstances? It sounds like if I have a high ratio of deleted to not-deleted records a partial index becomes necessary.