Now I'm curious: Assume for the sake of argument that we're dealing with an app on a single physical machine, no separate DB server. If you wanted to have production data and logs both in, say, Postgres, would you run two separate Postgres daemons, or is there some saner way of handling this?
It's not a good idea to run more than one database on the same physical machine. Especially a well-tuned database. The database always thinks it is the only and most important process on your machine. If you run more than one, they will constantly be contending with each other for the same I/O bandwidth. You will not see 1/N performance with N databases on the same machine, you will see something much worse.
On top of that, it's a bad idea because, where are you going to put your database logs? In the database? What happens when you run out of disk? All your applications are going to be trying desperately to log that they're out of space, and your database won't have anywhere to put those messages. This is why /var/log is traditionally a separate filesystem in Unix. But if you log to the database, unless you go through some hoops (tablespaces) they're all going to the same partition and the same physical disk. Believe me, these scenarios happen in real life, and usually on a weekend. Even worse, some applications (though not Postgres, I think) will try and hold onto pending writes until there is free disk, so the moment you free up space, you lose it. You don't want to have to shutdown the production database just because your logs ate up a bunch of space.
The right thing to do is let your database have its own machine (yes, physical machine) and its own disks. Ideally, your Postgres install has separate physical disks for the data and the WAL, and I'd probably put the regular logs on another disk as well if I could afford it. You'll be shocked how much better it will perform, too. Don't fool yourself. A bunch of VPSes is not the same as real hardware running a real database like Postgres.
I don't see why two daemons are necessary, simply having a separate database (in the CREATE DATABASE sense) should be sufficient to not make a big mess. There are lots of fun optimizations you can do like move its storage location to a separate disk to reduce IO contention. You could also avoid making indices on live log tables to improve INSERT performance. You could add an async buffering layer and do bulk-inserts which have much better performance.
If you're sufficiently careful, I imagine you could find similar performance with PostgreSQL-based logging as you would with a rotating flatfile log. Except you wouldn't need to worry about consolidating and parsing them.
