Skip to content

Using PostgreSQL

The library includes PostgreSQL adapters for the event store, the projection store and the distributed event broker. They use psycopg 3 and its connection pool, psycopg_pool. This guide covers creating the tables, connecting, and configuring each adapter.

The SQL that creates the tables is in the sql/ directory of the repository. These files are not included in the Python package, and the library has no helper to create or migrate tables. Copy the files you need into your own migration tooling and apply them there.

Which files you need depends on the features you use:

Feature Files
Event store create_events_table.sql, create_events_indices.sql
Projection store create_projections_table.sql, create_projections_indices.sql
Distributed event broker create_subscribers_table.sql, create_subscribers_indices.sql, create_subscriptions_table.sql, create_subscriptions_indices.sql
Similarity query function enable_pg_trgm_extension.sql

Apply each table file before its indices file. The files use plain CREATE TABLE and CREATE INDEX statements, so apply each one only once.

Upgrading the package does not change your tables. When you upgrade, compare the sql/ directory between the two versions and add anything new to your migrations. Missing indices make writes slow as your tables grow. See Running PostgreSQL in production.

The distributed event broker also reads events through the event store, so it needs the events table too. The singleton event broker only needs the events table.

Similarity scores text using the similarity function from PostgreSQL’s pg_trgm extension. You only need the extension if you sort projection searches by Similarity.

By default the adapters use tables named events, projections, subscribers and subscriptions, which match the SQL files. The event and projection storage adapters take a TableSettings to use a different table. If you change a name, change it in the SQL you apply as well, including in the index definitions.

make_event_broker always uses the default subscribers and subscriptions table names.

Both storage adapters take a connection_source, which is either a ConnectionSettings or a psycopg_pool.AsyncConnectionPool. The ConnectionSource type covers both.

When you pass ConnectionSettings, the adapter creates its own connection pool with psycopg’s default pool settings. You must call open() on the adapter before using it and close() when you are done. Using the adapter before opening it raises psycopg_pool.PoolClosed.

import asyncio
from logicblocks.event.persistence.postgres import ConnectionSettings
from logicblocks.event.store import EventStore, PostgresEventStorageAdapter
from logicblocks.event.types import NewEvent
connection_settings = ConnectionSettings(
host="localhost",
port=5432,
dbname="event-store",
user="admin",
password="super-secret",
)
async def main():
adapter = PostgresEventStorageAdapter(connection_source=connection_settings)
await adapter.open()
try:
store = EventStore(adapter)
stream = store.stream(category="profiles", stream="joe.bloggs")
await stream.publish(
events=[
NewEvent(
name="profile-created",
payload={"name": "Joe Bloggs"},
metadata=None,
)
]
)
latest = await stream.latest()
print(latest.name, latest.payload)
# profile-created {'name': 'Joe Bloggs'}
finally:
await adapter.close()
asyncio.run(main())

The repr of ConnectionSettings masks the password, so it is safe to log.

When you pass an AsyncConnectionPool, the adapter uses it as it is. You open and close the pool, and the adapter’s open() and close() do nothing. This lets several adapters share one pool, and lets you choose the pool’s size and other options.

This example shares one pool between an event store and a projection store. It uses a projector like the one in the basic example:

import asyncio
from psycopg import AsyncConnection
from psycopg_pool import AsyncConnectionPool
from logicblocks.event.persistence.postgres import ConnectionSettings
from logicblocks.event.projection import (
PostgresProjectionStorageAdapter,
ProjectionStore,
Projector,
)
from logicblocks.event.store import EventStore, PostgresEventStorageAdapter
from logicblocks.event.types import JsonValue, NewEvent, StreamIdentifier
connection_settings = ConnectionSettings(
host="localhost",
port=5432,
dbname="event-store",
user="admin",
password="super-secret",
)
class ProfileProjector(Projector[StreamIdentifier, dict[str, str]]):
name = "profile"
def initial_state_factory(self) -> dict[str, str]:
return {}
def initial_metadata_factory(self) -> JsonValue:
return {}
def id_factory(self, state, source: StreamIdentifier) -> str:
return source.stream
def profile_created(self, state, event):
state["name"] = event.payload["name"]
state["email"] = event.payload["email"]
return state
async def main():
async with AsyncConnectionPool[AsyncConnection](
connection_settings.to_connection_string(), open=False
) as pool:
event_store = EventStore(
PostgresEventStorageAdapter(connection_source=pool)
)
projection_store = ProjectionStore(
PostgresProjectionStorageAdapter(connection_source=pool)
)
stream = event_store.stream(category="profiles", stream="jane.doe")
await stream.publish(
events=[
NewEvent(
name="profile-created",
payload={"name": "Jane Doe", "email": "jane@example.com"},
metadata=None,
)
]
)
projection = await ProfileProjector().project(source=stream)
await projection_store.save(projection=projection)
loaded = await projection_store.locate(
source=stream.identifier, name="profile"
)
print(loaded.id, loaded.state)
# jane.doe {'name': 'Jane Doe', 'email': 'jane@example.com'}
asyncio.run(main())

