Skip to main content

PostgreSQL to Rails Migration Guide

Complete guide for converting PostgreSQL queries to Rails ActiveRecord with advanced features

What you'll learn:

  • • Convert PostgreSQL-specific queries to ActiveRecord
  • • Handle JSON/JSONB operations in Rails
  • • Work with PostgreSQL arrays and advanced data types
  • • Migrate window functions and CTEs to ActiveRecord

Table of Contents

1. PostgreSQL Setup in Rails

Database Configuration

Gemfile Setup

# Gemfile
gem 'pg', '~> 1.1'

Database Configuration

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: myapp_development
  username: myapp
  password: 
  host: localhost
  port: 5432

production:
  <<: *default
  database: myapp_production
  username: myapp
  password: 
  host:

Enable PostgreSQL Extensions

# In your migration
class EnablePostgresExtensions < ActiveRecord::Migration[7.0]
  def change
    enable_extension "pgcrypto"    # For UUID generation
    enable_extension "hstore"      # For key-value storage
    enable_extension "citext"      # Case-insensitive text
    enable_extension "pg_trgm"     # Trigram matching for search
  end
end

2. Basic Query Conversion

Common PostgreSQL to ActiveRecord Patterns

Case-Insensitive Queries

PostgreSQL SQL:

SELECT * FROM users 
WHERE email ILIKE '%gmail.com%';

Rails ActiveRecord:

User.where('email ILIKE ?', '%gmail.com%')

# Or using citext column type
User.where(email: email.downcase)

Regular Expressions

PostgreSQL SQL:

SELECT * FROM users 
WHERE phone ~ '^\+1[0-9]{10}$';

Rails ActiveRecord:

User.where('phone ~ ?', '^\+1[0-9]{10}$')

# Case-insensitive regex
User.where('phone ~* ?', '^\\+1[0-9]{10}$')

DISTINCT ON

PostgreSQL SQL:

SELECT DISTINCT ON (user_id) *
FROM posts 
ORDER BY user_id, created_at DESC;

Rails ActiveRecord:

Post.select('DISTINCT ON (user_id) *')
    .order(:user_id, created_at: :desc)

# Alternative approach using window functions
Post.select('*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn')
    .having('rn = 1')

3. JSON/JSONB Operations

Working with JSON Data

Model Setup for JSON

# Migration
class AddMetadataToUsers < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :metadata, :jsonb, default: {}
    add_index :users, :metadata, using: :gin
  end
end

# Model
class User < ApplicationRecord
  # Automatically handle JSON serialization
end

JSON Key Access

PostgreSQL SQL:

SELECT * FROM users 
WHERE metadata->>'age' = '25';

SELECT * FROM users 
WHERE metadata->'preferences'->>'theme' = 'dark';

Rails ActiveRecord:

User.where("metadata->>'age' = ?", '25')

User.where("metadata->'preferences'->>'theme' = ?", 'dark')

# Using Rails 7+ JSON path syntax
User.where("metadata @> ?", { age: 25 }.to_json)

JSON Contains (@>)

PostgreSQL SQL:

SELECT * FROM users 
WHERE metadata @> '{"premium": true}';

SELECT * FROM users 
WHERE metadata->'tags' @> '["admin"]';

Rails ActiveRecord:

User.where("metadata @> ?", { premium: true }.to_json)

User.where("metadata->'tags' @> ?", ["admin"].to_json)

# Using PostgreSQL gem helpers
User.where("metadata @> ?", JSON.generate(premium: true))

JSON Path Queries (jsonb_path_query)

PostgreSQL SQL:

SELECT * FROM users 
WHERE jsonb_path_exists(
  metadata, 
  '$.purchases[*].amount ? (@ > 100)'
);

Rails ActiveRecord:

User.where(
  "jsonb_path_exists(metadata, ?)", 
  '$.purchases[*].amount ? (@ > 100)'
)

JSON Performance Tips

  • • Use JSONB instead of JSON for better performance
  • • Add GIN indexes on JSONB columns for fast lookups
  • • Use partial indexes for frequently queried JSON paths
  • • Consider extracting frequently queried JSON fields to regular columns

4. PostgreSQL Arrays

Array Operations

Model Setup for Arrays

