begriffs open source - ai-pg/blob - full-docs/src/sgml/html/populate.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>14.4. Populating a Database</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses" /><link rel="next" href="non-durability.html" title="14.5. Non-Durable Settings" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">14.4. Populating a Database</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="performance-tips.html" title="Chapter 14. Performance Tips">Up</a></td><th width="60%" align="center">Chapter 14. Performance Tips</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="non-durability.html" title="14.5. Non-Durable Settings">Next</a></td></tr></table><hr /></div><div class="sect1" id="POPULATE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">14.4. Populating a Database <a href="#POPULATE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="populate.html#DISABLE-AUTOCOMMIT">14.4.1. Disable Autocommit</a></span></dt><dt><span class="sect2"><a href="populate.html#POPULATE-COPY-FROM">14.4.2. Use <code class="command">COPY</code></a></span></dt><dt><span class="sect2"><a href="populate.html#POPULATE-RM-INDEXES">14.4.3. Remove Indexes</a></span></dt><dt><span class="sect2"><a href="populate.html#POPULATE-RM-FKEYS">14.4.4. Remove Foreign Key Constraints</a></span></dt><dt><span class="sect2"><a href="populate.html#POPULATE-WORK-MEM">14.4.5. Increase <code class="varname">maintenance_work_mem</code></a></span></dt><dt><span class="sect2"><a href="populate.html#POPULATE-MAX-WAL-SIZE">14.4.6. Increase <code class="varname">max_wal_size</code></a></span></dt><dt><span class="sect2"><a href="populate.html#POPULATE-PITR">14.4.7. Disable WAL Archival and Streaming Replication</a></span></dt><dt><span class="sect2"><a href="populate.html#POPULATE-ANALYZE">14.4.8. Run <code class="command">ANALYZE</code> Afterwards</a></span></dt><dt><span class="sect2"><a href="populate.html#POPULATE-PG-DUMP">14.4.9. Some Notes about <span class="application">pg_dump</span></a></span></dt></dl></div><p>
   3    One might need to insert a large amount of data when first populating
   4    a database. This section contains some suggestions on how to make
   5    this process as efficient as possible.
   6   </p><div class="sect2" id="DISABLE-AUTOCOMMIT"><div class="titlepage"><div><div><h3 class="title">14.4.1. Disable Autocommit <a href="#DISABLE-AUTOCOMMIT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.13.7.3.2" class="indexterm"></a><p>
   7     When using multiple <code class="command">INSERT</code>s, turn off autocommit and just do
   8     one commit at the end.  (In plain
   9     SQL, this means issuing <code class="command">BEGIN</code> at the start and
  10     <code class="command">COMMIT</code> at the end.  Some client libraries might
  11     do this behind your back, in which case you need to make sure the
  12     library does it when you want it done.)  If you allow each
  13     insertion to be committed separately,
  14     <span class="productname">PostgreSQL</span> is doing a lot of work for
  15     each row that is added.  An additional benefit of doing all
  16     insertions in one transaction is that if the insertion of one row
  17     were to fail then the insertion of all rows inserted up to that
  18     point would be rolled back, so you won't be stuck with partially
  19     loaded data.
  20    </p></div><div class="sect2" id="POPULATE-COPY-FROM"><div class="titlepage"><div><div><h3 class="title">14.4.2. Use <code class="command">COPY</code> <a href="#POPULATE-COPY-FROM" class="id_link">#</a></h3></div></div></div><p>
  21     Use <a class="link" href="sql-copy.html" title="COPY"><code class="command">COPY</code></a> to load
  22     all the rows in one command, instead of using a series of
  23     <code class="command">INSERT</code> commands.  The <code class="command">COPY</code>
  24     command is optimized for loading large numbers of rows; it is less
  25     flexible than <code class="command">INSERT</code>, but incurs significantly
  26     less overhead for large data loads. Since <code class="command">COPY</code>
  27     is a single command, there is no need to disable autocommit if you
  28     use this method to populate a table.
  29    </p><p>
  30     If you cannot use <code class="command">COPY</code>, it might help to use <a class="link" href="sql-prepare.html" title="PREPARE"><code class="command">PREPARE</code></a> to create a
  31     prepared <code class="command">INSERT</code> statement, and then use
  32     <code class="command">EXECUTE</code> as many times as required.  This avoids
  33     some of the overhead of repeatedly parsing and planning
  34     <code class="command">INSERT</code>. Different interfaces provide this facility
  35     in different ways; look for <span class="quote">“<span class="quote">prepared statements</span>”</span> in the interface
  36     documentation.
  37    </p><p>
  38     Note that loading a large number of rows using
  39     <code class="command">COPY</code> is almost always faster than using
  40     <code class="command">INSERT</code>, even if <code class="command">PREPARE</code> is used and
  41     multiple insertions are batched into a single transaction.
  42    </p><p>
  43     <code class="command">COPY</code> is fastest when used within the same
  44     transaction as an earlier <code class="command">CREATE TABLE</code> or
  45     <code class="command">TRUNCATE</code> command. In such cases no WAL
  46     needs to be written, because in case of an error, the files
  47     containing the newly loaded data will be removed anyway.
  48     However, this consideration only applies when
  49     <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> is <code class="literal">minimal</code>
  50     as all commands must write WAL otherwise.
  51    </p></div><div class="sect2" id="POPULATE-RM-INDEXES"><div class="titlepage"><div><div><h3 class="title">14.4.3. Remove Indexes <a href="#POPULATE-RM-INDEXES" class="id_link">#</a></h3></div></div></div><p>
  52     If you are loading a freshly created table, the fastest method is to
  53     create the table, bulk load the table's data using
  54     <code class="command">COPY</code>, then create any indexes needed for the
  55     table.  Creating an index on pre-existing data is quicker than
  56     updating it incrementally as each row is loaded.
  57    </p><p>
  58     If you are adding large amounts of data to an existing table,
  59     it might be a win to drop the indexes,
  60     load the table, and then recreate the indexes.  Of course, the
  61     database performance for other users might suffer
  62     during the time the indexes are missing.  One should also think
  63     twice before dropping a unique index, since the error checking
  64     afforded by the unique constraint will be lost while the index is
  65     missing.
  66    </p></div><div class="sect2" id="POPULATE-RM-FKEYS"><div class="titlepage"><div><div><h3 class="title">14.4.4. Remove Foreign Key Constraints <a href="#POPULATE-RM-FKEYS" class="id_link">#</a></h3></div></div></div><p>
  67     Just as with indexes, a foreign key constraint can be checked
  68     <span class="quote">“<span class="quote">in bulk</span>”</span> more efficiently than row-by-row.  So it might be
  69     useful to drop foreign key constraints, load data, and re-create
  70     the constraints.  Again, there is a trade-off between data load
  71     speed and loss of error checking while the constraint is missing.
  72    </p><p>
  73     What's more, when you load data into a table with existing foreign key
  74     constraints, each new row requires an entry in the server's list of
  75     pending trigger events (since it is the firing of a trigger that checks
  76     the row's foreign key constraint).  Loading many millions of rows can
  77     cause the trigger event queue to overflow available memory, leading to
  78     intolerable swapping or even outright failure of the command.  Therefore
  79     it may be <span class="emphasis"><em>necessary</em></span>, not just desirable, to drop and re-apply
  80     foreign keys when loading large amounts of data.  If temporarily removing
  81     the constraint isn't acceptable, the only other recourse may be to split
  82     up the load operation into smaller transactions.
  83    </p></div><div class="sect2" id="POPULATE-WORK-MEM"><div class="titlepage"><div><div><h3 class="title">14.4.5. Increase <code class="varname">maintenance_work_mem</code> <a href="#POPULATE-WORK-MEM" class="id_link">#</a></h3></div></div></div><p>
  84     Temporarily increasing the <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a>
  85     configuration variable when loading large amounts of data can
  86     lead to improved performance.  This will help to speed up <code class="command">CREATE
  87     INDEX</code> commands and <code class="command">ALTER TABLE ADD FOREIGN KEY</code> commands.
  88     It won't do much for <code class="command">COPY</code> itself, so this advice is
  89     only useful when you are using one or both of the above techniques.
  90    </p></div><div class="sect2" id="POPULATE-MAX-WAL-SIZE"><div class="titlepage"><div><div><h3 class="title">14.4.6. Increase <code class="varname">max_wal_size</code> <a href="#POPULATE-MAX-WAL-SIZE" class="id_link">#</a></h3></div></div></div><p>
  91     Temporarily increasing the <a class="xref" href="runtime-config-wal.html#GUC-MAX-WAL-SIZE">max_wal_size</a>
  92     configuration variable can also
  93     make large data loads faster.  This is because loading a large
  94     amount of data into <span class="productname">PostgreSQL</span> will
  95     cause checkpoints to occur more often than the normal checkpoint
  96     frequency (specified by the <code class="varname">checkpoint_timeout</code>
  97     configuration variable). Whenever a checkpoint occurs, all dirty
  98     pages must be flushed to disk. By increasing
  99     <code class="varname">max_wal_size</code> temporarily during bulk
 100     data loads, the number of checkpoints that are required can be
 101     reduced.
 102    </p></div><div class="sect2" id="POPULATE-PITR"><div class="titlepage"><div><div><h3 class="title">14.4.7. Disable WAL Archival and Streaming Replication <a href="#POPULATE-PITR" class="id_link">#</a></h3></div></div></div><p>
 103     When loading large amounts of data into an installation that uses
 104     WAL archiving or streaming replication, it might be faster to take a
 105     new base backup after the load has completed than to process a large
 106     amount of incremental WAL data.  To prevent incremental WAL logging
 107     while loading, disable archiving and streaming replication, by setting
 108     <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> to <code class="literal">minimal</code>,
 109     <a class="xref" href="runtime-config-wal.html#GUC-ARCHIVE-MODE">archive_mode</a> to <code class="literal">off</code>, and
 110     <a class="xref" href="runtime-config-replication.html#GUC-MAX-WAL-SENDERS">max_wal_senders</a> to zero.
 111     But note that changing these settings requires a server restart,
 112     and makes any base backups taken before unavailable for archive
 113     recovery and standby server, which may lead to data loss.
 114    </p><p>
 115     Aside from avoiding the time for the archiver or WAL sender to process the
 116     WAL data, doing this will actually make certain commands faster, because
 117     they do not to write WAL at all if <code class="varname">wal_level</code>
 118     is <code class="literal">minimal</code> and the current subtransaction (or top-level
 119     transaction) created or truncated the table or index they change.  (They
 120     can guarantee crash safety more cheaply by doing
 121     an <code class="function">fsync</code> at the end than by writing WAL.)
 122    </p></div><div class="sect2" id="POPULATE-ANALYZE"><div class="titlepage"><div><div><h3 class="title">14.4.8. Run <code class="command">ANALYZE</code> Afterwards <a href="#POPULATE-ANALYZE" class="id_link">#</a></h3></div></div></div><p>
 123     Whenever you have significantly altered the distribution of data
 124     within a table, running <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> is strongly recommended. This
 125     includes bulk loading large amounts of data into the table.  Running
 126     <code class="command">ANALYZE</code> (or <code class="command">VACUUM ANALYZE</code>)
 127     ensures that the planner has up-to-date statistics about the
 128     table.  With no statistics or obsolete statistics, the planner might
 129     make poor decisions during query planning, leading to poor
 130     performance on any tables with inaccurate or nonexistent
 131     statistics.  Note that if the autovacuum daemon is enabled, it might
 132     run <code class="command">ANALYZE</code> automatically; see
 133     <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="24.1.3. Updating Planner Statistics">Section 24.1.3</a>
 134     and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a> for more information.
 135    </p></div><div class="sect2" id="POPULATE-PG-DUMP"><div class="titlepage"><div><div><h3 class="title">14.4.9. Some Notes about <span class="application">pg_dump</span> <a href="#POPULATE-PG-DUMP" class="id_link">#</a></h3></div></div></div><p>
 136     Dump scripts generated by <span class="application">pg_dump</span> automatically apply
 137     several, but not all, of the above guidelines.  To restore a
 138     <span class="application">pg_dump</span> dump as quickly as possible, you need to
 139     do a few extra things manually.  (Note that these points apply while
 140     <span class="emphasis"><em>restoring</em></span> a dump, not while <span class="emphasis"><em>creating</em></span> it.
 141     The same points apply whether loading a text dump with
 142     <span class="application">psql</span> or using <span class="application">pg_restore</span> to load
 143     from a <span class="application">pg_dump</span> archive file.)
 144    </p><p>
 145     By default, <span class="application">pg_dump</span> uses <code class="command">COPY</code>, and when
 146     it is generating a complete schema-and-data dump, it is careful to
 147     load data before creating indexes and foreign keys.  So in this case
 148     several guidelines are handled automatically.  What is left
 149     for you to do is to:
 150     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
 151        Set appropriate (i.e., larger than normal) values for
 152        <code class="varname">maintenance_work_mem</code> and
 153        <code class="varname">max_wal_size</code>.
 154       </p></li><li class="listitem"><p>
 155        If using WAL archiving or streaming replication, consider disabling
 156        them during the restore. To do that, set <code class="varname">archive_mode</code>
 157        to <code class="literal">off</code>,
 158        <code class="varname">wal_level</code> to <code class="literal">minimal</code>, and
 159        <code class="varname">max_wal_senders</code> to zero before loading the dump.
 160        Afterwards, set them back to the right values and take a fresh
 161        base backup.
 162       </p></li><li class="listitem"><p>
 163        Experiment with the parallel dump and restore modes of both
 164        <span class="application">pg_dump</span> and <span class="application">pg_restore</span> and find the
 165        optimal number of concurrent jobs to use. Dumping and restoring in
 166        parallel by means of the <code class="option">-j</code> option should give you a
 167        significantly higher performance over the serial mode.
 168       </p></li><li class="listitem"><p>
 169        Consider whether the whole dump should be restored as a single
 170        transaction.  To do that, pass the <code class="option">-1</code> or
 171        <code class="option">--single-transaction</code> command-line option to
 172        <span class="application">psql</span> or <span class="application">pg_restore</span>. When using this
 173        mode, even the smallest of errors will rollback the entire restore,
 174        possibly discarding many hours of processing.  Depending on how
 175        interrelated the data is, that might seem preferable to manual cleanup,
 176        or not.  <code class="command">COPY</code> commands will run fastest if you use a single
 177        transaction and have WAL archiving turned off.
 178       </p></li><li class="listitem"><p>
 179        If multiple CPUs are available in the database server, consider using
 180        <span class="application">pg_restore</span>'s <code class="option">--jobs</code> option.  This
 181        allows concurrent data loading and index creation.
 182       </p></li><li class="listitem"><p>
 183        Run <code class="command">ANALYZE</code> afterwards.
 184       </p></li></ul></div><p>
 185    </p><p>
 186     A data-only dump will still use <code class="command">COPY</code>, but it does not
 187     drop or recreate indexes, and it does not normally touch foreign
 188     keys.
 189
 190      <a href="#ftn.id-1.5.13.7.11.4.2" class="footnote"><sup class="footnote" id="id-1.5.13.7.11.4.2">[14]</sup></a>
 191
 192     So when loading a data-only dump, it is up to you to drop and recreate
 193     indexes and foreign keys if you wish to use those techniques.
 194     It's still useful to increase <code class="varname">max_wal_size</code>
 195     while loading the data, but don't bother increasing
 196     <code class="varname">maintenance_work_mem</code>; rather, you'd do that while
 197     manually recreating indexes and foreign keys afterwards.
 198     And don't forget to <code class="command">ANALYZE</code> when you're done; see
 199     <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="24.1.3. Updating Planner Statistics">Section 24.1.3</a>
 200     and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a> for more information.
 201    </p></div><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.id-1.5.13.7.11.4.2" class="footnote"><p><a href="#id-1.5.13.7.11.4.2" class="para"><sup class="para">[14] </sup></a>
 202        You can get the effect of disabling foreign keys by using
 203        the <code class="option">--disable-triggers</code> option — but realize that
 204        that eliminates, rather than just postpones, foreign key
 205        validation, and so it is possible to insert bad data if you use it.
 206       </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="performance-tips.html" title="Chapter 14. Performance Tips">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="non-durability.html" title="14.5. Non-Durable Settings">Next</a></td></tr><tr><td width="40%" align="left" valign="top">14.3. Controlling the Planner with Explicit <code class="literal">JOIN</code> Clauses </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 14.5. Non-Durable Settings</td></tr></table></div></body></html>