| name | PostgreSQL with Rails |
| description | This skill should be used when the user asks about "PostgreSQL", "Postgres", "database optimization", "indexes", "PostgreSQL features", "JSONB", "full-text search", or PostgreSQL-specific Rails patterns. Load this skill when working with PostgreSQL databases. |
| version | 0.1.0 |
PostgreSQL with Rails
PostgreSQL is the recommended production database for Rails. This skill covers PostgreSQL features, optimization, and Rails integration.
Why PostgreSQL
- Robust: ACID compliant, reliable
- Feature-rich: JSON, arrays, full-text search
- Performance: Excellent for most workloads
- Rails default: First-class support
Essential Indexing
Foreign keys (always index):
add_index :posts, :author_id
Frequently queried columns:
add_index :posts, :published_at
add_index :posts, :slug, unique: true
Composite indexes:
add_index :posts, [:author_id, :created_at]
JSONB Columns
Migration:
add_column :products, :metadata, :jsonb, default: {}
add_index :products, :metadata, using: :gin
Query:
Product.where("metadata @> ?", { color: "red" }.to_json)
Product.where("metadata->>'size' = ?", "large")
Full-Text Search
Add column:
add_column :posts, :tsv, :tsvector
add_index :posts, :tsv, using: :gin
Search:
Post.where("tsv @@ plainto_tsquery(?)", "rails postgresql")
PostgreSQL-Specific Types
- Arrays:
string, array: true - JSONB: Structured data
- UUID: Distributed IDs
- HSTORE: Key-value pairs
Performance Tips
- Use EXPLAIN: Analyze slow queries
- Index foreign keys: Always
- Connection pooling: Configure properly
- Partial indexes: For subsets
- Database views: For complex queries
Leverage PostgreSQL's power before adding external tools.
Additional Resources
Example Files
Working examples in examples/:
examples/migration-examples.rb- Comprehensive migration patterns including JSONB, arrays, full-text search, indexes, UUIDs, constraints, and safe column operations