Re: optimizing advice

On Tue, Dec 1, 2009 at 2:58 PM, Steve Atkins <steve(at)blighty(dot)com> wrote:
> For the bulk imports of old data, import the data first, then build the indexes and add constraints later. Disable autovacuum during 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.