179

I'm trying to port some old MySQL queries to PostgreSQL, but I'm having trouble with this one:

DELETE FROM logtable ORDER BY timestamp LIMIT 10;

PostgreSQL doesn't allow ordering or limits in its delete syntax, and the table doesn't have a primary key so I can't use a subquery. Additionally, I want to preserve the behavior where the query deletes exactly the given number or records -- for example, if the table contains 30 rows but they all have the same timestamp, I still want to delete 10, although it doesn't matter which 10.

So; how do I delete a fixed number of rows with sorting in PostgreSQL?

Edit: No primary key means there's no log_id column or similar. Ah, the joys of legacy systems!

Improve this question
2
  • 1
    Why not add the primary key? Piece o' cake in postgresql: alter table foo add column id serial primary key. Commented Mar 2, 2011 at 18:15
  • 1
    That was my initial approach, but other requirements prevent it. Commented Mar 2, 2011 at 20:36

7 Answers 7

Reset to default
264

You could try using the ctid:
demo at db<>fiddle

DELETE FROM ONLY 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.

In case there are tables that inherit from logtable, use DELETE FROM ONLY to prevent this from removing up to 10 rows from each of the descendants that happens to host a row with the same ctid. This field is only unique per table and by default, regular DELETE cascades down the hierarchy.

If logtable is partitioned, add tableoid to stop this from wiping up to 10 per partition:

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

DELETE FROM ONLY wouldn't remove anything because the main table is an empty relation, only redirecting to specific partitions.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This works, but how reliable is it? Are there any 'gotchas' I need to look out for? Is it possible for VACUUM FULL or autovacuum to cause problems if they change the ctid values in the table while the query is running?
Incremental VACUUMs won't change ctids, I don't think. Since that just compacts within each page, and the ctid is just the line number not a page offset. A VACUUM FULL or a CLUSTER operation would change the ctid, but those operations take an access exclusive lock on the table first.
@Whatsit: My impression of the ctid documentation is that ctid is stable enough to make this DELETE work okay but not stable enough to, for example, put in another table as a ghetto-FK. Presumably you don't UPDATE the logtable so you don't have to worry about that changing ctids and VACUUM FULL does lock the table (postgresql.org/docs/current/static/routine-vacuuming.html) so you don't have to worry about the other way that ctids can change. @araqnid's PostgreSQL-Fu is pretty strong and the docs agree with him to boot.
Thanks to both of you for the clarification. I did look into the docs but I wasn't certain I was interpreting them correctly. I'd never encountered ctids before this.
This is a very dangerous way to do it, you are asking for troubles, as example from postgres 10 behind that table logtable you can have multiple partitions, and basically you can have multiple rows with ctid (0,1) but on different partitions. Author of this reply should delete it.
|
79

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

Improve this answer

8 Comments

@Konrad Garus Here you go link, 'Fast first n rows removing'
@BlakeRegalia No, because there's no primary key in the table specified. This will delete all the rows with an "ID" found in the first 10. If all the rows have the same ID all the rows will be deleted.
If any (array( ... )); is faster than in ( ... ) that sounds like a bug in the query optimiser - it should be able to spot that transformation and do the same thing with the data itself.
I found this method to be considerable slower than using IN on an UPDATE (which might be the difference).
Measurement on 12 GB table: first query 450..1000 ms, second one 5..7 seconds: Fast one: delete from cs_logging where id = any (array( select id from cs_logging where date_created < now() - interval '1 days' * 30 and partition_key like '%I' order by id limit 500 )) Slow one: delete from cs_logging where id in ( select id from cs_logging where date_created < now() - interval '1 days' * 30 and partition_key like '%I' order by id limit 500 ). Using ctid was a lot slower (minutes).
|
23 
delete from logtable where log_id in (
    select log_id from logtable order by timestamp limit 10);
Improve this answer

Comments

14
+100

Level 1

Don't even bother with a LIMIT with DELETE.

