It’s more efficient than running a large number of INSERT statements or even multi-valued INSERTS. We recommend using the PostgreSQL COPY command to load data from one or more files. ALTER TABLE DISABLE TRIGGER ALLĪLTER TABLE ENABLE TRIGGER ALL Tip 5: Use COPY Command Disabling ALL triggers also include system triggers that enforce foreign key constraint checks. We recommend disabling all triggers in the target table before bulk loading data and enabling them after the load is finished. This is because each trigger will have logic that needs to be checked and operations that need to complete right after each row is INSERTed or DELETEd. INSERT or DELETE triggers (if the load process also involves deleting records from the target table) can cause delays in bulk data loading. Once again, increasing the maintenance_work_mem configuration parameter can improve the performance of recreating foreign key constraints. Unless restricted by business rules, we recommend dropping all foreign keys from the target table, loading the data in a single transaction, then recreating the foreign keys after committing the transaction. When loading a large number of rows, this trigger has to be fired off for each row, adding to the overhead. Behind-the-scene, PostgreSQL uses a trigger to perform the checking. This is because each foreign key in each inserted row has to be checked for the existence of a corresponding primary key. Like indexes, foreign key constraints can also impact bulk load performance. The method that yields better performance can be then followed for the live table. This newly copied table can be then tested with bulk insert for both scenarios: drop-and-recreate indexes, or dynamically updating them. The increased working memory can help create the indexes faster.Īnother option to play safe is to make a copy of the target table in the same database with existing data and indexes. It may be worthwhile to temporarily increase the maintenance_work_mem configuration parameter just before creating the indexes. Again, creating indexes on large tables can be time-consuming, but it will be generally faster than updating the indexes during load. We recommend dropping indexes in the target table where possible before starting the bulk insert, and recreating the indexes once the load is complete. This is because as each row is added, the corresponding index entry has to be updated as well. large lookup tables or dimension tables)Įxisting indexes can cause significant delays during bulk data inserts.
Tip 1: Change Target Table to Un-logged Modeįor PostgreSQL 9.5 and above, the target table can be first altered to UNLOGGED, then altered back to LOGGED once the data is loaded: ALTER TABLE SET UNLOGGED We recommend readers consider the pros and cons of any method before applying it. However, there may be situations where none of these tips will be an efficient solution. In this article, we will cover some best practice tips for bulk importing data into PostgreSQL databases. There are many reasons for such poor performance: indexes, triggers, foreign keys, GUID primary keys, or even the Write Ahead Log (WAL) can all cause delays. This process can be sometimes unacceptably slow. This is commonly known as bulk data import where the data source is typically one or more large files. Sometimes, PostgreSQL databases need to import large quantities of data in a single or a minimal number of steps.
0 kommentar(er)
