On Tue, Dec 1, 2009 at 2:58 PM, Steve Atkins <steve@blighty.com> wrote:
> For the bulk imports of old data, import the data first, then build the indexes and add constraints later. Disable
autovacuumduring the initial bulk import. Also, consider turning fsync off for those imports (less of a win on modern
versions,but every little helps).
>
for 8.3 (possibly earlier, I don't know) and up, you can
SET LOCAL synchronous_commit TO OFF
in your transactions and get the bulk of the benefit of turning off
fsync without having to mess with server level settings.
You can also use
SET SESSION synchronous_commit TO OFF
for your whole session to disable the fsync on commit (or implicit
commit per statement outside of transaction).
The beauty of this over fsync off is that if your DB crashes, you lose
things at transaction boundaries, not randomly, so it is easier to
re-do your work.