Generate synthetic data
PG server config
PG docs - populating a Database
Remove foreign key constraints
- foreign key constraints are more efficiently checked in "all at once" than row by row
- the same idea applies to indexes
- procedure
- drop foreign key constraints
- load the data
- re-create foreign key constraints
Remove foreign key constraints
PG server configuration
- configurable via
- config file, postresql.conf
- shell, server command line
- SQL query (in runtime)
(docs)
Increase max_wal_size
- Write Ahead Log
- WAL is the standard method of ensuring data integrity
- increasing max_wal_size reduces the number of checkpoints where PG needs to flush data pages to disk
- default value 1024 MB
- with the default settings, the checkpoint is requested after ~100k inserts
Increase max_wal_size
2023-05-01 12:45:47.740 UTC [59]
LOG: checkpoint starting: wal
2023-05-01 12:46:12.878 UTC [59] LOG:
checkpoint complete: wrote 746 buffers (4.6%);
0 WAL file(s) added, 0 removed, 33 recycled;
write=24.833 s, sync=0.260 s, total=25.138 s;
sync files=5, longest=0.247 s, average=0.052 s;
distance=540807 kB, estimate=542404 kB
2023-05-01 12:46:15.780 UTC [59]
LOG: checkpoints are occurring too frequently (28 seconds apart)
2023-05-01 12:46:15.780 UTC [59]
HINT: Consider increasing the configuration parameter "max_wal_size".(hint in logs)
Increase max_wal_size
I'm the last slide
Questions?
Generate synthetic data
By vrabac
