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?

Answer 1

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.

Answer 2

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)