Re: Naming-scheme for db-files

On Wed, Aug 28, 2002 at 11:25:44PM +0200, Markus Wollny wrote:
> 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.

What on earth is:

emailindex txtidx,

Anyway?

>     -----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)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
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.