Re: Garbage Collecting

Joshua Berry wrote:
> I'm a postgresql newbie that's inherited eight production servers
> running Postgresql 8.2.5 as the backend. I have many questions
> covering topics such as administration of the database (upgrading,
> maintaining conf files, etc), improving the schema of the system (many
> tables don't currently have primary keys; to do anything useful you
> must join at least 5 tables), optimizing poor performing queries that
> can take hours, and knowing where functionality of the system should
> reside (curenly as PL/SQL functions, as external c code, external php
> code, and external perl code).
Indexing can be a win once you figure out which quires are run the most
and what the common where clauses look like. Indexes can eat up allot
of disk space and slow performance in other places. Its a double edge
sword.

http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html

The database I maintain has far more Reads by a factor of 10,000 times
to inserts, updates, and deletes combine so having many indexes is a
win in my case. This is the part of tuning the database to the load.

http://wiki.postgresql.org/wiki/Performance_Optimization
http://wiki.postgresql.org/wiki/Database_Administration_and_Maintenance

>
> Please refer me to appropriate documentation/FAQs/books. I've read
> http://www.postgresql.org/docs/faqs.FAQ.html If anyone knows of
> writeups for newbies that touches upon the things I mentioned, that
> would probably be really helpful for me.
http://wiki.postgresql.org/wiki/Main_Page
and the help files with comments has lots of helpful information. Just
make sure that you are reading information related to the version of
Postgresql you are running.

http://www.postgresql.org/docs/8.2/interactive/index.html
>
> I have one specific question about "Garbage Collecting" within the
> database. The database system I'm working with has data that is no
> longer needed after a period of time. For example: transaction records
> only need to be kept around for the last 31 days; php web sessions
> that don't need to persist longer than a day. Could I create some
> function in the database that would act a bit like a daily cron job
> that deletes old records from tables (and then performs the
> appropriate VACUUM to regain the space)?
Yes you can do this with pgAgent it comes with pgAdmin
http://www.pgadmin.org/docs/1.4/pgagent.html
http://www.pgadmin.org/

If the records have a date when added then a command like so can be
scheduled

Delete from Mytable where DateAdded <= (Current_date - '31 day
'::interval' )::date
>
> If yes, how does one impliment something like that? As a trigger
> function written in PL/SQL? Can I hook the function into something
> that executes once per day?
Yes it can be written in PL/SQL function then have pgAgent call it at
midnight or at sometime thats off peak load times.

Also you want to make sure autovaccum is turned to your needs

http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html
this can have big impacts on performance.

> If no, why? Should the external scripts/code that puts the data into
> the database be responsible for removing the old data?

It really does not matter.
>
> Thanks in advance for any/all pointers!
>
> -Joshua
>
> --
> Joshua Berry
> Software Engineer
> Opentech, S.A.
> +(595 21) 282557 Work
> +(595) 981 330 701 Mobile