PostgreSQL 9.2.2 on Windows 7 64-bit.
The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting log_statement = 'all' degrade performance? I've read that this significantly reduces performance, but these claims come from older articles, so just how much of a performance loss should I expect relative to log_statement = 'none'?
Best Answer
Like almost everything else, it depends on your hardware, configuration and workload.
If you're running few expensive statements it'll make little difference; if you're running lots of tiny simple statements it'll make more difference.
If your logs are on the same file system or even just the same disk as the database, the logging will make more difference than if the logs are stored on a separate disk. Even though the logs aren't explicitly
fsync()'d and are written sequentially, they're still competing with the main DB for I/O.More importantly, some file systems (ext3 in particular) will flush all data when any file is
fsync()'d, so the log writes become closer to random I/O than sequential I/O and slow down every commit. IIRC this is not an issue on ext4 or xfs.It'll also depend on whether you have statement timing enabled, the logging method you're using, and more.
The best option is to benchmark it with a simulation of your real workload and see.