Horton
Menu

Thread

  1. reclaim database storage

    And. Andruikhanov <andy@euinf.dp.ua> — 2001-10-15T23:37:32Z

    Hi.

System Configuration
---------------------
   Architecture: Intel Pentium
   Operating System: FreeBSD 4.2-STABLE
   PostgreSQL version:   PostgreSQL-7.1.2
   Compiler used:  gcc 2.95.2

Description of problem
----------------------

   Some data-tables accumulate infromation from the system (by INSERT 
operations). And then old data has been deleted periodically (by DELETE 
operations). Also, I run "VACUUM analyze" command on this tables. But physically 
database indexs don't cleans. I mean, that size of index files in 
pgsql/data/base/... always grows.

   For example, size of table-file was about 2Mb, but size of index - 101Mb.
   Only DROP/CREATE index operations can corrects this problem, but ony 
temporary. Indexs was created by implicit keyword "UNIQUE", while I created 
table (on one column). Or from "psql" console by command:
CREATE UNIQUE INDEX idx_name on table_name(column_name);
Index column types are int4 and numeric(10).

   Also I had this problem on server version 7.0.*, but It was not important 
thing at that time. Upgrade procedure (from 7.0.* to 7.1.2) I made correctly 
without any problems (with backup/restore database).

Vaccuum process from pgsql log ("dirty" indexes):

2001-10-16 00:54:20 NOTICE:  Pages 265: Changed 0, reaped 176, Empty 0, New 0; T
up 10350: Vac 10198, Keep/VTL 0/0, Crash 0, UnUsed 291, MinLen 92, MaxLen 104; R
e-using: Free/Avail. Space 1034252/1032760; EndEmpty/Avail. Pages 0/142. CPU 0.0
0s/0.01u sec.
2001-10-16 00:55:26 NOTICE:  Index adj30_1_d10_id_key: Pages 13984; Tuples 10350
: Deleted 10198. CPU 1.37s/0.27u sec.
2001-10-16 00:55:47 NOTICE:  Index adj30_1_r_id_key: Pages 13658; Tuples 10350:
Deleted 10198. CPU 1.11s/0.31u sec.
2001-10-16 00:55:50 NOTICE:  Rel adj30_1: Pages: 265 --> 134; Tuple(s) moved: 56
76. CPU 0.11s/0.59u sec.
2001-10-16 00:56:57 NOTICE:  Index adj30_1_d10_id_key: Pages 13989; Tuples 10350
: Deleted 5676. CPU 1.20s/0.18u sec.
2001-10-16 00:57:19 NOTICE:  Index adj30_1_r_id_key: Pages 13658; Tuples 10350:
Deleted 5676. CPU 1.20s/0.15u sec.
2001-10-16 00:57:19 NOTICE:  Analyzing...

                   Table "adj30_1"
  Attribute |           Type           |  Modifier
-----------+--------------------------+-------------
  t1_t      | timestamp with time zone |
  t2_t      | timestamp with time zone |
  s0_0      | smallint                 | default 0
  s2_0      | character(1)             |
  s2_1      | character(1)             | default 't'
  s0_2      | smallint                 |
  s1_20     | integer                  |
  s1_21     | integer                  |
  s1_1      | integer                  | default 0
  flags0    | integer                  | default 0
  d10_id    | numeric(20,0)            |
  r_id      | integer                  | not null
Indices: adj30_1_d10_id_key,
          adj30_1_r_id_key
Constraints: (t2_t NOTNULL)
              (t1_t NOTNULL)
              (flags0 NOTNULL)
              ((s1_1 NOTNULL) AND (s1_1 >= 0))
              (s1_21 NOTNULL)
              (s1_20 NOTNULL)
              (s0_2 NOTNULL)
              (s2_1 NOTNULL)
              (s2_0 NOTNULL)

  May be I must use another operations for index control ?


   Good luck.
----