详解PostgreSQL提升批量数据导入性能的n种方法

关键字：批量数据导入，数据加载，大量插入，加快，提升速度
多元化选择时代，人生里很多事物都是如此，凡事都没有一成不变的方式和方法。不管白猫黑猫，能抓老鼠的就是好猫，适合自己的就是最好的。
提升批量数据导入的方法亦是如此，没有何种方法是最优的，应用任何方法前根据自己的实际情况权衡利弊，做出选择。
批量导入数据之前，无论采取何种方式，务必做好相应的备份。
导入完成后亦需对相应对象进行ANALYZE操作，这样查询优化器才会按照最新的统计信息生成正确的执行计划。

下面正式介绍提升批量数据导入性能的n种方法。

方法1：禁用自动提交。

psql
\set AUTOCOMMIT off

其他
BEGIN;
执行批量数据导入
COMMIT;

方法2：设置表为UNLOGGED。

导入数据之前先把表改成UNLOGGED模式，导入完成后改回LOGGED模式。

ALTER TABLE tablename SET UNLOGGED;
执行批量数据导入
ALTER TABLE tablename LOGGED;

优点：
导入信息不记录WAL日志，极大减少io，提升导入速度。
缺点：
1.在replication环境下，表无法设置为UNLOGGED模式。
2.导入过程一旦出现停电死机等会导致数据库不能干净关库的情况，数据库中所有UNLOGGED表的数据将丢失。

方法3：重建索引。

导入数据之前先删除相关表上的索引，导入完成后重新创建之。

DROP INDEX indexname;
执行批量数据导入
CREATE INDEX ...;

查询表上索引定义的方法

select * from pg_indexes where tablename ='tablename' and schemaname = 'schemaname';

方法4：重建外键。

导入数据之前先删除相关表上的外键，导入完成后重新创建之。

ALTER TABLE ...
 DROP CONSTRAINT ... ;
执行批量数据导入
ALTER TABLE ...
 ADD CONSTRAINT ...
 FOREIGN KEY ...
 REFERENCES ...;

相关信息可查询pg_constraint。

方法5：停用触发器

导入数据之前先DISABLE掉相关表上的触发器，导入完成后重新ENABLE之。

ALTER TABLE tablename DISABLE TRIGGER ALL;
执行批量数据导入
ALTER TABLE tablename ENABLE TRIGGER ALL;

相关信息可查询pg_trigger。

方法6：insert改copy

COPY针对批量数据加载进行了优化。

COPY ... FROM 'xxx';

方法7：单值insert改多值insert

减少sql解析的时间。

方法8：insert改PREPARE

通过使用PREPARE预备语句，降低解析消耗。

PREPARE fooplan (int, text, bool, numeric) AS
 INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

方法9：修改参数

增大maintenance_work_mem，增大max_wal_size。

方法10：关闭归档模式，降低wal日志级别。

修改archive_mode参数控制归档开启和关闭。降低wal_level值为minimal来减少日志信息记录。
此法需要重启数据库，需要规划停机时间。此外如有replication备库，还需考虑对其影响。

