首页
产品
CLup:PostgreSQL高可用集群平台CData高性能数据库云一体机CBackup数据库备份恢复云平台CPDA高性能双子星数据库机
解决方案
数据库专业技术服务全栈式PostgreSQL解决方案Oracle分布式存储化数据库云
文章
客户及伙伴
中启开源
关于我们
公司简介 联系我们
中启开源

1. 前言

中启乘数科技是一家专业的PostgreSQL和Greenplum数据库服务提供商,专注于数据库极致的性能,PostgreSQL 14在很多方面都进行了改进,其中之一就是二进制模式的copy命令的性能提升了20%以上。

2. 测试过程

我们准备在一台物理机器上准备两套环境,一套是PostgreSQL 13.3,另一套是PostgreSQL 14beta1。
物理机器的硬件为2路服务器,CPU为:Intel(R) Xeon(R) Silver 4210R CPU @ 2.40GHz,内存为256GB。

造测试表:

  1. create table foo5 (a text, b text, c text, d text, e text);

造测试数据:

  1. insert into foo5 select repeat('a', (random()*100)::int), 'bbb', 'cc','d', 'eee' from generate_series(1, 10000000);
  2. copy foo5 to '/tmp/foo5.bin' binary;

清空测试表:

  1. truncate table foo5;

把数据导入过来,看导入的时间:

  1. copy foo5 from '/tmp/foo5.bin' binary;

在PostgreSQL 13.3上

  1. [pg13@pg01 ~]$ psql
  2. psql (13.3)
  3. Type "help" for help.
  4. postgres=# \timing
  5. Timing is on.
  6. postgres=# create table foo5 (a text, b text, c text, d text, e text);
  7. CREATE TABLE
  8. Time: 61.039 ms
  9. postgres=# insert into foo5 select repeat('a', (random()*100)::int), 'bbb', 'cc',
  10. postgres-# 'd', 'eee' from generate_series(1, 10000000);
  11. INSERT 0 10000000
  12. Time: 12415.693 ms (00:12.416)
  13. postgres=# copy foo5 to '/tmp/foo5.bin' binary;
  14. COPY 10000000
  15. Time: 5239.320 ms (00:05.239)
  16. postgres=# truncate foo5;
  17. TRUNCATE TABLE
  18. Time: 145.215 ms
  19. postgres=# copy foo5 from '/tmp/foo5.bin' binary;
  20. COPY 10000000
  21. Time: 10817.424 ms (00:10.817)
  22. postgres=# truncate table foo5;
  23. TRUNCATE TABLE
  24. Time: 154.791 ms
  25. postgres=# copy foo5 from '/tmp/foo5.bin' binary;
  26. COPY 10000000
  27. Time: 10807.711 ms (00:10.808)

可以看出导入花费了10秒左右。

在PostgreSQL 14上

  1. [pg14@pg01 ~]$ psql
  2. psql (14beta1)
  3. Type "help" for help.
  4. postgres=# create table foo5 (a text, b text, c text, d text, e text);
  5. CREATE TABLE
  6. postgres=# insert into foo5 select repeat('a', (random()*100)::int), 'bbb', 'cc',
  7. postgres-# 'd', 'eee' from generate_series(1, 10000000);
  8. INSERT 0 10000000
  9. postgres=# \timing
  10. Timing is on.
  11. postgres=# copy foo5 to '/tmp/foo5.bin' binary;
  12. COPY 10000000
  13. Time: 5179.257 ms (00:05.179)
  14. postgres=# truncate foo5;
  15. TRUNCATE TABLE
  16. Time: 110.360 ms
  17. postgres=# copy foo5 from '/tmp/foo5.bin' binary;
  18. COPY 10000000
  19. Time: 8160.788 ms (00:08.161)

可以看出导入的时间是8秒,节省了2秒,时间基本缩短了20%左右。

3. COPY命令使用binary的好处

主要是导出binary格式的数据,速度比较快:

  1. [pg13@pg01 ~]$ psql
  2. psql (13.3)
  3. Type "help" for help.
  4. postgres=# create table foo1(id int, t text);
  5. CREATE TABLE
  6. postgres=# \timing
  7. Timing is on.
  8. postgres=# insert into foo1 select seq, repeat('a', 5000) from generate_series(1, 2000000) as seq;
  9. INSERT 0 2000000
  10. Time: 44657.110 ms (00:44.657)
  11. postgres=# copy foo1 to '/tmp/foo1.txt';
  12. COPY 2000000
  13. Time: 24079.960 ms (00:24.080)
  14. postgres=# copy foo1 to '/tmp/foo1.bin' binary;
  15. COPY 2000000
  16. Time: 8369.372 ms (00:08.369)

可以看到,导出BINARY格式的数据的速度比导出TXT类型的速度快的多,是近3倍。
上面是在PostgreSQL 13.3中测试的,在PostgreSQL 14中测试的结果也类似,如下所示:

  1. [pg14@pg01 ~]$ psql
  2. psql (14beta1)
  3. Type "help" for help.
  4. postgres=# create table foo1(id int, t text);
  5. CREATE TABLE
  6. postgres=# \timing
  7. Timing is on.
  8. postgres=# insert into foo1 select seq, repeat('a', 5000) from generate_series(1, 2000000) as seq;
  9. INSERT 0 2000000
  10. Time: 44571.181 ms (00:44.571)
  11. postgres=# \timing
  12. Timing is on.
  13. postgres=# copy foo1 to '/tmp/foo1.txt';
  14. ERROR: could not open file "/tmp/foo1.txt" for writing: Permission denied
  15. HINT: COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy.
  16. Time: 0.501 ms
  17. postgres=# copy foo1 to '/tmp/foo1.txt';
  18. COPY 2000000
  19. Time: 23975.182 ms (00:23.975)
  20. postgres=# copy foo1 to '/tmp/foo1.bin' binary;
  21. COPY 2000000
  22. Time: 8277.149 ms (00:08.277)

当然从BINARY格式的数据也更快:

  1. [pg14@pg01 ~]$ psql
  2. psql (14beta1)
  3. Type "help" for help.
  4. postgres=# \timing
  5. postgres=# copy foo1 from '/tmp/foo1.bin' binary;
  6. COPY 2000000
  7. Time: 46921.412 ms (00:46.921)
  8. postgres=# truncate table foo1;
  9. TRUNCATE TABLE
  10. Time: 86.614 ms
  11. postgres=# copy foo1 from '/tmp/foo1.txt';
  12. COPY 2000000
  13. Time: 78118.955 ms (01:18.119)

从上面可以看到,导入TXT类型的文件,花了78秒,而导入BINARY类型的文件,花了47秒。

4. 结论

到PostgreSQL 14之后,使用COPY命令导入二进制格式COPY数据时,性能有20%以上的性能提升。
当用COPY命令中使用binary选项把数据导入和导出时,通常会快很多,因为不需要做一些转换。今后大家可以多试试带binary选项的COPY命令。

PostgreSQL 14在使用BINARY选项的COPY命令导入数据时,比之前的版本快的原因,是使用了buffer,减少了文件系统的fread的调用,具体可以见: