Skip site navigation (1) Skip section navigation (2)

Re: size of indexes and tables (more than 1GB)

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: size of indexes and tables (more than 1GB)
Date: 2005-08-25 14:54:11
Message-ID: 60r7cink30.fsf@dba2.int.libertyrms.com (view raw or flat)
Thread:
Lists: pgsql-admin
jafn82(at)gmail(dot)com (jose fuenmayor) writes:
> I read and have seen that when a table has more than 1GB it is divided
> in several files with the names of inode,inode.1,inode.2,inode.3, etc.
>
> I have a table of 1.3 GB (9.618.118 rows,13 fields) it is divided in
> that way  as i see on /PGDATA/base but each file has the same size i
> mean
> table inode (1.3GB), inode.1(1.3GB),inode.2(1.3GB) so is  this not a
> waste of space?, are those file sizes reusable by postgresql?.
>
> The size of the table is 3 times bigger than,  for instance Visual Fox
> Pro dbf's? since is there fisically three times.

Having "file", "file.1", "file.2", and such is routine; that is the
normal handling of tables that grow beyond 1GB in size.  If there is
actually 3GB of data to store in the table, then there is nothing to
be 'fixed' about this.  There is no duplication of data; each of those
files contains distinct sets of tuples.

First question...

Are you vacuuming the table frequently to reclaim dead space?

If that table is heavily updated (e.g. - via DELETE/UPDATE; mere
INSERTs do NOT represent "updates" in this context), then maybe
there's a lot of dead space, and running VACUUM would cut down on the
size.

If you're running VACUUM often enough, then there's nothing wrong, and
nothing to be done.  You're simply observing how PostgreSQL handles
large tables.
-- 
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/multiplexor.html
"Funny, the  only thing that makes me  go Keanu about Microsoft is the
fact  that they are constantly  behind the times  and yet  claim to be
innovating." -- Steve Lamb <morpheus(at)despair(dot)rpglink(dot)com>

In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2005-08-25 16:08:11
Subject: Re: Question regarding blocking locks
Previous:From: Kevin KeithDate: 2005-08-25 14:28:13
Subject: Question regarding blocking locks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group