Re: Naming-scheme for db-files - Mailing list pgsql-general

After dropping the mentioned column emailindex of type txtidx (via
copying the data to a temporary table and renaming this table
afterwards), disk usage of that table evaporated from 1862MB to a humble
61MB. And emailindex was completely empty... Very strange, but ~60MB is
very much more more like what I had estimated the size of that table to
be.

    -----Ursprüngliche Nachricht----- 
    Von: Markus Wollny 
    Gesendet: Mi 28.08.2002 17:04 
    An: Martijn van Oosterhout 
    Cc: pgsql-general@postgresql.org 
    Betreff: Re: [GENERAL] Naming-scheme for db-files
    
    

    Hi!
    
    Yes, I run vacuum every night - and log-output indicates no
errors at
    all. Yet I am indeed quite puzzled about the size of this table.
Is
    there some way of finding out which column consumes so much
space?
    
    select count(*) from ct_com_user return 95858 rows.
    The CREATE-statement for this table is as follows:
    
    CREATE TABLE ct_com_user (
      user_id numeric(10, 0),
      login varchar(1000),
      password varchar(1000),
      status numeric(10, 0),
      rights varchar(20) DEFAULT 'r',
      firstname varchar(1000),
      firstname_visible numeric(1, 0) DEFAULT 0,
      lastname varchar(1000),
      lastname_visible numeric(1, 0) DEFAULT 0,
      clan varchar(1000),
      clan_visible numeric(1, 0) DEFAULT 0,
      street varchar(1000),
      street_visible numeric(1, 0) DEFAULT 0,
      zipcode varchar(1000),
      zipcode_visible numeric(1, 0) DEFAULT 0,
      city varchar(1000),
      city_visible numeric(1, 0) DEFAULT 0,
      country varchar(1000),
      country_visible numeric(1, 0) DEFAULT 0,
      phone1 varchar(1000),
      phone1_visible numeric(1, 0) DEFAULT 0,
      phone2 varchar(1000),
      phone2_visible numeric(1, 0) DEFAULT 0,
      mobile varchar(1000),
      mobile_visible numeric(1, 0) DEFAULT 0,
      fax varchar(1000),
      fax_visible numeric(1, 0) DEFAULT 0,
      email1 varchar(1000),
      email1_visible numeric(1, 0) DEFAULT 0,
      email2 varchar(1000),
      email2_visible numeric(1, 0) DEFAULT 0,
      icq varchar(1000),
      icq_visible numeric(1, 0) DEFAULT 0,
      homepage varchar(1000),
      homepage_visible numeric(1, 0) DEFAULT 0,
      description varchar(1000),
      description_visible numeric(1, 0) DEFAULT 0,
      hobbies varchar(1000),
      hobbies_visible numeric(1, 0) DEFAULT 0,
      signature1 varchar(4000),
      signature2 varchar(4000),
      signature3 varchar(4000),
      forum_view varchar(20) DEFAULT 'flat',
      temp_password varchar(100),
      registered timestamptz,
      last_login timestamptz,
      created timestamptz DEFAULT ('now'::text)::timestamp(6) with
time
    zone,
      lines numeric(3, 0) DEFAULT 400,
      lines_forum numeric(3, 0) DEFAULT 20,
      forum_lines numeric(3, 0) DEFAULT 20,
      forum_smileys varchar(50) DEFAULT 'enhanced',
      site_id numeric(10, 0) DEFAULT 0,
      flag_id numeric(10, 0) DEFAULT 0,
      forum_quoting varchar(50) DEFAULT 'enhanced',
      forum_flatpostings numeric(3, 0) DEFAULT 10,
      forum_images numeric(1, 0) DEFAULT 2,
      user_image numeric(1, 0) DEFAULT 0,
      user_image_visible numeric(1, 0) DEFAULT 0,
      chat_password varchar(50),
      chat_last_visit timestamptz,
      authorpoints_visible numeric(1, 0) DEFAULT 0,
      emailindex txtidx,
      CONSTRAINT idx_pk_ct_com_user UNIQUE (user_id),
      CONSTRAINT idx_u_ct_com_user_login UNIQUE (login)
    ) WITH OIDS;
    
    As hardly anyone actually uses a signature, hobby or
description, I
    cannot imagine how this should amount to almost 2 GB of data
(1862MB) -
    this would mean roughly 20k of data per row average - and no way
have we
    got this amount of data - the absolute maximum data per row as
defined
    would be about 30k, I guess, but that would mean that nearly all
of our
    users would use nearly all available space and I know that this
is quite
    far from true. I suspect it may have something to do with
emailindex of
    the txtidx-type... As it's not absolutely necessary, I'll try
and drop
    this column and see what I get...
    
    Regards,
    
            Markus
    
    > -----Ursprüngliche Nachricht-----
    > Von: Martijn van Oosterhout [mailto:kleptog@svana.org]
    > Gesendet: Mittwoch, 28. August 2002 16:30
    > An: Markus Wollny
    > Cc: pgsql-general@postgresql.org
    > Betreff: Re: [GENERAL] Naming-scheme for db-files
    >
    >
    > On Wed, Aug 28, 2002 at 03:54:45PM +0200, Markus Wollny wrote:
    > > Hi!
    > >
    > > As I was just checking disk-usage of a database (PostgreSQL
    > 7.2.1), I
    > > stumbled over some files named with a trailing .1 added to
the usual
    > > oid. Now if a table 'example' with oid 12345 exists, what
    > does the file
    > > 12345.1 contain exactly? I didn't find anything about
    > .1-files in the
    > > documentation...
    >
    > Postgres splits files at 1GB. The .1 file would be the second
    > part of the
    > file. When that also reaches 1GB, you'll get a .2 file.
    >
    > You're doing VACUUM [FULL] regularly, right?
    >
    > --
    > Martijn van Oosterhout   <kleptog@svana.org>  
    > http://svana.org/kleptog/
    > > There are 10 kinds of people in the world, those that can do
binary
    > > arithmetic and those that can't.
    >
    
    ---------------------------(end of
broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister
command
        (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)