On Sep 12, 2005, at 6:02 PM, Brandon Black wrote:
- using COPY instead of INSERT ?
(should be easy to do from the aggregators)
Possibly, although it would kill the current design of returning the database transaction status for a single client packet back to the client on transaction success/failure. The aggregator could put several clients' data into a series of delayed multi-row copy statements.
buffer through the file system on your aggregator. once you "commit" to local disk file, return back to your client that you got the data. then insert into the actual postgres DB in large batches of inserts inside a single Postgres transaction.
we have our web server log certain tracking requests to a local file. with file locks and append mode, it is extremely quick and has little contention delays. then every so often, we lock the file, rename it, release the lock, then process it at our leisure to do the inserts to Pg in one big transaction.
Vivek Khera, Ph.D.
+1-301-869-4449 x806