473,320 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

fixed-length row

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
5 3070
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jaz | last post by:
Trying to use a fixed layer for a couple of NAV buttons. I found this code, but the IE part is commented, and I don't understand the IF statement. It works great on Moz/Firebird and Opera BUT...
9
by: Paul Trautwein | last post by:
I'm trying to get an image to float in a window despite scrolling. I've gotten it to work on my Mac using IE 5.2, Netscape, and Safari, but it goes wonky when I test it on a PC. (testing with IE...
6
by: Mason A. Clark | last post by:
Masters: On two or three-column layouts, one column often has a list of links. Scrolling the page hides them. I'm aware there's supposed to be the ability to fix the column (frame-like). I...
2
by: hq105862 | last post by:
Hi, Is it possible to simulate frames behaviour with CSS? Currently, I've used CSS to create the visual look of my old framed site. For this I've used styles that place the images at absolute...
2
by: Jonathan Carmichael | last post by:
I'm trying to create a fixed, no-repeat, centered background image using an external css. Everything works great until I add background-attachment: fixed then it just doesn't show the image...
9
by: pout | last post by:
What are the purposes of fixed-point? When should it be used? I read: #define Int2Fixed(x) (((long)(short)x) << 16) and the fixed-point in 16.16 format. Does the 16 in the MACRO refer to...
4
by: Otie | last post by:
Hello, I am using the MSFlexGrd Control in VB5. I have 1 fixed row and one fixed column. I am trying to do a sort when the user clicks a column in the FIXED ROW. But when I capture the row...
1
by: O.B. | last post by:
In the example below, I'm trying to convert a fixed byte array to a string. I get an error about needing to use "fixed" but I have no clue where to apply it. Help? using System; using...
4
by: Jeff | last post by:
Hey I'm wondering how the Fixed-Width Text Format is What I know is that the top line in this text format will contain column names. and each row beneath the top line represent for example a...
15
by: ingejg | last post by:
I am starting to study internet synchronization, and my head is still spinning since internet is not my forte, however my boss is breathing down my neck at the moment. Our company has only one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.