Large DB, several tuning questions: Index sizes, VACUUM, REINDEX, Autovacuum

Hi All,

Maybe some questions are quite newbie ones, and I did try hard to scan
all the articles and documentation, but I did not find a satisfying
answer.

I'm running PostgreSQL 8.3.6 on a 32-Bit Centos 4 machine (which I
probably should update to 64 Bit soon)

I have some tables which tend to get huge (and will for sure hit the
wall of my storage system soon, total DB ~700 GB now):

SELECT relfilenode, relpages,reltuples,relname FROM pg_class WHERE
relpages > 10000 ORDER BY relpages DESC;
relfilenode | relpages | reltuples | relname
-------------+----------+-------------+---------------------------------
-
72693 | 51308246 | 4.46436e+09 | result_orig
72711 | 17871658 | 6.15227e+06 | test
73113 | 12240806 | 4.46436e+09 | result_orig_test_id
73112 | 12240806 | 4.46436e+09 | result_orig_prt_id
72717 | 118408 | 6.15241e+06 | test_orig
72775 | 26489 | 6.15241e+06 | test_orig_lt_id
72755 | 19865 | 6.15241e+06 | test_orig_test_id_key
73147 | 16872 | 6.15227e+06 | test_test_id
73146 | 16872 | 6.15227e+06 | test_lt_id

I'm going to work on the table size of the largest table (result_orig)
itself by eliminating columns, stuffing n Booleans into bit(n)'s,
replacing double precision by reals, etc.. By this I should be able to
reduce the storage per row to ~1/3 of the bytes currently used.

I have the same information stored in an Oracle 10g DB which consumes
only 70G data and 2G for indexes. The schema may be better optimized,
but for sure there is a table with 4 billion rows inside as well. So
it's about 10x smaller in disk space than PgSQL. I wonder why.

But still:

### My Issue No. 1: Index Size
What really worries me is the size of the two largest indexes
(result_orig_test_id, result_orig_prt_id) I'm using. Both are roughly
1/3 of the result_orig table size and each index only b-tree indexes a
single bigint column (prt_id, test_id) of result_orig. Roughly every
group of 100 rows of result_orig have the same prt_id, roughly every
group of 1000-10000 rows have the same test_id. Each of these two cols
is a Foreign Key (ON DELETE CASCADE).

So my fear is now, even if I can reduce the amount of data per row in
result_orig, my indexes will remain as large as before and then dominate
disk usage.

Is such disk usage for indexes expected? What can I do to optimize? I
could not run yet a VACUUM on result_orig, as I hit into max_fsm_pages
limit (still trying to adjust that one). I tried REINDEX, it didn't
change anything.

### My Issue No. 2: relpages and VACUUM
I have another table "test" which is - as starting point - created by
INSERTs and then UPDATE'd. It has the same columns and roughly the same
number of rows as table test_orig, but consumes 160 times the number of
pages. I tried VACUUM on this table but it did not change anything on
its relpages count. Maybe this is just because VACUUM without FULL does
not re-claim disk space, i.e. relpages stays as it is? I did observe
that after VACUUM, a REINDEX on this table did considerably shrink down
the size of its indexes (test_test_id, test_lt_id).

### My Issue No 3: VACCUM FULL out of memory
I tried to do a VACCUM FULL on the two tables (test, result_orig)
mentioned above. In both cases it fails with a very low number on out of
memory like this:

ERROR: out of memory
DETAIL: Failed on request of size 224.

I use these kernel settings:
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2147483648
vm.overcommit_memory = 2

And these postgresql.conf settings:
shared_buffers = 512MB # min 128kB or
max_connections*16kB
temp_buffers = 128MB # min 800kB
max_prepared_transactions = 1024 # can be 0 or more
work_mem = 16MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
max_stack_depth = 8MB # min 100kB
max_fsm_pages = 70000000 # min max_fsm_relations*16, 6
bytes each
max_fsm_relations = 4194304 # min 100, ~70 bytes each
#max_files_per_process = 1000 # min 25
#shared_preload_libraries = '' # (change requires restart)

What's going wrong here? I know, one should not use VACUUM FULL, but I
was curious to see if this would have any impact on relpages count
mentioned in Issue 2.

###My Issue No. 4: Autovacuum
I have the feeling that Autovacuum is not really running, else why are
tables and indexes growing that much, especially "test" table?

#-----------------------------------------------------------------------
-------
# AUTOVACUUM PARAMETERS
#-----------------------------------------------------------------------
-------

autovacuum = on # Enable autovacuum subprocess?
'on'
log_autovacuum_min_duration = 1000 # -1 disables, 0 logs all
actions and
autovacuum_max_workers = 3 # max number of autovacuum
subprocesses
autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 50 # min number of row updates
before
autovacuum_analyze_threshold = 50 # min number of row updates
before
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
vacuum
autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for
autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for

How would I check it is running correctly? I don't see any error
messages in syslog from autovacuum.

Any help, also on tuning postgresql.conf to this application, is greatly
appreciated!

Thanks

Andy