# Migration
class AddTagsToUsers < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :tags, :string, array: true, default: []
    add_index :users, :tags, using: :gin
  end
end

# Model
class User < ApplicationRecord
  validates :tags, presence: true
end

Array Contains (&&)

PostgreSQL SQL:

SELECT * FROM users 
WHERE tags && ARRAY['admin', 'moderator'];

SELECT * FROM users 
WHERE 'premium' = ANY(tags);

Rails ActiveRecord:

User.where('tags && ARRAY[?]', ['admin', 'moderator'])

User.where('? = ANY(tags)', 'premium')

# Using Rails array helpers
User.where('tags @> ARRAY[?]', ['admin'])

Array Length and Indexing

PostgreSQL SQL:

SELECT * FROM users 
WHERE array_length(tags, 1) > 3;

SELECT * FROM users 
WHERE tags[1] = 'admin';

Rails ActiveRecord:

User.where('array_length(tags, 1) > ?', 3)

User.where('tags[1] = ?', 'admin')

Working with Arrays in Ruby

# Creating and updating array fields
user = User.create(tags: ['admin', 'premium'])

# Adding to array
user.tags << 'moderator'
user.save

# Using ActiveRecord array operators
User.where('tags @> ?', '{admin}')  # Contains admin
User.where('tags && ?', '{admin,premium}')  # Overlaps with admin or premium

5. Full-Text Search

PostgreSQL Text Search

Basic Text Search

PostgreSQL SQL:

SELECT * FROM posts 
WHERE to_tsvector('english', title || ' ' || content) 
@@ to_tsquery('english', 'rails & activerecord');

Rails ActiveRecord:

Post.where(
  "to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', ?)",
  'rails & activerecord'
)

Search with Ranking

PostgreSQL SQL:

SELECT *, 
  ts_rank(to_tsvector('english', title || ' ' || content), 
          to_tsquery('english', 'rails & activerecord')) as rank
FROM posts 
WHERE to_tsvector('english', title || ' ' || content) 
  @@ to_tsquery('english', 'rails & activerecord')
ORDER BY rank DESC;

Rails ActiveRecord:

query = 'rails & activerecord'
Post.select("*, ts_rank(to_tsvector('english', title || ' ' || content), to_tsquery('english', ?)) as rank", query)
    .where("to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', ?)", query)
    .order('rank DESC')

Trigram Similarity Search

PostgreSQL SQL:

SELECT *, similarity(title, 'postgresql guide') as sim
FROM posts 
WHERE similarity(title, 'postgresql guide') > 0.3
ORDER BY sim DESC;

Rails ActiveRecord:

search_term = 'postgresql guide'
Post.select("*, similarity(title, ?) as sim", search_term)
    .where("similarity(title, ?) > 0.3", search_term)
    .order('sim DESC')

Creating Search Indexes

# Migration for full-text search
class AddSearchIndexToPosts < ActiveRecord::Migration[7.0]
  def up
    enable_extension "pg_trgm"
    
    # GIN index for full-text search
    add_index :posts, "to_tsvector('english', title || ' ' || content)", 
              using: :gin, name: 'posts_search_idx'
    
    # Trigram index for similarity search
    add_index :posts, :title, using: :gin, opclass: :gin_trgm_ops
  end
  
  def down
    remove_index :posts, name: 'posts_search_idx'
    remove_index :posts, :title
  end
end

6. Window Functions

Common Window Function Patterns

ROW_NUMBER and Ranking

PostgreSQL SQL:

SELECT *,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as row_num,
  RANK() OVER (ORDER BY score DESC) as rank
FROM posts;

Rails ActiveRecord:

Post.select("*,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as row_num,
  RANK() OVER (ORDER BY score DESC) as rank")

LAG and LEAD

PostgreSQL SQL:

SELECT *,
  LAG(score, 1) OVER (ORDER BY created_at) as previous_score,
  LEAD(score, 1) OVER (ORDER BY created_at) as next_score
FROM posts
ORDER BY created_at;

Rails ActiveRecord:

Post.select("*,
  LAG(score, 1) OVER (ORDER BY created_at) as previous_score,
  LEAD(score, 1) OVER (ORDER BY created_at) as next_score")
    .order(:created_at)

Running Totals

PostgreSQL SQL:

SELECT *,
  SUM(amount) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING) as running_total,
  AVG(amount) OVER (ORDER BY created_at ROWS 6 PRECEDING) as moving_avg
FROM orders;

Rails ActiveRecord:

Order.select("*,
  SUM(amount) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING) as running_total,
  AVG(amount) OVER (ORDER BY created_at ROWS 6 PRECEDING) as moving_avg")

Window Function Use Cases

  • • Finding the latest record per group (ROW_NUMBER)
  • • Calculating running totals and moving averages
  • • Ranking and percentile calculations
  • • Comparing current row with previous/next rows

7. Advanced PostgreSQL Features

Common Table Expressions (CTEs)

Recursive CTEs

PostgreSQL SQL:

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 as level
  FROM categories 
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Rails ActiveRecord:

Category.find_by_sql("
  WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 as level
    FROM categories 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
  )
  SELECT * FROM category_tree
")

UPSERT (ON CONFLICT)

PostgreSQL SQL:

INSERT INTO users (email, name) 
VALUES ('john@example.com', 'John Doe')
ON CONFLICT (email) 
DO UPDATE SET 
  name = EXCLUDED.name,
  updated_at = CURRENT_TIMESTAMP;

Rails ActiveRecord:

# Rails 6+
User.upsert(
  { email: 'john@example.com', name: 'John Doe' },
  unique_by: :email
)

# Multiple records
User.upsert_all([
  { email: 'john@example.com', name: 'John Doe' },
  { email: 'jane@example.com', name: 'Jane Doe' }
], unique_by: :email)

8. Performance Considerations

PostgreSQL-Specific Optimizations

Index Types

# Migration with PostgreSQL-specific indexes
class OptimizePostgresIndexes < ActiveRecord::Migration[7.0]
  def change
    # B-tree index (default)
    add_index :users, :email
    
    # Hash index for equality operations
    add_index :users, :status, using: :hash
    
    # GIN index for JSONB and arrays
    add_index :users, :metadata, using: :gin
    add_index :users, :tags, using: :gin
    
    # GiST index for full-text search
    add_index :posts, "to_tsvector('english', title || ' ' || content)", using: :gist
    
    # Partial index
    add_index :users, :email, where: "active = true"
    
    # Composite index
    add_index :posts, [:user_id, :created_at]
  end
end

Query Analysis

# Use EXPLAIN to analyze queries
User.where(active: true).explain

# More detailed analysis
User.where(active: true).explain(:analyze, :buffers)

# In Rails console
ActiveRecord::Base.logger = Logger.new(STDOUT) # See all SQL queries

Performance Tips

  • • Use JSONB instead of JSON for better query performance
  • • Add appropriate indexes for your query patterns
  • • Use partial indexes for filtered queries
  • • Consider materialized views for complex aggregations
  • • Use connection pooling for high-traffic applications

9. Migration Tips

Step-by-Step Migration Process

1. Audit Your Current Queries

# Find all raw SQL in your codebase
grep -r "find_by_sql\|execute\|connection\.select" app/
grep -r "WHERE.*=" app/ | grep -v ".rb~"

2. Start with Simple Conversions

# Convert basic SELECT statements first
# SQL: SELECT * FROM users WHERE active = true
User.where(active: true)

# Then move to more complex queries
# SQL: SELECT users.*, COUNT(posts.id) as post_count FROM users LEFT JOIN posts ON posts.user_id = users.id GROUP BY users.id
User.left_joins(:posts).group(:id).select('users.*, COUNT(posts.id) as post_count')

3. Test Performance

# Benchmark your conversions
require 'benchmark'

Benchmark.bm do |x|
  x.report("Raw SQL:") { User.find_by_sql("SELECT * FROM users WHERE active = true") }
  x.report("ActiveRecord:") { User.where(active: true).to_a }
end

Migration Checklist

  • • ✅ Update database.yml to use PostgreSQL adapter
  • • ✅ Enable required PostgreSQL extensions
  • • ✅ Convert raw SQL queries to ActiveRecord methods
  • • ✅ Add appropriate indexes for query patterns
  • • ✅ Test query performance with realistic data
  • • ✅ Update any database-specific configuration
  • • ✅ Train team on PostgreSQL-specific features

Continue Learning

Related Guides

Tools