Обсуждение: Delete/update with limit

Hi all,

This subject was touched a few times in the past, I looked into the
archives... the result is invariably key developers saying such a
feature is unsafe because the result is unpredictable, while the people
requesting is saying it is OK that way, it is expected... but no
compelling use case for it.

OK, I have one...

We have here quite a few processes which collect user input, and put
them in "batch" tables, which in turn are processed regularly and update
other tables with summary data, reject invalid records, etc.

The insertions are unpredictable, they can happen any time and any of
them in parallel, they are user input... and they must be very fast,
it's our user experience at stake.

The batch processing is done by a single periodical process. Now we had
a few attempts of making this parallelism safe enough so we don't loose
some of the concurrent input while we do the processing step, while
still keeping minimal overhead in the table. The end result was a scheme
where the batch processor deletes from the table and a delete trigger
puts the deleted rows into a temporary table, and then the processor can
do with that private data anything it pleases without interfering with
the inserts (the processing is actually quite complex on occasions).

This works fine in terms of correctness, however it turns out to be a
problem with high bursts of incoming data, or when the processor is not
running for a while and a lot of data is accumulating... then we have
lots of data to process at once, which leads to long running
transactions (the whole thing runs in one transaction) and worse,
connection timeouts.

On other databases, it is possible to limit the delete to a maximum
number of rows to be deleted. This way we can limit the size of one
batch with minimal overhead...

In postgres we're currently not chunking, due to the fact that the code
to do it is simply overly contorted and inefficient compared to the
other DBs we use. At least all the solutions we could think of to do the
chunking in a safe way while the inserts are running in parallel,
without disturbing them, have invariably resulted in overly complicated
code compared to the simple delete with limit + delete trigger solution
we have for the other DBs.

Now I don't put too much hope I can convince anybody that the limit on
the delete/update commands has valid usage scenarios, but then can
anybody help me find a good solution to chunk-wise process such a buffer
table where insert speed is the highest priority (thus no indexes, the
minimum of fields), and batch processing should still work fine with big
table size, while not impacting at all the inserts, and finish in short
time to avoid long running transactions ? Cause I can't really think of
one... other than our scheme with the delete with limit + trigger +
private temp table thing.

Cheers,
Csaba.

nagy@ecircle-ag.com (Csaba Nagy) writes:
> In postgres we're currently not chunking, due to the fact that the code
> to do it is simply overly contorted and inefficient compared to the
> other DBs we use. At least all the solutions we could think of to do the
> chunking in a safe way while the inserts are running in parallel,
> without disturbing them, have invariably resulted in overly complicated
> code compared to the simple delete with limit + delete trigger solution
> we have for the other DBs.
>
> Now I don't put too much hope I can convince anybody that the limit on
> the delete/update commands has valid usage scenarios, but then can
> anybody help me find a good solution to chunk-wise process such a buffer
> table where insert speed is the highest priority (thus no indexes, the
> minimum of fields), and batch processing should still work fine with big
> table size, while not impacting at all the inserts, and finish in short
> time to avoid long running transactions ? Cause I can't really think of
> one... other than our scheme with the delete with limit + trigger +
> private temp table thing.

All that comes to mind is to put a SERIAL primary key on the table,
which shouldn't be *too* terribly expensive an overhead, assuming
there is reasonably complex processing going on; you then do something
like:

- select ID from the incoming table, order by ID, limit 500, to grab a
  list of IDs;

- delete from the table for that set of IDs.

Actually, is there any particular reason why you couldn't simply have
your "batch processing" loop look like:

Loop Forever
  DELETE from incoming_table;
  VACUUM incoming_table;
End Loop;

???

The alternative that I suggested amounts to:

Loop Forever
  DELETE from incoming_table where id in (select id from incoming_table limit 500);
  VACUUM incoming_table;
End Loop;

I realize you're concerned that maintaining the index will be too
costly; I don't think it is obvious without actual benchmarking that
this is *in fact* too costly.

