PostgreSQL

The application uses PostgreSQL as the primary database.

Configuration

Defined in config/database.yml:

Adapter: postgresql
Connection pool: RAILS_DB_POOL or RAILS_MAX_THREADS + 5 (default: 10, sized for Puma + Sidekiq)
Databases: Primary (application data) and Cable (ActionCable via solid_cable)
Production: Uses DATABASE_URL environment variable (Heroku)

Extensions

Enabled in the schema:

plpgsql – PL/pgSQL procedural language
pg_stat_statements – Query performance monitoring

PostgreSQL-Specific Features

JSONB Columns

Used extensively for flexible data storage:

ahoy_events.properties
models.capabilities, modalities, metadata, pricing
study_events.event_data
tool_calls.arguments
reviews.phoneme_scores

GIN Indexes

GIN (Generalized Inverted Index) indexes support efficient queries on JSONB and full-text search:

ahoy_events.properties – jsonb_path_ops for JSON containment queries
models.capabilities, models.modalities – GIN for array/JSON lookups
sentences.sentence_fts – GIN with to_tsvector('simple', sentence) for full-text search

Full-Text Search

Sentence search uses PostgreSQL native full-text search with a GIN index on a tsvector expression. The index was created with disable_ddl_transaction! for concurrent index creation (see db/migrate/20251020205645_fix_sentences_fts_index.rb).

Multi-Column Indexes

Composite indexes for common query patterns:

[deck_id, status, due_at] on cards – FSRS card queue lookups
[visitor_token, started_at] on ahoy_visits – Visit tracking
[reviewable_type, reviewable_id, mode] on reviews – Review lookups