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
