Hacker News

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 < now() LIMIT 10;
    INSERT INTO foo .....;

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 things will eventually get deleted.

Obviously, this isn't viable when the deletions must happen due to strict policy (e.g. legal compliance) since it can't ensure when things get deleted, just that they eventually do. IIRC, in my case, I used it for a password reset tokens table. There's no legal issue there and keeping expired ones around is fine as long as the code also checks `expires_at` to make sure it's still valid (which would be a good practice regardless, for defense in depth).

silvestrov 6 days ago [ - ]

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.

DarkUranium 5 days ago [ - ]

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);