Re: Naming-scheme for db-files - Mailing list pgsql-general
From | Martijn van Oosterhout |
---|---|
Subject | Re: Naming-scheme for db-files |
Date | |
Msg-id | 20020829091814.A29874@svana.org Whole thread Raw |
In response to | Re: Naming-scheme for db-files ("Markus Wollny" <Markus.Wollny@computec.de>) |
List | pgsql-general |
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.
pgsql-general by date: