PostgreSQL performance fucking upgrade to the max

1M records inserted in 22 minutes works out to be 758 records/second. Each INSERT here is an individual commit to disk, with both write-ahead log and database components to it eventually. Normally I expect that even good hardware with a battery-backed cache and everything you will be lucky to hit 3000 commit/second. So you're not actually doing too bad if this is regular hardware without such write acceleration. The normal limit here is in the 500 to 1000 commits/second range in the situation you're in, without special tuning for this situation.

As for what that would look like, if you can't make the commits include more records each, your options for speeding this up include:

That's pretty much it. Anything else you touched that might help could potentially cause data corruption in a crash; these are all completely safe.

 

============================================================================

 

onze upgrade voor postgres.conf

 

synchronous_commit = off                # synchronization level; on, off, or local
wal_buffers = 16MB                      # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10000ms              # 1-10000 milliseconds
checkpoint_segments = 128               # in logfile segments, min 1, 16MB each
 

 

============================================================================

update shared memory linux

# echo "kernel.shmmax=2147483648" >> /etc/sysctl.conf