The translation of the article is specially prepared for the students of the course Database Management. Are you interested in developing in this area? We invite you to our Open Day, where we provide detailed information about the program, the features of the online format, the competencies, and the career prospects awaiting graduates after their studies.

PostgreSQL and Write Consistency Settings for Each Specific Connection
At Compose, we deal with many databases, which allows us to closely explore their functionalities and shortcomings. As we learn to appreciate the functional features of new databases, we sometimes begin to think about how great it would be if such features existed in more mature tools we have been working with for a long time. One of the new features we wanted to see in PostgreSQL was customizable write consistency per connection across the entire cluster. And as it turns out, we already have it, and today we want to share with you information on how you can utilize it.

Why do I need this?

The behavior of the cluster depends on your application. Take, for example, a billing application. You will need 100% consistency in the cluster, so you will have to enable synchronous commits to ensure your database waits for all changes to be made. However, if your application is a rapidly evolving social network, you would likely prefer quick responses over absolute consistency. To achieve this, you can use asynchronous commits in your cluster.

Meet the Trade-off

You will have to strike a balance between data consistency and performance. PostgreSQL moves away from consistency since the default configuration ends up being predictable and free from unexpected surprises. Now, let us get acquainted with the trade-offs.

Trade-off 1: Performance

If a PostgreSQL cluster does not require consistency, it can operate asynchronously. Writes are made to the cluster leader, and updates will be sent to its replicas after a few milliseconds. When the PostgreSQL cluster requires consistency, it must operate synchronously. The write will occur at the cluster leader, which will send updates to replicas and wait for confirmation that each replica has performed the write before sending confirmation to the client that initiated the write, indicating that it was successful. The practical difference between these approaches is that the asynchronous method requires two network hops, while the synchronous method requires four.

Compromise 2: Consistency

The outcome in the event of a leader failure in these two approaches will also be different. If the operation is performed asynchronously, not all writes will be recorded by the replicas in the event of such an error. How much will be lost? It depends on the application itself and the efficiency of replication. The Compose replication prevents a replica from becoming a leader if its information is 1 MB less than that of the leader, meaning that up to 1 MB of writes could potentially be lost during asynchronous operation.

This does not happen in synchronous mode. If the leader fails, all replicas are updated, since any write acknowledged by the leader must also be acknowledged by the replicas. Here it is — consistency.

Synchronous behavior makes sense to use in a billing application, where consistency has a clear advantage in finding a compromise between consistency and performance. The most important aspect for such an application is valid data. Now think about a social network, where the main task is to keep the user engaged by responding to requests as quickly as possible. In this case, performance with fewer network hops and less waiting for commits will be prioritized. However, the compromise between performance and consistency is not the only one to consider.

Compromise 3: Failures

It is crucial to understand how the cluster behaves during a failure. Consider a situation where one or more replicas fail. When commits are processed asynchronously, the leader will continue to operate, meaning it will accept and process records without waiting for missing replicas. When the replicas return to the cluster, they catch up to the leader. In synchronous replication, if the replicas do not respond, the leader has no choice but to continue waiting for the commit confirmation until the replica returns to the cluster and can accept and confirm the record.

One connection per transaction?

Each application requires a specific type of consistency and performance combination. Unless, of course, it is our billing application, which we imagine to be fully consistent, or our almost ephemeral social networking application. In all other cases, there will be times when some operations need to be synchronous while others can be asynchronous. You may not want the system to wait for a message sent in chat to be committed, but if a payment is being processed in the same application, waiting will be necessary.

All these decisions are, of course, made by the application developer. The right decisions on when to apply one approach or another will help maximize the cluster's potential. It is important for the developer to be able to switch between them at the SQL level for connections and transactions.

Ensuring control in practice

By default, PostgreSQL ensures consistency. This is controlled by the server parameter synchronous_commit. By default, it is in the onposition, but it has three other options: local, remote_write or off.

Setting the parameter to off stops all synchronous commits, even in the local system. The local parameter defines synchronous mode for the local system, but records in replicas are made asynchronously. Remote_write goes even further: records in replicas are made asynchronously but are returned when the replica accepts the record but has not written it to disk.

Considering the available options, we choose the behavior and, keeping in mind that on are synchronous writes, we will choose local for asynchronous network commits while keeping local commits synchronous.

Now, we will tell you how to set this up in a flash, but imagine that we installed synchronous_commit downward API support (simultaneously with this in local for the server. We wondered if the parameter could be changed synchronous_commit on the fly, and it turned out that not only can it be done, but there are actually two ways to do it. The first is to set your session as follows:

SET SESSION synchronous_commit TO ON;  
// Your writes go here

All subsequent writes in the session will confirm write operations to the replicas before returning a positive result to the connected client, unless, of course, you change the setting synchronous_commit again. You can omit the part SESSION in the command, as it will be at the default value.

The second method is good when you just want to ensure that you're getting synchronous replication for a single transaction. In many 'NoSQL' databases, the concept of transactions doesn't exist, but it does in PostgreSQL. In this case, you start a transaction and then set synchronous_commit downward API support (simultaneously with this in on before executing the write for the transaction. COMMIT will commit the transaction using whatever value of the parameter synchronous_commit, which was set at that moment, although it is best to set the variable in advance to make sure other developers understand that the writes are not asynchronous.

BEGIN;  
SET LOCAL synchronous_commit TO ON;  
// Your writes go here
COMMIT;  

All transaction commits will now be confirmed as written to the replicas before the database returns a positive response to the connected client.

Configuring PostgreSQL

Before this, we envisioned a PostgreSQL system with synchronous_commit, set up in local. To make this work on the server side, you will need to set two server configuration parameters. Another parameter synchronous_standby_names will come into play when synchronous_commit is in on. It defines which replicas are eligible for synchronous commits, and we will set it to *, which will mean involving all replicas. These values are usually configured in the configuration file by adding:

synchronous_commit = local  
synchronous_standby_names='*'

By setting the parameter synchronous_commit to local, we create a system in which local disks remain synchronous, but commits to network replicas are asynchronous by default, unless, of course, we decide to make those commits synchronous, as shown above.

If you have been following the development of the Governor project, you may have noticed some recent changes (1, 2), which allowed users to test these options and control their consistency.

A few more words…

Just a week ago, I would have told you that it is impossible to fine-tune PostgreSQL to that extent. That was when Kurt, a member of the Compose platform team, insisted that such a possibility exists. He quieted my objections and found in the PostgreSQL documentation the following:

This parameter can be changed at any time. The behavior for any transaction is determined by the setting that is active at the commit. Thus, it is possible and useful for some transactions to commit synchronously, while for others — asynchronously. For example, to make one multistatement transaction commit asynchronously, when the default setting is the opposite, set SET LOCAL synchronous_commit TO OFF in the transaction.

With this minor modification in the configuration file, we have given users the ability to control their consistency and performance.

Source: habr.com