Collect Postgres production log and use it with pgreplay
With pgreplay, we can emulate real workload for various purposes:
- performance testing (with ability to replay with 2x, 3x, etc speed),
- perform deep query analysis (with auto_explain, pg_stat_kcache and other tools involved – those that we will hardly use on production servers under real load),
- conduct Database Experiments when optimizing DB schema, Postgres parameters.
pgreplay won't guarantee the exact sequence of locks, it even cannot guarantee the same query execution plans in 100%, but for large amount of queries given and under the same circumstances (same instance configuration and Postgres config), it will give general picture close to reality, providing invaluable capabilities to explore and experiment.
Lower log_min_duration_statement
we have, the better picture will be. Ideally, it should be set to 0.
Current value used on production and staging nodes is 5 seconds.
Per multiple discussions, were decided to go down in iterations, carefully measuring I/O, not allowing it to hit disks.
Edited by Nikolay Samokhvalov