Speed & Memory Management

Hello all. I am designing a database that will could potentially grow to
have millions of tuples associated with it. My thought is that because I
will likely have many updates/inserts/deletes occurring I should set each
column that requires text to a static ammount (ie using char(30) instead of
text). However, this looks to be a double edged sword, since selecting from
the db is my largest concern (with updates a very close second). When I
pull these values back out of the db, it seems I am going to have to trim
each returned value (when I pull a ten character string out of this field,
it returns the 10 char string as well as 20 whitespaces.). I am trying to
assign a weight to each of these scenarios and figure out which is the
lesser of the two evils. If anyone has a suggestion of how I may circumvent
this issue or which possibility may work best, it would be greatly
appreciated. Thanks

Kris

How about using varchar(30)?

GB

Unless your application requires a 30 character limit at the logical
level, use text.

Robert Treat

On Mon, 2003-03-31 at 17:08, Kris wrote:
> Hello all. I am designing a database that will could potentially grow to
> have millions of tuples associated with it. My thought is that because I
> will likely have many updates/inserts/deletes occurring I should set each
> column that requires text to a static ammount (ie using char(30) instead of
> text). However, this looks to be a double edged sword, since selecting from
> the db is my largest concern (with updates a very close second). When I
> pull these values back out of the db, it seems I am going to have to trim
> each returned value (when I pull a ten character string out of this field,
> it returns the 10 char string as well as 20 whitespaces.). I am trying to
> assign a weight to each of these scenarios and figure out which is the
> lesser of the two evils. If anyone has a suggestion of how I may circumvent
> this issue or which possibility may work best, it would be greatly
> appreciated. Thanks
>

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> Unless your application requires a 30 character limit at the logical
> level, use text.

And if it does, use varchar(30). I will bet a very good lunch that
char(30) will be a complete dead loss on *every* measure: speed, disk
space, and convenience.

char(N) is not really fixed-width in Postgres, because N is measured
in characters not bytes (which are not the same thing if you use a
multibyte character encoding). Therefore, there are no optimizations
that could allow it to outperform varchar(N). When you consider the
extra cost of performing the padding step, the extra storage and I/O
incurred for all those pad blanks, and the client-side headaches of
having to trim the unwanted blanks again, it's just guaranteed to be
a loser.

The only case in which I could recommend char(N) is where you have
application semantics that constrain a field to exactly N characters
(postal codes are one common example). If the semantics are "at
most N characters", use varchar(N). If you are picking N out of the
air, don't bother: use text.

regards, tom lane

Kris writes:

> My thought is that because I will likely have many
> updates/inserts/deletes occurring I should set each column that requires
> text to a static ammount (ie using char(30) instead of text).

That would buy you absolutely nothing. char() is not faster than text.
Instead you would make everything slower because the system would
constantly have to pad and trim your values and it would bloat the storage
with the extra spaces.

--
Peter Eisentraut peter_e(at)gmx(dot)net

I added a column to a table of type boolean and then set its default to
false. Next, I ran an update on the table that was very similar to this
one:

update tablename SET bool_field_name = false;

This error is returned:

ERROR: unrecognized replace flag: 88

Has anyone seen this before?

Thanks for the input

Kris

Kris Kiger <kris(at)musicrebellion(dot)com> writes:
> I added a column to a table of type boolean and then set its default to
> false. Next, I ran an update on the table that was very similar to this
> one:

> update tablename SET bool_field_name = false;

> This error is returned:

> ERROR: unrecognized replace flag: 88

This sounds to me like you've uncovered a bug. What PG version is this?
Can you reproduce the error starting from an empty database, and if so how?

regards, tom lane