codekingpro/portable-devtools
115k
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>