Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2014

    Unanswered: large files | performance issues


    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

    postgresql\8.0\data\base\252975\253258 AND

    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!

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 8
    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).

    I suggest you read this:

    and then add then supply the missing information.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts