Sitemap

How to set up DB read replicas on Symfony 6 using Doctrine

4 min readAug 20, 2023

In the world of modern web applications, scalability and performance are paramount. As your application grows, handling an increasing number of database queries becomes a critical concern, which is where the concept of read/write replicas comes into play, as by distributing database operations across multiple database servers, you can improve response times and effectively manage heavy traffic loads. In this post, we’ll delve into how the implementation of read/write replicas on a Symfony project can be tackled using the Doctrine bundle.

Understanding the concept

Before diving into the technical details, let’s grasp the concept of read/write replicas. In a typical web application, there are two types of database operations: read operations (SELECT statements) and write operations (INSERT, UPDATE, DELETE statements). Instead of burdening a single database server with both types of operations, read replicas allow us to offload read queries onto separate database servers, thus distributing the load and improving overall performance. Furthermore, it may be useful to have read-only instances which serve secondary purposes (e.g. research) rather than primary ones.

Implementation

The steps below are for a Symfony 6.2 project, using doctrine/doctrine-bundle: ^2.7. For other minor versions of these packages (e.g. Symfony 6.4 and doctrine-bundle 2.10), the process should be close to identical.

Lets start with doctrine configuration (config/packages/doctrine.yaml):

when@prod:
doctrine:
dbal:
default_connection: default
connections:
default:
url: '%env(resolve:DATABASE_URL)%'
driver: pdo_pgsql
server_version: 15
replicas:
replica1:
url: '%env(resolve:DATABASE_RO_URL)%'

As you can see, in this example there is a single replica (replica1) added, but you can add more (Doctrine randomly picks one) depending on your use-case and database load. What is important to mention is that even though the replica instance will be preferred for read operations, your primary instance will also execute them if it was picked before during the lifetime of the connection (serving the request). If you wish to dedicate primary instance for write operations only, default connection in doctrine.yaml should have an additional setting of keep_replica: true (to keep an actual replica rather than using primary instance as a replica) and after executing any write operations you would need to manually ensure connection to the replica:

$connection = $this->getEntityManager()->getConnection();
if ($connection instanceof PrimaryReadReplicaConnection) {
$connection->ensureConnectedToReplica();
}

Pay attention to the instanceof condition — it is necessary to ensure that you are forcing primary connection only in case of production environment, where replica is actually configured. In development environment, your $connection most likely will be an instance of Doctrine\DBAL\Connection .

Furthermore, in the doctrine.yaml configuration DATABASE_URL refers to the primary instance of your database, and DATABASE_RO_URL refers to the read-only replica — make sure to have these variables defined in your env file:

DATABASE_URL="postgresql://user:password@read-write-database-instance:5432/table?charset=utf8"
DATABASE_RO_URL="postgresql://user:password@read-database-instance:5432/table?charset=utf8"

In fairly simple applications this configuration should be sufficient and using Doctrine’s Repository or QueryBuilder methods it will work out of the box. However, sometimes you may need to opt for SQL queries, where you care a lot about performance or just prefer not to waste time implementing your own complex DQL functions, since you don’t intend switching to different database engine and are comfortable with sacrificing DB-agnostic querying logic. When executing SQL queries, you need to be aware that executeQuery is a method specifically for READ operations only. The code snipped below will not work, because Doctrine will pick read-only replica and end up proxying DB engine errors stating that it cannot execute write operations.

$sql = <<<SQL
UPDATE smart_contract SET uaw = 0
FROM dapp_chain dc
WHERE dc.id = smart_contract.dapp_chain_id AND dc.chain_id = :chainId;
SQL;

$this->getEntityManager()->getConnection()->executeQuery($sql, [
'chainId' => $chainId,
]);

In order to overcome this problem, there are at least 2 potential solutions:

  1. (Recommended) Prefer executeStatement over executeQuery for any SQL statement that changes/updates any record state in the database. Using executeStatement, insert, delete and other write or transactional (beginTransaction, commit, rollback) operations enforces Doctrine to pick primary connection.
  2. In some cases, you may want to manually enforce Doctrine Connection to use the primary instance. The updated example script could look like this:
$sql = <<<SQL
UPDATE smart_contract SET uaw = 0
FROM dapp_chain dc
WHERE dc.id = smart_contract.dapp_chain_id AND dc.chain_id = :chainId;
SQL;

$connection = $this->getEntityManager()->getConnection();
if ($connection instanceof PrimaryReadReplicaConnection) {
$connection->ensureConnectedToPrimary();
}
$connection->executeQuery($sql, ['chainId' => $chainId]);

Conclusion

In the ever-evolving landscape of web applications, optimizing performance is a continuous journey. The integration of read/write replicas into your Symfony project using the Doctrine bundle can be a game-changer in this pursuit. By strategically distributing the database workload, you can bolster your application’s responsiveness and scalability, ensuring a seamless user experience even as traffic soars.

Have something else to add to this article? Feel free to comment!

Medium Logo
Medium Logo

Sign up to discover human stories that deepen your understanding of the world.

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

Responses (1)

Write a response