Обсуждение: 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