|
| 1 | + <chapter> |
| 2 | + <title>Populating a Database</title> |
| 3 | + |
| 4 | + <note> |
| 5 | + <title>Author</title> |
| 6 | + <para> |
| 7 | + Written by Tom Lane, from an e-mail message dated 1999-12-05. |
| 8 | + </para> |
| 9 | + </note> |
| 10 | + |
| 11 | + <para> |
| 12 | + One may need to do a large number of table insertions when first |
| 13 | + populating a database. Here are some tips and techniques for making that as |
| 14 | + efficient as possible. |
| 15 | + </para> |
| 16 | + |
| 17 | + <sect1> |
| 18 | + <title>Disable Auto-commit</title> |
| 19 | + |
| 20 | + <para> |
| 21 | + Turn off auto-commit and just do one commit at |
| 22 | + the end. Otherwise <productname>Postgres</productname> is doing a |
| 23 | + lot of work for each record |
| 24 | + added. In general when you are doing bulk inserts, you want |
| 25 | + to turn off some of the database features to gain speed. |
| 26 | + </para> |
| 27 | + </sect1> |
| 28 | + |
| 29 | + <sect1> |
| 30 | + <title>Use COPY FROM</title> |
| 31 | + |
| 32 | + <para> |
| 33 | + Use <command>COPY FROM STDIN</command> to load all the records in one |
| 34 | + command, instead |
| 35 | + of a series of INSERT commands. This reduces parsing, planning, etc |
| 36 | + overhead a great deal. If you do this then it's not necessary to fool |
| 37 | + around with autocommit. |
| 38 | + </para> |
| 39 | + </sect1> |
| 40 | + |
| 41 | + <sect1> |
| 42 | + <title>Remove Indices</title> |
| 43 | + |
| 44 | + <para> |
| 45 | + If you are loading a freshly created table, the fastest way is to |
| 46 | + create the table, bulk-load with COPY, then create any indexes needed |
| 47 | + for the table. Creating an index on pre-existing data is quicker than |
| 48 | + updating it incrementally as each record is loaded. |
| 49 | + </para> |
| 50 | + |
| 51 | + <para> |
| 52 | + If you are augmenting an existing table, you can <command>DROP |
| 53 | + INDEX</command>, load the table, then recreate the index. Of |
| 54 | + course, the database performance for other users may be adversely |
| 55 | + affected during the time that the index is missing. |
| 56 | + </para> |
| 57 | + </sect1> |
| 58 | + </chapter> |
| 59 | + |
| 60 | +<!-- Keep this comment at the end of the file |
| 61 | +Local variables: |
| 62 | +mode:sgml |
| 63 | +sgml-omittag:nil |
| 64 | +sgml-shorttag:t |
| 65 | +sgml-minimize-attributes:nil |
| 66 | +sgml-always-quote-attributes:t |
| 67 | +sgml-indent-step:1 |
| 68 | +sgml-indent-data:t |
| 69 | +sgml-parent-document:nil |
| 70 | +sgml-default-dtd-file:"./reference.ced" |
| 71 | +sgml-exposed-tags:nil |
| 72 | +sgml-local-catalogs:("/usr/lib/sgml/CATALOG") |
| 73 | +sgml-local-ecat-files:nil |
| 74 | +End: |
| 75 | +--> |
