Updated 12/27/2024

PostgreSQL Tips For Rails Developers

Practical PostgreSQL techniques and lesser-known features that can make your Rails development more efficient and your applications more performant.

Over the years, I've collected a set of PostgreSQL techniques that have proven invaluable in Rails projects. Some of these are basic navigation commands you'll use daily, while others are more advanced techniques for performance optimization and debugging complex queries.

Whether you're troubleshooting a slow query, exploring your schema, or implementing advanced features, these tips should help you work more effectively with PostgreSQL in your Rails applications.

Getting Started: Console Navigation

Let's start with the essentials you'll use most often:

Connecting to Your Database

The quickest way to connect to your Rails database is with rails dbconsole (or rails db for short). This automatically connects to the database specified in your config/database.yml for the current Rails environment.

If you need to connect manually, use psql -d database_name or check your database configuration with:

psql $(rails runner "puts ActiveRecord::Base.connection_config[:database]")

Essential psql Commands

Once you're in the psql console, these commands will be your daily drivers:

• \l - List all databases
• \c database_name - Connect to a specific database
• \dt - List all tables
• \d table_name - Describe a table's structure
• \di - List all indexes
• \du - List all users/roles
• \q - Quit psql

Performance Analysis and Optimization

Understanding Query Execution with EXPLAIN

When ActiveRecord queries are running slowly, EXPLAIN ANALYZE is your best friend. It shows you exactly how PostgreSQL is executing your query and where time is being spent:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Look for key indicators like:

• Seq Scan - Table scans that might benefit from indexes
• Nested Loop - Join operations that could be optimized
• High cost values - Expensive operations
• Actual time - Real execution time vs. estimates

Identifying Missing Indexes

Find queries that are doing table scans and might benefit from indexes:

SELECT schemaname, tablename, attname, n_distinct, correlation 
FROM pg_stats 
WHERE schemaname = 'public' 
AND n_distinct > 100;

Analyzing Table and Index Sizes

Understanding what's taking up space in your database:

SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public' 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Advanced PostgreSQL Features for Rails

Working with JSONB Data

PostgreSQL's JSONB support is incredibly powerful for Rails applications. You can query JSON data directly:

-- Find users with specific preferences
SELECT * FROM users WHERE preferences->>'theme' = 'dark';

-- Query nested JSON structures
SELECT * FROM orders WHERE details->'shipping'->>'method' = 'express';

-- Use JSON path queries
SELECT * FROM products WHERE features @> '{"color": "red"}';

Full-Text Search

PostgreSQL's built-in full-text search can be more efficient than external search engines for many use cases:

-- Create a full-text search index
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('english', title || ' ' || content));

-- Search for posts
SELECT * FROM posts 
WHERE to_tsvector('english', title || ' ' || content) @@ plainto_tsquery('rails postgresql');

Working with Arrays

PostgreSQL arrays can replace many-to-many relationships in certain scenarios:

-- Find records containing specific array elements
SELECT * FROM articles WHERE tags && ARRAY['ruby', 'rails'];

-- Check if array contains all specified elements
SELECT * FROM articles WHERE tags @> ARRAY['ruby', 'rails'];

Development and Debugging Tools

Finding Slow Queries

Enable query logging to identify performance bottlenecks:

-- Enable slow query logging (requires superuser)
ALTER SYSTEM SET log_min_duration_statement = 1000; -- Log queries taking > 1 second
SELECT pg_reload_conf();

Monitoring Active Connections

See what's currently running on your database:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query 
FROM pg_stat_activity 
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Checking Lock Conflicts

When you have hanging queries, check for lock conflicts:

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Data Maintenance and Cleanup

Vacuum and Analyze

Keep your database healthy with regular maintenance:

-- Manual vacuum for a specific table
VACUUM ANALYZE users;

-- Check when tables were last vacuumed
SELECT schemaname, tablename, last_vacuum, last_autovacuum 
FROM pg_stat_user_tables;

Finding Duplicate Data

Identify potential data quality issues:

-- Find duplicate emails
SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

Advanced Querying Techniques

Window Functions

Window functions can replace complex subqueries and are often more efficient:

-- Rank users by order count
SELECT name, order_count, 
       RANK() OVER (ORDER BY order_count DESC) as rank
FROM (
    SELECT u.name, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name
) ranked_users;

Common Table Expressions (CTEs)

CTEs make complex queries more readable and can improve performance:

-- Recursive CTE for hierarchical data
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 ORDER BY level, name;

Lateral Joins

Lateral joins let you reference earlier tables in the FROM clause:

-- Get each user's 3 most recent orders
SELECT u.name, recent_orders.created_at, recent_orders.total
FROM users u
CROSS JOIN LATERAL (
    SELECT created_at, total
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) recent_orders;

Rails-Specific Tips

Inspecting ActiveRecord Queries

See the actual SQL that ActiveRecord is generating:

# In rails console
User.where(email: '[email protected]').to_sql
# => "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"email\" = '[email protected]'"

Database-Level Constraints

Some validations are better handled at the database level:

-- Add a check constraint
ALTER TABLE orders ADD CONSTRAINT positive_total CHECK (total > 0);

-- Add a unique constraint with custom error handling
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Useful psql Configuration

Make your psql experience more pleasant by adding these to your ~/.psqlrc:

-- Better formatting
\set COMP_KEYWORD_CASE upper
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
\set PROMPT2 '[more] %R > '

-- Useful aliases
\set version 'SELECT version();'
\set extensions 'SELECT * FROM pg_available_extensions;'

Wrapping Up

These techniques have saved me countless hours of debugging and optimization work over the years. PostgreSQL is incredibly powerful, and while ActiveRecord handles most day-to-day database operations beautifully, knowing how to work directly with the database opens up a whole new level of capability.

The key is to start with the basics—get comfortable navigating in psql and understanding query execution plans—then gradually incorporate more advanced techniques as your applications grow in complexity.