small table occupies many relpages - Mailing list pgsql-admin

Hi,

we log access log data to a PostgreSQL 7.3.4 database. There is this
small table virtual_domains

access_logs2=# \d virtual_domains
                                        Table "public.virtual_domains"
      Column       |  Type   |                                   Modifiers
-------------------+---------+--------------------------------------------------------------------------------
 virtual_domain_id | integer | not null default nextval('public.virtual_domains_virtual_domain_id_seq'::text)
 virtual_domain    | text    |
Indexes: virtual_domains_pkey primary key btree (virtual_domain_id),
         virtual_domains_virtual_domain_key unique btree (virtual_domain)


It has a unique index on virtual_domain. Every day we do <10000 queries
of the form

insert into virtual_domains values (default, 'www.something.de')

to make sure www.something.de has an entry in virtual_domains. The table
virtual_domains does in fact contain only 4 records. The whole database
is vacuumed through pg_autovacuum (with its default parameters). We're
observing a steady growth of the relpages virtual_domains occupies:

access_logs2=# analyze virtual_domains ;
ANALYZE
access_logs2=# select relname, relpages from pg_class where relname like 'virtual_domains%';
                relname                | relpages
---------------------------------------+----------
 virtual_domains                       |      207
 virtual_domains_pkey                  |      257
 virtual_domains_virtual_domain_id_seq |        1
 virtual_domains_virtual_domain_key    |        2
(4 rows)

207 is pretty much for a table with 4 records :). We can fix that by running vacuum full:

access_logs2=# vacuum full verbose  analyze virtual_domains;
INFO:  --Relation public.virtual_domains--
INFO:  Pages 207: Changed 2, reaped 207, Empty 0, New 0; Tup 4: Vac 32191, Keep/VTL 0/0, UnUsed 1, MinLen 48, MaxLen
55;Re-using: Free/Avail. Space 1562616/7340; EndEmpty/Avail. Pages 206/1. 
        CPU 0.00s/0.01u sec elapsed 0.00 sec.
INFO:  Index virtual_domains_pkey: Pages 354; Tuples 4: Deleted 32191.
        CPU 0.02s/0.16u sec elapsed 0.93 sec.
INFO:  Index virtual_domains_virtual_domain_key: Pages 2; Tuples 4: Deleted 1.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Rel virtual_domains: Pages: 207 --> 1; Tuple(s) moved: 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_6130614--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0;
Re-using:Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. 
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_6130614_index: Pages 1; Tuples 0.
        CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  Analyzing public.virtual_domains
VACUUM
access_logs2=# select relname, relpages from pg_class where relname like 'virtual_domains%';
                relname                | relpages
---------------------------------------+----------
 virtual_domains                       |        1
 virtual_domains_pkey                  |      354
 virtual_domains_virtual_domain_id_seq |        1
 virtual_domains_virtual_domain_key    |        2
(4 rows)

The fsm settings are as follows:

max_fsm_relations = 2000        # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 2000000         # min 1000, fsm is free space map, ~6 bytes

The big table size slows down sequential scans significantly. What can
we do to prevent growth of the table? Are the fsm settings appropriate?
Why does the index on the primary key grow?

Thanks

--
Markus Bertheau <twanger@bluetwanger.de>