Skip to main content
PostgreSQL storage provides enterprise-grade persistence for Mastra with ACID transactions, custom indexes, and schema management.

​
Installation

npm install @mastra/pg

​
Configuration

​
Connection String

1

Import PostgresStore

import { PostgresStore } from '@mastra/pg';
2

Create storage with connection string

const storage = new PostgresStore({
  id: 'my-storage',
  connectionString: process.env.DATABASE_URL!,
});
3

Configure Mastra

import { Mastra } from '@mastra/core';

const mastra = new Mastra({
  storage,
});

​
Host Configuration

const storage = new PostgresStore({
  id: 'my-storage',
  host: 'localhost',
  port: 5432,
  database: 'mastra',
  user: 'postgres',
  password: process.env.PG_PASSWORD!,
});

​
Pre-configured Pool

import { Pool } from 'pg';
import { PostgresStore } from '@mastra/pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL!,
  max: 20,
  idleTimeoutMillis: 30000,
});

const storage = new PostgresStore({
  id: 'my-storage',
  pool,
});

​
Configuration Options

​
id
string
required
Unique identifier for the storage instance
​
connectionString
string
PostgreSQL connection string (e.g., postgresql://user:pass@host:5432/db)
​
host
string
Database host (alternative to connectionString)
​
port
number
default:5432
Database port
​
database
string
Database name
​
user
string
Database user
​
password
string
Database password
​
ssl
boolean | object
SSL configuration for secure connections
​
schemaName
string
default:"public"
PostgreSQL schema to use for tables
​
max
number
default:20
Maximum number of connections in the pool
​
idleTimeoutMillis
number
default:30000
Milliseconds before idle connections are closed
​
skipDefaultIndexes
boolean
default:false
Skip creating default indexes on tables
​
indexes
object
Custom index configurations per domain
​
disableInit
boolean
default:false
Disable automatic table initialization

​
Usage Examples

​
Access Storage Domains

const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
});

// Access memory domain
const memory = await storage.getStore('memory');
await memory?.saveThread({ thread });

// Access workflows domain
const workflows = await storage.getStore('workflows');
await workflows?.persistWorkflowSnapshot({
  workflowName: 'my-workflow',
  runId: 'run-123',
  snapshot,
});

​
Custom Schema

const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
  schemaName: 'mastra_prod',
});

​
Direct Database Access

const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
});

// Execute custom queries
const result = await storage.db.any(
  'SELECT * FROM users WHERE active = $1',
  [true]
);

// Access the underlying pg.Pool
const client = await storage.pool.connect();
try {
  await client.query('BEGIN');
  // ... your queries
  await client.query('COMMIT');
} finally {
  client.release();
}

​
Custom Indexes

const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
  skipDefaultIndexes: false,
  indexes: {
    memory: [
      {
        columns: ['user_id', 'created_at'],
        method: 'btree',
        unique: false,
      },
    ],
    workflows: [
      {
        columns: ['workflow_name', 'status'],
        method: 'btree',
      },
    ],
  },
});

​
Schema Export

Export Mastra schema as SQL DDL for version control: 
import { exportSchemas } from '@mastra/pg';

// Export for public schema
const ddl = exportSchemas();

// Export for custom schema
const ddl = exportSchemas('mastra_prod');

// Write to file
import { writeFileSync } from 'fs';
writeFileSync('schema.sql', ddl);

​
Storage Domains

PostgreSQL storage supports all Mastra domains:
  • memory - Thread-based conversation persistence
  • workflows - Workflow execution snapshots
  • observability - Telemetry and logging
  • agents - Agent configurations
  • datasets - Training and evaluation datasets
  • experiments - Evaluation experiment results
  • promptBlocks - Reusable prompt templates
  • scorerDefinitions - Evaluation scorer configurations
  • mcpClients - MCP client configurations
  • mcpServers - MCP server configurations
  • workspaces - Workspace metadata
  • skills - Agent skill definitions
  • blobs - Binary large object storage

​
Performance Optimization

​
Connection Pooling

const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
  max: 50, // Increase pool size for high concurrency
  idleTimeoutMillis: 60000, // Keep connections alive longer
});

​
Custom Indexes for Queries

const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
  indexes: {
    observability: [
      {
        columns: ['trace_id', 'timestamp'],
        method: 'btree',
      },
      {
        columns: ['span_name'],
        method: 'hash',
      },
    ],
  },
});

​
SSL Configuration

​
Basic SSL

const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
  ssl: true,
});

​
Custom SSL

import { readFileSync } from 'fs';

const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
  ssl: {
    rejectUnauthorized: true,
    ca: readFileSync('./ca-certificate.crt').toString(),
  },
});

​
Vector Store Support

PostgreSQL also provides pgvector support: 
import { PgVector } from '@mastra/pg';

const vectorStore = new PgVector({
  id: 'vectors',
  connectionString: process.env.DATABASE_URL!,
});

const mastra = new Mastra({
  vectors: {
    embeddings: vectorStore,
  },
});
See pgvector documentation for more details.

​
Best Practices

Use Connection Pooling

Let PostgresStore manage the connection pool for optimal performance.

Schema Isolation

Use custom schemas in multi-tenant deployments:
schemaName: `tenant_${tenantId}`

Index Strategy

Profile queries and add custom indexes for frequently filtered columns.

Connection Limits

Set appropriate pool sizes based on your workload:
  • API servers: 20-50 connections
  • Background workers: 5-10 connections

​
Cleanup

Close connections when shutting down: 
const storage = new PostgresStore({
  id: 'storage',
  connectionString: process.env.DATABASE_URL!,
});

// ... use storage

// Clean up
await storage.close();

pgvector

PostgreSQL vector extension for embeddings

LibSQL Storage

Lightweight SQLite-compatible storage

PostgreSQL Docs

Official PostgreSQL documentation

node-postgres

PostgreSQL client for Node.js

Build docs developers (and LLMs) love

Get started for freeTalk to us