On Tue, 28 Jun 2005, Don Erickson wrote:
Is this view still essentially true, or was it ever? At the time mysql was the most popular open source db and having to vacuum your database seemed somehow suspect. Not that "most popular" is so much of an endorsement, I'm thinking here of a certain operating system.
PostgreSQL was never really slow, but with the web boom of the 90s I think most web people saw its true ACID compliance as just extra bloat. Who needs transactions with web pages (well, you should use them with e-commerce, but anyway ...). Also, it seemed like every LAMP book every written used mysql (with bad table design). If you hear something enough and there is enough documentation, you tend to use it.
The vacuum question is a good one and it goes on how the internals of many databases work. The very rough description of what is going here is that when you rollback a transaction it doesn't really delete what it has already inserted. Instead, it just marks it as deletable/recyclable. The vacuum command just goes through the database and cleans these up. The other thing it can do is analyze your table and your indices. This process allows the engine to more intelligently be able to process your queries. In general, you should run a vacuum every night on your database as you back it up. It doesn't take that much longer. When you properly maintain the database using these methods you will see PostgreSQL perform much better for you. In the new versions of PostgreSQL they have added auto-vacuum features which can help out a database administrator.
This e-mail is already long enough, so I won't go into too much more detail. If you are really interested in the differences start reading the whitepapers on the internal workings of these databases. Not all databases are created equal.
//========================================================\ || D. Hageman [email protected] || \========================================================//