PostgreSQL Performance Tuning

[D. Hageman]

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                    <dhageman at dracken.com>  ||
\\========================================================//