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
|
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/populate.sgml,v 2.3 2000/09/29 20:21:34 petere Exp $
-->
<chapter id="populate">
<title>Populating a Database</title>
<note>
<title>Author</title>
<para>
Written by Tom Lane, from an e-mail message dated 1999-12-05.
</para>
</note>
<para>
One may need to do a large number of table insertions when first
populating a database. Here are some tips and techniques for making that as
efficient as possible.
</para>
<sect1 id="disable-autocommit">
<title>Disable Auto-commit</title>
<para>
Turn off auto-commit and just do one commit at
the end. Otherwise <productname>Postgres</productname> is doing a
lot of work for each record
added. In general when you are doing bulk inserts, you want
to turn off some of the database features to gain speed.
</para>
</sect1>
<sect1 id="populate-copy-from">
<title>Use COPY FROM</title>
<para>
Use <command>COPY FROM STDIN</command> to load all the records in one
command, instead
of a series of INSERT commands. This reduces parsing, planning, etc
overhead a great deal. If you do this then it's not necessary to fool
around with autocommit.
</para>
</sect1>
<sect1 id="populate-rm-indices">
<title>Remove Indices</title>
<para>
If you are loading a freshly created table, the fastest way is to
create the table, bulk-load with COPY, then create any indexes needed
for the table. Creating an index on pre-existing data is quicker than
updating it incrementally as each record is loaded.
</para>
<para>
If you are augmenting an existing table, you can <command>DROP
INDEX</command>, load the table, then recreate the index. Of
course, the database performance for other users may be adversely
affected during the time that the index is missing.
</para>
</sect1>
</chapter>
<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->
|
