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).
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.
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)?
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?
If no, why? Should the external scripts/code that puts the data into the database be responsible for removing the old data?
Thanks in advance for any/all pointers!
-Joshua
--
Joshua Berry
Software Engineer
Opentech, S.A.
+(595 21) 282557 Work
+(595) 981 330 701 Mobile
