Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The only scalable delete in Postgres is DROP TABLE

planetscale.com

Post reply on HN
Print view View original

81–87 of 87 posts

Re: The only scalable delete in Postgres is DROP TABLE

#81

Post by sgarland »

tremon wrote: post #79
ElectricalUnion wrote: post #50

Earlier quoted context omitted.

> The insert is upper boundable in advance A concurrent DML happening then suddenly your MERGE INTO WHEN NOT MATCHED INSERT/INSERT INTO SELECT is way larger that you thought? I thought "some workloads can suddenly be way larger that I expected" was supposed to be a thing in all non-trivial DML.

You don't even need a complex query; even the simplest of insert statements can cause cascade side effects if you have temporal tables or materialized views (or, Codd forbid, ON INSERT triggers).

I will die on the hill that triggers are a perfectly fine tool, when used reasonably. ON INSERT isn’t usually the one I point at causing problems; that’d be ON DELETE CASCADE. 1:M relationships with large values of M are already iffy for deletions or updates; couple that with unnecessarily wide columns (or just storing large text / json / blob), and worst-case, non-clustering indices, and “delete this user” turns into “fetch thousands of pages.”

Re: The only scalable delete in Postgres is DROP TABLE

#82

Post by Izkata »

crazygringo wrote: post #21
setr wrote: post #12

Earlier quoted context omitted.

> It takes just as much work to delete a row as it takes to insert a row. Why wouldn't it? Obviously you have to do almost all the same operations: write a log, write the deletion, update indices, replicate it, etc. It takes far more work to delete/update than insert. My recent example is updating ~2TB of text data was about 40x slower than inserting 12TB (was trying to correct some large text truncation that occurre…

> It takes far more work to delete/update than insert. Updating rows of text data is going to be more work, because variable-length text can't be updated in-place. So in terms of allocating space, it's more like a delete plus an insert. That's not surprising. (An in-place update that doesn't touch indices is generally going to be faster than an insert, though.) I'm not aware of instances where a delete is "far more w…

> Updating rows of text data is going to be more work, because variable-length text can't be updated in-place.

If we're still talking postgres, it doesn't update in-place. Update is implemented as delete+insert (where delete is updating metadata so the row is still around for still-running transactions but invisible to future transactions).

Re: The only scalable delete in Postgres is DROP TABLE

#84

Post by DarkUranium »

silvestrov wrote: post #74
DarkUranium wrote: post #70

Earlier quoted context omitted.

One thing I did a while ago was to make deletes part of inserts, to amortize the cost. The main reason was to avoid a separate cron job, but it had other benefits (and downsides) too. Something like: DELETE FROM foo WHERE expires_at Note the LIMIT: it ensures the latency stays under control even if we've suddenly hit 50k rows that need deleting. And by deleting (up to) 10 each time we insert one, it ensures obsolete…

Problem is that PostgreSQL does not support LIMIT on the DELETE command. I have no idea why, it seems such an obvious feature for supporting large databases.

Right, I must've forgotten. I know I had it working, but I don't have code at hand right now. I probably worked around it with a CTE or subquery then?

Something like:

    WITH ids AS (
        SELECT id FROM foo
        WHERE ...
        LIMIT 10
    )
    DELETE FROM foo
    USING ids
    WHERE foo.id=ids.id;
Or:

    DELETE FROM foo
    WHERE id IN (SELECT id FROM foo WHERE ... LIMIT 10);

Re: The only scalable delete in Postgres is DROP TABLE

#86

Post by venkat971 »

Associating drop table to scalabe delete is not a good framing. These are two different things, and very sensitive on production workloads if there are FK and MVs involved. Databases has 'Trucate table' for a reason. The post header should have used 'Truncate table' instead of 'Drop table' framing.
Post reply on HN