---
title: "PostgreSQL and MySQL Database Optimization"
description: "We optimize PostgreSQL and MySQL/MariaDB databases behind Ruby on Rails applications - slow queries, indices, locking, schema changes and configuration."
url: "https://qameta.com/database-optimization/"
---

# 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](https://qameta.com/ruby-on-rails-performance-analysis/) of the
application itself, or with a
[migration from MySQL/MariaDB to PostgreSQL](https://qameta.com/mysql-to-postgresql-migration/).

## 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](mailto:info@qameta.com).