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

2016-11-30

Aiven in Google Tokyo & other updates

We're happy to announce that we've recently added the new Google Cloud Northeast Asia (Tokyo) region to Aiven.  All Aiven services are now available in the new region which is our fourth supported cloud in Japan and 12th cloud in Asia.

We've also been busy enhancing our Kafka service with new topic management APIs and user inteface which we hope to roll out during the next couple of weeks.  On the PostgreSQL side we've just finished the development of our connection pooling system and PGBouncer -based connection pooling will be available in Aiven after our next service update.  The use cases and benefits of these new features will be covered in upcoming blog posts.

The rest of the week the Aiven founding team is busy meeting new and old customers and partners at the Slush startup conference here in Helsinki, Finland.

Cheers,
Team Aiven

2016-10-01

PostgreSQL 9.6 now available in AWS, Azure, Google Cloud with Aiven

PostgreSQL 9.6 is now available in Aiven, bringing the latest and most advanced PostgreSQL release to all the major clouds around the world: all Amazon Web Services, DigitalOcean, Google Cloud, Microsoft Azure and UpCloud regions are supported.



9.6 is the latest version of PostgreSQL incorporating a year's development effort and introducing major improvements to handling large amounts of data with new features like parallel query and reducing the need to continuously vacuum unmodified tables.

Aiven also lets you to fork an existing PostgreSQL 9.5 database into a new PostgreSQL 9.6 service. We're also planning to introduce in-place one-click upgrades of existing PostgreSQL 9.5 databases in the near future.

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-09-23

User management improvements and new Azure regions launched

In this week's updates to Aiven we've greatly enhanced project membership management as well as launched Aiven in five new Azure regions.

 

Project membership improvements

Project membership management improvements make it easier to share the ownership and management responsibilities of an Aiven project between multiple users in one organization.  You can now promote other team members to project administrators, allowing them to invite more members to the project and to adjust billing settings.

We've also updated project invite functionality to allow inviting users who haven't yet registered their Aiven accounts.  Such users are invited to sign up to Aiven and once they've signed up the console will display pending invitations for them.  Pending invitations are shown for all users and projects in the console.

These improvements make it easier for multiple users to start collaborating in Aiven.


New Azure regions launched

We're happy to announce immediate availability of Aiven in five new Azure regions:  All Aiven services are now available in Azure Japan West (Osaka), Japan East (Tokyo), East Asia (Hong Kong), Southeast Asia (Singapore) and Brazil South (São Paulo) regions.


Pricing updates

To reflect the cost differences between different cloud providers and regions we have adjusted pricing of new services in Amazon Web Services and Azure clouds.  Pricing in most AWS regions has been increased for new plans; the prices for current Aiven users will stay unchanged until the end of 2016

We have no plans for changing Google Cloud, DigitalOcean or UpCloud pricing in the foreseeable future.

We'd also like to remind you about Aiven's unique feature allowing seamless migrations between cloud providers, making it possible to migrate services to different clouds if needed.


Trying Aiven is free, no credit card required

Our free trial program is still open: you will receive US$10 worth of free credits at sign-up which you can use to try any of our service plans.

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

We value your feedback

We are always interested in ways of making our service better. Please send your feedback and suggestions via email, Facebook, LinkedIn or using our support system.

2016-09-15

Aiven brings easy, powerful hosted databases to Microsoft Azure

We are proud to announce that Aiven is now available in the Microsoft Azure cloud!

The services available initially at launch in Microsoft Azure are Aiven PostgreSQL, Aiven Redis, Aiven Elasticsearch, Aiven Kafka, Aiven InfluxDB and Aiven Grafana.

Microsoft AzureMicrosoft Azure is a leading global cloud provider and what makes them special is their high number of data centers around the world, currently totaling 30+.

All Aiven services will be available in all generally available Azure regions, bringing nineteen new cloud regions to Aiven and setting the total number of supported data centers to 47, making us the cloud database provider with the widest geographic availability in the world!

The first batch of new cloud regions immediately available are from Azure North America and Europe. Azure Asia and South America regions will follow soon and will be available in the upcoming weeks.

Here's an updated world map showing our supported data center locations. The new Azure regions are the light blue ones:



Microsoft Azure provides numerous services from computing resources to higher level services like machine learning. See azure.microsoft.com for more information about their services.

The new Aiven regions that are immediately available are:

  • United States
    • Iowa - Azure: Central US
    • Virginia - Azure: East US
    • Virginia - Azure: East US 2
    • Illinois - Azure: North Central US
    • Texas - Azure: South Central US
    • California - Azure: West US
    • Washington - Azure: West US 2
    • Wyoming - Azure: West Central US
  • Canada
    • Ontario - Azure: Canada Central
    • Quebec - Azure: Canada East
  • Europe
    • Ireland - Azure: North Europe
    • Netherlands - Azure: West Europe
    • England - Azure: UK South
    • Wales - Azure: UK West
  • Asia
    • Hong Kong - Azure: East Asia (available in the coming weeks)
    • Singapore - Azure: Southeast Asia (available in the coming weeks)
    • Tokyo, Japan - Azure: Japan East (available in the coming weeks)
    • Osaka, Japan - Azure: Japan West (available in the coming weeks)
  • South America
    • Brazil - Azure: Brazil South (available in the coming weeks)
New services can be launched in these regions today and using the Aiven zero-downtime migration, it is also possible to easily migrate existing services to Azure!

All of the Aiven services offer worry-free fully automated DBaaS hosting, including offsite backups, automatic failure recovery and hardened security.

We will continue to expand our Database-as-a-Service offering in both cloud and region support and by adding more services. We are always looking for feedback on what to improve so feel free to let us know if you have ideas on what you'd love to see us support next.

Go to aiven.io to get started! Free $10 credits at registration, no credit card required. Services are billed by the hour.

Last but not least, we would like to thank all of our customers who participated in our beta testing phase!


Cheers,
    Aiven team