新手问题 Postgresql 插入一亿条数据,有快速的方法吗?

jmmbite · 2018年07月19日 · 最后由 razertory 回复于 2018年11月26日 · 6977 次阅读

进入 shell:psql 插入 1 亿条数据太慢了,

下面这个是在 sqlite3 插入 1 亿条数据的方法,如何改写成 pg 数据库的?

g++ -c do.c && g++ -o do do.o -lsqlite3 && ./do

#include <iostream>
#include <string.h>
#include <sstream>
#include <time.h>
#include <sqlite3.h>

const int nCount = 200*50*10000;

int main (int argc,char** argv)
{
    sqlite3* db;
    sqlite3_open("testdb.db" ,&db);
    sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0);
    sqlite3_exec(db,"drop table if exists t1",0,0,0);
    sqlite3_exec(db,"create table t1(id integer,x integer,y integer ,weight real)",0,0,0);
    clock_t t1 = clock();

    sqlite3_exec(db,"begin;",0,0,0);
    sqlite3_stmt *stmt;
    const char* sql = "insert into t1 values(?,?,?,?)";
    sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,0);

    for(int i=0;i<nCount;++i)
    {
        // std::stringstream ssm;
        // ssm<<"insert into t1 values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";
        // sqlite3_exec(db,ssm.str().c_str(),0,0,0);
        sqlite3_reset(stmt);
        sqlite3_bind_int(stmt,1,i);
        sqlite3_bind_int(stmt,2,i*2);
        sqlite3_bind_int(stmt,3,i/2);
        sqlite3_bind_double(stmt,4,i*i);
        sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    sqlite3_exec(db,"commit;",0,0,0);
    clock_t t2 = clock();

    sqlite3_close(db);

    std::cout<<"cost tima: "<<(t2-t1)/(1000*1000)<<"s"<<std::endl;

    return 0;
}

load file 了解一下

liprais 回复

有没有什么办法,缩短时间。

如果要从某个数据源导入,可以生成 csv 文件然后 COPY ... FROMINSERT 能快到哪去……

如果只是随便生成大量数据,generate_series 函数了解一下。

整个文件拷过来,1 亿的数据大约几十个 GB 吧,一个小时左右差不多了

你只写了 sqlite 对应的代码,没有写 pg 是如何插入的,如何说明 pg 太慢?

可以很快的

nouse 回复

因为试过了才得出的结论, 非 ssd,插入时间超过 30 分钟以上了,后面我就直接中断退出了。

psql (9.6.9)
Type "help" for help.

postgres=# insert into topics select generate_series(2,100000000),2,1,1;

参考的是这篇帖子:1 亿条数据中 PostgreSQL 性能/

但是自己的测试结果并没有这么快,所以我想是不是从 postgresql 的 C 接口直接插入是否会更快,消耗更少。

因为第一次使用 pg,不知道该引用那个头文件更合适,不过已经把各种都测过了

最后还是参考了,官方文档,代码很简单就不贴了

https://www.postgresql.org/docs/10/static/libpq-example.html

https://www.postgresql.org/docs/10/static/xfunc-c.html

@jmmbite 本地试了下你的 SQL,总时间 3 分半。SSD

aaa

我感觉这样已经是最快的方案了,因为执行过程全在 PostgreSQL 内部。其他那种要到数据库外做一圈交互的方式应该性能更低。对比下来你的问题应该更多的在于硬件,所以升级机器吧。

楼上已经有人说了用 copy 这个命令 为啥你还抱着 insert 不放?

三分钟挺快了,当然 copy 之后再加索引也会很快。

所以到底需求是什么?找一个全宇宙最快的数据库?

在单个事务里面会更快

需要 登录 后方可回复, 如果你还没有账号请 注册新账号