I'm pretty sure of one countervailing consideration: there's a cost to
VACUUMing the table that will throw in some costs; it is possible that
the cost of the index would be noise against that.
--
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/lisp.html
When a man talks dirty to a woman, its sexual harassment. When a woman
talks dirty to a man, it's 3.95 per minute.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 07/23/07 10:56, Csaba Nagy wrote:
> Hi all,
>
> This subject was touched a few times in the past, I looked into the
> archives... the result is invariably key developers saying such a
> feature is unsafe because the result is unpredictable, while the people
> requesting is saying it is OK that way, it is expected... but no
> compelling use case for it.
>
[snip]
>
> Now I don't put too much hope I can convince anybody that the limit on
> the delete/update commands has valid usage scenarios, but then can
> anybody help me find a good solution to chunk-wise process such a buffer
> table where insert speed is the highest priority (thus no indexes, the
> minimum of fields), and batch processing should still work fine with big
> table size, while not impacting at all the inserts, and finish in short
> time to avoid long running transactions ? Cause I can't really think of
> one... other than our scheme with the delete with limit + trigger +
> private temp table thing.

Maybe add OIDs to the table, and delete based on the OID number?

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGpQ7zS9HxQb37XmcRArXQAJ9qcrWphVgtINdGlcwGubg/SEsjMgCeKyLt
I8xPs0NEGqg22Cvgf4awNVQ=
=l/yz
-----END PGP SIGNATURE-----

On Mon, 2007-07-23 at 17:56 +0200, Csaba Nagy wrote:

> Now I don't put too much hope I can convince anybody that the limit on
> the delete/update commands has valid usage scenarios, but then can
> anybody help me find a good solution to chunk-wise process such a buffer
> table where insert speed is the highest priority (thus no indexes, the
> minimum of fields), and batch processing should still work fine with big
> table size, while not impacting at all the inserts, and finish in short
> time to avoid long running transactions ? Cause I can't really think of
> one... other than our scheme with the delete with limit + trigger +
> private temp table thing.

Use partitioning: don't delete, just drop the partition after a while.

--
  Simon Riggs
  EnterpriseDB  http://www.enterprisedb.com

First of all, thanks for all the suggestions.

> put a SERIAL primary key on the table
Or:
> Maybe add OIDs to the table, and delete based on the OID number?

No, this is not acceptable, it adds overhead to the insertions. Normally
the overhead will be small enough, but on occasions it is noticeable.

> Loop Forever
>   DELETE from incoming_table;
>   VACUUM incoming_table;
> End Loop;

Not workable either, it still won't assure the table never getting too
big. Once the table is too big, it takes too much to process, and it
gets even bigger for the next time. The whole thing is transient (i.e.
the load will smooth out after a while), but then exactly when it should
work it doesn't... and if you didn't guess, the users want the results
immediately, not next day so we could do the processing at night when we
have virtually no load.

> Use partitioning: don't delete, just drop the partition after a while.

OK, this could work.

It will still be completely different than the code for the other DBs,
but it will work.

Thanks again for all the suggestions,
Csaba.

> How about using the following?
>
> delete from <table>
>     where ctid in (select ctid from <table> limit <num>);
>

I actually checked this out before starting this thread, and the plan
looked like:

> explain delete from my_table where ctid in (select ctid from my_table
limit 10);
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Merge IN Join  (cost=101.68..108.03 rows=10 width=6)
   Merge Cond: (public.my_table.ctid = "IN_subquery".ctid)
   ->  Sort  (cost=101.11..104.21 rows=1240 width=6)
         Sort Key: public.my_table.ctid
         ->  Seq Scan on my_table  (cost=0.00..37.40 rows=1240 width=6)
   ->  Sort  (cost=0.57..0.59 rows=10 width=6)
         Sort Key: "IN_subquery".ctid
         ->  Limit  (cost=0.00..0.30 rows=10 width=6)
               ->  Seq Scan on my_table  (cost=0.00..37.40 rows=1240
width=6)
(9 rows)

It looked strange to me, and I thought it won't work too well on a big
table... but it's true that was a toy table, so let's try on a big one:

> explain delete from big_table where ctid in (select ctid from
big_table limit 10);
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Merge IN Join  (cost=11086906.66..11404636.41 rows=10 width=60)
   Merge Cond: (public.big_table.ctid = "IN_subquery".ctid)
   ->  Sort  (cost=11086906.26..11245771.06 rows=63545920 width=66)
         Sort Key: public.big_table.ctid
         ->  Seq Scan on big_table  (cost=0.00..834103.20 rows=63545920
width=66)
   ->  Sort  (cost=0.40..0.42 rows=10 width=6)
         Sort Key: "IN_subquery".ctid
         ->  Limit  (cost=0.00..0.13 rows=10 width=6)
               ->  Seq Scan on big_table  (cost=0.00..834103.20
rows=63545920 width=6)
(9 rows)

