Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
populate.html206 linesDownload Raw Back to 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 16.3 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 populating4   a database. This section contains some suggestions on how to make5   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 do8    one commit at the end.  (In plain9    SQL, this means issuing <code class="command">BEGIN</code> at the start and10    <code class="command">COMMIT</code> at the end.  Some client libraries might11    do this behind your back, in which case you need to make sure the12    library does it when you want it done.)  If you allow each13    insertion to be committed separately,14    <span class="productname">PostgreSQL</span> is doing a lot of work for15    each row that is added.  An additional benefit of doing all16    insertions in one transaction is that if the insertion of one row17    were to fail then the insertion of all rows inserted up to that18    point would be rolled back, so you won't be stuck with partially19    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 load22    all the rows in one command, instead of using a series of23    <code class="command">INSERT</code> commands.  The <code class="command">COPY</code>24    command is optimized for loading large numbers of rows; it is less25    flexible than <code class="command">INSERT</code>, but incurs significantly26    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 you28    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 a31    prepared <code class="command">INSERT</code> statement, and then use32    <code class="command">EXECUTE</code> as many times as required.  This avoids33    some of the overhead of repeatedly parsing and planning34    <code class="command">INSERT</code>. Different interfaces provide this facility35    in different ways; look for <span class="quote">“<span class="quote">prepared statements</span>”</span> in the interface36    documentation.37   </p><p>38    Note that loading a large number of rows using39    <code class="command">COPY</code> is almost always faster than using40    <code class="command">INSERT</code>, even if <code class="command">PREPARE</code> is used and41    multiple insertions are batched into a single transaction.42   </p><p>43    <code class="command">COPY</code> is fastest when used within the same44    transaction as an earlier <code class="command">CREATE TABLE</code> or45    <code class="command">TRUNCATE</code> command. In such cases no WAL46    needs to be written, because in case of an error, the files47    containing the newly loaded data will be removed anyway.48    However, this consideration only applies when49    <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 to53    create the table, bulk load the table's data using54    <code class="command">COPY</code>, then create any indexes needed for the55    table.  Creating an index on pre-existing data is quicker than56    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, the61    database performance for other users might suffer62    during the time the indexes are missing.  One should also think63    twice before dropping a unique index, since the error checking64    afforded by the unique constraint will be lost while the index is65    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 checked68    <span class="quote">“<span class="quote">in bulk</span>”</span> more efficiently than row-by-row.  So it might be69    useful to drop foreign key constraints, load data, and re-create70    the constraints.  Again, there is a trade-off between data load71    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 key74    constraints, each new row requires an entry in the server's list of75    pending trigger events (since it is the firing of a trigger that checks76    the row's foreign key constraint).  Loading many millions of rows can77    cause the trigger event queue to overflow available memory, leading to78    intolerable swapping or even outright failure of the command.  Therefore79    it may be <span class="emphasis"><em>necessary</em></span>, not just desirable, to drop and re-apply80    foreign keys when loading large amounts of data.  If temporarily removing81    the constraint isn't acceptable, the only other recourse may be to split82    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 can86    lead to improved performance.  This will help to speed up <code class="command">CREATE87    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 is89    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 also93    make large data loads faster.  This is because loading a large94    amount of data into <span class="productname">PostgreSQL</span> will95    cause checkpoints to occur more often than the normal checkpoint96    frequency (specified by the <code class="varname">checkpoint_timeout</code>97    configuration variable). Whenever a checkpoint occurs, all dirty98    pages must be flushed to disk. By increasing99    <code class="varname">max_wal_size</code> temporarily during bulk100    data loads, the number of checkpoints that are required can be101    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 uses104    WAL archiving or streaming replication, it might be faster to take a105    new base backup after the load has completed than to process a large106    amount of incremental WAL data.  To prevent incremental WAL logging107    while loading, disable archiving and streaming replication, by setting108    <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>, and110    <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 archive113    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 the116    WAL data, doing this will actually make certain commands faster, because117    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-level119    transaction) created or truncated the table or index they change.  (They120    can guarantee crash safety more cheaply by doing121    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 data124    within a table, running <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> is strongly recommended. This125    includes bulk loading large amounts of data into the table.  Running126    <code class="command">ANALYZE</code> (or <code class="command">VACUUM ANALYZE</code>)127    ensures that the planner has up-to-date statistics about the128    table.  With no statistics or obsolete statistics, the planner might129    make poor decisions during query planning, leading to poor130    performance on any tables with inaccurate or nonexistent131    statistics.  Note that if the autovacuum daemon is enabled, it might132    run <code class="command">ANALYZE</code> automatically; see133    <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="25.1.3. Updating Planner Statistics">Section 25.1.3</a>134    and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.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 apply137    several, but not all, of the above guidelines.  To restore a138    <span class="application">pg_dump</span> dump as quickly as possible, you need to139    do a few extra things manually.  (Note that these points apply while140    <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 with142    <span class="application">psql</span> or using <span class="application">pg_restore</span> to load143    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 when146    it is generating a complete schema-and-data dump, it is careful to147    load data before creating indexes and foreign keys.  So in this case148    several guidelines are handled automatically.  What is left149    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 for152       <code class="varname">maintenance_work_mem</code> and153       <code class="varname">max_wal_size</code>.154      </p></li><li class="listitem"><p>155       If using WAL archiving or streaming replication, consider disabling156       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>, and159       <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 fresh161       base backup.162      </p></li><li class="listitem"><p>163       Experiment with the parallel dump and restore modes of both164       <span class="application">pg_dump</span> and <span class="application">pg_restore</span> and find the165       optimal number of concurrent jobs to use. Dumping and restoring in166       parallel by means of the <code class="option">-j</code> option should give you a167       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 single170       transaction.  To do that, pass the <code class="option">-1</code> or171       <code class="option">--single-transaction</code> command-line option to172       <span class="application">psql</span> or <span class="application">pg_restore</span>. When using this173       mode, even the smallest of errors will rollback the entire restore,174       possibly discarding many hours of processing.  Depending on how175       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 single177       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 using180       <span class="application">pg_restore</span>'s <code class="option">--jobs</code> option.  This181       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 not187    drop or recreate indexes, and it does not normally touch foreign188    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 recreate193    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 increasing196    <code class="varname">maintenance_work_mem</code>; rather, you'd do that while197    manually recreating indexes and foreign keys afterwards.198    And don't forget to <code class="command">ANALYZE</code> when you're done; see199    <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="25.1.3. Updating Planner Statistics">Section 25.1.3</a>200    and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.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 using203       the <code class="option">--disable-triggers</code> option — but realize that204       that eliminates, rather than just postpones, foreign key205       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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 14.5. Non-Durable Settings</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai