Re: Naming-scheme for db-files

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(at)postgresql(dot)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(at)svana(dot)org]
> Gesendet: Mittwoch, 28. August 2002 16:30
> An: Markus Wollny
> Cc: pgsql-general(at)postgresql(dot)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(at)svana(dot)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(at)postgresql(dot)org)