So, while the batch table is not expected to have 60M rows, on occasions
it got to a few 100Ks... and in that case the chunking would slow down
things even more.

I guess if the ctid in (...) thing would do a better job it would be the
best solution.

Regarding all the other questions, the "other DB" does the trick well
too, without any hidden cost. And the whole complicated mechanism is in
place not because of cost savings, but because I didn't find any better
way to do it so that concurrent inserts are neither slowed down nor
lost... the problem is that if you want to reliably delete only
processed rows, you must mark them somehow, and that would mean an
update + delete later - and I figured the delete + trigger + temp table
approach will be still cheaper. And the processing code will have to
scan the processed chunk multiple times, so for that purpose it is also
better to have it in a temp table. And we had to make sure an accidental
second run of the processor won't corrupt the data either (it happened
before)... the trigger approach helps there too...

We had here so many data losses on this processing with different
approaches, that I start to be tired about it... and this delete +
trigger + temp table looks to be the one which finally works correctly,
but gets us performance problems on occasions.

Cheers,
Csaba.

Csaba Nagy wrote:
> First of all, thanks for all the suggestions.
>
>> put a SERIAL primary key on the table
> Or:
>> Maybe add OIDs to the table, and delete based on the OID number?
>
> No, this is not acceptable, it adds overhead to the insertions. Normally
> the overhead will be small enough, but on occasions it is noticeable.

How about using the following?

delete from <table>
    where ctid in (select ctid from <table> limit <num>);

Here's a live example:

db=> select count(*) from sometable;
 count
-------
   381
(1 row)

db=> delete from sometable where ctid in (select ctid from sometable
limit 5);
DELETE 5
db=> select count(*) from sometable;
 count
-------
   376
(1 row)

Does anyone see problems with the above delete?

---

Anyway, do you have figures of the overhead you mentioned? How fast is
PG (with OIDs) and how does it compare with the alternatives you're using?

In your original post you wrote:
> On other databases, it is possible to limit the delete to a maximum
> number of rows to be deleted.

I don't know what "other databases" you're referring to, but are you
sure they don't have anything similar to PG OIDs, without even you
knowing it, and without any option to disable them? It's even possible
that in the "other databases" you're already paying that overhead, and
that makes it quite acceptable in PG, too. Or maybe there's some other
kind of overhead, much bigger than the OIDs one?

For example, you're using a high overhead mechanism to consume rows
(triggers on delete, insering into another table), are you sure that in
the "other databases" this doesn't slow all the inserts down much more
than adding OIDs on PG would do? PG has MVCC, I guess that makes deletes
and inserts on the same table play nice to each other, but how about the
other databases? Do they need to acquire a lock on inserts/deletes? That
would make your concurrent inserts/deletes much slower that just adding
a column to the table. Maybe you could even add an index, and still be
faster thanks to MVCC.

Also, the trigger is fired once for each deleted row. Have you
considered a single stored procedure that loops over the rows to be
processed, instead of relaying on deletes and triggers?

.TM.

On Tue, 24 Jul 2007, Csaba Nagy wrote:

> > How about using the following?
> >
> > delete from <table>
> >     where ctid in (select ctid from <table> limit <num>);
> >
>
> I actually checked this out before starting this thread, and the plan
> looked like:
>
> > explain delete from my_table where ctid in (select ctid from my_table
> limit 10);

Unfortunately the stuff that makes a ctid=<value> nice doesn't seem to be
used when you're doing an in. It's possible that a function that does
something like
 for rec in select ctid from my_table limit 10 loop
  delete from my_table where ctid=rec.ctid;
 end loop
might do okay, but I haven't tried it.

> Unfortunately the stuff that makes a ctid=<value> nice doesn't seem to be
> used when you're doing an in. It's possible that a function that does
> something like
>  for rec in select ctid from my_table limit 10 loop
>   delete from my_table where ctid=rec.ctid;
>  end loop
> might do okay, but I haven't tried it.

OK, I think this will work. It would be nice though to have the 'ctid
in' trick work just as well as 'ctid = ' ...

