I have a Win2000 server (I know!) running Postgresql 8.0
For the last few months, the server tends to slow up to the point where I need to restart it almost daily, and I am trying to eliminate any issues in postgresql. The only thing I really know how to do is the vacuum, which I run regularly.
I have the following files
both of which are precisely 1GB each, neither of which have been updated for a month. I assume from reading that these are table / index files which have hit the 1GB limit.
Additionally there is a file postgresql\8.0\data\base\252975\253166 which is around .5GB, and is being regularly updated, along with many other files in the same folder of varying but smaller size.
My question is, can the 1GB be tuned / deleted / moved / etc. in order to help performance?
NB the disk is 50gb with 40gb free.
Thank you for reading - any advice greatly appreciated!
First: Postgres 8.0 is is dead and has not been supported for over 4 years (and 8.0 has never been a good choice for Windows in the first place)
You should really (I mean: really) upgrade to a maintained, up-to-date version, e.g. 9.4
Each file represents a table, if you want to remove the file, you need to remove the data from the table (or drop the table).
The following query will show you the filepaths and size for each table:
select ns.nspname as schema_name, t.relname, pg_relation_filepath(t.oid), pg_relation_size(t.oid)
from pg_class t
join pg_namespace ns on ns.oid = t.relnamespace
where ns.nspname not like 'pg_%'
and ns.nspname <> 'information_schema';
But I have no idea if that will work with you ancient Postgres version.
But in general you don't "tune files" (or even tables) in a database, you need to tune the query accessing the table(s).