Split read/write requests over different read/write datasources
There is no automatic way to tell a data modifying query from a reading one.

Imagine the following SQL statement performing a function call:

SELECT delete_all();


There is no way of telling what the function does without analyzing its source code.

So you have to make the decision in your code that (hopefully) knows which queries modify data and which don't.



Using streaming replication in PostgreSQL to off-load read workload to standby servers works, but there is one caveat:

By default, replication is asynchronous, so there is a delay between the transaction commit on the primary server and the time the changes become visible on the standby. Your application has to be able to deal with this (or perform the queries that cannot on the replication primary).

The only way around that would be to use synchronous replication with synchronous_commit = remote_apply, but that will drastically increase the time a transaction commit takes, because it has to wait for the change to be replayed on a standby before it can report success.