Thanks,
Csaba.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Unfortunately the stuff that makes a ctid=<value> nice doesn't seem to be
> used when you're doing an in.

Yeah, see the header comments in tidpath.c:

 * There is currently no special support for joins involving CTID; in
 * particular nothing corresponding to best_inner_indexscan().    Since it's
 * not very useful to store TIDs of one table in another table, there
 * doesn't seem to be enough use-case to justify adding a lot of code
 * for that.

Of course, that argument is wrong for a self-join, which is what this
would essentially be.  So maybe it would be worth doing sometime.
Still, the issue doesn't come up very often.

[ thinks for a bit ... ] Actually, you can do it as of 8.2 or so,
by abusing the ScalarArrayOp stuff: turn the subquery into an array.
An example in the regression database:

regression=# explain update tenk1 set ten=ten+1
regression-#   where ctid = any (array(select ctid from tenk1 limit 10));
                               QUERY PLAN
-------------------------------------------------------------------------
 Tid Scan on tenk1  (cost=0.46..40.71 rows=10 width=250)
   TID Cond: (ctid = ANY ($0))
   InitPlan
     ->  Limit  (cost=0.00..0.46 rows=10 width=6)
           ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=6)
(5 rows)

It even seems to get the cost estimate right...

            regards, tom lane

On Tue, 2007-07-24 at 18:19, Tom Lane wrote:
> [ thinks for a bit ... ] Actually, you can do it as of 8.2 or so,
> by abusing the ScalarArrayOp stuff: turn the subquery into an array.
> An example in the regression database:
>
> regression=# explain update tenk1 set ten=ten+1
> regression-#   where ctid = any (array(select ctid from tenk1 limit 10));
>                                QUERY PLAN
> -------------------------------------------------------------------------
>  Tid Scan on tenk1  (cost=0.46..40.71 rows=10 width=250)
>    TID Cond: (ctid = ANY ($0))
>    InitPlan
>      ->  Limit  (cost=0.00..0.46 rows=10 width=6)
>            ->  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=6)
> (5 rows)
>
> It even seems to get the cost estimate right...

Cool, I will use this then (we do have the relevant DB on 8.2). It would
still be nice to have it work directly...

Thanks,
Csaba.

"Csaba Nagy" <nagy@ecircle-ag.com> writes:

>> Unfortunately the stuff that makes a ctid=<value> nice doesn't seem to be
>> used when you're doing an in. It's possible that a function that does
>> something like
>>  for rec in select ctid from my_table limit 10 loop
>>   delete from my_table where ctid=rec.ctid;
>>  end loop
>> might do okay, but I haven't tried it.
>
> OK, I think this will work. It would be nice though to have the 'ctid
> in' trick work just as well as 'ctid = ' ...

Unfortunately I don't think this will work. Multiple backends will happily
pick up the same ctid in their selects and then try to delete the same
records.

The second backend to get to a record to do the delete will have to block on
the first backend's lock destroying the parallelism you were hoping for. When
the first backend commits it will find the record deleted and end up finding
fewer records in its workset than the limit you specified.

I think you can make it work reasonably well by making each worker go and
update a field in the records it wants to process to indicate it has "grabbed"
them. Commit that. then go back and process them. Then go back and update them
again to delete them. But then you need some facility for dealing after a
crash with finding grabbed records which were never processed.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

On Tue, 24 Jul 2007, Gregory Stark wrote:

> "Csaba Nagy" <nagy@ecircle-ag.com> writes:
>
> >> Unfortunately the stuff that makes a ctid=<value> nice doesn't seem to be
> >> used when you're doing an in. It's possible that a function that does
> >> something like
> >>  for rec in select ctid from my_table limit 10 loop
> >>   delete from my_table where ctid=rec.ctid;
> >>  end loop
> >> might do okay, but I haven't tried it.
> >
> > OK, I think this will work. It would be nice though to have the 'ctid
> > in' trick work just as well as 'ctid = ' ...
>
> Unfortunately I don't think this will work. Multiple backends will happily
> pick up the same ctid in their selects and then try to delete the same
> records.

I'm pretty sure he said that the batch processing (and the delete) would
only be happening from one backend at a time, no concurrency on that
portion, merely concurrency with the large volume of inserts.

