Generic Best Practices for HikariCP with Azure Database for PostgreSQL
Author: Mohamed Baioumy Technology: Azure Database for PostgreSQL (Flexible Server & Single Server) Category: Connectivity | Performance | Application Design Introduction Connection pooling is a critical component of application performance when connecting to Azure Database for PostgreSQL. Creating a new PostgreSQL connection is an expensive operation that consumes CPU, memory, and networking resources. Reusing existing connections through a connection pool significantly reduces connection latency, improves throughput, and helps applications scale more efficiently. Many Java applications use HikariCP, one of the most popular high-performance JDBC connection pools. While HikariCP provides excellent performance out of the box, improperly configured connection pool settings can lead to issues such as: Connection pool exhaustion Stale or invalid connections Increased connection acquisition latency Excessive connection creation and destruction Database resource contention Application timeouts This article summarizes generic guidance and best practices for configuring HikariCP when working with Azure Database for PostgreSQL Flexible Server and Azure Database for PostgreSQL Single Server. Understanding Key HikariCP Parameters 1. Maximum Lifetime (maxLifetime) The maxLifetime property controls how long a connection can remain in the pool before HikariCP retires it and creates a new one. Why It Matters Connections can become stale over time due to: Network interruptions Infrastructure updates Connection state changes TCP idle behavior Recycling connections periodically helps prevent applications from using long-lived connections that may no longer be healthy. Recommended Practice Avoid configuring the value too low. When maxLifetime is set aggressively, HikariCP continuously destroys and recreates connections, resulting in: Additional authentication overhead Increased connection establishment latency Higher CPU utilization Reduced application throughput A reasonable starting point is: spring.datasource.hikari.maxLifetime=1800000 30 minutes (1,800,000 ms) is commonly used and aligns well with many production workloads. Depending on workload characteristics, values between 30 minutes and 1 hour are generally suitable Avoid maxLifetime=300000 (5 minutes) This often causes unnecessary connection churn without providing additional benefits. 2. Minimum Idle Connections (minimumIdle) The minimumIdle setting defines how many idle connections HikariCP should keep ready for immediate use. Why It Matters A pool with available idle connections can serve application requests immediately without waiting for new connections to be established. However, maintaining too many idle connections consumes unnecessary database resources. Recommended Practice For most workloads: minimumIdle = maximumPoolSize Or minimumIdle slightly lower than maximumPoolSize This ensures sufficient connections are already available during traffic spikes while avoiding excessive connection creation delays. Example maximumPoolSize=20 minimumIdle=15 Avoid maximumPoolSize=20 minimumIdle=20 only when the application experiences long periods of inactivity and conserving resources is more important than immediate responsiveness. 3. Idle Timeout (idleTimeout) The idleTimeout property determines how long an unused connection remains in the pool before being removed. Why It Matters Connections that sit idle for extended periods consume resources on both: The application server Azure Database for PostgreSQL However, removing idle connections too quickly causes the application to repeatedly establish new connections. Recommended Practice Keep the default value unless there is a specific requirement. spring.datasource.hikari.idleTimeout=600000 which equals: 10 minutes (600,000 ms) This setting provides a good balance between resource utilization and responsiveness. [Re: EXT: R...0040002947 | Outlook] The timeout should also be comfortably longer than any expected short application idle periods. Avoid idleTimeout=10000 (10 seconds) Such aggressive settings often result in unnecessary connection creation cycles. 4. Maximum Pool Size (maximumPoolSize) This parameter determines the maximum number of concurrent database connections the application can maintain. Why It Matters This is often the most important HikariCP setting. If the Pool Is Too Small Applications may experience: Connection is not available, request timed out because all available connections are already in use. Similar scenarios have been observed during customer investigations involving Hikari pool exhaustion. If the Pool Is Too Large Applications can overwhelm the database server with excessive concurrent sessions, resulting in: Connection contention Increased context switching Higher memory consumption Reduced overall performance Recommended Practice Pool size should be based on: Database compute configuration CPU core count Query execution duration Application concurrency requirements Workload characteristics There is no universal value that fits every workload. Start conservatively: maximumPoolSize=10 or maximumPoolSize=20 maximumPoolSize=20 and increase only after load testing demonstrates a need for additional concurrency. Fixed-Size Pool Recommendation For many production workloads, a fixed-size pool provides the simplest and most predictable behavior. Configure: maximumPoolSize=20 minimumIdle=20 or omit minimumIdle entirely so it defaults to maximumPoolSize. HikariCP commonly recommends maintaining a fixed-size pool for responsiveness during demand spikes. Benefits Faster connection acquisition Predictable performance Reduced connection creation latency Better handling of traffic spikes When using a small fixed-size pool, there is often little need to aggressively tune: minimumIdle idleTimeout Instead, simply recycle connections using: maxLifetime maxLifetime Additional Recommendations Enable TCP Keepalive One common cause of stale connections is network devices silently dropping inactive TCP sessions. For PostgreSQL applications, consider enabling TCP keepalive: tcpKeepAlive=true tcpKeepAlive=true The HikariCP project specifically recommends enabling TCP keepalive to prevent rare situations where pools can lose valid connections. Monitor Connection Usage Track: Active connections Idle connections Connection acquisition time Pool exhaustion events Database connection counts These metrics help identify whether pool sizing is appropriate. Investigate Long-Running Queries Connection pool problems are often symptoms rather than root causes. A frequent scenario is: A query becomes slow. Connections remain occupied longer. The pool becomes exhausted. Applications start timing out. When analyzing HikariCP issues, always review: Query performance Blocking situations Database resource utilization Application connection handling logic Sample Production Configuration spring.datasource.hikari.maximumPoolSize=20 spring.datasource.hikari.minimumIdle=15 spring.datasource.hikari.maxLifetime=1800000 spring.datasource.hikari.idleTimeout=600000 spring.datasource.hikari.connectionTimeout=30000 spring.datasource.hikari.keepaliveTime=60000 spring.datasource.hikari.maximumPoolSize=20 spring.datasource.hikari.minimumIdle=15 spring.datasource.hikari.maxLifetime=1800000 spring.datasource.hikari.idleTimeout=600000 spring.datasource.hikari.connectionTimeout=30000 spring.datasource.hikari.keepaliveTime=60000 This configuration provides a solid starting point for many Azure Database for PostgreSQL workloads and can be adjusted based on application-specific requirements. a { text-decoration: none; color: #464feb; } tr th, tr td { border: 1px solid #e6e6e6; } tr th { background-color: #f5f5f5; } Conclusion HikariCP is extremely efficient when configured appropriately. The goal is not to maximize the number of connections, but rather to maintain a healthy balance between application responsiveness and database resource consumption. As a general rule: Use a reasonable maxLifetime (30–60 minutes) Keep enough idle connections available for traffic spikes Avoid aggressive idleTimeout values Size the pool based on workload characteristics, not guesses Consider fixed-size pools for predictable performance Monitor connection usage and query performance regularly By following these practices, applications connecting to Azure Database for PostgreSQL can achieve improved scalability, lower latency, and more reliable connectivity. References Connection pooling best practices - Azure Database for PostgreSQL Performance best practices for using Azure Database for PostgreSQL – Connection Pooling HikariCP Documentation and Pool Sizing GuidanceAzure PostgreSQL Lesson Learned #6: Major Upgrade Blocked by Password Auth (The One-Change Fix)
Co‑authored with angesalsaa Symptoms Portal Upgrade action fails or the precheck reports that upgrading with password authentication from 11 isn’t allowed. Users still authenticate with legacy MD5/password (or the server’s auth isn’t set to allow SCRAM‑SHA‑256). Error you can hit: - Fail at precheck. Root Cause PostgreSQL 11 on Flexible Server requires SCRAM to be enabled before attempting an in‑place major version upgrade to higher versions. The service precheck blocks the upgrade if the server is still on password/MD5‑only auth. Why SCRAM? It’s the modern, secure challenge‑response protocol (SCRAM‑SHA‑256) recommended by Postgres. Mentioned in our Public Documentation under Unsupported Configuration Parameter: Major Version Upgrades - Azure Database for PostgreSQL | Microsoft Learn Contributing Factors No prior auth hardening: password_encryption not set to scram-sha-256. User passwords still stored as MD5 hashes, not re-issued under SCRAM. Clients/drivers unverified for SCRAM support (older libraries may fail). Specific Conditions We Observed Source server on PG 11 (Flexible Server). Upgrade target to a higher supported version via portal. Operational Checks Before you flip the switch, confirm you can safely move authentication: List server auth parameters (portal → Server parameters): Verify these 2 server parameters password_encryption & azure.accepted_password_auth_method = scram-sha-256 and authentication methods include SCRAM. Mitigation Goal: Enable SCRAM and re-issue all passwords → re-run the upgrade. 1) Enable SCRAM on the server Portal: Server parameters → set: password_encryption = scram-sha-256 azure.accepted_password_auth_method = scram-sha-256 Check Important Note: Connectivity with SCRAM - Azure Database for PostgreSQL | Microsoft Learn These are dynamic properties and don't require server restart. Verify client/driver compatibility Ensure your application drivers (JDBC, Npgsql, libpq, etc.) support SCRAM before enforcing it. Update client libraries if needed. Re‑run the Major Version Upgrade Portal: Overview → Upgrade → select target major version → Upgrade. Post‑Resolution Upgrade completed successfully. Authentication now uses SCRAM‑SHA‑256; users continue connecting with updated passwords. No further precheck blocks on auth. Prevention & Best Practices Standardize on SCRAM (password_encryption = scram-sha-256) across all environments. Inventory roles and rotate passwords under SCRAM before your upgrade window. Validate drivers in CI/CD for SCRAM support to avoid runtime surprises. Read the upgrade docs (concepts + how‑to) and version policy so you understand supported targets and timelines. Understand Unsupported Scenarios: Major Version Upgrades - Azure Database for PostgreSQL | Microsoft Learn Why This Matters Skipping this step causes failed upgrades, longer downtime, and emergency rollbacks. Moving to SCRAM not only unblocks the upgrade but also improves security posture (MD5 is deprecated in the community). Key Takeaways Issue: PG11 → higher major upgrade blocked due to password/MD5 authentication. Fix: Enable SCRAM and reset all role passwords, then retry the upgrade. References SCRAM in Azure Database for PostgreSQL – Flexible Server (how to enable & verify) — https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/security-connect-scram Authentication parameters (incl. password_encryption) — https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/param-connections-authentication-authentication Major version upgrade: concepts & steps — Concepts · https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-perform-major-version-upgradeHow to get the Query Text of Azure database for PostgreSQL
Introduction: Recently, due to security and privacy compliance, Query Text is no longer shown on Query Performance insights for Azure PostgreSQL and Azure MySQL. this to help avoiding unauthorized access to the query text, preventing any expose to the underlying schema, and minimizing the security risks. In this blog article, we will provide some SQL/KQL queries to get more insights on the running queries. and how to obtain the Query text from the database. Solution: Although query text is not available on the query performance insight portal, you still can use the Query Performance insights to obtain the query ID, and then you can obtain the query text by connecting to azure_sys database on your PostgreSQL Server and query on 'query_store.query_texts_view'. as shown in below figures: You can use below query to get the top 10 query, you can also adjust the order by clause to your preferences: SELECT query_id, query_sql_text, SUM(calls) total_calls, SUM(total_time) total_time, SUM(total_time) * 1.0 / SUM(calls) avg_time, SUM(rows) * 1.0 / SUM(calls) avg_rows FROM query_store.qs_view q JOIN pg_database d ON q.db_id = d.oid WHERE d.datname = 'database01' -- filter by database of interest AND user_id != 10 -- filter out utility queries GROUP BY query_id, query_sql_text ORDER BY total_time DESC LIMIT 10 -- change top N based on preferences P.S:- for Azure MySQL, you can use mysql.query_store and mysql.query_store_wait_stats to view the query text, more information can be found at: Query Performance Insight - Azure Database for MySQL | Microsoft Docs When you enable Query Store - Azure Database for PostgreSQL - Single Server | Microsoft Docs, you can use the log analytics premade KQL queries or custom queries to get more insights on the running queries, As shown in below screenshots. Note:- Allow up to 20 minutes for the first batch of data to persist in the azure_sys database. In Addition, You can enable diagnostic settings for your Postgres server, Diagnostic settings allows you to send your Postgres logs in JSON format to Azure Monitor Logs for analytics and alerting, Event Hubs for streaming, and Azure Storage for archiving. The log categories to configure are QueryStoreRuntimeStatistics and QueryStoreWaitStatistics. Query example #1: // Slowest queries // Identify top 5 slowest queries. AzureDiagnostics | where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL" | where Category == "QueryStoreRuntimeStatistics" | where user_id_s != "10" //exclude azure system user | summarize avg(todouble(mean_time_s)) by event_class_s , db_id_s ,query_id_s | top 5 by avg_mean_time_s desc Query example #2: let TimeCheckStart = ago(3d); let TimeCheckEnd = now(); let TopQueryNumber = 15; let ServerName = 'XXXXXX'; // add the server-name let WindowSize = 1s; let TopQuery =AzureDiagnostics | where LogicalServerName_s == ServerName | extend executionCount = calls_s | where todatetime(start_time_s) > TimeCheckStart and todatetime(start_time_s) < TimeCheckEnd | where user_id_s != 10 // exclude azure super user | summarize sum(todouble(total_time_s)) by query_id_s, LogicalServerName_s | top TopQueryNumber by sum_total_time_s desc; AzureDiagnostics | where LogicalServerName_s == ServerName | where todatetime(start_time_s) > TimeCheckStart and todatetime(start_time_s) < TimeCheckEnd | join kind = inner TopQuery on query_id_s | extend TIMESTAMP = bin(todatetime(start_time_s), WindowSize) | summarize sum(todouble(total_time_s)) by TIMESTAMP, query_id_s | evaluate pivot(query_id_s, sum(sum_total_time_s), TIMESTAMP) | order by TIMESTAMP asc | render timechart Sample output: Query example #3: You can use query below to check specific query based on query id, you need to update the server name, and query id. //Use to check specific query based on query id above let TimeCheckStart = ago(7d); let TimeCheckEnd = now(); let ServerName = 'XXXXXXX'; //Add the server name let WindowSize = 1m; AzureDiagnostics | where LogicalServerName_s =~ ServerName | extend executionCount = calls_s | where todatetime(start_time_s) > TimeCheckStart and todatetime(start_time_s) < TimeCheckEnd | where user_id_s != 10 // exclude azure super user | where query_id_s == "YYYYYYYYYY" // check specific query id | extend TIMESTAMP = bin(todatetime(start_time_s), WindowSize) | summarize total_time = sum(todouble(total_time_s)), avg_time = avg(todouble(mean_time_s)), total_calls = sum(todouble(calls_s)), total_rows = sum(todouble(rows_s)) by TIMESTAMP | order by TIMESTAMP asc | render timechart Sample output: Additional References: Monitor and tune - Azure Database for PostgreSQL - Single Server | Microsoft Docs Logs - Azure Database for PostgreSQL - Single Server | Microsoft Docs Query Store - Azure Database for PostgreSQL - Single Server | Microsoft Docs Query Performance Insight - Azure Database for PostgreSQL - Single Server | Microsoft Docs Thanks to Shawn Xiao for helping to create this content. Disclaimer Please note that products and options presented in this article are subject to change. This article reflects the query performance insights available for Azure database for PostgreSQL in March, 2022. Closing remarks We hope you find this article helpful. If you have any feedback, please do not hesitate to provide it in the comment section below. Ahmed S. Mazrouh
