X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9FCABD6FDE for ; Thu, 25 Aug 2005 12:31:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13656-05 for ; Thu, 25 Aug 2005 15:31:23 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 0C80CD6EB5 for ; Thu, 25 Aug 2005 12:31:23 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j7PFVKMM014835 for ; Thu, 25 Aug 2005 15:31:20 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j7PF43XE007216 for pgsql-admin@postgresql.org; Thu, 25 Aug 2005 15:04:03 GMT (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: size of indexes and tables (more than 1GB) Date: Thu, 25 Aug 2005 10:54:11 -0400 Organization: cbbrowne Computing Inc Lines: 37 Message-ID: <60r7cink30.fsf@dba2.int.libertyrms.com> References: <69f2062a05082505523a9e485a@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux) Cancel-Lock: sha1:V2LMnpsPuvuyhFMM1oKJSc6OP+0= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=-2.458 required=5 tests=[ALL_TRUSTED=-2.82, AWL=0.362] X-Spam-Level: X-Archive-Number: 200508/316 X-Sequence-Number: 18635 jafn82@gmail.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