Re: allow LIMIT in UPDATE and DELETE

----- Original Message ----
>From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
>To: Shelby Cain <alyandon(at)yahoo(dot)com>
>Cc: SCassidy(at)overlandstorage(dot)com; Postgres general mailing list ><pgsql-general(at)postgresql(dot)org>; pgsql-general-owner(at)postgresql(dot)org
>Sent: Friday, May 19, 2006 11:46:42 AM
>Subject: Re: [GENERAL] allow LIMIT in UPDATE and DELETE
>
>Well, sometimes it's not that easy. How would you handle a batch
>processing system which stores the incoming requests in a queue table in
>the data base, and then periodically processes a batch of it, with the
>additional constraint that it is allowed to process at most 1000 at a
>time so it won't produce a too long running transaction ? Suppose the
>processing is quite costly, and the queue can have bursts of incoming
>requests which then have to be slowly processed... the requests are
>coming from the web and must be processed asynchronously, the insert
>into the data base must be very fast.

I can't imagine a case where a properly tuned Postgresql installation with appropriate hardware backing it couldn't handle that particular kind of workload pattern. However, I usually work with Oracle so tables used as queues don't have the same performance issues you'd run into with Postgresql.

Regardless, this type of queue problem can also be tackled by having your data layer persisting the input from the web in memory (which maintains a low perceived response time to the client) and posting to the table as fast as the database allows.

>
>So what I'm talking about is not maintenance, but on-line operation...

Different problems will always require different solutions. In the case you present I don't really think partitioning is the answer.

Regards,

Shelby Cain