3 ms·
I think PostgreSQL still executes it as a transaction, even though it is a single statement. While benchmarking this, I learned that with the default and safe
by cuu508 11y ago
I think PostgreSQL still executes it as a transaction, even though it is a single statement.
While benchmarking this, I learned that with the default and safe settings (synchronous commit), PostgreSQL can only do few hundred write transactions per second, however simple. Set synchronous_commit=off in postgresql.conf and TPS goes into thousands.
- anarazel 11y agoWith synchronous_commit = on the throughput heavily depends on your IO subsystem and the number of concurrent transactions. As every transaction has to be durable at commit, fsync (well, fdatasync() if supported) of a sequential log is required. Most single drives can do a ~100 (rotational disks, normal speeds) to a few thousands fsyncs/sec. If you have a battery backed raid controller you can do tens to hundreds of thousands, since the data will not actually be written to disk. Concurrency matters because several commits can be made durable with a single flush to disk if they're happening concurrently (sometimes called "group commit"). EDIT: spelling, grammar, minor clarification.