Heavy write activity on first vacuum of fresh TOAST data - Mailing list pgsql-performance

Yesterday we moved a 300 GB table containing document images (mostly raster-scanned from paper), into a 215 GB
PostgreSQL8.2.5 database which contains the related case management data.  (This separation was never "right", since
therewere links from one to the other, but was necessary under our previous database package for practical reasons.) 

The data was inserted through a Java program using a prepared statement with no indexes on the table.  The primary key
wasthen added, and now I've started a vacuum.  The new table wound up being the first big table vacuumed, and I noticed
somethingodd.  Even though there have been no rollbacks, updates, or deletes on this table, the vacuum is writing as
muchas it is reading while dealing with the TOAST data. 

Here's the current tail of the VACUUM ANALYZE VERBOSE output:

INFO:  analyzing "pg_catalog.pg_auth_members"
INFO:  "pg_auth_members": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated
totalrows 
INFO:  vacuuming "public.DocImage"
INFO:  index "DocImage_pkey" now contains 2744753 row versions in 10571 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.15s/0.01u sec elapsed 0.28 sec.
INFO:  "DocImage": found 0 removable, 2744753 nonremovable row versions in 22901 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
32 pages contain useful free space.
0 pages are entirely empty.
CPU 0.46s/0.10u sec elapsed 1.12 sec.
INFO:  vacuuming "pg_toast.pg_toast_7729979"

And here's a snippet from vmstat 1 output:

procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  1    156 316500    556 63491808    0    0 58544 57910 1459 2417  2  4 85  9  0
 0  1    156 317884    556 63490780    0    0 61240 62087 1425 2106  2  5 84  9  0
 0  3    156 307500    556 63499004    0    0 56968 57882 1472 2091  2  5 84 10  0
 2  0    156 309280    556 63497976    0    0 59920 58218 1600 4503  5  4 79 11  0
 0  1    156 313592    556 63494892    0    0 57608 62371 1695 3425  3  5 84  8  0
 2  1    156 305844    556 63502088    0    0 54568 58164 1644 2962  3  4 84  9  0
 0  1    156 306560    556 63502088    0    0 61080 57949 1494 2808  3  5 83  9  0
 1  0    156 303432    552 63505176    0    0 49784 53972 1481 2629  2  4 84 10  0
 0  1    156 308232    552 63500036    0    0 57496 57903 1426 1954  1  4 85  9  0
 1  0    156 309008    552 63499008    0    0 62000 61962 1442 2401  2  4 85  8  0

It's been like this for over half an hour.  Not that I expect a vacuum of a 300 GB table to be blindingly fast, but if
thedata has just been inserted, why all those writes? 

-Kevin

 PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (prerelease) (SUSE Linux)

ccsa@SOCRATES:/var/pgsql/data/cc> free -m
             total       used       free     shared    buffers     cached
Mem:         64446      64147        298          0          0      62018
-/+ buffers/cache:       2128      62318
Swap:         1027          0       1027

listen_addresses = '*'
port = 5412
max_connections = 200
shared_buffers = 160MB
temp_buffers = 50MB
work_mem = 32MB
maintenance_work_mem = 1GB
max_fsm_pages = 800000
bgwriter_lru_percent = 20.0
bgwriter_lru_maxpages = 200
bgwriter_all_percent = 10.0
bgwriter_all_maxpages = 600
wal_buffers = 1MB
checkpoint_segments = 50
checkpoint_timeout = 30min
seq_page_cost = 0.5
random_page_cost = 0.8
effective_cache_size = 63GB
geqo = off
from_collapse_limit = 15
join_collapse_limit = 15
redirect_stderr = on
log_line_prefix = '[%m] %p %q<%u %d %r> '
stats_block_level = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 10s
autovacuum_vacuum_threshold = 1
autovacuum_analyze_threshold = 1
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
escape_string_warning = off
standard_conforming_strings = on
sql_inheritance = off

BINDIR = /usr/local/pgsql-8.2.5/bin
DOCDIR = /usr/local/pgsql-8.2.5/doc
INCLUDEDIR = /usr/local/pgsql-8.2.5/include
PKGINCLUDEDIR = /usr/local/pgsql-8.2.5/include
INCLUDEDIR-SERVER = /usr/local/pgsql-8.2.5/include/server
LIBDIR = /usr/local/pgsql-8.2.5/lib
PKGLIBDIR = /usr/local/pgsql-8.2.5/lib
LOCALEDIR =
MANDIR = /usr/local/pgsql-8.2.5/man
SHAREDIR = /usr/local/pgsql-8.2.5/share
SYSCONFDIR = /usr/local/pgsql-8.2.5/etc
PGXS = /usr/local/pgsql-8.2.5/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/pgsql-8.2.5' '--enable-integer-datetimes' '--enable-debug' '--disable-nls'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels
-fno-strict-aliasing-g 
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/usr/local/pgsql-8.2.5/lib'
LDFLAGS_SL =
LIBS = -lpgport -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.2.5

       Table "public.DocImage"
  Column   |     Type     | Modifiers
-----------+--------------+-----------
 countyNo  | "CountyNoT"  | not null
 docId     | "DocIdT"     | not null
 sectionNo | "SectionNoT" | not null
 docImage  | "ImageT"     | not null
Indexes:
    "DocImage_pkey" PRIMARY KEY, btree ("countyNo", "docId", "sectionNo")

 Schema |  Name      |  Type    | Modifier | Check
--------+------------+----------+----------+-------
 public | CountyNoT  | smallint |          |
 public | DocIdT     | integer  |          |
 public | SectionNoT | integer  |          |
 public | ImageT     | bytea    |          |