到此这篇关于PostgreSQL提升批量数据导入性能的n种方法的文章就介绍到这了,更多相关PostgreSQL批量数据导入内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 如何将excel表格数据导入postgresql数据库

    如何将excel表格数据导入postgresql数据库

    实际的工作中,我们经常会碰到统计数据的工作,有些维度的统计数据因为工作需要我们需要导出为excel作为报表附件供不同的部门审查.为了方便以后的对比工作,领导会让在数据库中创建一张表,用于专门记录这些数据.此时我们DBA需要将这些excel表格导入到数据库中,copy和\copy命令为我们提供了解决办法,本文主要通过copy命令的使用,介绍如何将excel表格导入至数据库中.关于copy及\copy命令的区别,请广大博友通过另一篇文章<如何将postgresql数据库表内数据导出为excel格式>

  • sqoop读取postgresql数据库表格导入到hdfs中的实现

    sqoop读取postgresql数据库表格导入到hdfs中的实现

    最近再学习spark streaming做实时计算这方面内容,过程中需要从后台数据库导出数据到hdfs中,经过调研发现需要使用sqoop进行操作,本次操作环境是Linux下. 首先确保环境安装了Hadoop和sqoop,安装只需要下载 ,解压 以及配置环境变量,这里不多说了,网上教程很多. 一.配置sqoop以及验证是否成功 切换到配置文件下:cd $SQOOP_HOME/conf 创建配置环境文件: cp sqoop-env-template.sh sqoop-env.sh 修改配置文件:co

  • PostgreSql 导入导出sql文件格式的表数据实例

    PostgreSql 导入导出sql文件格式的表数据实例

    PostgreSql默认导出的文件格式是.backup,而我们很多数据库导入数据库脚本时是.sql文件格式的,PostgreSql作为国内的新潮,会不会不支持导出.sql文件格式吗?答案是当然不会.下面我们借助Pg Admin III工具来导出.sql的脚本的. 一.导出 首先,你当然得有一张完整的数据表啦,并且保证里面是有数据的: 有了表和数据之后,选中该表: 选中右键 --> 备份 我们可以看到文件名默认以.backup格式的方式进行数据备份的. 首先将自定义格式 --> 无格式 点开之后

  • 使用python将mdb数据库文件导入postgresql数据库示例

    mdb格式文件可以通过mdbtools工具将内中包含的每张表导出到csv格式文件.由于access数据库和postgresQL数据库格式上会存在不通性,所以使用python的文件处理,将所得csv文件修改成正确.能识别的格式. 导入脚本说明(此脚本运行于linux): 1.apt-get install mdbtools,安装mdbtools工具 2.将mdb 文件拷贝到linux虚拟机中,修改脚本中mdb文件目录'dir' 3.修改服务器及数据库配置 4.执行脚本 复制代码 代码如下: # -

  • postgresql 实现数据的导入导出

    postgresql 实现数据的导入导出

    最近想把服务器上的测试数据库数据导到我本地的电脑上,本地电脑数据库是安装在windows系统下 之前没使用过pgsql,网上找了点资料,记入如下: 一,首先把服务器上的数据进行备份 pg_dump -U 用户名 数据库名 (-t 表名)> 数据存放路径 二,把.sql 文件下载到本地之后,首先切换到pgsql路径下的bin目录 然后执行这条命令: -d:数据库名 -h:地址 -p:端口 -u:用户名 -f:sql文件路径 之后输入口令: 这样就可以了! 补充:Sqoop从PostgreSQL导入

  • postgresql 导入数据库表并重设自增属性的操作

    postgresql 导入数据库表并重设自增属性的操作

    postgresql使用Navicat软件导出数据库表,在导入会数据库的操作. postgresql 的自增字段 是通过 序列 sequence来实现的. 1.先删除导出的数据库表中的自增属性 2.导入数据库表之后,需要创建序列. 注:一般序列名称由数据表名+主键字段+seq组成 (通常情况下主键字段即为自增字段),如下表名为cof_table_hot_analysis,主键字段为 id CREATE SEQUENCE cof_table_hot_analysis_id_seq START WI

  • 详解PostgreSQL提升批量数据导入性能的n种方法

    关键字:批量数据导入,数据加载,大量插入,加快,提升速度 多元化选择时代,人生里很多事物都是如此,凡事都没有一成不变的方式和方法.不管白猫黑猫,能抓老鼠的就是好猫,适合自己的就是最好的. 提升批量数据导入的方法亦是如此,没有何种方法是最优的,应用任何方法前根据自己的实际情况权衡利弊,做出选择. 批量导入数据之前,无论采取何种方式,务必做好相应的备份. 导入完成后亦需对相应对象进行ANALYZE操作,这样查询优化器才会按照最新的统计信息生成正确的执行计划. 下面正式介绍提升批量数据导入性能的n种方

  • 详解linux下批量替换文件内容的三种方法(perl,sed,shell)

    在建设本网站的时候,发现新建了很多的网页,突然发现,每个文件都需要进行修改一样的内容,一个一个打开很是麻烦,所以,总结了一下如何快速修改一个目录下多个文件进行内容替换.第三种方法用的不多 方法一 使用perl ,命令如下: 复制代码 代码如下: find -name '要查找的文件名' | xargs perl -pi -e 's|被替换的字符串|替换后的字符串|g' 方法二 使用sed命令如下: 复制代码 代码如下: sed -i "s/原字符串/新字符串/g" `grep 原字符串

  • 详解Struts2中Action访问Servlet API的几种方法

    详解Struts2中Action访问Servlet API的几种方法 在通常的web开发中Request和Response对象比较常见,但在Struts2框架中由于Action能与JSP页面进行数据交互,所以通常都不会用到这两个对象.如果想在Struts2程序中用到这两个对象,也有解决方法 Struts2的Action并未直接与任何Servlet API耦合,这是Struts2的一个改良之处,因为Action类不再与Servlet API耦合,能更轻松的测试该Action.但如何访问? Web应

  • 详解python中读取和查看图片的6种方法

    详解python中读取和查看图片的6种方法

    目录 1 OpenCV 2 imageio 3 PIL 4 scipy.misc 5 tensorflow 6 skimage 本文主要介绍了python中读取和查看图片的6种方法,分享给大家,具体如下: file_name1='test_imgs/spect/1.png' # 这是彩色图片 file_name2='test_imgs/mri/1.png' # 这是灰度图片 1 OpenCV 注:用cv2读取图片默认通道顺序是B.G.R,而不是通常的RGB顺序,所以读进去的彩色图直接显示会出现变

  • 详解node服务器中打开html文件的两种方法

    详解node服务器中打开html文件的两种方法

    本文介绍了详解node服务器中打开html文件的两种方法,分享给大家,具体如下: 方法1:利用 Express 托管静态文件,详情查看这里 方法2:使用fs模块提供的readFile方法打开文件,让其以text/html的形式输出. 代码: var express = require('express'); var fs=require("fs"); var app = express(); //方法1:通过express.static访问静态文件,这里访问的是ajax.html //

  • 详解pandas的外部数据导入与常用方法

    外部数据导入 导入excel文件 pandas导入excel用read_excel()方法: import pandas as pd excel_file1 = pd.read_excel('data/测试.xlsx',encoding='utf-8') 姓名  年龄    工作    工资 0   张三  25    学生      200 1   李四  24    工人     3000 2   王伟  28    NaN      5000 3  王二毛  22  自由职业   6000

  • SQLServer 数据导入导出的几种方法小结

    SQLServer 数据导入导出的几种方法小结

    1. SQL Server导入导出向导,这种方式是最方便的.导入向导,微软提供了多种数据源驱动,包括SQL Server Native Cliant, OLE DB For Oracle,Flat File Source,Access,Excel,XML等,基本上可以满足系统开发的需求. 同样导出向导也有同样多的目的源驱动,可以把数据导入到不同的目的源. 对数据库管理人员来说这种方式简单容易操作,导入时SQL Server也会帮你建立相同结构的Table. 2. 用.NET的代码实现(比如有一个

  • 详解微信小程序的不同函数调用的几种方法

    一.调取参数 直接调取当前js中的方法, 调取参数that.bindViewTap(); 二.跳转页面 navigateTo: function () { wx.navigateTo({ url: '../page4/page4' }); }, 全局变量使用方法 a.js var app = getApp() Page({ data: { hex1: [], })} //设置全局变量 if (hex1 != null) { app.globalData.hex1 = hex1; } b.js 接

  • 详解用Python为直方图绘制拟合曲线的两种方法

    详解用Python为直方图绘制拟合曲线的两种方法

    直方图是用于展示数据的分组分布状态的一种图形,用矩形的宽度和高度表示频数分布,通过直方图,用户可以很直观的看出数据分布的形状.中心位置以及数据的离散程度等. 在python中一般采用matplotlib库的hist来绘制直方图,至于如何给直方图添加拟合曲线(密度函数曲线),一般来说有以下两种方法. 方法一:采用matplotlib中的mlab模块 mlab模块是Python中强大的3D作图工具,立体感效果极佳.在这里使用mlab可以跳出直方图二维平面图形的限制,在此基础上再添加一条曲线.在这里,

  • 详解在React中跨组件分发状态的三种方法

    当我问自己第一百次时,我正在研究一个典型的CRUD屏幕:"我应该将状态保留在这个组件中还是将其移动到父组件?". 如果需要对子组件的状态进行轻微控制.您可能也遇到了同样的问题. 让我们通过一个简单的例子和​​三种修复方法来回顾它.前两种方法是常见的做法,第三种方法不太常规. 问题: 为了向您展示我的意思,我将使用一个简单的书籍CRUD(译者注:增加(Create).读取查询(Retrieve).更新(Update)和删除(Delete))屏幕(如此简单,它没有创建和删除操作). 我们有

随机推荐