Populating a Database

10.4. Populating a Database

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.

10.4.1. Disable Autocommit

Turn off autocommit and just do one commit at the end. (In plain SQL, this means issuing BEGIN at the start and COMMIT 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, PostgreSQL is doing a lot of work for each record added. An additional benefit of doing all insertions in one transaction is that if the insertion of one record were to fail then the insertion of all records inserted up to that point would be rolled back, so you won't be stuck with partially loaded data.

10.4.2. Use COPY FROM

Use COPY FROM STDIN to load all the records in one command, instead of using a series of INSERT commands. This reduces parsing, planning, etc. overhead a great deal. If you do this then it is not necessary to turn off autocommit, since it is only one command anyway.

10.4.3. Remove Indexes

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.

If you are augmenting an existing table, you can DROP INDEX, 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. 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.

10.4.4. Run ANALYZE Afterwards

It's a good idea to run ANALYZE or VACUUM ANALYZE anytime you've added or updated a lot of data, including just after initially populating a table. This ensures that the planner has up-to-date statistics about the table. With no statistics or obsolete statistics, the planner may make poor choices of query plans, leading to bad performance on queries that use your table.

Home
Online Resources
General
Beginner Tutorials
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Contact Us
Random quote of the moment:
Laugh, and the world ignores you. Crying doesn't help either.
 
http://www.sql.org/
 
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!