1. Introduction
Response time of inserting big data(e.g 10GB~) is often longer than one of writing that data into storage. I looked up the cause of this difference by reading PostgreSQL source and performing simple experiment.
2. Prerequities
Version of PostgreSQL: PostgreSQL13.0
Experimental Setting: CentOS7, PC spec: Ryzen 7 3700X 3.6GHz 8Cores, 128GB memory, SSD M.2 1000GB, shared_buffers = 50GB
3. What I looked up
(1) PostgreSQL internal processing performing insert command
(2) PostgreSQL internal processing performingcopy command
(3) PostgreSQL internal processing performingcreate table as select ~ command
(4) PostgreSQL internal processing performingcreate materialized view command
(5) Bottleneck of inserting big data in PostgreSQL
4. Setup
\echo setting for source reading
create database testinsert;
\c testinsert
create table t1(col1 float8);
insert into t1 values(1);
insert into t1 values(2);
copy t1 from ‘<data_dir>/data.csv’ with csv;
create table t2 as select col1 from t1;
create materialized view t3 as select select col1 from t1;
\echo setting for experiment
create table t4(col1 float8, col2 float8, col3 float8, col4 float8, col5 float8, col6 float8, col7 float8, col8 float8, col9 float8, col10 float8);
select setseed(0.5);
insert into t4 select random(), random(),random(),random(),random(),random(),random(),random(),random(),random() from generate_series(1,100000000,1) t;
5. What I found
5.1 Insert command
I found the heap_insert function is a core function of insert operation. heap_insert function is defined in the following source.
https://github.com/postgres/postgres/blob/REL_13_STABLE/src/backend/access/heap/heapam.c
Calling seuquence from Executor entry point PortalRun to heap_insert is roughly equal to
PortalRun>ProcessQuery>ExecutorRun>ExecModifyTable>ExecInsert>table_tuple_insert>heapam_tuple_insert>heap_insert.
heap_insert process
(i) Getting block buffer to which insert record and getting lock for the block
(ii) Making wal record
(iii) Insert wal record to buffer and getting lock for the inserting wal record
5.2 Copy command
I found the heap_insert function is a core function of copy command. heap_multi_insert function is defined in the following source.
https://github.com/postgres/postgres/blob/REL_13_STABLE/src/backend/access/heap/heapam.c
Calling seuquence from Executor entry point PortalRun to heap_insert is roughly equal to
PortalRun>ProcessUtility>DoCopy>table_multi_insert>heap_multi_insert.
According to the source comment of heap_multi_insert, heap_multi_insert is different from heap_inser in that heap_multi_insert merge multiple insert operation to a single block to one wal record. So heap_multi_insert reduce the number of operations for making wal records and for lock.
5.3 Create table as select ~ command
I found the heap_insert function is a core function of create table as select ~ command. Calling seuquence from Executor entry point PortalRun to heap_insert is roughly equal to
PortalRun>ProcessUtility>ExecCreateTableAs>ExcutorRun>CatalogTupleInsertWithInfo>heap_insert.
5.4 Create materialized view command
I found the heap_insert function is a core function of create materialized view command. Calling seuquence from Executor entry point PortalRun to heap_insert is roughly equal to one of create table as select ~ command.
5.5 Bottleneck of inserting big data in PostgreSQL
I perform the following command with some options
command: create table t5 as select * from t4;
options:
(1) normal table or unlogged table
(2) fsync on or off
Respons times are the followings.
normal table + fsync on: 57sec
normal table + fsync off: 45sec
unlogged table + fsync on: 27sec
So I think breakdown of total response time is roughly the following.
Query execution and Making tuple time: 27sec
Getting block and Making wal and Getting locks time: 18sec
Wal flush time: 12sec