PostgreSQL 批量导入性能 (采用dblink 异步调用)

13 minute read

背景

批量导入数据,怎样能挖掘出系统的比较极限的潜能?

瓶颈通常在哪里?

1、WAL lock

2、INDEX lock

3、EXTEND LOCK

4、autovacuum 干扰

因此最好的方法就是排除以上问题,例如

1、使用多表,解决单表EXTEND LOCK问题

2、使用unlogged table(异常时会丢失数据,切记仅限场景使用)多表,解决WAL LOCK问题

3、不使用索引,解决INDEX LOCK问题

4、导入时不使用autovacuum,解决autovacuum干扰问题

基本上可以挖掘出机器的最大潜力。

《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》

单表测试

1、创建测试表

postgres=# create unlogged table ut(c1 int8) with (autovacuum_enabled=off, toast.autovacuum_enabled=off);  
CREATE TABLE  
Time: 12.723 ms  

2、生成1亿数据

postgres=# insert into ut select generate_series(1,100000000);  
INSERT 0 100000000  
Time: 43378.465 ms (00:43.378)  
  
postgres=# copy ut to '/data01/pg/ut.csv';  
COPY 100000000  
Time: 20292.684 ms (00:20.293)  
# ll -ht /data01/pg/ut.csv   
-rw-r--r-- 1 digoal digoal 848M Apr 27 22:02 /data01/pg/ut.csv  

3、创建插件

create extension dblink;  

4、创建重复建立连接不报错的函数

create or replace function dis_conn(name) returns void as $$      
declare      
begin      
  perform dblink_disconnect($1);      
  return;      
exception when others then      
  return;      
end;      
$$ language plpgsql strict;      
  
create or replace function conn(          
  name,   -- dblink名字          
  text    -- 连接串,URL          
) returns void as $$            
declare            
begin            
  perform dis_conn($1);  
  perform dblink_connect($1, $2);           
  return;            
exception when others then            
  return;            
end;            
$$ language plpgsql strict;     

5、创建测试函数,调用dblink,56个并发同时写单个表

create or replace function get_res() returns setof record as $$      
declare      
begin      
  for i in 0..55 loop             
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres');           
    perform 1 from dblink_get_result('link'||i) as t(res text);            
    perform dblink_send_query('link'||i, format('copy ut from %L', '/data01/pg/ut.csv'));            
  end loop;         
  for i in 0..55 loop      
    return query select * from dblink_get_result('link'||i) as t(res text);      
  end loop;      
end;      
$$ language plpgsql strict;    

6、测试

\timing  
  
select * from get_res() as t(id int);  

7、观测锁等待,都在单表的扩展BLOCK上。

postgres=# select wait_event_type, wait_event, count(*) from pg_stat_activity where wait_event is not null group by 1,2;  
 wait_event_type |     wait_event      | count   
-----------------+---------------------+-------  
 Activity        | WalWriterMain       |     1  
 Lock            | extend              |    55  
 Activity        | LogicalLauncherMain |     1  
 Activity        | BgWriterMain        |     1  
 Activity        | CheckpointerMain    |     1  
 Activity        | AutoVacuumMain      |     1  
 IO              | DataFileWrite       |     1  
(7 rows)  

src/include/storage/lock.h

/*  
 * LOCKTAG is the key information needed to look up a LOCK item in the  
 * lock hashtable.  A LOCKTAG value uniquely identifies a lockable object.  
 *  
 * The LockTagType enum defines the different kinds of objects we can lock.  
 * We can handle up to 256 different LockTagTypes.  
 */  
typedef enum LockTagType  
{  
        LOCKTAG_RELATION,                       /* whole relation */  
        /* ID info for a relation is DB OID + REL OID; DB OID = 0 if shared */  
        LOCKTAG_RELATION_EXTEND,        /* the right to extend a relation */  
        /* same ID info as RELATION */  
        LOCKTAG_PAGE,                           /* one page of a relation */  
        /* ID info for a page is RELATION info + BlockNumber */  
        LOCKTAG_TUPLE,                          /* one physical tuple */  
        /* ID info for a tuple is PAGE info + OffsetNumber */  
        LOCKTAG_TRANSACTION,            /* transaction (for waiting for xact done) */  
        /* ID info for a transaction is its TransactionId */  
        LOCKTAG_VIRTUALTRANSACTION, /* virtual transaction (ditto) */  
        /* ID info for a virtual transaction is its VirtualTransactionId */  
        LOCKTAG_SPECULATIVE_TOKEN,      /* speculative insertion Xid and token */  
        /* ID info for a transaction is its TransactionId */  
        LOCKTAG_OBJECT,                         /* non-relation database object */  
        /* ID info for an object is DB OID + CLASS OID + OBJECT OID + SUBID */  
  
        /*  
         * Note: object ID has same representation as in pg_depend and  
         * pg_description, but notice that we are constraining SUBID to 16 bits.  
         * Also, we use DB OID = 0 for shared objects such as tablespaces.  
         */  
        LOCKTAG_USERLOCK,                       /* reserved for old contrib/userlock code */  
        LOCKTAG_ADVISORY                        /* advisory user locks */  
} LockTagType;  

8、观察 iotop,写入速度625MB/s

Total DISK READ :      15.46 K/s | Total DISK WRITE :     625.28 M/s  
Actual DISK READ:      15.46 K/s | Actual DISK WRITE:     620.88 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                         
12574 be/4 digoal      7.73 K/s   30.95 M/s  0.00 % 14.23 % postgres: postgres postgres 127.0.0.1(58722) COPY          
16198 be/4 postgres    0.00 B/s   61.84 K/s  0.00 %  4.55 % postgres: stats collector process  
12524 be/4 root        0.00 B/s    0.00 B/s  0.00 %  4.36 % [kworker/u112:2]  
12590 be/4 digoal      3.87 K/s   36.13 M/s  0.00 %  4.05 % postgres: postgres postgres 127.0.0.1(58754) COPY          
12579 be/4 digoal      3.87 K/s   52.75 M/s  0.00 %  3.24 % postgres: postgres postgres 127.0.0.1(58732) COPY          
27166 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.02 % [kworker/39:2]  
12589 be/4 digoal      0.00 B/s   30.92 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58752) COPY waiting  
12569 be/4 digoal      0.00 B/s   56.44 M/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58712) COPY waiting  
12607 be/4 digoal      0.00 B/s   51.55 M/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58788) COPY waiting  
12556 be/4 digoal      0.00 B/s   20.59 M/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58686) COPY waiting  
12610 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58794) COPY waiting  
12586 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58746) COPY waiting  
12576 be/4 digoal      0.00 B/s   26.20 M/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58726) COPY waiting  
12601 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58776) COPY waiting  
12591 be/4 digoal      0.00 B/s   61.84 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58756) COPY waiting  
12593 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58760) COPY waiting  
12603 be/4 digoal      0.00 B/s   30.92 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58780) COPY waiting  
12604 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58782) COPY waiting  
12605 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58784) COPY waiting  
12600 be/4 digoal      0.00 B/s   27.90 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58774) COPY waiting  
12562 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58698) COPY waiting  
12561 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58696) COPY waiting  
12572 be/4 digoal      0.00 B/s   61.99 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58718) COPY          
12557 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58688) COPY waiting  
12588 be/4 digoal      0.00 B/s   30.95 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58750) COPY waiting  
12585 be/4 digoal      0.00 B/s   26.69 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58744) COPY waiting  
12587 be/4 digoal      0.00 B/s 1298.69 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58748) COPY waiting  
12575 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58724) COPY waiting  
12555 be/4 digoal      0.00 B/s   16.97 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58684) COPY waiting  
12584 be/4 digoal      0.00 B/s   30.98 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58742) COPY waiting  
12578 be/4 digoal      0.00 B/s    9.72 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58730) COPY waiting  
12595 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58764) COPY waiting  
12609 be/4 digoal      0.00 B/s   14.62 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58792) COPY waiting  
12565 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58704) COPY waiting  
12597 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58768) COPY waiting  
12596 be/4 digoal      0.00 B/s    8.52 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58766) COPY waiting  
12567 be/4 digoal      0.00 B/s   30.95 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58708) COPY waiting  
12568 be/4 digoal      0.00 B/s   76.46 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58710) COPY waiting  
12577 be/4 digoal      0.00 B/s    6.10 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58728) COPY waiting  
12581 be/4 digoal      0.00 B/s   61.84 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58736) COPY waiting  
12583 be/4 digoal      0.00 B/s    7.31 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58740) COPY waiting  

