This is an automated email from the ASF dual-hosted git repository.
pjfanning pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/pekko-persistence-jdbc.git
The following commit(s) were added to refs/heads/main by this push:
new a1de7c8b add tag indexes (#593)
a1de7c8b is described below
commit a1de7c8b83ca878363b22b29211d564cf87b1360
Author: PJ Fanning <[email protected]>
AuthorDate: Fri Aug 21 23:23:34 2026 +0100
add tag indexes (#593)
* add tag indexes
* add tag indexes
* links
* mysql test issues
---
core/src/main/resources/schema/h2/h2-create-schema.sql | 2 ++
.../migration-2.0.0/oracle-event-tag-index-migration.sql | 14 ++++++++++++++
.../main/resources/schema/oracle/oracle-create-schema.sql | 6 ++++++
.../postgresql-event-tag-index-migration.sql | 8 ++++++++
.../resources/schema/postgres/postgres-create-schema.sql | 2 ++
.../sqlserver-event-tag-index-migration.sql | 8 ++++++++
.../resources/schema/sqlserver/sqlserver-create-schema.sql | 2 ++
.../pekko/persistence/jdbc/state/DurableStateTables.scala | 1 +
8 files changed, 43 insertions(+)
diff --git a/core/src/main/resources/schema/h2/h2-create-schema.sql
b/core/src/main/resources/schema/h2/h2-create-schema.sql
index de030c54..b6aa4f0e 100644
--- a/core/src/main/resources/schema/h2/h2-create-schema.sql
+++ b/core/src/main/resources/schema/h2/h2-create-schema.sql
@@ -29,6 +29,8 @@ CREATE TABLE IF NOT EXISTS PUBLIC."event_tag" (
ON DELETE CASCADE
);
+CREATE INDEX IF NOT EXISTS "event_tag_idx" on PUBLIC."event_tag" ("tag");
+
CREATE TABLE IF NOT EXISTS PUBLIC."snapshot" (
"persistence_id" VARCHAR(255) NOT NULL,
"sequence_number" BIGINT NOT NULL,
diff --git
a/core/src/main/resources/schema/oracle/migration-2.0.0/oracle-event-tag-index-migration.sql
b/core/src/main/resources/schema/oracle/migration-2.0.0/oracle-event-tag-index-migration.sql
new file mode 100644
index 00000000..8fca5a52
--- /dev/null
+++
b/core/src/main/resources/schema/oracle/migration-2.0.0/oracle-event-tag-index-migration.sql
@@ -0,0 +1,14 @@
+-- SPDX-License-Identifier: Apache-2.0
+
+-- see https://github.com/apache/pekko-persistence-jdbc/pull/593
+
+-- Add a tag-leading index on event_tag for efficient eventsByTag queries.
+-- The PK (persistence_id, sequence_number, tag) has tag as the third column,
+-- so it cannot be used efficiently for tag-first lookups.
+CREATE INDEX EVENT_TAG_IDX ON EVENT_TAG (TAG)
+/
+
+-- Add a global_offset index on durable_state for efficient changesByTag
queries.
+-- This index was already in place for other databases, but was missing for
Oracle.
+CREATE INDEX STATE_GLOBAL_OFFSET_IDX ON DURABLE_STATE (GLOBAL_OFFSET)
+/
diff --git a/core/src/main/resources/schema/oracle/oracle-create-schema.sql
b/core/src/main/resources/schema/oracle/oracle-create-schema.sql
index f2951b07..4e215a5c 100644
--- a/core/src/main/resources/schema/oracle/oracle-create-schema.sql
+++ b/core/src/main/resources/schema/oracle/oracle-create-schema.sql
@@ -33,6 +33,9 @@ CREATE TABLE EVENT_TAG (
)
/
+CREATE INDEX EVENT_TAG_IDX ON EVENT_TAG (TAG)
+/
+
CREATE TABLE SNAPSHOT (
PERSISTENCE_ID VARCHAR(255) NOT NULL,
SEQUENCE_NUMBER NUMERIC NOT NULL,
@@ -75,6 +78,9 @@ CREATE TABLE DURABLE_STATE
)
/
+CREATE INDEX STATE_GLOBAL_OFFSET_IDX ON DURABLE_STATE (GLOBAL_OFFSET)
+/
+
CREATE OR REPLACE TRIGGER DURABLE_STATE__GLOBAL_OFFSET_SEQ_TRG
before insert
on DURABLE_STATE
diff --git
a/core/src/main/resources/schema/postgres/migration-2.0.0/postgresql-event-tag-index-migration.sql
b/core/src/main/resources/schema/postgres/migration-2.0.0/postgresql-event-tag-index-migration.sql
new file mode 100644
index 00000000..19433b86
--- /dev/null
+++
b/core/src/main/resources/schema/postgres/migration-2.0.0/postgresql-event-tag-index-migration.sql
@@ -0,0 +1,8 @@
+-- SPDX-License-Identifier: Apache-2.0
+
+-- see https://github.com/apache/pekko-persistence-jdbc/pull/593
+
+-- Add a tag-leading index on event_tag for efficient eventsByTag queries.
+-- The PK (persistence_id, sequence_number, tag) has tag as the third column,
+-- so it cannot be used efficiently for tag-first lookups.
+CREATE INDEX CONCURRENTLY IF NOT EXISTS event_tag_idx on public.event_tag(tag);
diff --git a/core/src/main/resources/schema/postgres/postgres-create-schema.sql
b/core/src/main/resources/schema/postgres/postgres-create-schema.sql
index 190dc966..53102fd1 100644
--- a/core/src/main/resources/schema/postgres/postgres-create-schema.sql
+++ b/core/src/main/resources/schema/postgres/postgres-create-schema.sql
@@ -33,6 +33,8 @@ CREATE TABLE IF NOT EXISTS public.event_tag(
ON DELETE CASCADE
);
+CREATE INDEX CONCURRENTLY event_tag_idx on public.event_tag(tag);
+
CREATE TABLE IF NOT EXISTS public.snapshot (
persistence_id VARCHAR(255) NOT NULL,
sequence_number BIGINT NOT NULL,
diff --git
a/core/src/main/resources/schema/sqlserver/migration-2.0.0/sqlserver-event-tag-index-migration.sql
b/core/src/main/resources/schema/sqlserver/migration-2.0.0/sqlserver-event-tag-index-migration.sql
new file mode 100644
index 00000000..5f203c6a
--- /dev/null
+++
b/core/src/main/resources/schema/sqlserver/migration-2.0.0/sqlserver-event-tag-index-migration.sql
@@ -0,0 +1,8 @@
+-- SPDX-License-Identifier: Apache-2.0
+
+-- see https://github.com/apache/pekko-persistence-jdbc/pull/593
+
+-- Add a tag-leading index on event_tag for efficient eventsByTag queries.
+-- The PK (persistence_id, sequence_number, tag) has tag as the third column,
+-- so it cannot be used efficiently for tag-first lookups.
+CREATE INDEX event_tag_idx on event_tag (tag);
diff --git
a/core/src/main/resources/schema/sqlserver/sqlserver-create-schema.sql
b/core/src/main/resources/schema/sqlserver/sqlserver-create-schema.sql
index 762b3bc4..9cfd96e4 100644
--- a/core/src/main/resources/schema/sqlserver/sqlserver-create-schema.sql
+++ b/core/src/main/resources/schema/sqlserver/sqlserver-create-schema.sql
@@ -30,6 +30,8 @@ CREATE TABLE event_tag
on delete CASCADE
);
+CREATE INDEX event_tag_idx on event_tag (tag);
+
CREATE TABLE "snapshot" (
"persistence_id" NVARCHAR(255) NOT NULL,
"sequence_number" NUMERIC(10,0) NOT NULL,
diff --git
a/core/src/main/scala/org/apache/pekko/persistence/jdbc/state/DurableStateTables.scala
b/core/src/main/scala/org/apache/pekko/persistence/jdbc/state/DurableStateTables.scala
index 32320d30..5cf58006 100644
---
a/core/src/main/scala/org/apache/pekko/persistence/jdbc/state/DurableStateTables.scala
+++
b/core/src/main/scala/org/apache/pekko/persistence/jdbc/state/DurableStateTables.scala
@@ -69,6 +69,7 @@ import
pekko.persistence.jdbc.config.DurableStateTableConfiguration
val stateTimestamp: Rep[Long] =
column[Long](durableStateTableCfg.columnNames.stateTimestamp)
val globalOffsetIdx = index(s"${tableName}_globalOffset_idx",
globalOffset, unique = true)
+ val tagIdx = index(s"${tableName}_tag_idx", tag)
}
lazy val durableStateTable = new TableQuery(new DurableState(_))
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
