Should hibernate use unique sequences for each table?

get next sequence value from database using hibernate
jpa sequence generator example
hibernate sequence
how to generate table using hibernate in a specific column sequence?
hibernate: select hibernate_sequence.nextval from dual
table generator hibernate
hibernate auto increment primary key example
jpa sequence nextval

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?


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.

Why you should never use the TABLE identifier generator with JPA , Database sequences are the most efficient Hibernate identifier choice, allowing sequence call optimizers and without compromising JDBC batching. If you want database portability, you don't really need the TABLE generator. You can mix the SEQUENCE and the IDENTITY generator as explained in this article. 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


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)

How do Identity, Sequence, and Table (sequence-like) generators , Let's define an Entity using the IDENTITY generation strategy: Query:{[call next value for hibernate_sequence][]}. Query:{[call next value for One or multiple tables can be used to hold the identifier sequence counter. The rules in Oracle are 1 sequence per table because if you drop a table and populate it after you can have a hole in your sequence of several values. So for the references tables use an unique specific key and for data tables use one seuqence for each and with hibernate reference them in your mapping files.


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;

How to generate primary keys with JPA and Hibernate, How can I use my own sequence? Solution: The JPA specification provides a set of annotations to define the primary key generation strategy. First of all, you have � By default, Hibernate will try to use a shared hibernate_sequence, but it is a good idea to use custom sequences for individual entities. The sequence generator allocation size should be the same as the database sequence's incrementation size to avoid unexpected identifiers being generated (especially negative ones).


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?

Hibernate Tips: How to use a custom database sequence, Learn about Identity, Sequence, and Table in Hibernate. The increment process is very efficient since it uses a database internal lightweight One or multiple tables can be used to hold the identifier sequence counter. Unlike identity columns and sequences, which can increment the sequence in a single request, the TABLE generator entails a significant performance overhead. For this reason, Hibernate comes with a series of optimizers which can improve performance for both SEQUENCE and TABLE generators, like the pooled or pooled-lo optimizers .


Hibernate Identity, Sequence and Table (Sequence) Generator , You can also optionally define the catalog, the schema as well as unique You can, for example, specify your own subclass of org.hibernate.persister. We recommend all new projects to use the new enhanced identifier generators. SEQUENCE and TABLE require additional configurations that you can set using � If you want to use Hibernate’s default sequence, you just need to annotate your primary key attribute with @GeneratedValue and set the strategy to GenerationType.SEQUENCE. You can also use a custom database sequence when you add a @SequenceGenerator annotation. It allows you to define the name and database schema of your sequence and the


Chapter 5. Basic O/R Mapping, Hibernate and JPA both make the following assumptions about the corresponding database column(s):. UNIQUE - The values must uniquely identify each row. SEQUENCE - Indicates that database sequence should be used for obtaining primary key TableGenerator annotation -> Section 6.3.4, “Using identifier table”. By default, Hibernate uses a sequence called hibernate_sequence. You can also tell Hibernate to use one of your own database sequences. I explained that in more details in Hibernate Tips: How to use a custom database sequence. Generate UUID Primary Keys


Chapter 6. Identifiers, Several different value generation strategies can be used as explained below. The Auto Strategy; The Identity Strategy; The Sequence Strategy; The Table uses the global number generator to generate a primary key for every new entity object. These generated values are unique at the database level and are never� TABLE: Hibernate uses a database table to simulate a sequence. I prefer to use the GenerationType.SEQUENCE because it is very efficient and allows Hibernate to decide when to perform the insert statement. This provides the required flexibility to use other performance optimization techniques like JDBC batching.