2016-12-28

Aiven PostgreSQL read-only replicas

We are happy to announce that we have enabled read-only replica access to all of our PostgreSQL plans that have one or more standby nodes. Utilizing the standby server nodes for read-only queries allows you to scale your database reads by moving some of the load away from the master server node to the otherwise mostly idle replica nodes.

What are master and standby nodes?

PostgreSQL master node is the primary server node that processes SQL queries, makes the necessary changes to the database files on the disk and returns back the results to the client application.

PostgreSQL standby nodes replicate (which is why they are also called "replicas") the changes from the master node and try to maintain an up-to-date copy of the same database files that exists on the master.

Standby nodes are useful for multiple reasons:
  • Having another physical copy of the data in case of hardware/software/network failures
  • Having a standby node typically reduces the data loss window in disaster scenarios
  • Restoring the database back to operation is quick by a controlled failover in case of failures, as the standby is already installed, running and in-sync with the data
  • Standby nodes can be used for read-only queries to reduce the load on the master server

What is the difference between having 0, 1 or even 2 standby nodes?

Aiven offers PostgreSQL plans with different number of standby server nodes in each:
  • Hobbyist and Startup plans have just a single master node and no standby nodes
  • Business plans have one master node and one standby node
  • Premium plans have one master node and two standby nodes
The difference between the plans is primary the behavior during failure scenarios. The are many bad things that can happen to cloud servers (or any server in general): hardware failures, disk system crashes, network failures, power failures, software errors, running our of memory, operator mistakes, fires, floods and so on.

Single node plans are most prone to data loss during failures. For example, if the server power suddenly goes out, some of the latest database changes may not have made it out from the server into backups. The size of the data loss window is dependent on the backup method used.

Single node plans are also the slowest to recover back to operation from failures. When the server virtual machine fails, it takes time to launch a new virtual machine and to restore it from the backups. The restore time can be anything from a couple of minutes to several hours, the primary factor in it being the size of the database that needs to be restored.

Adding a "hot" standby node helps with both of the above issues: the data loss window can be much smaller as the master is streaming out the data changes in real-time to the standby as they happen. The "lag" between the master and standby is typically very low, from tens of bytes to hundreds of bytes of data.

Also recovery from failure is much faster as the standby node is already up and running and just waiting to get the signal to get promoted as the master, so that it can replace the old failed master.

What about having two standby nodes? What is the point in that?

The added value of having a second standby node is that even during recovery from (single-node) failures, there are always two copies of the data on two different nodes. If another failure strikes after a failover when there is just a single master node running, we again risk losing some of the latest changes written to the database. It takes time to rebuild a new standby node and getting it in sync node after a failover when there is a lot of data in the database, and it often makes sense to protect the data over this time period by having another replica. This is especially important when the database size is large and recreating a replacement node for the faulted one can take hours.

Using standby nodes for read-only queries


Standby nodes are also useful for distributing the load away from the master server. In Aiven the replica nodes can be accessed by using the separate "Read-only replica URL" visible in the Aiven web console:



Using the replica URL in a database client application will connect to one of the available replica server nodes. Previously replica node access was only available in our Premium plans (master + two standbys) and now we have enabled it in our Business plans (master + one standby) as well.

So if you have had high CPU usage on the master node of your Startup plan, it may be worthwhile looking into the possibility of increasing your read throughput by using the replica servers for reads. Of course in addition by using a Business plan you'll also make the service have better high availability characteristics by having a standby to fail over to.

A good thing to note is that since the PostgreSQL replication used in Aiven PostgreSQL is asynchronous there is a small replication lag involved. What this means in practice is that if you do an INSERT on the master it takes a while (usually much less than a second) for the change to be propagated to the standby and to visible there.

Replica Usage

To start using your read-replica find its database URL and after that you can connect to it by copying the Read-only replica URL:

