4 ms·

Why is this comment greyed out? Seems like a reasonable comment

hobs 3y ago
I didn't vote it down but there's huge tradeoffs here: 1. Row bloat. 2. Bad plan estimation due to bloat. 3. Needing filtered indexes for anything that's a row based unique constraint. 4. All history in one table means changing schemas is a PITA, and you're backfilling stuff that's not real. 5. There's more but that's just off the top.
- hinkley 3y ago
  1. Most of the problem with row 'bloat' is complexity of indexes, row deletion increases the number of required indexes, and lack of those indexes is a footgun 2. Got a citation for that? Pretty sure #3 makes that not true, but I'm open to be educated. 3. Yes, and? You're going to have those indexes anyway. That's the point. 4. You don't have to have all rows in a single table just because you took delete out of any user-facing query logic. 5. I think you need some more examples.
  - arthurcolle 3y ago
    I agree with all of your comments - this is why I asked initial question because literally all his initial comments seems perfectly valid. I appreciate the discourse, and I work on this stuff every day so I was just confused
  - hobs 3y ago
    And the number of rows in the table, effectively bloating it for all time. Deleting rows should generally be via the same type of query patterns you use to find them, or that's pretty weird. I think #4 is something that most people dont implement - ime they just tombstone in their own table, and then all of the other problems are pretty rampant. Having separate indexes for deletes might be a thing, but taking a history of every change to a thing in a relational way is still a big pain because of the schema cost, merging changes in an audit table when its really a slowly changing dimension is really weird to most query patterns. To #2 - it depends on your query engine, but if every query does have filtered indexes there's still a cost to having two tables in one table (ignoring #4)
    - hinkley 3y ago
      > And the number of rows in the table, effectively bloating it for all time. Particularly in the case of growth oriented companies, if your user base is growing exponentially, then half of your records are less than a year old anyway. This bloat is not the problem you make it. And as I said elsewhere, a row that is tombstoned can be deleted offline at some cadence that doesn’t break your other workflows. For instance when nearly all of its siblings are also dead.
      - hobs 3y ago
        Users per second and rows per second are not really that strongly correlated - plenty of services create an excessive amount of noise for a fairly low amount of "real rows". You can push on devs to fix things like pointless logical updates but its really easy to have a rogue process create copies of your entire dataset each time it runs.
hinkley 3y ago
HN is not immune to forgetting things we used to have consensus on. There's always someone who is prepared to downvote, literally or figuratively, something that will eventually be the solution to some problem they created in production. When I get downvoted for ideas and not demeanor, 2/3rds of the time it's just job security.
- refulgentis 3y ago
  Neutral party here: not the case, on your end, the mind-reading distracts from hearing the voice of Chesterton talking about his fence. c.f. sibling comment written 10 minutes before yours, several trivial things. More concretely from a downvoter: 1. I want _nothing_ to do with user data. Nothing. Toxic nuclear waste. The idea of keeping the waste on hand needs strong justification. 2. The idea of "just leave the rows but mark is_deleted" has a fundamental distate to a profession where O(n) is a constant worry. 3. The breezy way it dismisses this as an obvious solution that ~eveyone agrees suggets a small breadth of experience. That's not bad! But then we're at Chesterton's fence. In summary, you can get downvoted for ruling out someone else's concrete lived experience, not just because your dangerous ideas threaten their paycheck (I don't even have a job!)
  - hinkley 3y ago
    The lived experience you talk about is that this is a difficult problem without a solution, not a different solution that I dismissed. What I dismissed is the problem. How do we solve X? You don't solve X, you solve Y. The XY problem, not Chesterton's Fence. Not sure I follow you entirely on user data. There's user data that absolutely requires an audit trail, like subscriptions or orders. There's user data that might start out as null and eventually transition to an inane value, like avatar or self-description. You don't need complex merge rules for that data. It possibly only happens when their account is being hacked and that's not the data you need in that particular situation, yeah?
  - vlovich123 3y ago
    Couldn’t you just mark a deletion timestamp and then lazily delete later as part of a GC operation?

hobs 3y ago

I didn't vote it down but there's huge tradeoffs here: 1. Row bloat. 2. Bad plan estimation due to bloat. 3. Needing filtered indexes for anything that's a row based unique constraint. 4. All history in one table means changing schemas is a PITA, and you're backfilling stuff that's not real. 5. There's more but that's just off the top.

hinkley 3y ago
1. Most of the problem with row 'bloat' is complexity of indexes, row deletion increases the number of required indexes, and lack of those indexes is a footgun 2. Got a citation for that? Pretty sure #3 makes that not true, but I'm open to be educated. 3. Yes, and? You're going to have those indexes anyway. That's the point. 4. You don't have to have all rows in a single table just because you took delete out of any user-facing query logic. 5. I think you need some more examples.
- arthurcolle 3y ago
  I agree with all of your comments - this is why I asked initial question because literally all his initial comments seems perfectly valid. I appreciate the discourse, and I work on this stuff every day so I was just confused
- hobs 3y ago
  And the number of rows in the table, effectively bloating it for all time. Deleting rows should generally be via the same type of query patterns you use to find them, or that's pretty weird. I think #4 is something that most people dont implement - ime they just tombstone in their own table, and then all of the other problems are pretty rampant. Having separate indexes for deletes might be a thing, but taking a history of every change to a thing in a relational way is still a big pain because of the schema cost, merging changes in an audit table when its really a slowly changing dimension is really weird to most query patterns. To #2 - it depends on your query engine, but if every query does have filtered indexes there's still a cost to having two tables in one table (ignoring #4)
  - hinkley 3y ago
    > And the number of rows in the table, effectively bloating it for all time. Particularly in the case of growth oriented companies, if your user base is growing exponentially, then half of your records are less than a year old anyway. This bloat is not the problem you make it. And as I said elsewhere, a row that is tombstoned can be deleted offline at some cadence that doesn’t break your other workflows. For instance when nearly all of its siblings are also dead.
    - hobs 3y ago
      Users per second and rows per second are not really that strongly correlated - plenty of services create an excessive amount of noise for a fairly low amount of "real rows". You can push on devs to fix things like pointless logical updates but its really easy to have a rogue process create copies of your entire dataset each time it runs.

hinkley 3y ago

HN is not immune to forgetting things we used to have consensus on. There's always someone who is prepared to downvote, literally or figuratively, something that will eventually be the solution to some problem they created in production. When I get downvoted for ideas and not demeanor, 2/3rds of the time it's just job security.

refulgentis 3y ago
Neutral party here: not the case, on your end, the mind-reading distracts from hearing the voice of Chesterton talking about his fence. c.f. sibling comment written 10 minutes before yours, several trivial things. More concretely from a downvoter: 1. I want _nothing_ to do with user data. Nothing. Toxic nuclear waste. The idea of keeping the waste on hand needs strong justification. 2. The idea of "just leave the rows but mark is_deleted" has a fundamental distate to a profession where O(n) is a constant worry. 3. The breezy way it dismisses this as an obvious solution that ~eveyone agrees suggets a small breadth of experience. That's not bad! But then we're at Chesterton's fence. In summary, you can get downvoted for ruling out someone else's concrete lived experience, not just because your dangerous ideas threaten their paycheck (I don't even have a job!)
- hinkley 3y ago
  The lived experience you talk about is that this is a difficult problem without a solution, not a different solution that I dismissed. What I dismissed is the problem. How do we solve X? You don't solve X, you solve Y. The XY problem, not Chesterton's Fence. Not sure I follow you entirely on user data. There's user data that absolutely requires an audit trail, like subscriptions or orders. There's user data that might start out as null and eventually transition to an inane value, like avatar or self-description. You don't need complex merge rules for that data. It possibly only happens when their account is being hacked and that's not the data you need in that particular situation, yeah?
- vlovich123 3y ago
  Couldn’t you just mark a deletion timestamp and then lazily delete later as part of a GC operation?