PostgreSQL Storage

The PostgreSQL storage implementation provides a production-ready storage solution using PostgreSQL databases.

Installation

npm install @mastra/pg@beta

Usage

import { PostgresStore } from "@mastra/pg";

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

Parameters

connectionString:

string

PostgreSQL connection string (e.g., postgresql://user:pass@host:5432/dbname)

schemaName?:

string

The name of the schema you want the storage to use. Will use the default schema if not provided.

Constructor Examples

You can instantiate PostgresStore in the following ways:

import { PostgresStore } from "@mastra/pg";

// Using a connection string only
const store1 = new PostgresStore({
  id: 'pg-storage-1',
  connectionString: "postgresql://user:password@localhost:5432/mydb",
});

// Using a connection string with a custom schema name
const store2 = new PostgresStore({
  id: 'pg-storage-2',
  connectionString: "postgresql://user:password@localhost:5432/mydb",
  schemaName: "custom_schema", // optional
});

// Using individual connection parameters
const store4 = new PostgresStore({
  id: 'pg-storage-3',
  host: "localhost",
  port: 5432,
  database: "mydb",
  user: "user",
  password: "password",
});

// Individual parameters with schemaName
const store5 = new PostgresStore({
  id: 'pg-storage-4',
  host: "localhost",
  port: 5432,
  database: "mydb",
  user: "user",
  password: "password",
  schemaName: "custom_schema", // optional
});

Additional Notes

Schema Management

The storage implementation handles schema creation and updates automatically. It creates the following tables:

• mastra_workflow_snapshot: Stores workflow state and execution data
• mastra_evals: Stores evaluation results and metadata
• mastra_threads: Stores conversation threads
• mastra_messages: Stores individual messages
• mastra_traces: Stores telemetry and tracing data
• mastra_scorers: Stores scoring and evaluation data
• mastra_resources: Stores resource working memory data

Initialization

When you pass storage to the Mastra class, init() is called automatically before any storage operation:

import { Mastra } from "@mastra/core";
import { PostgresStore } from "@mastra/pg";

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

const mastra = new Mastra({
  storage, // init() is called automatically
});

If you're using storage directly without Mastra, you must call init() explicitly to create the tables:

import { PostgresStore } from "@mastra/pg";

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

// Required when using storage directly
await storage.init();

// Access domain-specific stores via getStore()
const memoryStore = await storage.getStore('memory');
const thread = await memoryStore?.getThreadById({ threadId: "..." });

warning

If init() is not called, tables won't be created and storage operations will fail silently or throw errors.

Direct Database and Pool Access

PostgresStore exposes both the underlying database object and the pg-promise instance as public fields:

store.db; // pg-promise database instance
store.pgp; // pg-promise main instance

This enables direct queries and custom transaction management. When using these fields:

• You are responsible for proper connection and transaction handling.
• Closing the store (store.close()) will destroy the associated connection pool.
• Direct access bypasses any additional logic or validation provided by PostgresStore methods.

This approach is intended for advanced scenarios where low-level access is required.

Usage Example

Adding memory to an agent

To add PostgreSQL memory to an agent use the Memory class and create a new storage key using PostgresStore. The connectionString can either be a remote location, or a local database connection.

src/mastra/agents/example-pg-agent.ts

import { Memory } from "@mastra/memory";
import { Agent } from "@mastra/core/agent";
import { PostgresStore } from "@mastra/pg";

export const pgAgent = new Agent({
  id: "pg-agent",
  name: "PG Agent",
  instructions:
    "You are an AI agent with the ability to automatically recall memories from previous interactions.",
  model: "openai/gpt-5.1",
  memory: new Memory({
    storage: new PostgresStore({
      id: 'pg-agent-storage',
      connectionString: process.env.DATABASE_URL!,
    }),
    options: {
      generateTitle: true, // Explicitly enable automatic title generation
    },
  }),
});

Using the agent

Use memoryOptions to scope recall for this request. Set lastMessages: 5 to limit recency-based recall, and use semanticRecall to fetch the topK: 3 most relevant messages, including messageRange: 2 neighboring messages for context around each match.

src/test-pg-agent.ts

import "dotenv/config";

import { mastra } from "./mastra";

const threadId = "123";
const resourceId = "user-456";

const agent = mastra.getAgent("pg-agent");

const message = await agent.stream("My name is Mastra", {
  memory: {
    thread: threadId,
    resource: resourceId,
  },
});

await message.textStream.pipeTo(new WritableStream());

const stream = await agent.stream("What's my name?", {
  memory: {
    thread: threadId,
    resource: resourceId,
  },
  memoryOptions: {
    lastMessages: 5,
    semanticRecall: {
      topK: 3,
      messageRange: 2,
    },
  },
});

for await (const chunk of stream.textStream) {
  process.stdout.write(chunk);
}

Index Management

PostgreSQL storage provides index management to optimize query performance.

Default Indexes

PostgreSQL storage creates composite indexes during initialization for common query patterns:

• mastra_threads_resourceid_createdat_idx: (resourceId, createdAt DESC)
• mastra_messages_thread_id_createdat_idx: (thread_id, createdAt DESC)
• mastra_ai_spans_traceid_startedat_idx: (traceId, startedAt DESC)
• mastra_ai_spans_parentspanid_startedat_idx: (parentSpanId, startedAt DESC)
• mastra_ai_spans_name_startedat_idx: (name, startedAt DESC)
• mastra_ai_spans_scope_startedat_idx: (scope, startedAt DESC)
• mastra_scores_trace_id_span_id_created_at_idx: (traceId, spanId, createdAt DESC)

These indexes improve performance for filtered queries with sorting, including dateRange filters on message queries.

Configuring Indexes

You can control index creation via constructor options:

import { PostgresStore } from "@mastra/pg";

// Skip default indexes (manage indexes separately)
const store = new PostgresStore({
  id: 'pg-storage',
  connectionString: process.env.DATABASE_URL,
  skipDefaultIndexes: true,
});

// Add custom indexes during initialization
const storeWithCustomIndexes = new PostgresStore({
  id: 'pg-storage',
  connectionString: process.env.DATABASE_URL,
  indexes: [
    {
      name: "idx_threads_metadata_type",
      table: "mastra_threads",
      columns: ["metadata->>'type'"],
    },
    {
      name: "idx_messages_status",
      table: "mastra_messages",
      columns: ["metadata->>'status'"],
    },
  ],
});

For advanced index types, you can specify additional options:

• unique: true for unique constraints
• where: 'condition' for partial indexes
• method: 'brin' for time-series data
• storage: { fillfactor: 90 } for update-heavy tables
• concurrent: true for non-blocking creation (default)

Index Options

name:

string

Unique name for the index

table:

string

Table name (e.g., 'mastra_threads')

columns:

string[]

Array of column names with optional sort order (e.g., ['id', 'createdAt DESC'])

unique?:

boolean

Creates a unique constraint index

concurrent?:

boolean

Creates index without locking table (default: true)

where?:

string

Partial index condition (PostgreSQL specific)

method?:

'btree' | 'hash' | 'gin' | 'gist' | 'spgist' | 'brin'

Index method (default: 'btree')

opclass?:

string

Operator class for GIN/GIST indexes

storage?:

Record<string, any>

Storage parameters (e.g., { fillfactor: 90 })

tablespace?:

string

Tablespace name for index placement

Schema-Specific Indexes

When using custom schemas, index names are prefixed with the schema name:

const storage = new PostgresStore({
  id: 'pg-storage',
  connectionString: process.env.DATABASE_URL,
  schemaName: "custom_schema",
  indexes: [
    {
      name: "idx_threads_status",
      table: "mastra_threads",
      columns: ["status"],
    },
  ],
});

// Creates index as: custom_schema_idx_threads_status

Managing Indexes via SQL

For advanced index management (listing, dropping, analyzing), use direct SQL queries via the db accessor:

// List indexes for a table
const indexes = await storage.db.any(`
  SELECT indexname, indexdef
  FROM pg_indexes
  WHERE tablename = 'mastra_messages'
`);

// Drop an index
await storage.db.none('DROP INDEX IF EXISTS idx_my_custom_index');

// Analyze index usage
const stats = await storage.db.one(`
  SELECT idx_scan, idx_tup_read
  FROM pg_stat_user_indexes
  WHERE indexrelname = 'mastra_messages_thread_id_createdat_idx'
`);

Index Types and Use Cases

PostgreSQL offers different index types optimized for specific scenarios:

Index Type	Best For	Storage	Speed
btree (default)	Range queries, sorting, general purpose	Moderate	Fast
hash	Equality comparisons only	Small	Very fast for =
gin	JSONB, arrays, full-text search	Large	Fast for contains
gist	Geometric data, full-text search	Moderate	Fast for nearest-neighbor
spgist	Non-balanced data, text patterns	Small	Fast for specific patterns
brin	Large tables with natural ordering	Very small	Fast for ranges