Thread: Hibernate, web application and only one sequence for all primary keys
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-tp25490498p25490498.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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-tp25490498p25490895.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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
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
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-tp25490498p25491915.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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-tp25490498p25491924.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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-tp25490498p25491931.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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 > >
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 >
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
I appreciate very much your opinions. They'll help me to adjust my approach to this problem. As I meant, it wouldn't be "cool" to have hibernate hoarding 50 ids, I didn't mean the id-gaps and id-loss, that's no problem, but also to lose the timely clear id alignment in the case of parallel access of the database with another application... Sure, I'll have timestamps vor that, but... :) Finally I'll go - certainly - with one sequence per PK. 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-tp25490498p25504364.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Re: Hibernate, web application and only one sequence for all primary keys
From
Kenneth Marshall
Date:
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
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