TIP

🔥 Checkout the Azure Developer page at azure.com/developer (opens new window).

💡 Learn more : Azure PostgreSQL Databases (opens new window).

📺 Watch the video : How to develop apps with Azure Database for PostgreSQL using best practices (opens new window).

How to develop apps with Azure Database for PostgreSQL using best practices

Managed PostgreSQL provides availability, performance, elasticity and security

PostgreSQL is a great relational database with lots of features. Azure Database for PostgreSQL (opens new window) is based on PostgreSQL Community Edition (opens new window) and offers a managed version of PostgreSQL, which enables elastic scaling, high availability, data protection and more.

You can create incredible applications with Azure Database for PostgreSQL and when you do, it is important to follow best practices (opens new window) to optimize security, availability and performance. This post discusses some of the best practices for building an application with Azure Database for PostgreSQL.

Prerequisites

If you want to follow along, you'll need the following:

• An Azure subscription (If you don't have an Azure subscription, create a free account (opens new window) before you begin)

Keep your PostgreSQL server secure

The first best practice is about security. It is important to follow the latest security guidelines to keep your application safe. In Azure Database for PostgreSQL, you can configure security by:

• Enabling the PostgreSQL server firewall (opens new window)
• Run the PostgreSQL server and your application in a virtual network (opens new window)
• Use Azure Private Link (opens new window) to create a secure link between your application and the PostgreSQL server
• Set the minimum TLS version (opens new window) of the PostgreSQL server to 1.2

You can configure security in the Azure Database for PostgreSQL database Connection security blade.

(Configure PostgreSQL Connection Security)

Use environment variables for connection information

When you connect to Azure Database for PostgreSQL from an application, you shouldn't store the connection string (including username and password) in your application code. It is best practice to use environment variables for the connection string. If your application runs in an Azure App Service Web App (opens new window), you can have the application read from the environment variables, which you can configure in the Configuration menu of the App Service. In there, you can configure connection strings and application settings, without storing them in your application code.

You can even take it a step further and store connection string data in Azure Key Vault (opens new window) and have the application retrieve it from there.

(Configure connection strings in Azure App Service)

Enable read replication to mitigate failovers and enhance performance

If your database server fails for whatever reason, your application becomes unusable. To prevent this from happening, you can increase your application availability, by creating read replicas of your Azure Database for PostgreSQL (opens new window). Read replicas are Azure Database for PostgreSQL databases in another geographic region that will have all the data of the original database synchronized to them. When your original database is unavailable, you can change your application to use the replica database and read data from it. You can also upgrade the replica to become a read/write database, so that your application can also write to it, whilst your primary database is still offline.

Additionally, you can use read replicas to enhance geographic performance. You can create read-only replicas in geographic regions where your users are active and where you also deploy another instance of your application. By doing this, the data is close to your users, which improves performance.

You can add replicas in the Azure Database for PostgreSQL database Replication blade.

(Azure Database for PostgreSQL replicas)

Use autovacuum

By default, when PostgreSQL performs a delete operation, the records are marked for deletion and will be purged later. PostgreSQL purges these records when it performs a vacuum job. If you don't perform vacuum jobs on a regular basis, your database will grow and slow down because of data bloat, suboptimal indexes and increased I/O.

Luckily, you can enable autovacuum (opens new window) in Azure Database for PostgreSQL. Autovacuum performs the vacuum job regularly, which cleans up your database and keeps it performant.

To enable the autovacuum feature, navigate to the Server parameters blade of the Azure Database for PostgreSQL database and find and turn autovacuum to ON.

(Azure Database for PostgreSQL autovacuum setting)

Monitor performance with the Query Store

Most managed databases in Azure enable you to monitor and analyze query statistics. This enables you to troubleshoot which queries are fast and which ones are slow and which queries are executed the most. To enable this feature for Azure Database for PostgreSQL, you need to enable the Query Store (opens new window) feature, which is disabled by default.

You can enable the Query Store feature in the Server parameters blade of the Azure Database for PostgreSQL database. In there, you need to set the pg_qs.query_capture_mode parameter to ALL and save the changes.

(Enable the Query Store feature)

After 20 minutes or so, query statistics will show up in the Query Performance Insight blade of the database.

(The Query Performance Insight blade)

Conclusion

Azure Database for PostgreSQL (opens new window) provides a scalable, highly available, secure and performant version of PostgreSQL. And when you use best practices (opens new window), like optimized server security (opens new window), not storing connection strings in code (opens new window), use read replications (opens new window), enable autovacuum (opens new window), and use the Query Store (opens new window) feature, you make sure that your application will perform at its best and will stay available and secure. Go and check it out!