$ psql postgres://avnadmin:foo@replica.demopg.demoprj.aivencloud.com:10546/defaultdb?sslmode=require
psql (9.6.1, server 9.6.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)Type "help" for help.

defaultdb=>

After which you can run any read-only query without slowing down the master.

Also while connected, PostgreSQL can tell you whether you're connected to either a master or standby node. To check that out you can run:

defaultdb=> SELECT * FROM pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

If it returns TRUE you're connected to the replica, if it returns FALSE you're connected to the master server.

Try PostgreSQL 9.6 for free in Aiven

Remember that trying Aiven is free: you will receive US$10 worth of free credits at sign-up which you can use to try any of our service plans. The offer works for all of our services: PostgreSQL, Redis, InfluxDB, Grafana, Elasticsearch and Kafka!

Go to https://aiven.io/ to get started!

Cheers,
Team Aiven

2016-12-21

Aiven PostgreSQL connection pooling

We're happy to announce that Aiven PostgreSQL now has support for connection pools. Connection pooling allow you to maintain very large numbers of connections to a database while keeping the server resource usage low.

Aiven PostgreSQL connection pooling utilizes PGBouncer for managing the database connection and each pool can handle up to 5000 database client connections. Unlike when connecting directly to the PostgreSQL server, each client connection does not require a separate backend process on the server. PGBouncer automatically interleaves the client queries and only uses a limited number of actual backend connections, leading to lower resource usage on the server and better total performance.

Why connection pooling?


Eventually a high number of backend connections becomes a problem with PostgreSQL as the resource cost per connection is quite high due to the way PostgreSQL manages client connections. PostgreSQL creates a separate backend process for each connection and the unnecessary memory usage caused by the processes will start hurting the total throughput of the system at some point. Also, if each connection is very active, the performance can be affected by the high number of parallel executing tasks.

It makes sense to have enough connections so that each CPU core on the server has something to do (each connection can only utilize a single CPU core [1]), but a hundred connections per CPU core may be too much. All this is workload specific, but often a good number of connections to have is in the ballpark of 3-5 times the CPU core count.

[1] PostgreSQL 9.6 introduced limited parallelization support for running queries in parallel on multiple CPU cores.

Without a connection pooler the database connections are handled directly by PostgreSQL backend processes, one process per connection:





Adding a PGBouncer pooler that utilizes fewer backend connections frees up server resources for more important uses, such as disk caching:




Many frameworks and libraries (ORMs, Django, Rails, etc.) support client-side pooling, which solves much the same problem. However, when there are many distributed applications or devices accessing the same database, a client-side solution is not enough.

Connection pooling modes


Aiven PostgreSQL supports three different operational pool modes: "session", "transaction" and "statement".

  • The "session" pooling mode means that once a client connection is granted access to a PostgreSQL server-side connection, it can hold it until the client disconnects from the pooler. After this the server connection will be returned back into the connection pooler's free connection list to wait for its next client connection. Client connections will be accepted (at TCP level), but their queries will only proceed once another client disconnects and frees up its backend connection back into the pool. This mode can be helpful in some cases for providing a wait queue for incoming connections while keeping the server memory usage low, but has limited usefulness under most common scenarios due to the slow recycling of the backend connections.
  • The "transaction" pooling mode on the other hand allows each client connection to take their turn in using a backend connection for the duration of a single transaction. After the transaction is committed, the backend connection is returned back into the pool and the next waiting client connection gets to reuse the same connection immediately. In practise this provides quick response times for queries as long as the typical transaction execution times are not excessively long. This is the most commonly used PGBouncer mode and also the Aiven PostgreSQL default pooling mode.
  • The third operational pooling mode is "statement" and it is similar to the "transaction" pool mode, except that instead of allowing a full transaction to be run, it cycles the server side connections after each and every database statement (SELECT, INSERT, UPDATE, DELETE statements, etc.) Transactions containing multiple SQL statements are not allowed in this mode. This mode is sometimes used for example when running specialized sharding front-end proxies.

