By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

fixed-length row

P: n/a
The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). A fixed-length row is a
row which is comprised of only fixed-length fields. A fixed-length field
takes a fixed amount of bytes for storage (e.g. INT = 4 bytes, CHAR(M) =
M bytes, etc).

Is there a similar recommendation in PostgreSQL? I notice that most data
types are stored in variable-length mode anyway (is cidr and inet data
types fixed-length?)

Is there a command/query in psql which can show storage requirement for
each field? For example:

db1=# \d t1;
Table "public.t1"
Column | Type | Modifiers | Storage size
--------+-------------+-----------+--------------
id | inet | not null | 24
i | integer | | 4
c | varchar(10) | | variable
Indexes:
"t1_pkey" primary key, btree (id)

--
dave
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Thursday 15 January 2004 14:17, David Garamond wrote:
The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). A fixed-length row is a
row which is comprised of only fixed-length fields. A fixed-length field
takes a fixed amount of bytes for storage (e.g. INT = 4 bytes, CHAR(M) =
M bytes, etc).

Is there a similar recommendation in PostgreSQL? I notice that most data
types are stored in variable-length mode anyway (is cidr and inet data
types fixed-length?)
Not really - there have been various discussions about timing differences
between char() and varchar() and I don't recall one being noticably faster
than the others.
Is there a command/query in psql which can show storage requirement for
each field? For example:


No, but there's stuff in the archives, and I think something on techdocs too.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #2

P: n/a
David Garamond <li***@zara.6.isreserved.com> writes:
The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). Is there a similar recommendation in PostgreSQL?


No. There are some marginal optimizations that take place if your
columns are fixed-width and not null, but I wouldn't suggest contorting
your database design to enable them to occur.

In particular, people who have taken this bait generally think that
it's a good idea to substitute char(n) for varchar(n). That is almost
inevitably a pessimization, because the extra I/O time for all those
padding blanks will surely swamp the few CPU cycles saved by using
precalculated field offsets. (Not to mention that char(n) is not really
fixed-width anyway, in Postgres or any other implementation that
supports variable-length character encodings.)

I'd be willing to speculate that the MySQL manual's advice is bad
even for MySQL, but I haven't benchmarked the case there.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #3

P: n/a
On Thu, Jan 15, 2004 at 09:17:55PM +0700, David Garamond wrote:
The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). A fixed-length row is a
row which is comprised of only fixed-length fields. A fixed-length field
takes a fixed amount of bytes for storage (e.g. INT = 4 bytes, CHAR(M) =
M bytes, etc).
Just remember that there are no fixed length string types. Your assumption
that char(M) = M bytes is wrong. M characters can take essentially any
length in bytes depending on the encoding. Storing strings as CHAR a field
takes the same amount of space as in a VARCHAR or TEXT field. The only
difference is in the contraint checking on insert and the handling of
trailing spaces.

That said, somewhere in the pg_attriute table is a column that says the size
in bytes or variable.
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFABwLWY5Twig3Ge+YRAhsAAKDfEqRLRs3PcBynSWCCqQ RCr4AcwQCfZ7ac
P5hZEXDAFWkizSCzzrhKuvs=
=OgyT
-----END PGP SIGNATURE-----

Nov 22 '05 #4

P: n/a
Martijn van Oosterhout wrote:
That said, somewhere in the pg_attriute table is a column that says the size
in bytes or variable.


Perfect, thanks. It shows that cidr and inet are indeed variable-length.

--
dave
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #5

P: n/a
Richard Huxton wrote:
On Thursday 15 January 2004 14:17, David Garamond wrote:
The MySQL manual recommends that we create a "fixed-length row" if
possible, for speed (especially scanning speed). A fixed-length row is a
row which is comprised of only fixed-length fields. A fixed-length field
takes a fixed amount of bytes for storage (e.g. INT = 4 bytes, CHAR(M) =
M bytes, etc).

Is there a similar recommendation in PostgreSQL? I notice that most data
types are stored in variable-length mode anyway (is cidr and inet data
types fixed-length?)


Not really - there have been various discussions about timing differences
between char() and varchar() and I don't recall one being noticably faster
than the others.
Is there a command/query in psql which can show storage requirement for
each field? For example:


No, but there's stuff in the archives, and I think something on techdocs too.


FAQ item 4.14 covers this, and reports CHAR() and VARCHAR() have the
same performance characteristics.

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.