IDENTITY vs SEQUENCE vs TABLE: generated DDL, SQL, and when the INSERT runs

######## H2 ########
database: H2Dialect / H2 2.4.240 (2025-09-22)

== DDL Hibernate generates
create sequence seq_one_item start with 1 increment by 1;
create sequence seq_pooled_item start with 1 increment by 50;
create sequence SeqDefaultItem_SEQ start with 1 increment by 50;
create table hibernate_sequences (next_val bigint, sequence_name varchar(255) not null, primary key (sequence_name));
insert into hibernate_sequences(sequence_name, next_val) values ('default',0);
create table IdentityItem (id bigint generated by default as identity, name varchar(255), primary key (id));
create table SeqDefaultItem (id bigint not null, name varchar(255), primary key (id));
create table SeqOneItem (id bigint not null, name varchar(255), primary key (id));
create table SeqPooledItem (id bigint not null, name varchar(255), primary key (id));
create table TableItem (id bigint not null, name varchar(255), primary key (id));

== IDENTITY: persist() runs the INSERT immediately
-- persist(item)  -- no flush(), no commit() yet
   sql> insert into IdentityItem (name,id) values (?,default)
item.getId() right after persist : 1
rows visible after rollback : 0

== SEQUENCE, allocationSize = 1: one sequence call per entity
persist 120 entities, one transaction
sequence calls : 120   inserts : 120
first ids : [1, 2, 3, 4, 5]  ...  last id : 120

== SEQUENCE, allocationSize = 50 (pooled optimizer): one sequence call per 50 entities
persist 120 entities, one transaction
sequence calls : 4   inserts : 120
first ids : [1, 2, 3, 4, 5]  ...  last id : 120
sequence statements : [select next value for seq_pooled_item]
(the first allocation of a fresh sequence costs two calls: one to learn the starting value, one to learn the upper bound of the first block of 50)
persist another 120 entities, same SessionFactory : sequence calls : 2

== TABLE generator: one read + one update of the generator row per allocation
persist 120 entities: statements other than the 120 INSERTs : 8 (seen by the JDBC counter; Hibernate's StatementInspector does not see them)
   sql> select next_val from hibernate_sequences where sequence_name=? for update
   sql> update hibernate_sequences set next_val=? where next_val=? and sequence_name=?

######## POSTGRES ########
SKIPPED: no Docker daemon was reachable when this transcript was generated, so PostgreSQL was not run.