Why use logical replication?
Postgres logical replication allows granular scaling which can be useful in scenarios such as:
- Using separate databases for multiple tenants and regions
- Using a separate database for read-intensive applications like Analytics or Data Warehouses
- Replicate specific tables for another service
Before learning how to setup database replicas in with Rails, check my previous post/video about Postgres logical replication:
https://www.youtube.com/@DoCodeDo
Our scenario
Our goal is to improve our Rails app performance by having a separate database for read operations.
We will be using Postgres logical replication that listen to changes for all tables (including new ones).
That introduces some problems due data conflict in Rails metadata tables: schema_migrations and ar_internal_metadata.
So, In addition to setting up Postgres as explained in the previous post, we will:
- Configure Rails to use separate databases for write and read
- Configure Rails to handle table schemas changes in both databases
If you want the code presented in this article, you get it here:
https://github.com/viniciusoyama/rails-postgres-logical-replication-example
Configuring rails to use separate databases
First, we need to configure Rails to use separate databases for writing and reading.
Connection setup
Edit your config/database.yml as following
default: &default
adapter: "postgresql"
encoding: "unicode"
database: "<%= ENV.fetch("POSTGRES_DB") { "example_app" } %>"
username: "<%= ENV.fetch("POSTGRES_USER") { "user" } %>"
password: "<%= ENV.fetch("POSTGRES_PASSWORD") { "secret" } %>"
port: "<%= ENV.fetch("POSTGRES_PORT") { 5432 } %>"
pool: "<%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>"
development:
main:
<<: *default
host: postgres-main
database: <%= ENV.fetch("POSTGRES_DB") { "example_app" } %>_development
read:
<<: *default
host: postgres-replica
database: <%= ENV.fetch("POSTGRES_DB") { "example_app" } %>_development_replica
test:
main:
<<: *default
database: <%= ENV.fetch("POSTGRES_DB") { "example_app" } %>_test
read:
<<: *default
database: <%= ENV.fetch("POSTGRES_DB") { "example_app" } %>_test
production:
main:
url: <%= ENV.fetch("MAIN_DATABASE_URL", "") %>
read:
url: <%= ENV.fetch("READ_DATABASE_URL", "") %>Key notes:
- We named the main database as
mainand our read database asread. I know. SHOCKING… - In development/test environment we are using different Postgres services
- In production environment we are using different URLs for each database
Creating the databases
After configuring the database.yml we use Rails to create the databases on both instances (write and read):
docker compose exec web bundle exec rails db:create:main
docker compose exec web bundle exec rails db:create:read
For now on, when running any rails db command, we will specify which database (main or read) the command should target.
Managing schema migrations
Remember that logical replication doesn’t copy the schema.
Since the schema_migrations table will be replicated from the main database to the read database, how do we know which migrations have already run on the read database?
Our solution is to configure our Rails to use a different table to track migrations for the read database.
Setup rails to use different schema migration table
Lets create a new initializer called logical_replication.rb
if ENV['SCHEMA_MIGRATIONS_TABLE_NAME'].present?
Rails.application.configure do
config.active_record.schema_migrations_table_name = ENV['SCHEMA_MIGRATIONS_TABLE_NAME']
config.active_record.internal_metadata_table_name = 'custom_ar_internal_metadata'
end
end
Now, if we set a SCHEMA_MIGRATIONS_TABLE_NAME environment variable it will use its value as schema_migrations_table_name. We are also changing the ar_internal_metadata table name so that the table on the read database doesn’t conflict with the table on the main database
If you want to know more about the ar_internal_metadata check this quick post:
https://vinioyama.com/blog/ruby-on-rails-what-is-ar_internal_metadata-metada-table/
Now let’s run the migrations on each database:
docker compose exec -e SCHEMA_MIGRATIONS_TABLE_NAME=replica_schema_migrations web bundle exec rails db:migrate:read
docker compose exec web bundle exec rails db:migrate:main
After executing the two commands we will have a schema_migrations and ar_internal_metadata tables on the main database and a replica_schema_migrations and custom_ar_internal_metadata on the read database.
Note that the read database doesn’t have a schema_migrations and a ar_internal_metadata (we will create them later)
Creating the logical replication
Let’s create the publication on the main database for all tables:
docker compose exec postgres-main psql -U user -d example_app_development -c "CREATE PUBLICATION all_changes_pub FOR TABLES IN SCHEMA public;"
Now we can create the subscription on the read database.
Since the main database is publishing changes for all tables, we need to create the schema_migrations table on the read database. Otherwise the replication will not work.
We need schema_migrations on the read database to BE EMPTY so it can be filled by our subscription.
Create the schema_migrations and ar_internal_metadata tables on read database:
docker compose exec postgres-replica psql -U user -d example_app_development_replica -c "
CREATE TABLE schema_migrations (LIKE replica_schema_migrations INCLUDING ALL);"
docker compose exec postgres-replica psql -U user -d example_app_development_replica -c "
CREATE TABLE ar_internal_metadata (LIKE custom_ar_internal_metadata INCLUDING ALL);"
Create the subscription on the read database:
docker compose exec postgres-replica psql -U user -d example_app_development_replica -c "
CREATE SUBSCRIPTION replica_sub
CONNECTION 'host=postgres-main
dbname=example_app_development
user=user
password=secret'
PUBLICATION all_changes_pub;
;"
Now we’re almost done. We just need to configure Rails to use different databases depending on our request method (so it automatically knows if it should use the read or the write database).
Setup active record read and write databases
Edit your app/model/application_record.rb with:
class ApplicationRecord < ActiveRecord::Base
primary_abstract_class
connects_to database: { writing: :main, reading: :main_replica }
end
Automatically switch between read/write databases
As explained here: https://guides.rubyonrails.org/active_record_multiple_databases.html#activating-automatic-role-switching
Automatic switching allows the application to switch from the writer to replica or replica to writer based on the HTTP verb and whether there was a recent write by the requesting user.
That’s why we have an initializer multi_db.rb with:
Rails.application.configure do
config.active_record.database_selector = { delay: 2.seconds }
config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session
end
Testing with data
Let’s insert some data and check if it is replicated:
Our seed.rb
pedro = Author.create!(name: 'Pedro')
paulo = Author.create!(name: 'Paulo')
maria = Author.create!(name: 'maria')
pedro_movie1 = Movie.create!(name: "Pedro Movie 1", author: pedro)
pedro_movie2 = Movie.create!(name: "Pedro Movie 2", author: pedro)
maria_movie1 = Movie.create!(name: "Maria Movie 1", author: maria)
Seed the database with the following command:
docker compose exec web bundle exec rails db:seed
On main database:
docker compose exec postgres-main psql -U user -d example_app_development -c "SELECT * FROM authors; SELECT * FROM MOVIES;"
id | name | created_at | updated_at
----+-------+----------------------------+----------------------------
1 | Pedro | 2024-09-13 21:38:46.295779 | 2024-09-13 21:38:46.295779
2 | Paulo | 2024-09-13 21:38:46.299138 | 2024-09-13 21:38:46.299138
3 | maria | 2024-09-13 21:38:46.30177 | 2024-09-13 21:38:46.30177
(3 rows)
id | name | author_id | created_at | updated_at
----+---------------+-----------+----------------------------+----------------------------
1 | Pedro Movie 1 | 1 | 2024-09-13 21:38:46.315274 | 2024-09-13 21:38:46.315274
2 | Pedro Movie 2 | 1 | 2024-09-13 21:38:46.317652 | 2024-09-13 21:38:46.317652
3 | Maria Movie 1 | 3 | 2024-09-13 21:38:46.31926 | 2024-09-13 21:38:46.31926
(3 rows)
On read database:
docker compose exec postgres-replica psql -U user -d example_app_development_replica -c "SELECT * FROM authors; SELECT * FROM MOVIES;"
id | name | created_at | updated_at
----+-------+----------------------------+----------------------------
1 | Pedro | 2024-09-13 21:38:46.295779 | 2024-09-13 21:38:46.295779
2 | Paulo | 2024-09-13 21:38:46.299138 | 2024-09-13 21:38:46.299138
3 | maria | 2024-09-13 21:38:46.30177 | 2024-09-13 21:38:46.30177
(3 rows)
id | name | author_id | created_at | updated_at
----+---------------+-----------+----------------------------+----------------------------
1 | Pedro Movie 1 | 1 | 2024-09-13 21:38:46.315274 | 2024-09-13 21:38:46.315274
2 | Pedro Movie 2 | 1 | 2024-09-13 21:38:46.317652 | 2024-09-13 21:38:46.317652
3 | Maria Movie 1 | 3 | 2024-09-13 21:38:46.31926 | 2024-09-13 21:38:46.31926
(3 rows)
Question: what happens when we create a new table?
When altering the schema, it’s necessary to refresh the database publication.
Let’s test it by creating a new CRUD for a songs table:
docker compose exec web bin/rails g scaffold Song name:string
Migrate on read database:
docker compose exec -e SCHEMA_MIGRATIONS_TABLE_NAME=replica_schema_migrations web bundle exec rails db:migrate:read
Migrate on main database:
docker compose exec web bundle exec rails db:migrate:main
Let’s create a new song on the main database and see what happens:
docker compose exec web bundle exec rails r "Song.create(name: 'New song')"
Check the songs table on the main database:
docker compose exec postgres-main psql -U user -d example_app_development -c "SELECT * FROM songs;"
id | name | created_at | updated_at
----+---------------+----------------------------+----------------------------
1 | New song | 2024-09-11 22:56:57.291918 | 2024-09-11 22:56:57.291918
(1 row)Check the songs table on the read database:
docker compose exec postgres-replica psql -U user -d example_app_development_replica -c "SELECT * FROM songs;"
id | name | created_at | updated_at
----+------+------------+------------
(0 rows)After changing our schema, we need to refresh the publication. You can do this running the command below:
docker compose exec postgres-replica psql -U user -d example_app_development_replica -c "ALTER SUBSCRIPTION replica_sub REFRESH PUBLICATION;"
Now you can check the read database:
docker compose exec postgres-replica psql -U user -d example_app_development_replica -c "SELECT * FROM songs;"
id | name | created_at | updated_at
----+---------------+----------------------------+----------------------------
1 | New song | 2024-09-11 22:56:57.291918 | 2024-09-11 22:56:57.291918
(1 row)Everything is working fine and now you can start playing with this setup!
Conclusion
So far we’ve learned:
- How to setup Postgres logical replication
- How to configure Rails to work with multiple databases
- How to dynamically change the schema migrations table on Rails and ar_internal_metadata
- How to refresh our publications when our schema changes
Postgres logical replication paired with Rails provides a easy way to scale our applications.
Not only does it allow you to handle read-heavy operations more efficiently, but it also enables architectural flexibility that can improve both performance, maintainability and also reduce costs.
Subscribe to my newsletter!
I share content about Software Development & Architecture, Entrepreneurship and Lifelong Learning
