Hi, Tom, Tom Lane wrote: >>Some time ago, I had some tests with large bulk insertions, and it >>turned out that SERIALIZABLE seemed to be 30% faster, which surprised us. > > That surprises me too --- can you provide details on the test case so > other people can reproduce it? AFAIR the only performance difference > between SERIALIZABLE and READ COMMITTED is the frequency with which > transaction status snapshots are taken; your report suggests you were > spending 30% of the time in GetSnapshotData, which is a lot higher than > I've ever seen in a profile. It was in my previous Job two years ago, so I don't have access to the exact code, and my memory is foggy. It was PostGIS 0.8 and PostgreSQL 7.4. AFAIR, it was inserting into a table with about 6 columns and some indices, some columns having database-provided values (now() and a SERIAL column), where the other columns (a PostGIS Point, a long, a foreign key into another table) were set via the aplication. We tried different insertion methods (INSERT, prepared statements, a pgjdbc patch to allow COPY support), different bunch sizes and different number of parallel connections to get the highest overall insert speed. However, the project never went productive the way it was designed initially. As you write about transaction snapshots: It may be that the PostgreSQL config was not optimized well enough, and the hard disk was rather slow. > As to the original question, a transaction that hasn't modified the > database does not bother to write either a commit or abort record to > pg_xlog. I think you'd be very hard pressed to measure any speed > difference between saying COMMIT and saying ROLLBACK after a read-only > transaction. It'd be worth your while to let transactions run longer > to minimize their startup/shutdown overhead, but there's a point of > diminishing returns --- you don't want client code leaving transactions > open for hours, because of the negative side-effects of holding locks > that long (eg, VACUUM can't reclaim dead rows). Okay, so I'll stick with my current behaviour (Autocommit off and ROLLBACK after each bunch of work). Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