多表测试

1、创建多表

do language plpgsql $$  
declare  
begin  
  for i in 0..55 loop  
    execute format('drop table if exists ut%s', i);  
    execute format('create unlogged table ut%s (like ut) with (autovacuum_enabled=off, toast.autovacuum_enabled=off)', i);  
  end loop;  
end;  
$$;  

2、修改写入函数,每个异步调用写入一张独立表

create or replace function get_res() returns setof record as $$      
declare      
begin      
  for i in 0..55 loop             
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres');             
    perform 1 from dblink_get_result('link'||i) as t(res text);            
    perform dblink_send_query('link'||i, format('copy ut%s from %L', i, '/data01/pg/ut.csv'));            
  end loop;         
  for i in 0..55 loop      
    return query select * from dblink_get_result('link'||i) as t(res text);      
  end loop;      
end;      
$$ language plpgsql strict;    

3、测试

\timing  
  
select * from get_res() as t(id int);  
  
Time: 457688.440 ms (07:37.688)  

4、观察锁,变成了数据文件扩展锁,而不是多会话争抢的扩展BLOCK锁。

postgres=# select wait_event_type, wait_event, count(*) from pg_stat_activity where wait_event is not null group by 1,2;  
 wait_event_type |     wait_event      | count   
-----------------+---------------------+-------  
 Activity        | WalWriterMain       |     1  
 Activity        | LogicalLauncherMain |     1  
 IO              | DataFileExtend      |    55  
 Activity        | BgWriterMain        |     1  
 Activity        | CheckpointerMain    |     1  
 Activity        | AutoVacuumMain      |     1  
(6 rows)  

5、观察iotop ,写入速度达到了1098MB/s

