3 ms·

That SQL statement that does update and insert is pretty cool. It chains together both parts into single statement so it doesn't even need to start and commit t

• 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 ago
    With 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.
• danialtz 11y ago
  Nevertheless, one should be very careful with unescaped queries like that in nginx. There is a reason frameworks exists around such topics.