# Eventium PostgreSQL
PostgreSQL-based event store implementation for production event sourcing systems.
## Overview
`eventium-postgresql` provides a robust, production-ready event store implementation backed by PostgreSQL. It leverages PostgreSQL's ACID guarantees, indexing capabilities, and reliability for persistent event storage with high performance and data integrity.
## Features
- ✅ **ACID Transactions** - Full consistency guarantees
- ✅ **Optimistic Concurrency** - Prevents lost updates with version checks
- ✅ **Efficient Indexing** - Fast event retrieval by stream and position
- ✅ **Global Event Ordering** - Sequence numbers for time-ordered queries
- ✅ **Type-Safe Access** - Uses Persistent library for database operations
- ✅ **Production Ready** - Battle-tested PostgreSQL backend
- ✅ **Multi-Process Support** - Concurrent access from multiple applications
## Database Schema
The implementation creates two main tables:
- **Events Table** - Stores events with aggregate keys, versions, and payloads
- **Global Events Table** - Maintains global ordering with sequence numbers
Indexes ensure fast lookups by:
- Stream key + version
- Global sequence number
- Event types (for projections)
## Installation
Add to your `package.yaml`:
```yaml
dependencies:
- eventium-core
- eventium-sql-common
- eventium-postgresql
- persistent-postgresql # PostgreSQL driver
```
## Usage
```haskell
import Eventium.Store.Postgresql
import Database.Persist.Postgresql
main :: IO ()
main = do
let connStr = "host=localhost dbname=eventstore user=postgres"
withPostgresqlPool connStr 10 $ \pool -> do
-- Initialize schema
flip runSqlPool pool $ do
runMigration migrateAll
-- Create event store
let store = makePostgresqlEventStore pool
-- Use with command handlers
result <- applyCommandHandler
(eventStoreWriter store)
(eventStoreReader store)
commandHandler
aggregateId
command
```
## Configuration
### Connection String
```haskell
-- Basic connection
"host=localhost port=5432 dbname=mydb user=myuser password=mypass"
-- With connection pool
withPostgresqlPool connectionString poolSize $ \pool -> ...
```
### Connection Pooling
Recommended settings for production:
```haskell
-- Pool size based on concurrent requests
poolSize = numCores * 2 + effectiveSpindleCount
-- Example: 10 connections for typical web app
withPostgresqlPool connStr 10 $ \pool -> ...
```
## Setup
### Start PostgreSQL with Docker
```bash
# Using docker-compose (provided in project root)
docker-compose up -d postgres
# Or manually
docker run -d \
--name eventium-postgres \
-e POSTGRES_PASSWORD=postgres \
-e POSTGRES_DB=eventstore \
-p 5432:5432 \
postgres:15
```
### Run Migrations
```haskell
runSqlPool (runMigration migrateAll) pool
```
## Performance
PostgreSQL provides excellent performance characteristics:
- **Writes**: ~1000-5000 events/sec (single connection)
- **Reads**: ~10000-50000 events/sec (with proper indexing)
- **Scalability**: Read replicas for query scaling
See `postgres-event-store-bench/` for benchmarking scripts.
## Best Practices
1. **Use Connection Pooling** - Essential for web applications
2. **Index Strategy** - Default indexes cover common queries
3. **Backup Strategy** - Regular PostgreSQL backups
4. **Monitoring** - Watch connection pool usage and query performance
5. **Read Replicas** - Scale read models with PostgreSQL replication
## Production Considerations
- **High Availability** - Use PostgreSQL replication
- **Backup & Recovery** - Point-in-time recovery with WAL archiving
- **Monitoring** - Track event growth and query performance
- **Connection Limits** - Configure max_connections appropriately
## Example: Complete Setup
```haskell
import Eventium.Store.Postgresql
import Control.Monad.Logger (runStdoutLoggingT)
setupEventStore :: IO ()
setupEventStore = runStdoutLoggingT $ do
let connStr = "host=localhost dbname=eventstore"
withPostgresqlPool connStr 10 $ \pool -> do
-- Run migrations
flip runSqlPool pool $ runMigration migrateAll
-- Event store is ready to use
liftIO $ putStrLn "Event store initialized"
```
## Documentation
- [Main README](../README.md) - Project overview
- [SQL Common](../eventium-sql-common/) - Shared SQL utilities
- [Design Documentation](../DESIGN.md) - Architecture details
## License
MIT - see [LICENSE.md](LICENSE.md)