Total DISK READ :       3.85 K/s | Total DISK WRITE :    1098.74 M/s  
Actual DISK READ:       3.85 K/s | Actual DISK WRITE:    1096.81 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                         
13584 be/4 digoal      0.00 B/s   18.69 M/s  0.00 % 84.78 % postgres: postgres postgres 127.0.0.1(58926) COPY  
13629 be/4 digoal      3.85 K/s   18.20 M/s  0.00 % 84.68 % postgres: postgres postgres 127.0.0.1(59016) COPY  
13612 be/4 digoal      0.00 B/s   18.26 M/s  0.00 % 84.64 % postgres: postgres postgres 127.0.0.1(58982) COPY  
13622 be/4 digoal      0.00 B/s   18.38 M/s  0.00 % 84.54 % postgres: postgres postgres 127.0.0.1(59002) COPY  
13582 be/4 digoal      0.00 B/s   18.28 M/s  0.00 % 84.51 % postgres: postgres postgres 127.0.0.1(58922) COPY  
13585 be/4 digoal      0.00 B/s   18.08 M/s  0.00 % 84.46 % postgres: postgres postgres 127.0.0.1(58928) COPY  
13597 be/4 digoal      0.00 B/s   18.60 M/s  0.00 % 84.38 % postgres: postgres postgres 127.0.0.1(58952) COPY  
13602 be/4 digoal      0.00 B/s   19.00 M/s  0.00 % 84.33 % postgres: postgres postgres 127.0.0.1(58962) COPY  
13605 be/4 digoal      0.00 B/s   18.88 M/s  0.00 % 84.28 % postgres: postgres postgres 127.0.0.1(58968) COPY  
13607 be/4 digoal      0.00 B/s   18.73 M/s  0.00 % 84.28 % postgres: postgres postgres 127.0.0.1(58972) COPY  
13631 be/4 digoal      0.00 B/s   18.90 M/s  0.00 % 84.27 % postgres: postgres postgres 127.0.0.1(59020) COPY  
13581 be/4 digoal      0.00 B/s   18.65 M/s  0.00 % 84.25 % postgres: postgres postgres 127.0.0.1(58920) COPY  
13633 be/4 digoal      0.00 B/s   18.93 M/s  0.00 % 84.22 % postgres: postgres postgres 127.0.0.1(59024) COPY  
13589 be/4 digoal      0.00 B/s   18.87 M/s  0.00 % 84.15 % postgres: postgres postgres 127.0.0.1(58936) COPY  
13601 be/4 digoal      0.00 B/s   19.03 M/s  0.00 % 84.06 % postgres: postgres postgres 127.0.0.1(58960) COPY  
13610 be/4 digoal      0.00 B/s   19.83 M/s  0.00 % 84.04 % postgres: postgres postgres 127.0.0.1(58978) COPY  
13609 be/4 digoal      0.00 B/s   19.44 M/s  0.00 % 84.03 % postgres: postgres postgres 127.0.0.1(58976) COPY  
13588 be/4 digoal      0.00 B/s   19.09 M/s  0.00 % 83.94 % postgres: postgres postgres 127.0.0.1(58934) COPY  
13619 be/4 digoal      0.00 B/s   19.70 M/s  0.00 % 83.90 % postgres: postgres postgres 127.0.0.1(58996) COPY  
13580 be/4 digoal      0.00 B/s   19.56 M/s  0.00 % 83.89 % postgres: postgres postgres 127.0.0.1(58918) COPY  
13630 be/4 digoal      0.00 B/s   19.03 M/s  0.00 % 83.87 % postgres: postgres postgres 127.0.0.1(59018) COPY  
13625 be/4 digoal      0.00 B/s   19.03 M/s  0.00 % 83.82 % postgres: postgres postgres 127.0.0.1(59008) COPY  
13624 be/4 digoal      0.00 B/s   18.96 M/s  0.00 % 83.80 % postgres: postgres postgres 127.0.0.1(59006) COPY  
13579 be/4 digoal      0.00 B/s   19.29 M/s  0.00 % 83.77 % postgres: postgres postgres 127.0.0.1(58916) COPY  
13613 be/4 digoal      0.00 B/s   19.45 M/s  0.00 % 83.76 % postgres: postgres postgres 127.0.0.1(58984) COPY  
13608 be/4 digoal      0.00 B/s   18.76 M/s  0.00 % 83.75 % postgres: postgres postgres 127.0.0.1(58974) COPY  
13617 be/4 digoal      0.00 B/s   19.07 M/s  0.00 % 83.74 % postgres: postgres postgres 127.0.0.1(58992) COPY  
13611 be/4 digoal      0.00 B/s   19.53 M/s  0.00 % 83.69 % postgres: postgres postgres 127.0.0.1(58980) COPY  
13590 be/4 digoal      0.00 B/s   19.45 M/s  0.00 % 83.63 % postgres: postgres postgres 127.0.0.1(58938) COPY  
13593 be/4 digoal      0.00 B/s   19.30 M/s  0.00 % 83.63 % postgres: postgres postgres 127.0.0.1(58944) COPY  
13592 be/4 digoal      0.00 B/s   18.99 M/s  0.00 % 83.63 % postgres: postgres postgres 127.0.0.1(58942) COPY  
13594 be/4 digoal      0.00 B/s   19.33 M/s  0.00 % 83.63 % postgres: postgres postgres 127.0.0.1(58946) COPY  
13620 be/4 digoal      0.00 B/s   19.13 M/s  0.00 % 83.62 % postgres: postgres postgres 127.0.0.1(58998) COPY  
13578 be/4 digoal      0.00 B/s   19.89 M/s  0.00 % 83.62 % postgres: postgres postgres 127.0.0.1(58914) COPY  
13598 be/4 digoal      0.00 B/s   19.66 M/s  0.00 % 83.60 % postgres: postgres postgres 127.0.0.1(58954) COPY  
13596 be/4 digoal      0.00 B/s   19.58 M/s  0.00 % 83.50 % postgres: postgres postgres 127.0.0.1(58950) COPY  
13623 be/4 digoal      0.00 B/s   19.63 M/s  0.00 % 83.49 % postgres: postgres postgres 127.0.0.1(59004) COPY  
13618 be/4 digoal      0.00 B/s   19.89 M/s  0.00 % 83.43 % postgres: postgres postgres 127.0.0.1(58994) COPY  
13591 be/4 digoal      0.00 B/s   19.94 M/s  0.00 % 83.43 % postgres: postgres postgres 127.0.0.1(58940) COPY  
13595 be/4 digoal      0.00 B/s   19.81 M/s  0.00 % 83.39 % postgres: postgres postgres 127.0.0.1(58948) COPY  
13621 be/4 digoal      0.00 B/s   19.94 M/s  0.00 % 83.36 % postgres: postgres postgres 127.0.0.1(59000) COPY  
13615 be/4 digoal      0.00 B/s   19.34 M/s  0.00 % 83.28 % postgres: postgres postgres 127.0.0.1(58988) COPY  
13614 be/4 digoal      0.00 B/s   19.59 M/s  0.00 % 83.27 % postgres: postgres postgres 127.0.0.1(58986) COPY  
13599 be/4 digoal      0.00 B/s   18.60 M/s  0.00 % 83.26 % postgres: postgres postgres 127.0.0.1(58956) COPY  
13626 be/4 digoal      0.00 B/s   21.10 M/s  0.00 % 83.16 % postgres: postgres postgres 127.0.0.1(59010) COPY  
13604 be/4 digoal      0.00 B/s   19.62 M/s  0.00 % 83.05 % postgres: postgres postgres 127.0.0.1(58966) COPY  
13627 be/4 digoal      0.00 B/s   20.44 M/s  0.00 % 82.94 % postgres: postgres postgres 127.0.0.1(59012) COPY  
13603 be/4 digoal      0.00 B/s   20.69 M/s  0.00 % 82.91 % postgres: postgres postgres 127.0.0.1(58964) COPY  
13587 be/4 digoal      0.00 B/s   20.30 M/s  0.00 % 82.91 % postgres: postgres postgres 127.0.0.1(58932) COPY  
13628 be/4 digoal      0.00 B/s   20.24 M/s  0.00 % 82.65 % postgres: postgres postgres 127.0.0.1(59014) COPY  
13616 be/4 digoal      0.00 B/s   19.89 M/s  0.00 % 82.62 % postgres: postgres postgres 127.0.0.1(58990) COPY  
13606 be/4 digoal      0.00 B/s   20.26 M/s  0.00 % 82.55 % postgres: postgres postgres 127.0.0.1(58970) COPY  

