"Phoenix Kiula" <phoenix.kiula@xxxxxxxxx> writes: > If I am reading this right, does this mean it is probably better to > leave fsync as "fsync=off" on production machines? Only if you can reconstruct your data from other sources in the case of a server crash or power failure. I wouldn't recommend it. > Also, is COMMIT automatic for my queries? In some minor testing I did (not > scientific I did at all) some queries through Perl did not update the > database at all. I had "fsync=off" in my conf file, and there was no COMMIT > etc in my SQL, just plain SQL. So I am a bit confused. What's a good > starting point? psql runs in autocommit mode by default. If you want multiple queries in a transaction you have to issue a BEGIN statement. Drivers may do various things by default. >> Grouping more work into a single transaction makes the delay for the fsync at >> COMMIT time less of a problem. > > > Agree. That's what I am trying to do. Include as many UPDATEs etc into > the same TRANSACTION block, but my worry is when I read about > autocommit and how it is enabled by default in postgresql 8. > Specifying explicit BEGIN and COMMIT blocks should only commit when I > want the DB to, or will each UPDATE in the middle of this block get > executed? > > Sorry if this is a naive question. I am reading up as much as I can. If you issue a BEGIN then nothing gets committed until you issue a COMMIT. If anything happens in the meantime then everything you've done since the BEGIN disappears. For batch work like loading then it makes sense to handle a 100-1000 records per transaction. But for most purposes you want to group things together based on what you want to happen if an error occurs. Group together into a single transaction precisely the changes that you want to be committed together or rolled back together. Don't structure your program around the performance issues. For the remaining questions I would say you need to experiment. Perhaps others will have more ideas. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
