File: populate.html

package info (click to toggle)
pgadmin3 1.4.3-2
  • links: PTS
  • area: main
  • in suites: etch, etch-m68k
  • size: 29,796 kB
  • ctags: 10,758
  • sloc: cpp: 55,356; sh: 6,164; ansic: 1,520; makefile: 576; sql: 482; xml: 100; perl: 18
file content (175 lines) | stat: -rw-r--r-- 10,310 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>13.4.Populating a Database</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="performance-tips.html" title="Chapter13.Performance Tips">
<link rel="prev" href="explicit-joins.html" title="13.3.Controlling the Planner with Explicit JOIN Clauses">
<link rel="next" href="admin.html" title="PartIII.Server Administration">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="populate"></a>13.4.Populating a Database</h2></div></div></div>
<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" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="disable-autocommit"></a>13.4.1.Disable Autocommit</h3></div></div></div>
<a name="id633614"></a><p>    Turn off autocommit and just do one commit at the end.  (In plain
    SQL, this means issuing <code class="command">BEGIN</code> at the start and
    <code class="command">COMMIT</code> 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" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="populate-copy-from"></a>13.4.2.Use <code class="command">COPY</code></h3></div></div></div>
<p>    Use <a href="sql-copy.html">COPY</a> to load
    all the rows in one command, instead of using a series of
    <code class="command">INSERT</code> commands.  The <code class="command">COPY</code>
    command is optimized for loading large numbers of rows; it is less
    flexible than <code class="command">INSERT</code>, but incurs significantly
    less overhead for large data loads. Since <code class="command">COPY</code>
    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 <code class="command">COPY</code>, it may help to use <a href="sql-prepare.html">PREPARE</a> to create a
    prepared <code class="command">INSERT</code> statement, and then use
    <code class="command">EXECUTE</code> as many times as required.  This avoids
    some of the overhead of repeatedly parsing and planning
    <code class="command">INSERT</code>.
   </p>
<p>    Note that loading a large number of rows using
    <code class="command">COPY</code> is almost always faster than using
    <code class="command">INSERT</code>, even if <code class="command">PREPARE</code> is used and
    multiple insertions are batched into a single transaction.
   </p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="populate-rm-indexes"></a>13.4.3.Remove Indexes</h3></div></div></div>
<p>    If you are loading a freshly created table, the fastest way is to
    create the table, bulk load the table's data using
    <code class="command">COPY</code>, 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 adding large amounts of data to an existing table,
    it may be a win to 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" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="populate-rm-fkeys"></a>13.4.4.Remove Foreign Key Constraints</h3></div></div></div>
<p>    Just as with indexes, a foreign key constraint can be checked
    &#8220;<span class="quote">in bulk</span>&#8221; more efficiently than row-by-row.  So it may be
    useful to drop foreign key constraints, load data, and re-create
    the constraints.  Again, there is a trade-off between data load
    speed and loss of error checking while the constraint is missing.
   </p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="populate-work-mem"></a>13.4.5.Increase <code class="varname">maintenance_work_mem</code></h3></div></div></div>
<p>    Temporarily increasing the <a href="runtime-config-resource.html#guc-maintenance-work-mem">maintenance_work_mem</a>
    configuration variable when loading large amounts of data can
    lead to improved performance.  This will help to speed up <code class="command">CREATE
    INDEX</code> commands and <code class="command">ALTER TABLE ADD FOREIGN KEY</code> commands.
    It won't do much for <code class="command">COPY</code> itself, so this advice is
    only useful when you are using one or both of the above techniques.
   </p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="populate-checkpoint-segments"></a>13.4.6.Increase <code class="varname">checkpoint_segments</code></h3></div></div></div>
<p>    Temporarily increasing the <a href="runtime-config-wal.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> will
    cause checkpoints to occur more often than the normal checkpoint
    frequency (specified by the <code class="varname">checkpoint_timeout</code>
    configuration variable). Whenever a checkpoint occurs, all dirty
    pages must be flushed to disk. By increasing
    <code class="varname">checkpoint_segments</code> temporarily during bulk
    data loads, the number of checkpoints that are required can be
    reduced.
   </p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="populate-analyze"></a>13.4.7.Run <code class="command">ANALYZE</code> Afterwards</h3></div></div></div>
<p>    Whenever you have significantly altered the distribution of data
    within a table, running <a href="sql-analyze.html">ANALYZE</a> is strongly recommended. This
    includes bulk loading large amounts of data into the table.  Running
    <code class="command">ANALYZE</code> (or <code class="command">VACUUM ANALYZE</code>)
    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 class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="populate-pg-dump"></a>13.4.8.Some Notes About <span class="application">pg_dump</span></h3></div></div></div>
<p>    Dump scripts generated by <span class="application">pg_dump</span> automatically apply
    several, but not all, of the above guidelines.  To reload a
    <span class="application">pg_dump</span> dump as quickly as possible, you need to
    do a few extra things manually.  (Note that these points apply while
    <span class="emphasis"><em>restoring</em></span> a dump, not while <span class="emphasis"><em>creating</em></span> it.
    The same points apply when using <span class="application">pg_restore</span> to load
    from a <span class="application">pg_dump</span> archive file.)
   </p>
<p>    By default, <span class="application">pg_dump</span> uses <code class="command">COPY</code>, and when
    it is generating a complete schema-and-data dump, it is careful to
    load data before creating indexes and foreign keys.  So in this case
    the first several guidelines are handled automatically.  What is left
    for you to do is to set appropriate (i.e., larger than normal) values
    for <code class="varname">maintenance_work_mem</code> and
    <code class="varname">checkpoint_segments</code> before loading the dump script,
    and then to run <code class="command">ANALYZE</code> afterwards.
   </p>
<p>    A data-only dump will still use <code class="command">COPY</code>, but it does not
    drop or recreate indexes, and it does not normally touch foreign
    keys.

     <sup>[<a name="id634089" href="#ftn.id634089">8</a>]</sup>

    So when loading a data-only dump, it is up to you to drop and recreate
    indexes and foreign keys if you wish to use those techniques.
    It's still useful to increase <code class="varname">checkpoint_segments</code>
    while loading the data, but don't bother increasing
    <code class="varname">maintenance_work_mem</code>; rather, you'd do that while
    manually recreating indexes and foreign keys afterwards.
    And don't forget to <code class="command">ANALYZE</code> when you're done.
   </p>
</div>
<div class="footnotes">
<br><hr width="100" align="left">
<div class="footnote"><p><sup>[<a name="ftn.id634089" href="#id634089">8</a>] </sup>       You can get the effect of disabling foreign keys by using
       the <code class="option">-X disable-triggers</code> option [mdash ] but realize that
       that eliminates, rather than just postponing, foreign key
       validation, and so it is possible to insert bad data if you use it.
      </p></div>
</div>
</div></body>
</html>