What are you worried about? That you'll lock up the table? Nope, this isn't MySQL. As long as a row that gets deleted isn't used in some other transaction, you'll not block anything else.

Level 2

OK OK, but sometimes you want to make sure you run potentially long-running tasks in parallel, or in cron-jobs that might run in parallel (eg, the DELETE job takes longer than the period scheduled). For that, the solution is:

BEGIN
  LOCK TABLE blah_blah IN SHARE ROW EXCLUSIVE MODE ;
  DELETE FROM blah_blah WHERE some_condition ;
COMMIT ;

and call it a day. If this job takes too long, the next job will simply fail noisily but no harm done. You won't run into deadlock issues, like you might with some of the pseudo-LIMIT solutions elsewhere.

Level 3

OK OK, but sometimes you really really only want to delete a few because you're curious what might happen, or because you only want to delete the top X of something and return those values. But definitely not because you want to put something in a cron-job... :

  DELETE FROM blah_blah WHERE primary_key IN ( 
    SELECT primary_key FROM blah_blah WHERE some_condition
    LIMIT 10 
  ) RETURNING * ;

If you put this in cronjob or automated task please combine Level 2 && Level 3. Or deadlock-gremlins will find you and torture you.

Level 4

Right, but you're a serious DBA / Sysadmin and you know better, right? You really really need to place "nice" and not hog up system resources. For instance, if you're doing PIT and doing such a big delete operation will hammer the WAL system. Or maybe (as increasingly is the case these days) you actually get charged for CPU time, and you want to keep the performance curve as smooth as possible.

We can use PL/PGSQL to create a loop to semi-automate the job for us. We'll call the procedure batch.

To use it, let's imagine you've got a table node with a primary key id and with a timestamp-field named timestamp. You would delete the rows like this:

  WITH deleted AS ( 
    DELETE FROM node WHERE id IN ( 
      SELECT id FROM node WHERE timestamp < now() - interval $$1 days$$ 
    ) RETURNING ID 
   ) SELECT count(*) FROM deleted

This will delete 100 records at a time, provided those records are older than 1 day. The count deleted will be "returned" for reasons that are important as you will see. (BTW, I'm not sorting the IDs here, as some suggest you should do, because it's inefficient, and locking the table in the surrounding block makes more sense.) ....

To invoke this batchx procedure, with the above query, you'll do it like this:

CALL batch( 300, 100, 
  'WITH deleted AS ( 
    DELETE FROM node WHERE id IN ( 
      SELECT id FROM node WHERE timestamp < now() - interval $$1 days$$ 
      ORDER BY id 
      LIMIT @@ 
    ) RETURNING ID 
   ) SELECT count(*) FROM deleted' );

This will run the query repeatedly in batches of 100 (second parameter) for up to 300 seconds (first parameter), or until no more records are deleted. Note the use of @@ instead of the number! This allows you to parameterize the limit so that you don't have to modify the query each time. (This will come in handy in Level 5).

Please note:the code replaces the string LIMIT @@ including the leading space. Make sure it's not a newline or tab or something.)

create or replace procedure batch( max_time double precision, chunk_size int, query varchar )
language plpgsql
as $$
declare 
  final_query     varchar;
  loop_time       double precision;
  target_end      timestamp;
  loop_timestamp  timestamp;
  last_timestamp  timestamp:=now();
  result          int;
  i               int:=0;
begin
  target_end := clock_timestamp() + INTERVAL '1 second' * max_time;
  final_query := REPLACE( query, ' LIMIT @@', ' LIMIT ' || chunk_size::varchar);
  LOOP
    i:=i+1;
    EXECUTE final_query INTO result;
    loop_timestamp = clock_timestamp();
    loop_time := extract(epoch from ( loop_timestamp - last_timestamp) )::DOUBLE PRECISION;
    last_timestamp := loop_timestamp;
    RAISE info  'loop %: count= %  loop_time= %  records/s= %', i, result, loop_time, to_char(result / loop_time, 'FM99999999.00');
    EXIT WHEN result = 0 OR loop_timestamp > target_end ;
  END LOOP;