async with opens the pool on entry and closes it on exit.

PostgresEventStorageAdapter stores events in the events table. Pass it to EventStore as you would the in-memory adapter. Apart from connection_source, its main options are:

  • serialisation_guarantee: an EventSerialisationGuarantee, which defaults to EventSerialisationGuarantee.LOG;
  • query_settings: a PostgresQuerySettings, whose scan_query_page_size sets how many events each scan query fetches. It defaults to 100;
  • table_settings: a TableSettings, which defaults to the events table.
from logicblocks.event.persistence.postgres import (
ConnectionSettings,
TableSettings,
)
from logicblocks.event.store import PostgresEventStorageAdapter
from logicblocks.event.store.adapters import (
EventSerialisationGuarantee,
PostgresQuerySettings,
)
connection_settings = ConnectionSettings(
host="localhost",
port=5432,
dbname="event-store",
user="admin",
password="super-secret",
)
adapter = PostgresEventStorageAdapter(
connection_source=connection_settings,
serialisation_guarantee=EventSerialisationGuarantee.STREAM,
query_settings=PostgresQuerySettings(scan_query_page_size=500),
table_settings=TableSettings(table_name="account_events"),
)

Before each write, the adapter takes a transaction-scoped PostgreSQL advisory lock, then checks any write conditions and inserts the events. The serialisation guarantee sets the scope of that lock:

  • LOG serialises all writes to the table;
  • CATEGORY serialises writes to the same category;
  • STREAM serialises writes to the same stream.

Lock names include the table name, so adapters on different tables do not block each other. Every process that writes to a table should use the same guarantee, because writes under different guarantees take different locks and are not serialised against each other. The concurrency guide explains the trade-offs.

Under the default LOG guarantee, one slow write holds up every other write to the table. See Running PostgreSQL in production for how to choose a guarantee and keep writes fast.

Scans read events in pages ordered by sequence number, one query per page. A long-running scan can therefore yield events that were saved after it started.

PostgresProjectionStorageAdapter stores projections in the projections table. Pass it to ProjectionStore, as in the connection pool example. It takes a connection_source and, optionally, a table_settings that defaults to the projections table:

from logicblocks.event.persistence.postgres import (
ConnectionSettings,
TableSettings,
)
from logicblocks.event.projection import (
PostgresProjectionStorageAdapter,
ProjectionStore,
)
connection_settings = ConnectionSettings(
host="localhost",
port=5432,
dbname="event-store",
user="admin",
password="super-secret",
)
projection_store = ProjectionStore(
PostgresProjectionStorageAdapter(
connection_source=connection_settings,
table_settings=TableSettings(table_name="account_projections"),
)
)

As with the event storage adapter, call open() and close() on the adapter when you pass ConnectionSettings.

The adapter stores each projection’s state, metadata and source as jsonb. Saving inserts the projection or, if one with the same name and id exists, replaces its state and metadata. Only projections whose source is a log, category or stream identifier can be read back.

See the projection store guide for saving, loading and searching projections.

The event broker can keep its state in PostgreSQL. Pass EventBrokerStorageType.Postgres to make_event_broker with both a ConnectionSettings and an open AsyncConnectionPool. Here, connection_settings and pool are as in the connection pool example:

from logicblocks.event.processing import (
DistributedEventBrokerSettings,
EventBrokerStorageType,
EventBrokerType,
make_event_broker,
)
event_broker = make_event_broker(
node_id="node-1",
broker_type=EventBrokerType.Distributed,
storage_type=EventBrokerStorageType.Postgres,
connection_settings=connection_settings,
connection_pool=pool,
settings=DistributedEventBrokerSettings(),
)

The distributed broker:

  • stores subscribers and subscriptions in the subscribers and subscriptions tables, using the pool;
  • reads events through a PostgresEventStorageAdapter on the pool, unless you pass your own adapter;
  • uses a PostgresLockManager to choose which node coordinates the subscriber groups. The lock manager opens its own connections from ConnectionSettings rather than using the pool, and holds a PostgreSQL advisory lock for the duration of each connection’s transaction.

The singleton broker with Postgres storage keeps no state in the database. It reads events through a PostgresEventStorageAdapter on the pool, unless you pass your own adapter. It requires connection_settings but does not use it.

See the event processing guide for subscribers, event processors and running the broker.