Skip site navigation (1) Skip section navigation (2)

Re: Optimization of this SQL sentence

From: mark(at)mark(dot)mielke(dot)cc
To: Mario Weilguni <mweilguni(at)sime(dot)com>
Cc: Alexander Staubo <alex(at)purefiction(dot)net>,pgsql-performance(at)postgresql(dot)org, Ruben Rubio <ruben(at)rentalia(dot)com>
Subject: Re: Optimization of this SQL sentence
Date: 2006-10-18 12:50:09
Message-ID: 20061018125009.GA10759@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Oct 18, 2006 at 11:31:44AM +0200, Mario Weilguni wrote:
> It's not a bad idea. Usually I use postal codes with 25 chars, and never had 
> any problem. With text, the limit would be ~1 GB. No matter how much testing 
> in the application happens, the varchar(25) as last resort is a good idea.

> And in most cases, the application itself limits the length, and thus it's 
> good to reflect this in the database design.

> Feel free to use text anywhere for your application, and feel free to use 
> numeric(1000) instead of numeric(4) if you want to be prepared for really 
> long numbers, but don't tell other people it's bad database design - it 
> isn't.

It's unnecessary design.

Suggestions in this regard lead towards the user seeing a database error,
instead of a nice specific message provided by the application.

I used to use varchar instead of text, but have since softened, as the
number of times it has ever actually saved me is zero, and the number of
times it has screwed me up (picking too small of a limit too early) has
been a few.

It's kind of like pre-optimization before there is a problem. Sometimes
it works for you, sometimes it works against.

Cheers,
mark

-- 
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/


In response to

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2006-10-18 12:51:47
Subject: Re: Jdbc/postgres performance
Previous:From: Arnaud LesauvageDate: 2006-10-18 12:12:29
Subject: Re: Index on two columns not used

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group