PostgreSQL Storage

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

Installation

npm install @mastra/pg@latest

Usage

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

const storage = new PostgresStore({
  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({
  connectionString: "postgresql://user:password@localhost:5432/mydb",
});

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

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

// Individual parameters with schemaName
const store5 = new PostgresStore({
  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

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.

Index Management

PostgreSQL storage provides comprehensive index management capabilities to optimize query performance.

Automatic Performance Indexes

PostgreSQL storage automatically 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_traces_name_starttime_idx: (name, startTime DESC)
• mastra_evals_agent_name_created_at_idx: (agent_name, created_at DESC)

These indexes significantly improve performance for filtered queries with sorting.

Creating Custom Indexes

Create additional indexes to optimize specific query patterns:

// Basic index for common queries
await storage.createIndex({
  name: "idx_threads_resource",
  table: "mastra_threads",
  columns: ["resourceId"],
});

// Composite index with sort order for filtering + sorting
await storage.createIndex({
  name: "idx_messages_composite",
  table: "mastra_messages",
  columns: ["thread_id", "createdAt DESC"],
});

// GIN index for JSONB columns (fast JSON queries)
await storage.createIndex({
  name: "idx_traces_attributes",
  table: "mastra_traces",
  columns: ["attributes"],
  method: "gin",
});

For more advanced use cases, you can also use:

• 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

Managing Indexes

List and monitor existing indexes:

// List all indexes
const allIndexes = await storage.listIndexes();
console.log(allIndexes);
// [
//   {
//     name: 'mastra_threads_pkey',
//     table: 'mastra_threads',
//     columns: ['id'],
//     unique: true,
//     size: '16 KB',
//     definition: 'CREATE UNIQUE INDEX...'
//   },
//   ...
// ]

// List indexes for specific table
const threadIndexes = await storage.listIndexes("mastra_threads");

// Get detailed statistics for an index
const stats = await storage.describeIndex("idx_threads_resource");
console.log(stats);
// {
//   name: 'idx_threads_resource',
//   table: 'mastra_threads',
//   columns: ['resourceId', 'createdAt'],
//   unique: false,
//   size: '128 KB',
//   definition: 'CREATE INDEX idx_threads_resource...',
//   method: 'btree',
//   scans: 1542,           // Number of index scans
//   tuples_read: 45230,    // Tuples read via index
//   tuples_fetched: 12050  // Tuples fetched via index
// }

// Drop an index
await storage.dropIndex("idx_threads_status");

Schema-Specific Indexes

When using custom schemas, indexes are created with schema prefixes:

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

// Creates index as: custom_schema_idx_threads_status
await storage.createIndex({
  name: "idx_threads_status",
  table: "mastra_threads",
  columns: ["status"],
});

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