We purchase data from the state gevernment, change the
data model from flat file to relational, perform
additional analysis and put it all into a PostgreSQL
7.1.3 database running on FreeBSD 4.4.
With one year's worth of data, the database was
approximately 4GB. I expected that adding a second
year would increase the size of the database to 9GB or
10GB. After adding the second year's data, the size
was 15GB. To reduce the size, I dropped 2 tables (one
from each year) that are used only for initial
processing. This decreased the size, after vacuuming,
to 12GB.
I converted the database to MySQL for an industry peer
who purchased the data, but isn't allowed to run Unix.
The resulting database was approximately 5GB.
Looking at the vast difference is size, I thought that
there might be a lot of space wasted in tables that
can't be cleaned out by vacuum. Therefore, I:
1. Used COPY TO to move the largest tables to text
files. (Nulls were recorded as ''.)
2. Deleted all rows from these tables.
3. Vacuumed the tables.
4. Dropped the indexes for these tables.
5. Used COPY FROM to repopulate the tables.
6. Recreated the indexes.
7. Used vacuumdb -d [database name] to vacuum the
database.
The process above resulted in an **increase** in
database size from 12GB to 14GB.
I'm both surprised and stumped. Obviously, there's
much I'm not understanding about COPY, vacuumdb and
database size.
Does anyone have any guidance?
Thanks,
Andrew Gould
__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
