Horizontal Scaling
Overview
- Converting from Sqlite3 to PostgreSQL
- Other requirements: Load Balancer, SSL Certificate
- Optimizing for PostgreSQL
As your needs increase, you can move to bigger and bigger servers. Source At some point, you may need more, or simply want resiliency, or perhaps want to serve requests closer to your users.
Sqlite3 is a wonderful database, and the Rails team invested a lot of effort into making Sqlite ready for production with Rails 8. You can read (or watch) more in Stephen Margheim's talk: Supercharge the One Person Framework with SQLite: Rails World 2024. Read more A quote from that presentation:
The next detail to be considerate of is that SQLite is built to work best on a single machine.
This generally means that when you are ready to make the move from a single server to multiple machines, you will need to convert from Sqlite3 to a database like PostgreSQL or MySQL. For this, unless you are the type to change your own oil in your vehicles, you will want a managed database solution. Some of the things a managed database will take care of for you:
- Provisioning the right cluster configuration for you
- Scaling storage and memory resources
- Upgrading Postgres versions and security patches
- Developing a database backup and restoration plan
- Monitoring and alerts
- Recovering from outages
- Global replication
- Configuration tuning
- Advanced customization
A few providers to choose from:
- Amazon RDS for PostgreSQL
- Azure Database for PostgreSQL
- Crunchy Bridge Managed Postgres
- Digital Ocean Managed Postgres
- Fly.io Managed Postgres
- Google Cloud SQL for PostgreSQL
- Heroku Managed Data Services
- Neon Serverless Postgres
- PlanetScale Serverless MySQL
- Supabase Postgres
Following are instructions for converting to PostgreSQL, a popular and widely supported open source database.
- Install the
pg
gem with thebundle add pg
command. This generally requires you to have first installed PostgreSQL on your development machine, even if you aren't going to use PostgreSQL in development. MacOS users may want to usebrew install postgresql@17
, Debian users will wantsudo apt install postgresql
. - In your
Dockerfile
, replacesqlite3
withlibpq-dev postgresql-client
. Again, you can use dockerfile-rails to take care of this chore for you. - The Active Record guides recommend updating
config/database.yml
, but it is generally easier to set aDATABASE_URL
secret. More info If you would prefer to follow the recommendation, Rails providesrails db:system:change --to=postgresql
, which can make the necessary changes for you, but be aware that this will convert your application to using PostgreSQL not only in production, but also in development.
There is a tool named pgloader which you can use to copy your Sqlite3 database to PostgreSQL.
Once you define a second application machine, you will need to add a load balancer to route requests between machines. This is generally a paid feature. Hetzner Load Balancer You will also need an SSL certificate for your load balancer, as Kamal's SSL support is only for one server.
As PostgreSQL requests tend to have a higher latency per request than Sqlite3, you will likely need to tune your application. Look for places where you execute a query and iterate over the results, and inside the iteration you traverse over relationships—this will generally require multiple queries. You can avoid this using includes, preloads, or eager_load.