Anatomy of the Message Store

The message store is a single table named messages. Interaction with the message store is effected through Postgres server functions that ensure the correct semantics for the writing of messages to streams, and the reading of messages from streams and categories.

Messages Table

ColumnTypeDescriptionDefaultNullable
idUUIDIdentifier of a message entry and primary keygen_random_uuid()No
stream_namevarcharName of stream to which the message belongsNo
typevarcharThe type of the messageNo
positionbigintThe ordinal position of the message in its stream. Position is gapless.No
global_positionbigintThe ordinal position of the message in the entire message store. Global position may have gaps.No
datajsonbMessage payloadNULLYes
metadatajsonbMessage metadataNULLYes
timetimestampTimestamp when the message was written. The timestamp does not include a time zone.now() AT TIME ZONE 'utc'No

Indexes

NameColumnsUniqueNote
messages_id_idxidNoUniqueness is enforced as primary key
messages_stream_name_position_uniq_idxstream_name, positionYesEnsures uniqueness of position number in a stream
messages_category_global_position_idxcategory(stream_name), global_positionNoUsed when retrieving by category name

Source Code

Table Definition

-- ----------------------------
--  Table structure for messages
-- ----------------------------
CREATE TABLE IF NOT EXISTS "public"."messages" (
  "id" UUID NOT NULL DEFAULT gen_random_uuid(),
  "stream_name" text NOT NULL COLLATE "default",
  "type" text NOT NULL COLLATE "default",
  "position" bigint NOT NULL,
  "global_position" bigserial NOT NULL ,
  "data" jsonb,
  "metadata" jsonb,
  "time" TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'utc') NOT NULL
)
WITH (OIDS=FALSE);

-- ----------------------------
--  Primary key structure for table messages
-- ----------------------------
ALTER TABLE "public"."messages" ADD PRIMARY KEY ("global_position") NOT DEFERRABLE INITIALLY IMMEDIATE;

Source: https://github.com/eventide-project/message-store-postgres-database/blob/master/database/table/messages.sql

Index Definitions

CREATE UNIQUE INDEX CONCURRENTLY "messages_id_uniq_idx" ON "public"."messages" USING btree(id ASC NULLS LAST);

Source: https://github.com/eventide-project/message-store-postgres-database/blob/master/database/indexes/messages-id-uniq.sql

CREATE UNIQUE INDEX CONCURRENTLY "messages_stream_name_position_uniq_idx" ON "public"."messages" USING btree(stream_name COLLATE "default" "pg_catalog"."text_ops" ASC NULLS LAST, "position" "pg_catalog"."int8_ops" ASC NULLS LAST);

Source: https://github.com/eventide-project/message-store-postgres-database/blob/master/database/indexes/messages-stream-name-position-uniq.sql

CREATE INDEX CONCURRENTLY "messages_category_global_position_idx" ON "public"."messages" USING btree(category(stream_name) COLLATE "default" "pg_catalog"."text_ops" ASC NULLS LAST, "global_position" "pg_catalog"."int8_ops" ASC NULLS LAST);

Source: https://github.com/eventide-project/message-store-postgres-database/blob/master/database/indexes/messages-category-global-position.sql