my client called me. Help I m doing big insert on partitionned tables and it s running very low.
Beside that the statement is faster during the midnight.
You want to increase the max_lock_per_transactions :
PostgreSQL: You might need to increase max_locks_per_transaction (cybertec-postgresql.com)
You want some tips to deal with locks
When Postgres blocks: 7 tips for dealing with locks (citusdata.com)
You want to detect the locks :
Postgresql : détecter efficacement les locks – Blog | SETRA-Conseil
You want to reduce the AccessShareLock :
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 How Does PostgreSQL Implement Batch Update, Deletion, and Insertion? - Alibaba Cloud Community
Explaination :
Before you did not close well your connections, the locks stay active until you don't close your transactions with end ;
Indeed my client didnot use a BEGIN... END; statement
And during midnight we had some admin jobs who closed the idle process.
Other solution is to kill the "idle" connections if the client doesn't change anything....
Solution 1 :
Write several batchs :
batch 1
BEGIN
insert 1 on table 1
insert 2 on table 2
loop of insert .. on table 3
END;
batch 2
BEGIN
insert 1 on table 1
insert 2 on table 2
loop of insert .. on table 3
END;
BEGIN
insert 1 on table 1
insert 2 on table 2
loop of insert .. on table 3
END;
etc ...
Solution 2 :
Increase the max_lock_per_transactions
Solution 3 :
Detect the idle connections and kill them.
SELECT oracle_username || ' (' || s.osuser || ')' username, s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object, object_type, DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE (v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR (lmode)) mode_held FROM v$locked_object v, dba_objects d, v$lock l, v$session s WHERE v.object_id = d.object_id AND v.object_id = l.id1 AND v.session_id = s.sid ORDER BY oracle_username, session_id;
https://emarcel.com/identifyandkill/