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:
schemaName?:
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 datamastra_evals
: Stores evaluation results and metadatamastra_threads
: Stores conversation threadsmastra_messages
: Stores individual messagesmastra_traces
: Stores telemetry and tracing datamastra_scorers
: Stores scoring and evaluation datamastra_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 constraintswhere: 'condition'
for partial indexesmethod: 'brin'
for time-series datastorage: { fillfactor: 90 }
for update-heavy tablesconcurrent: true
for non-blocking creation (default)
Index Options
name:
table:
columns:
unique?:
concurrent?:
where?:
method?:
opclass?:
storage?:
tablespace?:
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 |