6、导入完成后,创建索引,开启autovacuum.

create or replace function set_tbl() returns setof record as $$      
declare      
begin      
  for i in 0..55 loop             
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres');             
    perform 1 from dblink_get_result('link'||i) as t(res text);            
    perform dblink_send_query('link'||i, format('alter table ut%s set (autovacuum_enabled=on, toast.autovacuum_enabled=on)', i));            
  end loop;         
  for i in 0..55 loop      
    return query select * from dblink_get_result('link'||i) as t(res text);      
  end loop;      
end;      
$$ language plpgsql strict;   
create or replace function crt_idx_tbl() returns setof record as $$      
declare      
begin      
  for i in 0..55 loop             
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres');             
    perform 1 from dblink_get_result('link'||i) as t(res text);            
    perform dblink_send_query('link'||i, format('create index idx_ut%s_1 on ut%s (c1)', i, i));            
  end loop;         
  for i in 0..55 loop      
    return query select * from dblink_get_result('link'||i) as t(res text);      
  end loop;      
end;      
$$ language plpgsql strict;   
\timing  
  
select * from set_tbl() as t(res text);  
  
select * from crt_idx_tbl() as t(res text);  

小结

本文介绍了如何非常快速的导入数据到数据库中,使得硬件的能力得以发挥。在阿里云ECS上,采用PostgreSQL实现了每分钟约导入8亿记录的效果。

有兴趣的同学可以拿阿里云RDS PG,RDS PPAS 10试一试。

文件可以是本地,也可以是OSS上的文件,如果使用阿里云RDS PG系列产品,可以使用OSS外部表异步并行导入,速度也是杠杠的。

《阿里云RDS PostgreSQL OSS 外部表实践 - (dblink异步调用封装并行) 从OSS并行导入数据》

《阿里云RDS PostgreSQL OSS 外部表实践 - (dblink异步调用封装并行) 数据并行导出到OSS》

参考

《阿里云 RDS PostgreSQL 隐藏开关 - hash to btree, unlogged to logged》

《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》

Flag Counter

digoal’s 大量PostgreSQL文章入口