Should hibernate use unique sequences for each table?

Question

I have several entities using AUTO key generation strategy with Hibernate and postgres.

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;

This will result in a hibernate_sequence generated, and each entity will make use of that sequence when assigning keys.

Now I have a table that has lots of cache data (like 100k entries), and some user tables. As both use strategy AUTO, they both get their keys from the same hibernate sequence. As a result, even if I only have 10 users, they will all have an id of 6-7 digits long, like 123123.

I wonder if, in general, one should introduce a custom sequence for each table? Or shouldn't I care about the id generation that much?

No but I'd prefer the id's just counting up from one to the other, as when viewing the database would directly see how many objects where ever generated (of course this could also be done by counter). — membersound, Apr 11 '14 at 07:33
I tend to keep distinct sequences for closely related tables (ie. class hierarchies, if any, mapped using `JOINED` table strategy) but that's because, like you, I'm happier seeing low ids for few entries. In your case I'd use separate sequences too. The practical benefit is perhaps visibility and quick(er) consistency checks when manually fiddling with the database. — VH-NZZ, Apr 11 '14 at 07:50
How would you create the different sequences? Using `@GeneratedValue(generator = "seq")` with `@SequenceGenerator(name="seq", initialValue=1, allocationSize=100)`? — membersound, Apr 11 '14 at 07:55

Marko Topolnik · Accepted Answer · 2014-04-11T08:26:29.107

I have recently solved this problem for my project. I use the Enhanced sequence generator (which is the default for sequence-style generators) and set the prefer_sequence_per_entity parameter to true.

Contents of my package-info.java:

@GenericGenerator(
    name = "optimized-sequence",
    strategy = "enhanced-sequence",
    parameters = {
        @Parameter(name="prefer_sequence_per_entity", value="true"),
        @Parameter(name="optimizer", value="hilo"),
        @Parameter(name="increment_size", value="50")})
package org.example.model;

import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;

On the usage side you just need

@Id @GeneratedValue(generator="optimized-sequence")
public long id;

I prefer having separate sequences because occasionally I'll drop a table and recreate it, and I want the ID's starting from one.

score 1 · Answer 2 · answered Apr 11 '14 at 07:41

You can use serial datatype for your useid , or use PostgreSQL sequence. LIKE :

digoal=# create table tt(id serial, info text);
CREATE TABLE
digoal=# insert into tt (info) values ('test'),('test');
INSERT 0 2
digoal=# select * from tt;
 id | info 
----+------
  1 | test
  2 | test
(2 rows)

OR

digoal=# create table tt1(id int, info text);
CREATE TABLE
digoal=# create sequence seq_tt1;
CREATE SEQUENCE
digoal=# alter table tt1 alter column id set default nextval('seq_tt1'::regclass);
ALTER TABLE
digoal=# insert into tt1 (info) values ('test'),('test');
INSERT 0 2
digoal=# select * from tt1;
 id | info 
----+------
  1 | test
  2 | test
(2 rows)

score 1 · Answer 3 · answered Apr 11 '14 at 08:04

Try with Sequence

First create sequence in postgres

CREATE SEQUENCE YOUR_ENTITY_SEQ;

In entity, use generation strategy as SEQUENCE and for next time gen value allocation set allocationSize as necessary

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "YOUR_ENTITY_SEQ")
@SequenceGenerator(name = "YOUR_ENTITY_SEQ", sequenceName = "YOUR_ENTITY_SEQ", allocationSize = 1)
private Long id;

score 0 · Answer 4 · edited May 23 '17 at 12:08

This is just the preferred way for Hibernate to manage primary keys. It will generate the appropiate SQL idiom depending on your underlying database (a sequence for Oracle, and identity key field for DB2, etc.)

However, you can perfectly define composite keys if you feel they are more appropiate for your business. Someone gave a great explanation on this here in Stackoverflow:

How to map a composite key with Hibernate?

Should hibernate use unique sequences for each table?

4 Answers4

Linked