<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Populating a Database</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Performance Tips"
HREF="performance-tips.html"><LINK
REL="PREVIOUS"
TITLE="Controlling the Planner with Explicit JOIN Clauses"
HREF="explicit-joins.html"><LINK
REL="NEXT"
TITLE="Server Administration"
HREF="admin.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="explicit-joins.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="performance-tips.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 13. Performance Tips</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="performance-tips.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="admin.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="POPULATE"
>13.4. Populating a Database</A
></H1
><P
> One may need to insert a large amount of data when first populating
a database. This section contains some suggestions on how to make
this process as efficient as possible.
</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="DISABLE-AUTOCOMMIT"
>13.4.1. Disable Autocommit</A
></H2
><A
NAME="AEN14931"
></A
><P
> Turn off autocommit and just do one commit at the end. (In plain
SQL, this means issuing <TT
CLASS="COMMAND"
>BEGIN</TT
> at the start and
<TT
CLASS="COMMAND"
>COMMIT</TT
> at the end. Some client libraries may
do this behind your back, in which case you need to make sure the
library does it when you want it done.) If you allow each
insertion to be committed separately,
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> is doing a lot of work for
each row that is added. An additional benefit of doing all
insertions in one transaction is that if the insertion of one row
were to fail then the insertion of all rows inserted up to that
point would be rolled back, so you won't be stuck with partially
loaded data.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="POPULATE-COPY-FROM"
>13.4.2. Use <TT
CLASS="COMMAND"
>COPY</TT
></A
></H2
><P
> Use <A
HREF="sql-copy.html"
><I
>COPY</I
></A
> to load
all the rows in one command, instead of using a series of
<TT
CLASS="COMMAND"
>INSERT</TT
> commands. The <TT
CLASS="COMMAND"
>COPY</TT
>
command is optimized for loading large numbers of rows; it is less
flexible than <TT
CLASS="COMMAND"
>INSERT</TT
>, but incurs significantly
less overhead for large data loads. Since <TT
CLASS="COMMAND"
>COPY</TT
>
is a single command, there is no need to disable autocommit if you
use this method to populate a table.
</P
><P
> If you cannot use <TT
CLASS="COMMAND"
>COPY</TT
>, it may help to use <A
HREF="sql-prepare.html"
><I
>PREPARE</I
></A
> to create a
prepared <TT
CLASS="COMMAND"
>INSERT</TT
> statement, and then use
<TT
CLASS="COMMAND"
>EXECUTE</TT
> as many times as required. This avoids
some of the overhead of repeatedly parsing and planning
<TT
CLASS="COMMAND"
>INSERT</TT
>.
</P
><P
> Note that loading a large number of rows using
<TT
CLASS="COMMAND"
>COPY</TT
> is almost always faster than using
<TT
CLASS="COMMAND"
>INSERT</TT
>, even if <TT
CLASS="COMMAND"
>PREPARE</TT
> is used and
multiple insertions are batched into a single transaction.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="POPULATE-RM-INDEXES"
>13.4.3. Remove Indexes</A
></H2
><P
> If you are loading a freshly created table, the fastest way is to
create the table, bulk load the table's data using
<TT
CLASS="COMMAND"
>COPY</TT
>, then create any indexes needed for the
table. Creating an index on pre-existing data is quicker than
updating it incrementally as each row is loaded.
</P
><P
> If you are augmenting an existing table, you can drop the index,
load the table, and then recreate the index. Of course, the
database performance for other users may be adversely affected
during the time that the index is missing. One should also think
twice before dropping unique indexes, since the error checking
afforded by the unique constraint will be lost while the index is
missing.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="POPULATE-WORK-MEM"
>13.4.4. Increase <TT
CLASS="VARNAME"
>maintenance_work_mem</TT
></A
></H2
><P
> Temporarily increasing the <A
HREF="runtime-config.html#GUC-MAINTENANCE-WORK-MEM"
>maintenance_work_mem</A
>
configuration variable when loading large amounts of data can
lead to improved performance. This is because when a B-tree index
is created from scratch, the existing content of the table needs
to be sorted. Allowing the merge sort to use more memory
means that fewer merge passes will be required. A larger setting for
<TT
CLASS="VARNAME"
>maintenance_work_mem</TT
> may also speed up validation
of foreign-key constraints.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="POPULATE-CHECKPOINT-SEGMENTS"
>13.4.5. Increase <TT
CLASS="VARNAME"
>checkpoint_segments</TT
></A
></H2
><P
> Temporarily increasing the <A
HREF="runtime-config.html#GUC-CHECKPOINT-SEGMENTS"
>checkpoint_segments</A
> configuration variable can also
make large data loads faster. This is because loading a large
amount of data into <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> can
cause checkpoints to occur more often than the normal checkpoint
frequency (specified by the <TT
CLASS="VARNAME"
>checkpoint_timeout</TT
>
configuration variable). Whenever a checkpoint occurs, all dirty
pages must be flushed to disk. By increasing
<TT
CLASS="VARNAME"
>checkpoint_segments</TT
> temporarily during bulk
data loads, the number of checkpoints that are required can be
reduced.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="POPULATE-ANALYZE"
>13.4.6. Run <TT
CLASS="COMMAND"
>ANALYZE</TT
> Afterwards</A
></H2
><P
> Whenever you have significantly altered the distribution of data
within a table, running <A
HREF="sql-analyze.html"
><I
>ANALYZE</I
></A
> is strongly recommended. This
includes bulk loading large amounts of data into the table. Running
<TT
CLASS="COMMAND"
>ANALYZE</TT
> (or <TT
CLASS="COMMAND"
>VACUUM ANALYZE</TT
>)
ensures that the planner has up-to-date statistics about the
table. With no statistics or obsolete statistics, the planner may
make poor decisions during query planning, leading to poor
performance on any tables with inaccurate or nonexistent
statistics.
</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="explicit-joins.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="admin.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Controlling the Planner with Explicit <TT
CLASS="LITERAL"
>JOIN</TT
> Clauses</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="performance-tips.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Server Administration</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
