0

We are using hibernate, jpa and spring and our db is postgres 9. We are using sequence to autogenrate primary key. But what we have noticed is, it is skipping 20 numbers when new records is inserted in that tables and in our sequence we have increment by 1, then why postgres incrementing next value to 20. We do use cache value as "20".

user509755
  • 2,941
  • 10
  • 48
  • 82
  • The sequence cache is per connection. So if you are opening and closing connections for every transaction this could explain it. –  Jan 25 '14 at 21:27
  • yes; I think horse is correct. You are incrementing by 1, but caching 20 of them. So, it will cache 20 numbers for every transaction and then throw them away when the connection is closed. – David S Jan 26 '14 at 02:37
  • so does that mean disabling cache for sequence will resolve this issue? Also how big the impact is. – user509755 Jan 30 '14 at 13:05
  • Possible duplicate of [Gaps between primary key id in sql table](http://stackoverflow.com/questions/39099905/gaps-between-primary-key-id-in-sql-table) – e4c5 Aug 24 '16 at 00:58

1 Answers1

3

That's normal. You can tell Hibernate not to cache sequence values - at a performance cost to inserts - but this still doesn't mean you won't have sequence gaps.

I wrote more about this on an older answer - here.

Sequences have gaps. That's their nature. If they couldn't have gaps, you could only have one transaction inserting at a time.

See:

for details.

If you expect gapless sequences, you need to understand that you'll have to do all your inserts serially, with only one transaction able to do work at a time. To learn more, search for "postgresql gaples sequence". Relying on gapless sequences in the DB is usually a bad idea; instead, have your application construct the user-visible values when it fetches values, using the row_number() window function or similar.

Related:

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778