Thread: determining maxsize for character varying
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
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°
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 >
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
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