Tuning pointers for the DB. - Mailing list pgsql-admin

From Warren Vanichuk
Subject Tuning pointers for the DB.
Date
Msg-id Pine.LNX.4.21.0011221418370.1722-100000@urd.street-light.com
Whole thread Raw
List pgsql-admin
Greetings.  :)

Having just gotten our database all planned out and implimented, and rolled
out live, I'd like to now take the time to sit back and tweak the system up
to get the most bang for my processing buck so to speak.

The database itself isn't that large, comprising 14 tables.  Most of the
tables are fairly static, only getting new information added to them every
week or so, if they are lucky.  4 tables however, get alot of
updates/inserts done onto them with each web request, which averages 2-3
requests a second.

The general flow of the data is like :
    -> User hits a webpage.

    -> A storage procedure is called that updates a pageview table,
           simply incrementing a counter.

    -> The program selects N links out of the database from a view,
           based on various criteria like category Y.

    -> For the N links selected out of the database a storage procedure
           is called that either inserts a row into two tables (if the data
           doesn't already exist) or updates a row in two tables (if the
           data already exists).

The entire thing is done inside a transaction (BEGIN/END).

And now the questions begin.. :)

How often should I vacuum, given than the 2 statistics tables being updated
constantly are joined in the view to produce the list of links?  When is a
vacuum analyze a good idea in this instance? :)

I have setup an index on the two stats tables for the linkid, but I don't
want to go overboard with them, as warned against in the manual.  However,
given the following setup :

table1             table2
-------------      ----------
linkid int4       linkid int4
linkmax int4       linkcur int4
linkurl text       linkcat int4
           linkloc int4

And the query :

select linkid, linkurl from table1, table2 where table1.linkid =
table2.linkid and table1.linkmax < table2.linkcur

Is there any benefit to setting up an index on table2 like :

create index table2_linkidcur_idx on table2 (linkid, linkcur ); ?

Basically I'm wondering when it's a good idea to create indexes, if there is
an easy way to spot that an index is needed, etc..  :)

Any information and pointers anybody could offer would be greatly
apprieciated. :)

Sincerely, Warren


pgsql-admin by date:

Previous
From: "Oliver Elphick"
Date:
Subject: Re: [GENERAL] Logging
Next
From: "Thomas Heller"
Date:
Subject: Re: Lack of Performance