pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddetermining maxsize for character varying
5+ messages / 4 participants
[nested] [flat]
* determining maxsize for character varying
@ 2007-06-16 09:53 okparanoid@free.fr
0 siblings, 2 replies; 5+ messages in thread
From: okparanoid@free.fr @ 2007-06-16 09:53 UTC (permalink / raw)
To: pgsql-performance
Hello i would like to know if not determining a max size value for a character
varying's fields decrease the perfomance (perhaps size of stockage ? or
something else ?)
If not it is a good way to not specify a max size value ?
If it has an importance is it possible to have a general environnment variable
saying to postgres to automatically truncate fields which postgres have to
insert or update with a length superior at the max length.
Sorry for my bad english...
Lot of thanks
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: determining maxsize for character varying
@ 2007-06-16 10:55 Andreas Kretschmer <akretschmer@spamfence.net>
parent: okparanoid@free.fr
1 sibling, 1 reply; 5+ messages in thread
From: Andreas Kretschmer @ 2007-06-16 10:55 UTC (permalink / raw)
To: pgsql-performance
okparanoid@free.fr <okparanoid@free.fr> schrieb:
> Hello i would like to know if not determining a max size value for a character
> varying's fields decrease the perfomance (perhaps size of stockage ? or
> something else ?)
No problem because of the TOAST-technology:
http://www.postgresql.org/docs/current/static/storage-toast.html
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: determining maxsize for character varying
@ 2007-06-16 11:35 okparanoid@free.fr
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 5+ messages in thread
From: okparanoid@free.fr @ 2007-06-16 11:35 UTC (permalink / raw)
To: pgsql-performance
Thanks
if i understand well that means that if i choose character varying(3) or
character varying(8) or character varying(32) or character varying with no max
length the fields will take the same place in the disk (8kb) except for fields
too long to take place in the 8kb whose are stored in another place ?
Is that correct ?
So for small strings it's better to choose character(n) when it's possible ?
Best regards,
Loic
Selon Andreas Kretschmer <akretschmer@spamfence.net>:
> okparanoid@free.fr <okparanoid@free.fr> schrieb:
>
> > Hello i would like to know if not determining a max size value for a
> character
> > varying's fields decrease the perfomance (perhaps size of stockage ? or
> > something else ?)
>
> No problem because of the TOAST-technology:
> http://www.postgresql.org/docs/current/static/storage-toast.html
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknow)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: determining maxsize for character varying
@ 2007-06-16 12:30 Ragnar <gnari@hive.is>
parent: okparanoid@free.fr
0 siblings, 0 replies; 5+ messages in thread
From: Ragnar @ 2007-06-16 12:30 UTC (permalink / raw)
To: okparanoid@free.fr; +Cc: pgsql-performance
On lau, 2007-06-16 at 13:35 +0200, okparanoid@free.fr wrote:
> Thanks
>
> if i understand well that means that if i choose character varying(3) or
> character varying(8) or character varying(32) or character varying with no max
> length the fields will take the same place in the disk (8kb) except for fields
> too long to take place in the 8kb whose are stored in another place ?
>
> Is that correct ?
not at all
a varchar will occupy the bytelength of your actual string,
+ a small fixed overhead+padding, except when the total rowsize causes
TOASTing
in single-byte encodings, the string 'okparanoid' will occupy
the same amount of diskspace in a varchar, varchar(10) or a
varchar(1000) column, namely around 16 bytes.
hope this helps
gnari
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: determining maxsize for character varying
@ 2007-06-16 15:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: okparanoid@free.fr
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-06-16 15:07 UTC (permalink / raw)
To: okparanoid@free.fr; +Cc: pgsql-performance
okparanoid@free.fr writes:
> Hello i would like to know if not determining a max size value for a
> character varying's fields decrease the perfomance (perhaps size of
> stockage ?
No, more the other way around: specifying varchar(N) when you had to
pick N out of the air decreases performance, because of all the
essentially useless checks of the string length that Postgres has to
make. If you cannot defend a specific limit N as being required by your
application, then just make it unconstrained varchar (or better text).
Do *not* use char(N) for data with highly variable width; that one
definitely will cost you performance and disk space.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-06-16 15:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-16 09:53 determining maxsize for character varying okparanoid@free.fr
2007-06-16 10:55 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-06-16 11:35 ` okparanoid@free.fr
2007-06-16 12:30 ` Ragnar <gnari@hive.is>
2007-06-16 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox