astrocyte-postgres

PostgreSQL implementation of the Astrocyte VectorStore and WikiStore SPIs (provider-spi.md). Uses pgvectorscale (DiskANN) for ANN indexing — pgvectorscale CASCADE-installs pgvector, so pgvector's vector type and <=> operator are available too. The shipped Docker image bakes pgvectorscale in; for self-host, use that image, Supabase, Neon, or Timescale Cloud.

Install

From the monorepo (with astrocyte available):

cd adapters-storage-py/astrocyte-postgres
uv sync
# or: pip install -e ../../astrocyte-py && pip install -e .

Entry point names:

• postgres (group astrocyte.vector_stores) for raw/compiled memory vectors.
• postgres (group astrocyte.document_stores) for BM25 keyword retrieval over the same table.
• postgres (group astrocyte.wiki_stores) for durable wiki pages/revisions/provenance.

PostgreSQL with Docker

Use the combined Compose stack in ../../astrocyte-services-py/docker-compose.yml to run Postgres (pgvector) + the reference REST service together:

cd astrocyte-services-py
docker compose up -d

For Postgres only (no HTTP), start only postgres:

cd astrocyte-services-py
docker compose up -d postgres

Default DSN from your host (port 5433 maps to Postgres in the compose file):

postgresql://astrocyte:astrocyte@127.0.0.1:5433/astrocyte

Schema migrations (production)

DDL is shipped as plain SQL under migrations/ and applied with psql via scripts/migrate.sh (no Python migration framework).

export DATABASE_URL='postgresql://astrocyte:astrocyte@127.0.0.1:5433/astrocyte'
cd adapters-storage-py/astrocyte-postgres
./scripts/migrate.sh

Requirements: PostgreSQL 15+ (for CREATE INDEX CONCURRENTLY IF NOT EXISTS), psql on PATH.

After migrations are applied, set bootstrap_schema: false in vector_store_config so the app does not run CREATE TABLE / indexes at runtime (see configuration table below). For a single command that starts Postgres, runs migrations, then starts the stack with runbook config, use runbook-up.sh (see Runbook).

Embedding width: migrations/002_astrocyte_vectors.sql creates vector(${ASTROCYTE_EMBEDDING_DIMENSIONS:-128}). That must match embedding_dimensions in config. For OpenAI text-embedding-3-small, run migrations with ASTROCYTE_EMBEDDING_DIMENSIONS=1536.

ANN backend (DiskANN via pgvectorscale): vector indexes are created with USING diskann (embedding vector_cosine_ops) WITH (num_neighbors = 50). pgvectorscale CASCADE-installs pgvector, so application code using pgvector's <=> operator works unchanged. Astrocyte standardized on pgvectorscale in 2026-05 — the LongMemEval bench surfaced HNSW per-page write-lock drift (~1.0s → 2.0s/session as the index grew under concurrent retain), and DiskANN's graph layout serializes less aggressively. pgvectorscale is OSS under the PostgreSQL License with no feature gates.

./scripts/migrate.sh

Supported deployment targets: the shipped ghcr.io/astrocyteai/astrocyte-postgres image (bakes pgvectorscale in), Supabase, Neon, Timescale Cloud, or any Postgres with postgresql-16-pgvectorscale from Timescale's apt repo. AWS RDS, GCP Cloud SQL, and Azure Database for PostgreSQL do not ship pgvectorscale and are not supported targets — run the shipped image on ECS / GCE / a VM instead.

The DiskANN index DDL is in migrations/003_indexes.sql and migrations/009_entities_trigram_embedding.sql; the CREATE EXTENSION vectorscale CASCADE lives in migrations/001_extension.sql.

Custom table_name: The shipped SQL targets astrocyte_vectors. If you use another table name, copy and adjust the migration files accordingly.

The later migrations add the Hindsight-comparable Postgres substrate around vectors: bank metadata and access grants, lifecycle columns (retained_at, forgotten_at), durable wiki pages/revisions/provenance, canonical entity/link tables, and normalized temporal facts.

Configuration

How this fits astrocyte_gateway

1. astrocyte-py defines the VectorStore protocol and discovers adapters by entry point (astrocyte.vector_stores).
2. astrocyte-postgres registers postgres → PostgresStore. Installing this package makes the name postgres available to resolve_provider().
3. astrocyte_gateway/wiring.py calls resolve_vector_store(config), which loads the class from the entry point and passes vector_store_config from YAML (or env-only defaults).
4. astrocyte_gateway/brain.py builds Astrocyte + PipelineOrchestrator with that store and your chosen llm_provider (still mock unless you configure a real LLM).

Example ASTROCYTE_CONFIG_PATH snippet:

provider_tier: storage
vector_store: postgres
llm_provider: mock
vector_store_config:
  dsn: postgresql://astrocyte:astrocyte@127.0.0.1:5433/astrocyte
  embedding_dimensions: 128
  bootstrap_schema: false
wiki_store: postgres
wiki_store_config:
  dsn: postgresql://astrocyte:astrocyte@127.0.0.1:5433/astrocyte
  bootstrap_schema: false

Then run the REST service (from repo layout):

export ASTROCYTE_CONFIG_PATH=/path/to/that.yaml
cd astrocyte-services-py/astrocyte-gateway-py && uv run astrocyte-gateway-py

Or set only env (no YAML file):

export ASTROCYTE_VECTOR_STORE=postgres
export DATABASE_URL=postgresql://astrocyte:astrocyte@127.0.0.1:5433/astrocyte
# embedding_dimensions default 128 — override via YAML if you add a file
cd astrocyte-services-py/astrocyte-gateway-py && uv sync --extra postgres

Note: vector_store_config for dimensions is only merged from YAML today; for env-only mode, add a small YAML or extend brain.py to pass ASTROCYTE_EMBEDDING_DIMENSIONS (future improvement).

Production notes

• DiskANN parameters (num_neighbors) live in migrations/003_indexes.sql; tune with DBA guidance as load grows.
• Embedding dimension must match the LLMProvider.embed() output used by the pipeline.
• Use secrets for dsn, not committed YAML.