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 678FAD6EBB for ; Thu, 25 Aug 2005 13:09:24 -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 24288-02 for ; Thu, 25 Aug 2005 16:09:22 +0000 (GMT) Received: from 217-20-115-93.internetserviceteam.com (217-20-115-93.internetserviceteam.com [217.20.115.93]) by svr1.postgresql.org (Postfix) with ESMTP id A48CBD6D7B for ; Thu, 25 Aug 2005 13:09:19 -0300 (ADT) Received: from [192.168.1.121] (191.Red-83-44-161.pooles.rima-tde.net [83.44.161.191]) by 217-20-115-93.internetserviceteam.com (Postfix) with ESMTP id 3241739557; Thu, 25 Aug 2005 17:19:58 +0200 (CEST) Message-ID: <430DED26.1000907@mediaroot.de> Date: Thu, 25 Aug 2005 17:09:10 +0100 From: Aldor User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Chris Browne Cc: pgsql-admin@postgresql.org Subject: Re: size of indexes and tables (more than 1GB) References: <69f2062a05082505523a9e485a@mail.gmail.com> <60r7cink30.fsf@dba2.int.libertyrms.com> In-Reply-To: <60r7cink30.fsf@dba2.int.libertyrms.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 required=5 tests=[none] X-Spam-Level: X-Archive-Number: 200508/318 X-Sequence-Number: 18637 Hi Chris, > 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. Wrong. I have a big table - running VACUUM the first time needs as long as I run it after the VACUUM has finished. There are other problems with VACUUM, fixed in 8.1. In 8.1. you have a server internal AUTOVACUUM - setting this correct might be the solution. My table has about 40GB of data with about 120 million tuples. Correct max_fsm settings, etc... I created test datases with about 10-20 million tuples - and VACUUM runs fast, but not when you do many changes and your tables are mooooore bigger. Chris Browne wrote: > 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.