How do I delete a fixed number of rows with sorting in PostgreSQL?

Postgres docs recommend to use array instead of IN and subquery. This should work much faster

DELETE FROM logtable 
WHERE id = any (array(SELECT id FROM logtable ORDER BY timestamp LIMIT 10));

This and some other tricks can be found here


delete from logtable where log_id in (
    select log_id from logtable order by timestamp limit 10);

You could try using the ctid:

DELETE FROM logtable
WHERE ctid IN (
    SELECT ctid
    FROM logtable
    ORDER BY timestamp
    LIMIT 10
)

The ctid is:

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier.

There's also oid but that only exists if you specifically ask for it when you create the table.

Tags:

Sql

Postgresql