Thread: Postgres advice for Java/Hibernate project

Hello,

We're building a distributed Java/Hibernate/Postgres desktop
application for a small office setting, so we largely remain safely in
our "Java cocoon" without needing to venture into the SQL or Postgres
details too often!  We are looking for a few tiny pointers regarding a
few design decisions. Our project is certainly NOT highly optimised,
and does not take Postgress to its limits.  The natural speed,
stability, and heritage make Postgres an easy choice for us -
especially since our competitors typically use MS-Access etc.

Question ONE: If we design a field (say) 50 characters long - and we
have an instance/row using only (say) 20 characters - does Postgres
"use" the whole 50, or only the 20??

The issue here is a trade-off in how tightly we need to specify field
lengths that we are currently unsure of.  Are we wasting space if we
make them large?

Question TWO: When following typical Hibernate examples we notice that
String fields are typically specified with a length at a "binary
boundary".  So they seem to always be specified at 16, 32, 64, 128
etc. Really the question should be "is a String length 17 (or 33 or
65) significantly slower to insert/search/retrieve than a String of
length 16 (or 32 or 64)?".

I cannot imaging any significant performance reason for this in an
"un-optimised" situation like ours. We are not really interested in
marginal or theoretical improvements, just good solid "sensible
postgres practice" improvements.

Any suggestions are appreciated.

Thanks,
-Damian

> Question ONE: If we design a field (say) 50 characters long - and we
> have an instance/row using only (say) 20 characters - does Postgres
> "use" the whole 50, or only the 20??

http://www.postgresql.org/docs/8.1/interactive/datatype-character.html
It looks like either is possible depending upon the datatype that you choose for your field.


> The issue here is a trade-off in how tightly we need to specify field
> lengths that we are currently unsure of.  Are we wasting space if we
> make them large?

it looks like char will use all of the field length by "padding" your value with additional spaces
to complete the field length.


> Question TWO: When following typical Hibernate examples we notice that
> String fields are typically specified with a length at a "binary
> boundary".  So they seem to always be specified at 16, 32, 64, 128
> etc. Really the question should be "is a String length 17 (or 33 or
> 65) significantly slower to insert/search/retrieve than a String of
> length 16 (or 32 or 64)?".

I am not to sure here.  If no one else respondes, you can always develop a test case around the
type and quanity of data you are using to see if makes a difference.

Regards,

Richard Broersma Jr.

Assuming Hibernate creates varchar columns (rather than fixed length,
space padded char columns) there is no difference in storage or
performance. This page explains all of the details:

http://www.postgresql.org/docs/8.1/interactive/datatype-character.html


On Jun 18, 2006, at 9:30 PM, Damian C wrote:

> Question ONE: If we design a field (say) 50 characters long - and we
> have an instance/row using only (say) 20 characters - does Postgres
> "use" the whole 50, or only the 20??
>
> The issue here is a trade-off in how tightly we need to specify field
> lengths that we are currently unsure of.  Are we wasting space if we
> make them large?
>
> Question TWO: When following typical Hibernate examples we notice that
> String fields are typically specified with a length at a "binary
> boundary".  So they seem to always be specified at 16, 32, 64, 128
> etc. Really the question should be "is a String length 17 (or 33 or
> 65) significantly slower to insert/search/retrieve than a String of
> length 16 (or 32 or 64)?".



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

"Damian C" <jamianb@gmail.com> writes:
> Question ONE: If we design a field (say) 50 characters long - and we
> have an instance/row using only (say) 20 characters - does Postgres
> "use" the whole 50, or only the 20??

Do you have a concrete reason to put a limit on the field width at all?
If not, don't.  Use type text, or varchar without any particular length
limit.  In any case, avoid type char(N), which is a historical hangover
that no longer has an excuse to live ...

> Question TWO: When following typical Hibernate examples we notice that
> String fields are typically specified with a length at a "binary
> boundary".  So they seem to always be specified at 16, 32, 64, 128
> etc. Really the question should be "is a String length 17 (or 33 or
> 65) significantly slower to insert/search/retrieve than a String of
> length 16 (or 32 or 64)?".

Perhaps there's some database somewhere that cares, but Postgres
certainly doesn't.  I rather doubt there's a reason for it on the Java
side either.

            regards, tom lane

Many thanks to Richard, John and Tom for your replies.  Our way
forward on these issues is now clear.

(1) Hibernate converts our Java Strings to SQL/Postgres "varchar"
defaulting to length 256 if we don't specify anything (currently we
always do specify).  We now have a basis to chose a way forward.

(2) The "binary boundry length" always seemed to be a "furfie" - and
that is reinforced by your comments.

On a separate issue I would like to point out that we have spent
nearly 18 months developing this product and we have prototypes
operating successfully in industry, and this has been our first post
to the Postgres forum!!!  I think it is a credit to the Postgres
product and community that it can be deployed so successfully with so
little specialist knowledge.  No doubt there are many improvements
that we could make, but to date it has been a pleasure for us members
of "the great unwashed" to work with.

Many thanks,
-Damian