Let’s Demystify PostgreSQL for SAP BTP on Cloud Foundry
Before diving in, there are several topics to cover. I’ll provide the TL;DR version where possible, including why a tool like pgAdmin4 is essential, how to size your PostgreSQL instance, and the best practices to follow on SAP BTP Cloud Foundry.
Why PostgreSQL over HANA?
Choosing PostgreSQL over SAP HANA Cloud may seem counterintuitive, but there are strong reasons to consider it. The foremost reason is cost-efficiency. PostgreSQL is a reliable, open-source database, and when deployed on SAP BTP’s hyperscaler option, it presents a much cheaper alternative to HANA while still maintaining key features.
While HANA provides unparalleled performance for SAP-centric operations, PostgreSQL excels in scenarios where flexibility, cost savings, and the open-source ecosystem are top priorities.
How to Size PostgreSQL?
A critical step is ensuring your PostgreSQL instance is correctly sized to handle the expected workload. Sizing involves understanding your vCPU, memory, storage requirements, and the number of concurrent connections.
To avoid under or over-provisioning, start with a small instance if you’re testing or running a proof of concept (PoC). Keep these tips in mind:
Use PgBouncer (we’ll discuss this in more detail) for connection pooling if your app handles many short-lived database connections.
Monitor and scale where needed to keep your environment optimised.
Unlike SAP HANA, which uses Capacity Units (CUs) for sizing, PostgreSQL on BTP is more straightforward, with a focus on storage blocks and compute power. The process is simpler and far less expensive.
Refer to the Azure offerings on SAP BTP for PostgreSQL
Connection Limits by Instance Type
A key consideration when sizing PostgreSQL is the number of connections your instance can handle. The following data gives a basic overview of connection limits for PostgreSQL instances on SAP BTP through Azure:
Instance Type | Max Connections | User Connections |
B1ms | 50 | 35 |
B2s | 429 | 414 |
D4s_v3 | 1,718 | 1,703 |
D8s_v3 | 3,437 | 3,422 |
D16s_v3 | 5,000 | 4,985 |
D32s_v3 | 5,000 | 4,985 |
D64s_v3 | 5,000 | 4,985 |
This information is available in greater detail at the official Azure PostgreSQL Limits documentation.
Implementing PostgreSQL Best Practices on SAP BTP
PostgreSQL deployment on SAP BTP doesn’t come with a large set of pre-established best practices, so it’s essential to rely on general database management practices from other hyperscalers. Here are some key areas to focus on:
Connection Pooling
To get the most out of your PostgreSQL instance, use a tool like PgBouncer to manage your connection pool. Connection pooling reduces the number of active connections and prevents your instance from reaching connection limits, which can cause performance issues.
PgBouncer can be deployed to SAP BTP Cloud Foundry as a Docker container and act as your pool management solution. It ensures efficient resource use, especially for applications with many short-lived connections.
Backup & Restore
It’s important to note that SAP BTP only takes daily snapshots of the database, with retention for 14 days. This isn’t as flexible as an hourly backup, so you need to plan around this limitation. Depending on the hyperscaler (Azure, AWS, or GCP), you may have additional backup options available to safeguard your data or only break your db in the night!!
Query Optimisation
Regularly analysing and optimising your SQL queries is crucial. Use PostgreSQL’s EXPLAIN ANALYSE and other performance monitoring tools to avoid bottlenecks and ensure your queries are running efficiently.
Monitoring and Administering PostgreSQL on SAP BTP
PostgreSQL has emerged as the go-to option for persistence on SAP BTP due to its lower costs. However, compared to HANA Cloud, it lacks robust built-in tools for monitoring and managing the database. This is where pgAdmin4 becomes essential.
With pgAdmin4, you gain access to:
Visual query builders
Server monitoring and statistics
Backup and restore management
Query execution analysis
Why pgAdmin4 is Essential
Many blogs suggest using an SSH tunnel to connect to PostgreSQL through the Cloud Foundry CLI, but I’m here to show you how to connect directly to the database, fully encapsulated within SAP BTP. PgAdmin4 enhances the PostgreSQL management experience, making database administration smoother and more transparent.
What You Need to Get Started
Entitlements needed on SAP BTP assigned to your subaccount:
PostgreSQL, hyperscaler option (start with the free tier for testing).
Cloud Foundry Runtime (at least 1GB memory allocation).
How to Set Up PostgreSQL and pgAdmin4
Create the DB
Log in to SAP BTP cockpit.
Go to your subaccount and create a PostgreSQL instance from the hyperscaler option. Assign the required service plan (start with the free tier for experimentation).
Prepare the Deployment for pgAdmin4
You will need a Dockerised version of pgAdmin4. Create a Dockerfile for pgAdmin4, push it to your Cloud Foundry environment, and deploy it as a service.
Login to cloudfoundry
cf login -a "<api for your subacount>"Create a manifest.yml file somewhere on your BAS or desktop for pgAdmin4.
PGADMIN_DEFAULT_EMAIL: myemail@domain.com // this is your admin email
PGADMIN_DEFAULT_PASSWORD: MyPassword123! // this is your admin password
PGADMIN_LISTEN_ADDRESS // this is to fix the inital port issue you will face when trying to deploy a docker file that runs on port 8080
- postgresDB // this is your DB you created, this command adds a binding to the service
applications:
- name: pgadmin4
docker:
image: dpage/pgadmin4:latest
memory: 1024M
instances: 1
health-check-type: process
random-route: true
env:
PGADMIN_DEFAULT_EMAIL: myemail@domain.com
PGADMIN_DEFAULT_PASSWORD: MyPassword123!
PGADMIN_LISTEN_ADDRESS: 0.0.0.0
services:
- postgresDB
Push the Dockerised pgAdmin4 using the cf push command
cf push -f manifest.ymlNavigate to the space you created the pgAdmin4 application, and click the link.
Add the DB to pgAdmin4
Once deployed, configure pgAdmin4 to connect to your PostgreSQL instance on SAP BTP:
Open the pgAdmin4 UI.
Add your PostgreSQL server by inputting the connection details (host, port, user, etc.). These details can be found against the postgreSQL service on BTP under instances and subscriptions:
Create a Service key if non exists
b. Open your Key and get the details
c. Paste the details into the create server details:
Start monitoring and managing your database directly from pgAdmin4.
By using pgAdmin4 and PostgreSQL on SAP BTP, you can manage your PostgreSQL databases more effectively while keeping costs under control. PgAdmin4 provides the features developers need for managing queries, monitoring performance, and maintaining healthy database operations—all while running on the robust SAP BTP platform.
Alternate option for PostgreSQL on SAP BTP
While SAP provides a solid managed PostgreSQL service on BTP, it tends to obscure certain database management aspects, which might not meet the strict SLA or Disaster Recovery (DR) requirements of some organisations. If your business demands higher flexibility, tighter control, or more advanced features that native PostgreSQL services like AWS RDS or Azure Databases provide, you may want to consider deploying your database directly via a hyperscaler.
This option allows you to:
Gain full access to all native PostgreSQL features, including automated scaling, multi-AZ failover, and more customisable DR options.
Enjoy more comprehensive monitoring and backup capabilities.
Control updates and fine-tune performance without the limitations imposed by SAP BTP's managed service.
By leveraging the hyperscaler’s native database services, you retain more control over performance, security, and disaster recovery, ensuring your database infrastructure aligns with your organisation's requirements.
For organisations with critical database needs, this approach might offer a more suitable path than relying solely on SAP BTP’s managed PostgreSQL service.
