pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSpeed & Memory Management
14+ messages / 10 participants
[nested] [flat]
* Speed & Memory Management
@ 2003-03-31 22:08 Kris <kkiger@depauw.edu>
0 siblings, 3 replies; 14+ messages in thread
From: Kris @ 2003-03-31 22:08 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-01 07:02 GB <GB@nirvana.nil>
parent: Kris <kkiger@depauw.edu>
2 siblings, 0 replies; 14+ messages in thread
From: GB @ 2003-04-01 07:02 UTC (permalink / raw)
To: pgsql-admin
How about using varchar(30)?
GB
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-01 18:41 Robert Treat <xzilla@users.sourceforge.net>
parent: Kris <kkiger@depauw.edu>
2 siblings, 1 reply; 14+ messages in thread
From: Robert Treat @ 2003-04-01 18:41 UTC (permalink / raw)
To: Kris <kkiger@depauw.edu>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-01 19:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2003-04-01 19:07 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Kris <kkiger@depauw.edu>; pgsql-admin
Robert Treat <xzilla@users.sourceforge.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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-01 21:42 Peter Eisentraut <peter_e@gmx.net>
parent: Kris <kkiger@depauw.edu>
2 siblings, 0 replies; 14+ messages in thread
From: Peter Eisentraut @ 2003-04-01 21:42 UTC (permalink / raw)
To: Kris <kkiger@depauw.edu>; +Cc: pgsql-admin
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@gmx.net
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-02 18:00 Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
0 siblings, 1 reply; 14+ messages in thread
From: Murthy Kambhampaty @ 2003-04-02 18:00 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-admin
This, and the message from Tom Lane, seem inconsistent with the
documentation re "Character Types"
(http://developer.postgresql.org/docs/postgres/datatype-character.html)
which says:
"Tip: There are no performance differences between these three types, apart
from the increased storage size when using the blank-padded type."
-----Original Message-----
From: Peter Eisentraut [mailto:peter_e@gmx.net]
Sent: Tuesday, April 01, 2003 16:42
To: Kris
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Speed & Memory Management
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@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-02 21:13 Juan Miguel <juanmime@ono.com>
0 siblings, 2 replies; 14+ messages in thread
From: Juan Miguel @ 2003-04-02 21:13 UTC (permalink / raw)
To: pgsql-admin
> 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.
First, sorry about my english.
Well ... but ... reading database theory books, you can see that fixed size
records are "better" than variant size records. When the records are size
fixed, inserts, updates and deletes can recalculate easier the position of
the rest records in the pages. These books tell you that in general cases,
variant record size tables (records that have variant type colums, for
example) imply a lower performance in the system ...
Why in postgresql these things don't matter ?
Thanks.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-02 22:20 Peter Eisentraut <peter_e@gmx.net>
parent: Juan Miguel <juanmime@ono.com>
1 sibling, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2003-04-02 22:20 UTC (permalink / raw)
To: Juan Miguel <juanmime@ono.com>; +Cc: pgsql-admin
Juan Miguel writes:
> Well ... but ... reading database theory books, you can see that fixed size
> records are "better" than variant size records.
Theory and practice are only the same in theory. In practice there is a
difference.
Anyway, char(30) means 30 characters, not 30 bytes. So it's not a
fixed-size record anyway. Other factors are out-of-line storage and
automatic compression of long values.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-02 22:23 Peter Eisentraut <peter_e@gmx.net>
parent: Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
0 siblings, 0 replies; 14+ messages in thread
From: Peter Eisentraut @ 2003-04-02 22:23 UTC (permalink / raw)
To: Murthy Kambhampaty <murthy.kambhampaty@goeci.com>; +Cc: pgsql-admin
Murthy Kambhampaty writes:
> This, and the message from Tom Lane, seem inconsistent with the
> documentation re "Character Types"
> (http://developer.postgresql.org/docs/postgres/datatype-character.html)
> which says:
>
> "Tip: There are no performance differences between these three types, apart
> from the increased storage size when using the blank-padded type."
What are you saying? This is exactly true. Of course the padding might
also take time, not only space, but we don't want to get too picky here.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-02 22:24 Andrew Biagioni <andrew.biagioni@e-greek.net>
parent: Juan Miguel <juanmime@ono.com>
1 sibling, 0 replies; 14+ messages in thread
From: Andrew Biagioni @ 2003-04-02 22:24 UTC (permalink / raw)
To: Juan Miguel <juanmime@ono.com>; +Cc: pgsql-admin
Juan Miguel wrote:
>>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.
>>
>>
>
>First, sorry about my english.
>
>Well ... but ... reading database theory books, you can see that fixed size
>records are "better" than variant size records. When the records are size
>fixed, inserts, updates and deletes can recalculate easier the position of
>the rest records in the pages. These books tell you that in general cases,
>variant record size tables (records that have variant type colums, for
>example) imply a lower performance in the system ...
>
>Why in postgresql these things don't matter ?
>
>
Because unlike other DBs, PostgreSQL doesn't actually remove anything
when you do an update or delete, it marks the old record as "dead" and
(for an update) adds a new instance of the record at the end. That's
one reason why you want to vacuum tables after some activity, to remove
the dead records fro all updates/deletes.
>Thanks.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-03 18:22 Jodi Kanter <jkanter@virginia.edu>
0 siblings, 1 reply; 14+ messages in thread
From: Jodi Kanter @ 2003-04-03 18:22 UTC (permalink / raw)
To: pgsql-admin
In response to the speed and memory management messages posted. Can
someone tell me if the varchar(N) data type also impedes performance by
padding with spaces?
Thank you,
Jodi
--
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu <mailto:jkanter@virginia.edu>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-03 19:26 Kris Kiger <kris@musicrebellion.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 14+ messages in thread
From: Kris Kiger @ 2003-04-03 19:26 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Juan Miguel <juanmime@ono.com>; pgsql-admin
Under what circumstances does a character not take up a set number of
bytes? For example, i was under the impression that utf-8 allocated 6
bytes per character. While the character may not occupy all six bytes,
each byte would still be reserved for that character set.
Kris
>Juan Miguel writes:
>
>
>
>>Well ... but ... reading database theory books, you can see that fixed size
>>records are "better" than variant size records.
>>
>>
>
>Theory and practice are only the same in theory. In practice there is a
>difference.
>
>Anyway, char(30) means 30 characters, not 30 bytes. So it's not a
>fixed-size record anyway. Other factors are out-of-line storage and
>automatic compression of long values.
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-03 19:47 Robert Treat <xzilla@users.sourceforge.net>
parent: Jodi Kanter <jkanter@virginia.edu>
0 siblings, 0 replies; 14+ messages in thread
From: Robert Treat @ 2003-04-03 19:47 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-admin
On Thu, 2003-04-03 at 13:22, Jodi Kanter wrote:
> In response to the speed and memory management messages posted. Can
> someone tell me if the varchar(N) data type also impedes performance by
> padding with spaces?
> Thank you,
> Jodi
>
Well no since varchar doesn't pad spaces. Theoretically this does make
it an improvement over char, although by this same theory it does have
to do length checks on the input strings, which would slow things down
versus text data types *in theory*.
Don't forget also that a varchar(30) will _prevent_ strings longer than
30 chars, not truncate them.
Robert Treat
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Speed & Memory Management
@ 2003-04-04 16:46 Peter Eisentraut <peter_e@gmx.net>
parent: Kris Kiger <kris@musicrebellion.com>
0 siblings, 0 replies; 14+ messages in thread
From: Peter Eisentraut @ 2003-04-04 16:46 UTC (permalink / raw)
To: Kris Kiger <kris@musicrebellion.com>; +Cc: pgsql-admin
Kris Kiger writes:
> Under what circumstances does a character not take up a set number of
> bytes? For example, i was under the impression that utf-8 allocated 6
> bytes per character.
Nope, UTF-8 is variable length. And besides, UTF-8 is not the only
supported encoding.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2003-04-04 16:46 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-31 22:08 Speed & Memory Management Kris <kkiger@depauw.edu>
2003-04-01 07:02 ` GB <GB@nirvana.nil>
2003-04-01 18:41 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-01 19:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-01 21:42 ` Peter Eisentraut <peter_e@gmx.net>
2003-04-02 18:00 Re: Speed & Memory Management Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
2003-04-02 22:23 ` Peter Eisentraut <peter_e@gmx.net>
2003-04-02 21:13 Re: Speed & Memory Management Juan Miguel <juanmime@ono.com>
2003-04-02 22:20 ` Peter Eisentraut <peter_e@gmx.net>
2003-04-03 19:26 ` Kris Kiger <kris@musicrebellion.com>
2003-04-04 16:46 ` Peter Eisentraut <peter_e@gmx.net>
2003-04-02 22:24 ` Andrew Biagioni <andrew.biagioni@e-greek.net>
2003-04-03 18:22 Re: Speed & Memory Management Jodi Kanter <jkanter@virginia.edu>
2003-04-03 19:47 ` Robert Treat <xzilla@users.sourceforge.net>
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