pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHibernate, web application and only one sequence for all primary keys
12+ messages / 7 participants
[nested] [flat]
* Hibernate, web application and only one sequence for all primary keys
@ 2009-09-17 12:47 rawi <only4com@web.de>
2009-09-17 13:07 ` Re: Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:11 ` Re: Hibernate, web application and only one sequence for all primary keys Leo Mannhart <leo.mannhart@beecom.ch>
2009-09-17 13:59 ` Re: Hibernate, web application and only one sequence for all primary keys Tom Lane <tgl@sss.pgh.pa.us>
2009-09-19 02:38 ` Re: Hibernate, web application and only one sequence for all primary keys Lew <noone@lwsc.ehost-services.com>
0 siblings, 4 replies; 12+ messages in thread
From: rawi @ 2009-09-17 12:47 UTC (permalink / raw)
To: pgsql-sql
Hello!
The subject says it...
Grails/Hibernate wishes per default one sequence for all tables-PKs and all
PKs as BigInt.
What would you think about a database with some tens of tables and
incidentally low to moderate insert concurrency spread in about the half of
the tables from at most 10 concurrent users with some 10-20 inserts each?
And (for the eventuality of an unexpected need to scale up in the future -
e.g. integration of multiple databases), from about which size would you
expect performance penalties due to the sole sequence and the BigInt-PKs?
I first intend to deploy it on an Intel Pentium 2 Duo (2.5-2.8GHz) with 3 GB
RAM and SATA hard disk under Ubuntu Server.
Thank you very much in advance!
Regards
Rawi
--
View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp2549...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
@ 2009-09-17 13:07 ` rawi <only4com@web.de>
3 siblings, 0 replies; 12+ messages in thread
From: rawi @ 2009-09-17 13:07 UTC (permalink / raw)
To: pgsql-sql
SORRY for incorrectly posting this here!
I couldn't move it to PostgreSQL - performance... afterwards.
While I don't want to double-post: It would be perfect, if the mail list
admin - please - could correct my mistake...
Regards, Rawi
--
View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp2549...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
@ 2009-09-17 13:11 ` Leo Mannhart <leo.mannhart@beecom.ch>
2009-09-17 14:47 ` Re: Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
3 siblings, 1 reply; 12+ messages in thread
From: Leo Mannhart @ 2009-09-17 13:11 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
rawi wrote:
> Hello!
>
> The subject says it...
>
> Grails/Hibernate wishes per default one sequence for all tables-PKs and all
> PKs as BigInt.
>
> What would you think about a database with some tens of tables and
> incidentally low to moderate insert concurrency spread in about the half of
> the tables from at most 10 concurrent users with some 10-20 inserts each?
Caveat: If you use the standard sequence generator in hibernate, it is
not using the postgres-sequence in the "usual" manner. hibernate itself
caches 50 ID's as sequence numbers by default. This means, hibernate
only does a select on the database sequence every 50 numbers. it
multyplies the database sequence by 50 to get the "real" sequence
number. it generates the sequence numbers in blocks of 50 numbers or
according to the sequence cache size.
That said, you would probably not see any performance bottlenecks
because of the sequence number generator in the database, even with
thousands of inserts per second.
>
> And (for the eventuality of an unexpected need to scale up in the future -
> e.g. integration of multiple databases), from about which size would you
> expect performance penalties due to the sole sequence and the BigInt-PKs?
>
> I first intend to deploy it on an Intel Pentium 2 Duo (2.5-2.8GHz) with 3 GB
> RAM and SATA hard disk under Ubuntu Server.
>
> Thank you very much in advance!
> Regards
> Rawi
cheers, leo
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:11 ` Re: Hibernate, web application and only one sequence for all primary keys Leo Mannhart <leo.mannhart@beecom.ch>
@ 2009-09-17 14:47 ` rawi <only4com@web.de>
2009-09-17 14:54 ` Re: Hibernate, web application and only one sequence for all primary keys Kenneth Marshall <ktm@rice.edu>
2009-09-17 15:05 ` Re: Hibernate, web application and only one sequence for all primary keys Rob Sargent <robjsargent@gmail.com>
2009-09-17 15:07 ` Re: Hibernate, web application and only one sequence for all primary keys Leo Mannhart <leo.mannhart@beecom.ch>
0 siblings, 3 replies; 12+ messages in thread
From: rawi @ 2009-09-17 14:47 UTC (permalink / raw)
To: pgsql-sql
Leo Mannhart wrote:
>
> Caveat: If you use the standard sequence generator in hibernate, it is
> not using the postgres-sequence in the "usual" manner. hibernate itself
> caches 50 ID's as sequence numbers by default. This means, hibernate
> only does a select on the database sequence every 50 numbers. it
> multyplies the database sequence by 50 to get the "real" sequence
> number. it generates the sequence numbers in blocks of 50 numbers or
> according to the sequence cache size.
> That said, you would probably not see any performance bottlenecks
> because of the sequence number generator in the database, even with
> thousands of inserts per second.
>
Hi Leo, thank you for the explanation!
I don't know if it is that cool to lose up to 50 IDs on each session-end of
Hibernate...
And what do you suppose it would happen, if I set the cache size of
Hibernate's own sequence (after generation) by hand to 1 instead of 50? I
wouldn't need tausends of inserts per second...
Kind regards, Rawi
--
View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp2549...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:11 ` Re: Hibernate, web application and only one sequence for all primary keys Leo Mannhart <leo.mannhart@beecom.ch>
2009-09-17 14:47 ` Re: Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
@ 2009-09-17 14:54 ` Kenneth Marshall <ktm@rice.edu>
2 siblings, 0 replies; 12+ messages in thread
From: Kenneth Marshall @ 2009-09-17 14:54 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
On Thu, Sep 17, 2009 at 07:47:13AM -0700, rawi wrote:
>
>
> Leo Mannhart wrote:
> >
> > Caveat: If you use the standard sequence generator in hibernate, it is
> > not using the postgres-sequence in the "usual" manner. hibernate itself
> > caches 50 ID's as sequence numbers by default. This means, hibernate
> > only does a select on the database sequence every 50 numbers. it
> > multyplies the database sequence by 50 to get the "real" sequence
> > number. it generates the sequence numbers in blocks of 50 numbers or
> > according to the sequence cache size.
> > That said, you would probably not see any performance bottlenecks
> > because of the sequence number generator in the database, even with
> > thousands of inserts per second.
> >
>
> Hi Leo, thank you for the explanation!
>
> I don't know if it is that cool to lose up to 50 IDs on each session-end of
> Hibernate...
> And what do you suppose it would happen, if I set the cache size of
> Hibernate's own sequence (after generation) by hand to 1 instead of 50? I
> wouldn't need tausends of inserts per second...
>
> Kind regards, Rawi
>
Hi Rawi,
If hibernate manages its pool of 50, it can use much lighter-weight
processes than using a full SQL query to a database. I would recommend
leaving it be. You can afford to lose a few ids in 2**63.
Regards,
Ken
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:11 ` Re: Hibernate, web application and only one sequence for all primary keys Leo Mannhart <leo.mannhart@beecom.ch>
2009-09-17 14:47 ` Re: Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
@ 2009-09-17 15:05 ` Rob Sargent <robjsargent@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Rob Sargent @ 2009-09-17 15:05 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
Above all, do not fret about whether or not it is "cool to lose" some
ids. There are plenty of integers; the ids need not be consecutive. I
don't think Grails requires a single sequence source and I know
hibernate does not. Hibernate will allow one to inject any sequence/id
generator you wish to use (including one which generates UUIDs).
rawi wrote:
> Leo Mannhart wrote:
>
>> Caveat: If you use the standard sequence generator in hibernate, it is
>> not using the postgres-sequence in the "usual" manner. hibernate itself
>> caches 50 ID's as sequence numbers by default. This means, hibernate
>> only does a select on the database sequence every 50 numbers. it
>> multyplies the database sequence by 50 to get the "real" sequence
>> number. it generates the sequence numbers in blocks of 50 numbers or
>> according to the sequence cache size.
>> That said, you would probably not see any performance bottlenecks
>> because of the sequence number generator in the database, even with
>> thousands of inserts per second.
>>
>>
>
> Hi Leo, thank you for the explanation!
>
> I don't know if it is that cool to lose up to 50 IDs on each session-end of
> Hibernate...
> And what do you suppose it would happen, if I set the cache size of
> Hibernate's own sequence (after generation) by hand to 1 instead of 50? I
> wouldn't need tausends of inserts per second...
>
> Kind regards, Rawi
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:11 ` Re: Hibernate, web application and only one sequence for all primary keys Leo Mannhart <leo.mannhart@beecom.ch>
2009-09-17 14:47 ` Re: Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
@ 2009-09-17 15:07 ` Leo Mannhart <leo.mannhart@beecom.ch>
2 siblings, 0 replies; 12+ messages in thread
From: Leo Mannhart @ 2009-09-17 15:07 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
rawi wrote:
>
> Leo Mannhart wrote:
>> Caveat: If you use the standard sequence generator in hibernate, it is
>> not using the postgres-sequence in the "usual" manner. hibernate itself
>> caches 50 ID's as sequence numbers by default. This means, hibernate
>> only does a select on the database sequence every 50 numbers. it
>> multyplies the database sequence by 50 to get the "real" sequence
>> number. it generates the sequence numbers in blocks of 50 numbers or
>> according to the sequence cache size.
>> That said, you would probably not see any performance bottlenecks
>> because of the sequence number generator in the database, even with
>> thousands of inserts per second.
>>
>
> Hi Leo, thank you for the explanation!
>
> I don't know if it is that cool to lose up to 50 IDs on each session-end of
> Hibernate...
What you mean "loose 50 IDs"? Sequences are never meant to be gap-free
therefore you are not "loosing" IDs at all. OTOH are you saying, that
one session is just inserting one row and then disconnects from the
database? Then it would be somewhat a waste to use hibernate and all
this caching mechanism, but I highly doubt this. Isn't your app running
on a middle tier and hibernate will only be shutdown when the app server
will shut down? Then there is no "loosing" of IDs either.
> And what do you suppose it would happen, if I set the cache size of
> Hibernate's own sequence (after generation) by hand to 1 instead of 50? I
> wouldn't need tausends of inserts per second...
Why should you do that? You want to know, how much is the network
roundtrip adding to the response time? Just let it how it is; it is a
good starting point.
>
> Kind regards, Rawi
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
@ 2009-09-17 13:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-09-17 14:37 ` Re: Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 14:55 ` Re: Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-18 02:01 ` Re: Hibernate, web application and only one sequence for all primary keys Craig Ringer <craig@postnewspapers.com.au>
3 siblings, 3 replies; 12+ messages in thread
From: Tom Lane @ 2009-09-17 13:59 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
rawi <only4com@web.de> writes:
> Grails/Hibernate wishes per default one sequence for all tables-PKs and all
> PKs as BigInt.
Redesign that software; this is fundamentally broken and stupid.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:59 ` Re: Hibernate, web application and only one sequence for all primary keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-09-17 14:37 ` rawi <only4com@web.de>
2 siblings, 0 replies; 12+ messages in thread
From: rawi @ 2009-09-17 14:37 UTC (permalink / raw)
To: pgsql-sql
Tom Lane-2 wrote:
>
>> Grails/Hibernate wishes per default one sequence for all tables-PKs and
>> all
>> PKs as BigInt.
>
> Redesign that software; this is fundamentally broken and stupid.
>
Thank you Tom...
but redesigning Grails and Hibernate is far beyond my possibilities :)
I could work around this in my app and explicitly ask Hibernate to use my
hand made sequences in PostgreSQL.
While this asks me for some more definition work, I wanted to know, if this
is worth the effort...
I know it now, thanks :)
Kind regards, Rawi
--
View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp2549...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:59 ` Re: Hibernate, web application and only one sequence for all primary keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-09-17 14:55 ` rawi <only4com@web.de>
2 siblings, 0 replies; 12+ messages in thread
From: rawi @ 2009-09-17 14:55 UTC (permalink / raw)
To: pgsql-sql
Tom Lane-2 wrote:
>
>> Grails/Hibernate wishes per default one sequence for all tables-PKs and
>> all
>> PKs as BigInt.
>
> Redesign that software; this is fundamentally broken and stupid.
>
Hi Tom, its me again...
I only need to further understand...
What for a source did have the OIDs in the past, as they were standard in
each table?
I thought they have been also generated by an unique sequence?
Regards, Rawi
--
View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp2549...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:59 ` Re: Hibernate, web application and only one sequence for all primary keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-09-18 02:01 ` Craig Ringer <craig@postnewspapers.com.au>
2 siblings, 0 replies; 12+ messages in thread
From: Craig Ringer @ 2009-09-18 02:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: rawi <only4com@web.de>; pgsql-sql
Tom Lane wrote:
> rawi <only4com@web.de> writes:
>> Grails/Hibernate wishes per default one sequence for all tables-PKs and all
>> PKs as BigInt.
>
> Redesign that software; this is fundamentally broken and stupid.
It's a pretty silly default, but it's clearly intended for simple /
small databases. In any real database you'll be using one sequence per
generated PK. I've had no problems with this with Hibernate, and am a
bit puzzled that the OP is trying to fit their DB to Hibernate rather
than doing the trivial configuration required to get Hibernate to fit
the DB.
The Hibernate documentation is pretty good, and covers this sort of
thing well.
--
Craig Ringer
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Hibernate, web application and only one sequence for all primary keys
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
@ 2009-09-19 02:38 ` Lew <noone@lwsc.ehost-services.com>
3 siblings, 0 replies; 12+ messages in thread
From: Lew @ 2009-09-19 02:38 UTC (permalink / raw)
To: pgsql-sql
rawi wrote:
> Grails/Hibernate wishes per default one sequence for all tables-PKs and all
> PKs as BigInt.
How is that a Hibernate default?
Hibernate lets you define a multitude of types as a primary key, and the
sequence each uses is a matter of XML or annotation configuration, at least in
the Java version of Hibernate which is the only form of it that I've used.
I've used both "old-fashioned" Hibernate with *.hbm.xml mapping descriptors,
and the new-fangled JPA (Java Persistence API) version.
I've used Hibernate with String and (long) integer key types, sequenced and
not. For my learning, I use a system on Linux with Java and PostgreSQL. It
works just fine.
I'm not familiar with Grails.
--
Lew
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2009-09-19 02:38 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-17 12:47 Hibernate, web application and only one sequence for all primary keys rawi <only4com@web.de>
2009-09-17 13:07 ` rawi <only4com@web.de>
2009-09-17 13:11 ` Leo Mannhart <leo.mannhart@beecom.ch>
2009-09-17 14:47 ` rawi <only4com@web.de>
2009-09-17 14:54 ` Kenneth Marshall <ktm@rice.edu>
2009-09-17 15:05 ` Rob Sargent <robjsargent@gmail.com>
2009-09-17 15:07 ` Leo Mannhart <leo.mannhart@beecom.ch>
2009-09-17 13:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-09-17 14:37 ` rawi <only4com@web.de>
2009-09-17 14:55 ` rawi <only4com@web.de>
2009-09-18 02:01 ` Craig Ringer <craig@postnewspapers.com.au>
2009-09-19 02:38 ` Lew <noone@lwsc.ehost-services.com>
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