On Mon, Jan 14, 2002 at 04:25:12PM +0200, David Anthony wrote:
> Hi
>
> I have a postgresql database with about 5 tables. On a nightly basis, all
> the records in all 5 tables are deleted and repopulated with fresh data.
> Some of the tables have between 30 000 and 80 000 records. I have been
> looking through the docs to try and work out how best to optimize the
> database as I have noticed a definite lag in response as the database grows.
>
> 1. I have placed indexes on the columns that are used in searching
> 2. when deleting all the records from a table I use TRUNCATE instead of
> DELETE
> 3. the repopulation occurs via a perl script and at the end of the script I
> run VACUUM ANALYZE on the database.
>
> Any other things I should look at? Also, is there a way to check when the
> last time VACUUM ANALYZE was run on the db. I am unsure whether the perl
> script is running the command successfully.
>
Well one thing to keep in mind is that pg 7.1.3 and befor does not
automaticly reclaim index space that is no longer in use. one way
to handle this is to use the reindex command on each table every so
often, daily, weekly, monthly to get your disk back and speed up
searches. Another way to do it is as part of your data import
drop and recreate the database, you delete all records anyway.
The reindexing command is reindex:
\dstb_tracking=# \h reindex
Command: REINDEX
Description: Recover corrupted system indexes under stand-alone Postgres
Syntax:
REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]
good luck
marc
> Thanks
> Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
