PostgreSQL Mass Data import method

has not paid attention to this function, yesterday looked at a bit, the database inserted bottlenecks, today studied a bit:

The main scenarios are as follows:

1. import from a file using copy :

Copy table_001 (A, B, "F", D, C, "E") from ' D:/data1.txt ' (delimiter ', ');

Extremely fast speed:

Without index:

Query succeeded: A total of 69971 rows were affected, time consuming: 4351 milliseconds (ms).

Query succeeded: A total of 69971 rows were affected, time consuming: 4971 milliseconds (ms).

with index:

Query succeeded: A total of 69971 rows were affected, time consuming: 15582 milliseconds (ms).

Query succeeded: A total of 69971 rows were affected, time consuming: 12833 milliseconds (ms).

Need to do is to periodically generate temporary data files, and constantly switch, clear.

2. Using SQL in Multi-insert format

Similar to: INSERT into test values (' ASD ', ' ADEWF ', ' n '), (' Asd2 ', ' adewf2 ', 12);

Currently adopting this scheme, the change is not big, just modified the SQL format, currently meet the requirements (about 250,000 records per minute, 4200 per second), so temporarily adopt it.

3. Turn off auto-commit, use INSERT or multi-insert format SQL, insert large amounts of data

Currently not tested, but this program effect with the online introduction should also be good.

4. Using temporary tables

This option, temporary table in order to speed up, should not add any index and log, data stability after the index and limit, compression data, vacuum and other data optimization , which needs to be used in conjunction with the Sub-table is better.

5. Adjust database parameters, this is to improve the overall performance of the database

Online introduction of these optimization parameters: Shared_buffers, Work_mem, Effective_cache_size, Maintence_work_mem

These can be configured for use, please refer to Chapter 14 in postgresql-9.2-a4.pdf for details. Performance Tips.

One might 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.

14.4.1. Disable autocommit

When using a multiple inserts, turn off autocommit and just do one commits at the end. (In plain

SQL, this means issuing BEGIN at the start and COMMIT at the end. Some client libraries might do this

Behind your back, in which case you need-make sure the library does it when you want it-done.) If

All insertion to be committed separately, PostgreSQL are doing a lot of work for each row

That's added. An additional benefit of doing all insertions in one transaction are that if the insertion of

One row were to fail and the insertion of all rows inserted up to that point would is rolled back, so

You won ' t is stuck with partially loaded data.

14.4.2. Use COPY

Use COPY to load all the rows in one command, instead of using a series of INSERT commands. The

COPY command is optimized for loading large numbers of rows; It's less flexible than inserts, but

Incurs significantly less overhead for large data loads. Since COPY is a single command, there is no

Need to disable autocommit if the This method to populate a table.

If you cannot with COPY, it might help to use PREPARE to create a prepared INSERT statement, and

Then use EXECUTE as many times as required. This avoids some of the overhead of repeatedly parsing

and planning INSERT. Different interfaces provide this facility in Different ways; Look for "prepared

Statements "in the interface documentation.

Note that loading a large number of rows using the COPY is almost always faster than using INSERT, even

If PREPARE is used and multiple insertions be batched into a single transaction.

COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE

Command. In such cases no WAL needs to being written, because in case of an error, the files containing the newly loaded data would be Removed anyway. However, this consideration only applies when

Wal_level is minimal as all commands must write WAL otherwise.

367

14.4.3. Remove Indexes

If you're loading a freshly created table, the fastest method is to create the table, bulk load the table ' s

Data using COPY, 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.

If you is adding large amounts of data to an existing table, it might is a win to drop the indexes,

Load the table, and then recreate the indexes. Of course, the database performance for other users

Might suffer during the time the indexes is missing. One should also think twice before dropping a

Unique index, since the error checking afforded by the unique constraint would be lost while the index

Is missing.

14.4.4. Remove Foreign Key Constraints

Just As with indexes, a FOREIGN key constraint can be checked ' in bulk ' more efficiently than row-byrow. So it might is 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.

What's more, when you load data to a table with existing foreign key constraints, each new row

Requires an entry in the server's list of pending trigger events (since it's the firing of a trigger that

Checks the row ' s foreign key constraint). Loading Many millions of rows can cause the trigger event

Queue to overflow available memory, leading to intolerable swapping or even outright failure of the

Command. Therefore It may is necessary, not just desirable, to drop and re-apply foreign keys when

Loading large amounts of data. If temporarily removing the constraint isn ' t acceptable, the only other

Recourse May and split up the load operation into smaller transactions.

14.4.5. Increase Maintenance_work_mem

Temporarily increasing the MAINTENANCE_WORK_MEM configuration variable when loading large

Amounts of data can leads to improved performance. This would help the speed up CREATE INDEX

Commands and ALTER TABLE ADD FOREIGN KEY commands. It won ' t do much to COPY itself, so

This advice was only useful if you were using one or both of the above techniques.

14.4.6. Increase Checkpoint_segments

Temporarily increasing the Checkpoint_segments configuration variable can also make large data

Loads faster. This was because loading a large amount of data into PostgreSQL would cause checkpoints

To occur more often than the normal checkpoint frequency (specified by the Checkpoint_timeout

Configuration variable). Whenever a checkpoint occurs, all dirty pages must is flushed to disk. By

Increasing checkpoint_segments temporarily during bulk data loads, the number of checkpoints

That is required can be reduced.

14.4.7. Disable WAL Archival and streaming Replication

When loading large amounts of the data into a installation that uses WAL archiving or streaming replication, it might is fast Er to take a new base backup after the load have completed than to process

A large amount of incremental WAL data. To prevent incremental WAL logging when loading, disable archiving and streaming replication, by setting Wal_level to Min iMAL, Archive_mode to OFF, and

Max_wal_senders to zero. But note that changing these settings requires a server restart.

Aside from avoiding the time for the archiver or Wal-Sender to process the Wal-data, doing this

Would actually make certain commands faster, because they is designed not to write WAL @ all if

Wal_level is minimal. (They can guarantee crash safety more cheaply by doing a fsync at the

End than by writing WAL.) This applies to the following commands:

Create TABLE as SELECT

Create INDEX (and variants such as ALTER TABLE ADD PRIMARY KEY)

Alter TABLE SET tablespace

Cluster

copy from, when the target table had been created or truncated earlier in the same transaction

14.4.8. Run ANALYZE afterwards

Whenever you have significantly altered the distribution of data within a table, running ANALYZE

is strongly recommended. This includes bulk loading large amounts of data into the table. Running

ANALYZE (or VACUUM ANALYZE) ensures that the planner have up-to-date statistics about the table.

With no statistics or obsolete statistics, the planner might make poor decisions during query planning,

leading to poor performance on any tables with inaccurate or nonexistent statistics. Note that if the

Autovacuum daemon is enabled, it might run ANALYZE automatically; See section 23.1.3 and Sections

23.1.6 for more information.