PostgreSQL and MySQL Database Optimization

In most Rails applications, the database is where performance is won or lost. We work with PostgreSQL and MySQL/MariaDB every day, and we know both sides: how ActiveRecord turns into SQL, and how the database executes it.

Signs your database needs attention

• Database CPU or I/O constantly close to its limit
• Queries that were fast a year ago and are slow now
• Lock waits and deadlocks during peak hours or deployments
• Migrations on large tables that you are afraid to run
• Disk usage that grows faster than your data

How we work

• Collect the expensive queries from pg_stat_statements, the MySQL performance schema or the slow query log
• Rank them by total time, so we fix what matters most first
• Read the query plans with EXPLAIN ANALYZE to see what the database actually does
• Verify every change against production-like data before it ships

What we optimize

Queries and indices

• Missing indices, and the unused or duplicate ones that slow down every write
• Composite, partial and covering indices that match how your application really queries
• Rewriting slow queries - in ActiveRecord or in SQL - and checking the result with the query plan
• Pagination, counting and reporting queries on large tables

Schema and operations

• Schema changes and data migrations on large tables without downtime
• Locking problems and long-running transactions
• Table and index bloat, autovacuum and statistics on PostgreSQL
• Database configuration for your workload - memory, connections and connection pooling

From experience

Database work often goes hand in hand with a performance analysis of the application itself, or with a migration from MySQL/MariaDB to PostgreSQL.

Summary

A few well-chosen indices and rewritten queries often make a bigger difference than a larger database server. We find them with data, ship them safely, and show you the numbers before and after.

Is your database slowing your application down? Get in touch.