It's probably important to define just what your requirements are gonna be. If you want to scale to truly huge load numbers, then most likely, you'll want to run Postgresql. It's the most heavily optimized of the three for very heavy parallel access.
If you want solid fk/pk relations, constraints, and such, but want something that is easy to administer and it only has to handle a few dozen users to a hundred at a time, then firebird is likely a good choice.
Under no circumstances would I personally ever use MySQL to handle financial / monetary applications. It's data handling / checking / constraints are simple too sloppy to be trusted with such an operation.
About vacuuming: Postgresql uses a method of locking called MVCC, which stands for multi-version concurrency control. It allows for a model where writers never block readers and vice versa, and literally hundreds of updates can hit the same table at the same time and all commit without waiting for each other, as long as they aren't hitting the same exact rows.
However, this method comes with a cost, dead tuples. Basically, every update in MVCC consists of a delete and an insert, and the deleted row has to be left around in case another transaction might be looking at it. Due to the huge performance hit that would be encountered should the transaction deleting that row have to check with every other transaction to see if it is still looking for every update / delete, Postgresql simply marks the new row with a more recent date / time, and moves along. New transactions now see the updated row, while old transactions see the old row(s).
Now, this is where vacuuming comes in. Every so often, the tables need to be "vacuumed" to reclaim lost space. In the past this was done either nightly, hourly, or weekly depending on the upate frequency, and was handled by cron jobs set up by the admin.
With the release of 7.3 and up, a contrib module called auto_vacuum was added, and it allows the administrator to kick off a daemon that monitors the statistics collector to see when individual tables need to be vacuumed and to do so automatically without user intervention.
FirebirdSQL uses MVCC as well, but it pays the price of vacuuming at the end of each transaction that touches a row, thus making it somewhat slower under heavy parallel load, but less likely to get itself in a bind should someone forget to vacuum like in PostgreSQL.
PostgreSQL probably has more people using it in heavy load production environments than any other open source database. And by heavy load I mean loads that make Slashdot look like a slacker. Terabytes of data, millions of transactions a day kind of load.
Easy, fast, reliable. Pick two 🙂
