
Performance Tuning PostgreSQL - twampss
http://www.revsys.com/writings/postgresql-performance.html
======
epi0Bauqu
After working with PostgreSQL for many years, my best advice (for repeated
queries, e.g. often hit Web app queries) is to make sure they all use indexes
and all the indexes fit in memory.

You can get sizes from: SELECT relname,relfilenode,relpages*8/1024 AS MB FROM
pg_class ORDER BY MB DESC LIMIT 10;

Newer versions avoid bloat better, but it can still happen if you don't vacuum
full, which you don't because of downtime implications. So every now and then
it is good to recopy everything in.

------
lsb
The guts of the article: give your DB lots of RAM for its cache, prepare your
statements, normalize your tables.

------
Femur
I think the article does a decent job of explaining how important properly
written SQL is to the health of an application.

I disagree with the notion that you should rely on your hardware or juice up
your system to gain performance. This should be a last resort only after
ensuring that you have well written SQL running on a properly laid-out
database.*

*caveat: Hardware can sometimes be cheaper than paying somebody to write SQL statements the right way.

Edit: changed "can" to "rely"

------
c00p3r
There are another great tutorial about performance tuning from IBM - Informix
Dynamic Server Performance Guide - all those ideas are discussed in that
volume.

By the way, Informix Dynamic Server itself is an advanced and very stable
product. From version 7.30 which was released almost a decade ago it offers
advanced functionality with low resource consumption. (No bloated java stuff
and other buzzwords).

It was a collection of multithreaded apps written in portable c++ which were
purchased by IBM for $1bn.

I personally had run several big installations for some chain of retail stores
in 2000, when all those magazines were never mention linux and informix as
enterprice solution.

Today Informix was resurected by IBM which once decided to discontinue it in
favour to support its DB/2 monster.

~~~
diN0bot
i'm curious why this was downovted. the author seems to have personal
experience when sharing his opinion...so doesn't seem like bad spam to me.

~~~
neilc
Seems off-topic to me.