On Tue, 2007-07-24 at 19:06, Stephan Szabo wrote:
> > Unfortunately I don't think this will work. Multiple backends will happily
> > pick up the same ctid in their selects and then try to delete the same
> > records.
>
> I'm pretty sure he said that the batch processing (and the delete) would
> only be happening from one backend at a time, no concurrency on that
> portion, merely concurrency with the large volume of inserts.

Yes it's exactly like that... only it also happened accidentally that 2
batch processes started at the same time, and they should not double
process the data, nor loose some of it. The above scheme is OK with that
too... but the array version from Tom is even better :-)

Regarding the proposed mark/process/delete version, we've done it that
way, and we always managed to get some corner case which lost us data...
so even if it's possible to do it well, it's definitely not easy. The
delete/copy/process private data version is much safer, and it actually
can be done in one transaction to assure crash safety.

Cheers,
Csaba.

Andrew,

Thanks for your input, comments below.

On Thu, 2007-07-26 at 13:30, Andrew Kroeger wrote:
> After reading through this thread, I have an idea that should accomplish
> what I believe are your 3 main goals (avoid any negative performance
> impact on the user's inserts, do not lose any data from those inserts,
> and do not double process any data) and possibly improve performance
> (I'm not sure what the overhead is for triggers, so there may not be any
> benefit).

The essential changes you're proposing are:

1) use a fixed table instead of temp table -> this is a reasonable one,
temp tables are not exactly free if they are created and dropped too
often;

2) use an "insert selected/delete" scheme instead of the "delete/insert
via trigger". I doubt this would be faster... in fact I wonder if
instead of the trigger it is not possible to use a rule to do it - I've
never used the postgres rule system, but it seems to me it is possible
to create a rule which inserts the row in another table when deleting
it. I'm not sure how cheap that would be compared to the trigger
version.

In any case, on top of all these thoughts there is one more goal I have:
the solution used for postgres should be as close to the other DBs as
possible, for obvious maintenance reasons.

Cheers,
Csaba.

Csaba Nagy wrote:
> On Tue, 2007-07-24 at 19:06, Stephan Szabo wrote:
>>> Unfortunately I don't think this will work. Multiple backends will happily
>>> pick up the same ctid in their selects and then try to delete the same
>>> records.
>> I'm pretty sure he said that the batch processing (and the delete) would
>> only be happening from one backend at a time, no concurrency on that
>> portion, merely concurrency with the large volume of inserts.
>
> Yes it's exactly like that... only it also happened accidentally that 2
> batch processes started at the same time, and they should not double
> process the data, nor loose some of it. The above scheme is OK with that
> too... but the array version from Tom is even better :-)
>
> Regarding the proposed mark/process/delete version, we've done it that
> way, and we always managed to get some corner case which lost us data...
> so even if it's possible to do it well, it's definitely not easy. The
> delete/copy/process private data version is much safer, and it actually
> can be done in one transaction to assure crash safety.

After reading through this thread, I have an idea that should accomplish
what I believe are your 3 main goals (avoid any negative performance
impact on the user's inserts, do not lose any data from those inserts,
and do not double process any data) and possibly improve performance
(I'm not sure what the overhead is for triggers, so there may not be any
benefit).

One-time changes that must be done before running the batch process:
A. Create a persistent table (I'll refer to this table as "stage") to
hold records that are pulled from the table that the user data is
inserted into (I'll refer to this table as "user").  This table will
have one extra column (say "orig_ctid") of type tid.

B. Remove the delete trigger from the user table.

The actual batch process:
1. Start an explicit transaction with serializable isolation level

2. Get an exclusive lock on the stage table.  This will prevent any
other batch processes from running concurrently.  The serializable
isolation level ensures that if a given batch process has to wait for
the lock, it will see all changes made by the prior batch process run.

3. Select records (including ctid) from user with a limit clause and
insert directly into stage:
    insert into stage select *, ctid as orig_ctid from user limit 10;

4. Remove records from user that were just inserted into stage (not sure
of performance here, as it's not a self-join):
    delete from user where ctid = any (array(select orig_ctid from stage));

5. Continue normal processing of records in the stage table.

6. Truncate the stage table.

7. Commit the transaction.  This ensures all data is processed (and only
processed once).

With all that said, this isn't something I've actually done in PG.  I've
done similar things in other other databases, so the concept should be
sound.

Hope this helps.

Andrew