end
$$;

Level 5

That was cool and all, but now it's time to bring it to the next and final level: let the procedure auto-scale the limit according to the performance. Instead of specifying the limit-size, specify the time you want wach batch to run.

Let's call this the autobatch procedure:

create or replace procedure autobatch( max_time double precision, chunk_time double precision, query varchar )
language plpgsql
as $$
declare 
  max_scaleup   double precision:=2;
  max_scaledown double precision:=0.1;
  next_limit    int;
  loop_time     double precision;
  factor        double precision;
  result        int;
  target_end    timestamp;
  loop_timestamp  timestamp;
  last_loop_timestamp timestamp:=now();
  final_query varchar;
  i int:=0;
begin
  target_end := clock_timestamp() + INTERVAL '1 second' * max_time;
  next_limit := max_time ;
  LOOP
    i:=i+1;

    final_query := REPLACE( query, ' LIMIT @@', ' LIMIT ' || next_limit::varchar);

    EXECUTE final_query INTO result;

    loop_timestamp = clock_timestamp();
    loop_time := extract(epoch from ( loop_timestamp - last_loop_timestamp) )::DOUBLE PRECISION;
    factor := GREATEST( max_scaledown, LEAST( max_scaleup, chunk_time / loop_time ));
    next_limit := next_limit * factor;
    last_loop_timestamp := loop_timestamp;
    RAISE info  'loop %: count = %  next_limit = %  loop_time = %', i, result, next_limit, loop_time;
    EXIT WHEN result = 0 OR loop_timestamp > target_end ;
  END LOOP;
end
$$;

Sample output:

call autobatch( 1200, 30, 'with deleted as (delete from node where id in ( select id from node where timestamp < now() - interval $$1 days$$ LIMIT @@ ) returning id) select count(*) from deleted' );
INFO:  loop 1: count = 1200  next_limit = 2400  loop_time = 3.210725
INFO:  loop 2: count = 2400  next_limit = 4800  loop_time = 6.350344
INFO:  loop 3: count = 4800  next_limit = 9600  loop_time = 12.373832
INFO:  loop 4: count = 9600  next_limit = 12261  loop_time = 23.489029
INFO:  loop 5: count = 12261  next_limit = 12338  loop_time = 29.812288
INFO:  loop 6: count = 12338  next_limit = 12675  loop_time = 29.202712
INFO:  loop 7: count = 12675  next_limit = 13360  loop_time = 28.461273

See how it adjusts the limit-size to maximize the batch-time? If it goes over, it will scale down to. Scaling up is capped at 2x, while scaling down is capped at 1/10th. That's mainly to stabilize behavior when queries result in cache hits.

Improve this answer

1 Comment

There are format() value placeholders and execute..using param placeholder you can use instead of a custom limit @@. You don't need the with x as(delete..returning..)select count(*)from x wrapper in PL/pgSQL, each query you run already returns a count you can access via get diagnostics result := row_count;
5

If you don't have a primary key you can use the array Where IN syntax with a composite key.

delete from table1 where (schema,id,lac,cid) in (select schema,id,lac,cid from table1 where lac = 0 limit 1000);

This worked for me.

Improve this answer

Comments

2

Assuming you want to delete ANY 10 records (without the ordering) you could do this:

DELETE FROM logtable as t1 WHERE t1.ctid < (select t2.ctid from logtable as t2  where (Select count(*) from logtable t3  where t3.ctid < t2.ctid ) = 10 LIMIT 1);

For my use case, deleting 10M records, this turned out to be faster.

Improve this answer

2 Comments

This is not performant! Use instead: delete from "table" where ("table_ID") in (select "table_ID" from "table" limit 10)
Except as the problem specified, there is no table ID
0

You could write a procedure which loops over the delete for individual lines, the procedure could take a parameter to specify the number of items you want to delete. But that's a bit overkill compared to MySQL.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.