How Does PostgreSQL Implement Batch Update, Deletion, and Insertion?

Image for post
Image for post

Background

This article aims to answer all the following questions:

  • How do I insert multiple records at once?

Batch operations help reduce the interactions between databases and applications and improve data processing throughput.

Batch Insertion

Batch Insertion 1

Use the insert into ... select method as shown below.

postgres=# insert into tbl1 (id, info ,crt_time) select generate_series(1,10000),'test',now();    
INSERT 0 10000
postgres=# select count(*) from tbl1;
count
-------
10001
(1 row)

Batch Insertion 2

Use the values(),(),...() method as shown below.

postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now()), (2,'test2',now()), (3,'test3',now());    
INSERT 0 3

Batch Insertion 3

Use the BEGIN; ... multiple insert statements... ; END method as shown below.

Strictly speaking, this is not a batch operation, but it helps to reduce the synchronization wait time when transactions are submitted. It also improves performance.

postgres=# begin;    
BEGIN
postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now());
INSERT 0 1
postgres=# insert into tbl1 (id,info,crt_time) values (2,'test2',now());
INSERT 0 1
postgres=# insert into tbl1 (id,info,crt_time) values (3,'test3',now());
INSERT 0 1
postgres=# end;
COMMIT

Batch Insertion 4

Unlike Insert protocol, the Copy protocol is more streamlined and makes insertion more efficient.

test03=# \d test  
Table "public.test"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | integer | not null
info | text |
crt_time | timestamp without time zone |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)

test03=# copy test from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 8 'test' '2017-01-01'
>> 9 'test9' '2017-02-02'
>> \.
COPY 2

The corresponding copy interface varies with the language driver.

Batch Update

Implement a batch update as shown below.

test03=# update test set info=tmp.info from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) where test.id=tmp.id;  
UPDATE 3
test03=# select * from test;
id | info | crt_time
----+--------------+----------------------------
3 | hello | 2017-04-24 15:31:49.14291
4 | digoal0123 | 2017-04-24 15:42:50.912887
5 | hello digoal | 2017-04-24 15:57:29.622045
1 | new1 | 2017-04-24 15:58:55.610072
2 | new2 | 2017-04-24 15:28:20.37392
6 | new6 | 2017-04-24 15:59:12.265915
(6 rows)

Use other table names after ‘from’ to perform multi-table JOIN batch update.

Batch Deletion

Implement batch deletion as shown below.

test03=# delete from test using (values (3),(4),(5)) as tmp(id) where test.id=tmp.id;  
DELETE 3
test03=# select * from test;
id | info | crt_time
----+---------+----------------------------
1 | new1 | 2017-04-24 15:58:55.610072
2 | new2 | 2017-04-24 15:28:20.37392
6 | new6 | 2017-04-24 15:59:12.265915

Use other table names after ‘using’ to perform multi-table JOIN batch deletion.

If you want to clear the table, we recommend usig truncate.

test03=# set lock_timeout = '1s';
SET
test03=# truncate test;
TRUNCATE TABLE
test03=# select * from test;
id | info | crt_time
----+------+----------
(0 rows)

Batch Update or Deletion -If Cartesian or One-to-Many or Many-to-Many JOIN Operations Occur

In a scenario where a Cartesian or one-to-many or many-to-many JOIN operations occur, the database might not know the target row for which the value needs to be updated.

postgres=# create table t1 (id int primary key, info text);
CREATE TABLE
postgres=# create table t2(id int, info text);
CREATE TABLE
postgres=# insert into t1 select generate_series(1,10), 't1';
INSERT 0 10
postgres=# insert into t2 values (1,'t2');
INSERT 32796 1
postgres=# insert into t2 values (1,'t2');
INSERT 32797 1
postgres=# insert into t2 values (1,'t3');
INSERT 32798 1
postgres=# insert into t2 values (1,'t4');
INSERT 32799 1
postgres=# update t1 set info=t2.info from t2 where t1.id=t2.id;
UPDATE 1
postgres=# select * from t1 where id=1;
id | info
----+------
1 | t2
(1 row)
postgres=# delete from t1 using t2 where t1.id=t2.id;
DELETE 1
postgres=# drop table t1;
DROP TABLE
postgres=# drop table t2;
DROP TABLE
postgres=# create table t1 (id int, info text);
CREATE TABLE
postgres=# create table t2 (id int, info text);
CREATE TABLE
postgres=# insert into t1 values (1,'t1');
INSERT 32814 1
postgres=# insert into t1 values (1,'t1');
INSERT 32815 1
postgres=# insert into t1 values (1,'t1');
INSERT 32816 1
postgres=# insert into t2 values (1,'t2');
INSERT 32817 1
postgres=# insert into t2 values (1,'t3');
INSERT 32818 1
postgres=# insert into t2 values (1,'t4');
INSERT 32819 1
postgres=# insert into t1 values (2,'t1');
INSERT 32820 1
postgres=# select * from t1;
id | info
----+------
1 | t1
1 | t1
1 | t1
2 | t1
(4 rows)
postgres=# select * from t2;
id | info
----+------
1 | t2
1 | t3
1 | t4
(3 rows)
postgres=# update t1 set info=t2.info from t2 where t1.id=t2.id;
UPDATE 3
postgres=# select ctid,* from t1;
ctid | id | info
-------+----+------
(0,4) | 2 | t1
(0,5) | 1 | t2
(0,6) | 1 | t2
(0,7) | 1 | t2
(4 rows)
postgres=# delete from t1 using t2 where t1.id=t2.id;
DELETE 3
postgres=# select ctid,* from t1;
ctid | id | info
-------+----+------
(0,4) | 2 | t1
(1 row)

References

Original Source:

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store