Clever Geek Handbook

在PostgreSQL中批量加载数据的七个实用技巧

免费翻译文章“ PostgreSQL批量数据加载的7个最佳实践技巧”



有时，只需几个简单的步骤就需要将大量数据加载到PostgreSQL数据库中。这种做法通常称为批量导入，其中一个或多个大文件用作数据源。这个过程有时会慢得令人无法接受。表现不佳的原因有很多。索引，触发器，外键和主键，甚至写入WAL文件都可能导致延迟。



在本文中，我们将提供一些实用的技巧，以将数据批量导入PostgreSQL数据库。但是，在某些情况下，它们都不是解决问题的有效方法。我们鼓励读者在应用任何方法之前都要考虑其优缺点。



提示1.将目标表置于非日志记录模式



在PostgreSQL9.5和更高版本中，可以将目标表设置为非日志记录模式，并在加载数据后返回到日志记录模式。



ALTER TABLE <target table> SET UNLOGGED;
<bulk data insert operations…>
ALTER TABLE <target table> LOGGED;


非日志记录模式可确保PostgreSQL不会将加载操作发送到预写日志（WAL）中的表。这样可以大大加快下载过程。但是，由于未记录这些操作，因此如果在上传过程中PostgreSQL服务失败或计划外崩溃，将无法恢复数据。重启后，PostgreSQL将自动记录所有未记录的表。



此外，未记录的表不会复制到从属服务器。在这种情况下，建议在加载副本之前停止对副本的前滚更改，并在加载之后还原它。根据主服务器上的数据量和副本数，复制恢复可能需要很长时间，这可能无法满足高可用性系统的要求。



:



  • c ;
  • ;
  • , , .


2.



. , , .



, -, , . , , , , .



DROP INDEX <index_name1>, <index_name2> … <index_name_n>
<bulk data insert operations…>
CREATE INDEX <index_name> ON <target_table>(column1, …,column n)


maintenance_work_mem. .



, . : . , , .



3.



, , – , . PostgreSQL .



, , . , , , .



ALTER TABLE <target_table> 
    DROP CONSTRAINT <foreign_key_constraint>;
BEGIN TRANSACTION;
    <bulk data insert operations…>
COMMIT;
ALTER TABLE <target_table> 
    ADD CONSTRAINT <foreign key constraint>  
    FOREIGN KEY (<foreign_key_field>) 
    REFERENCES <parent_table>(<primary key field>)...;


, maintenance_work_mem .



4.



INSERT DELETE ( ) . , , , .



, . , .



ALTER TABLE <target table> DISABLE TRIGGER ALL;
<bulk data insert operations…>
ALTER TABLE <target table> ENABLE TRIGGER ALL;


5. COPY



PostgreSQL – COPY . COPY . , INSERT INSERT- VALUE



COPY <target table> [( column1>, … , <column_n>)]
    FROM  '<file_name_and_path>' 
    WITH  (<option1>, <option2>, … , <option_n>)


COPY:



  • , , ;
  • ;
  • ;
  • WHERE.


6. INSERT VALUE



INSERT – . , INSERT , , WAL.



INSERT VALUE .



INSERT INTO <target_table> (<column1>, <column2>, …, <column_n>) 
VALUES 
    (<value a>, <value b>, …, <value x>),
    (<value 1>, <value 2>, …, <value n>),
    (<value A>, <value B>, …, <value Z>),
    (<value i>, <value ii>, …, <value L>),
    ...;


INSERT VALUES . .



, , , PostgreSQL INSERT VALUES. INSERT, RAM , .



effective_cache_size 50%, shared_buffer 25% . , , INSERT VALUES, 1000 .



7. ANALYZE



, ANALYZE . , , . , . ANALYZE .





并非每天都会批量导入数据库应用程序的数据，但这会影响查询性能。这就是为什么必须尽可能减少加载时间的原因。DBA可以最大程度地降低发生任何意外的可能性的一件事是在具有类似服务器和类似配置的PostgreSQL的测试环境中执行负载优化。加载数据有不同的方案，最好尝试每种方法并选择一种有效的方法。



More articles:


All Articles