How to get started with Aiven PostgreSQL connection pooling


First you need an Aiven PostgreSQL service, for the purposes of this tutorial we assume you already have created one. A quick Getting Started guide is available that walks you through the service creation part.

This the overview page for our PostgreSQL service in the Aiven web console. You can connect directly to the PostgreSQL server using the settings described next to "Connection parameters" and "Service URL", but note that these connections will not utilize PGBouncer pooling.



Clicking the "Pools" tab opens a list of PGBouncer connection pools defined for the service. Since this service was launched, there are no pools defined yet:

 
 


To add a new pool click on the "Add pool" button:


The pool settings are:
  • Pool name: Allows you to name your connection pool. This will also become the "database" or "dbname" connection parameter for your pooled client connections.
  • Database: Allows you to choose which database to connect to. Each pool can only connect to a single database.
  • Username: Selects which database username to use when connecting to the backend database.
  • Pool mode: Refers to the pooling mode descried in more detail earlier in this article.
  • Pool size: How many PostgreSQL server connections can this pool use at a time.

For the purposes of this tutorial we'll name the pool as "mypool" and set the pool size as 1 and the pool mode as "statement". Confirming the settings by clicking "Add pool" will immediately create the pool and the pool list is updated:


Clicking the "Info" button next to the pool information shows you the database connection settings for this pool. Note that PGBouncer pools are available under a different port number from the regular unpooled PostgreSQL server port. Both pooled and unpooled connections can be used at the same time.



Verifying the connection pool


We can use the psql command-line client to verify that the pooling works as supposed:

From terminal #1:

$ psql <pool-uri>

From terminal #2:

$ psql <pool-uri>
Now we have two open client connections to the PGBouncer pooler. Let's verify that each connection is able access the database:

Terminal #1:

mypool=> SELECT 1;
 ?column?
──────────
        1
(1 row)


Terminal #2:

mypool=> SELECT 1;
 ?column?
──────────
        1
(1 row)


Both connections respond as they should. Now let's check how many connections there are to the PostgreSQL backend  database:

Terminal #1:

mypool=> SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'avnadmin';
 count
-------
     1
(1 row)

And as we can see from the pg_stat_activity output the two psql sessions use the same PostgreSQL server database connection.

Summary


The more client connections you have to your database, the more useful connection pooling becomes. Aiven PostgreSQL makes using connection pooling an easy task and migrating from non-pooled connections to pooled connections is just a matter of gradually changing your client-side connection database name and port number!

Try PostgreSQL 9.6 for free in Aiven


Remember that trying Aiven is free: you will receive US$10 worth of free credits at sign-up which you can use to try any of our service plans. The offer works for all of our services: PostgreSQL, Redis, InfluxDB, Grafana, Elasticsearch and Kafka!

Go to https://aiven.io/ to get started!

Cheers,
Team Aiven

2016-12-20

New AWS regions, Kafka topic management and PostgreSQL connection pooling

We're happy to announce the immediate availability of two new Amazon Web Services regions.  The new regions are in London and Montreal (Canada Central).  All Aiven services are available in the two new regions which brings the total number of available Aiven regions to 51!

Kafka topic management


As mentioned in our previous blog post, we've rolled out a new Kafka topic management interface for our web console.  The new console is immediately available to all current and new Kafka users.

PostgreSQL connection pooling


Connection pooling, also mentioned in the previous blog post, is now available in the web console.  Connection pooling is available in all of our Startup, Business and Premium plans.  The feature will be covered in depth in an upcoming blog post.

Try the new clouds and features for free


Remember that trying out Aiven is free: you will receive US$10 worth of free credits at sign-up which you can use to try any of our service plans. The offer allows you to try out our new clouds and features, plus any existing clouds and services already available in Aiven.

Go to https://aiven.io/ to get started!

Cheers,
Team Aiven