Unit 4.1: Database and Caching Integration

Introduction

You are building the TaskBoard API and the data tier has to run on IONOS managed databases. That changes a few assumptions you may carry from other clouds. Managed databases here are reached over a private LAN, not a public endpoint, every client connection is TLS-encrypted, and the database clusters expose no read replicas you can point read traffic at. Your read-scaling lever is not a replica endpoint, it is the In-Memory DB cache.

This unit is connection code. You will wire psycopg2 and SQLAlchemy to PostgreSQL, mysql-connector to MariaDB, pymongo to MongoDB, and redis-py to the In-Memory DB cluster, all with TLS and pooling. You will also handle the two operational realities that bite developers: connection ceilings are fixed by cluster RAM and cannot be raised by a client flag, and the Backup Service does not back up managed databases, so recovery is the database's own PITR and your own logical dumps.

1. Connecting to Managed PostgreSQL

PostgreSQL clusters listen on port 5432 and live on a private LAN inside your data center. The cluster object holds a datacenterId, a lanId, and a primaryInstanceAddress, and your application connects to that primary instance address. There is no public hostname. The default SSL mode is prefer and TLS cannot be disabled by the client, so plan for encrypted connections from the first line of code.

The supported major versions are 14, 15, and 16. The server certificate chains to the ISRG Root X1 root, so a current CA bundle on your application host validates the connection without extra setup.

1.1 Connecting with psycopg2

Connect with sslmode=require so the driver fails closed if TLS cannot be negotiated. The host is the cluster primary instance address reachable on your private LAN.

import psycopg2

conn = psycopg2.connect(
    host="10.7.222.10",        # primaryInstanceAddress from the cluster object
    port=5432,
    dbname="taskboard",
    user="taskboard_app",
    password="<from-secret-store>",
    sslmode="require",
    connect_timeout=10,
)
conn.autocommit = False

with conn.cursor() as cur:
    cur.execute(
        "INSERT INTO tasks (title, status) VALUES (%s, %s) RETURNING id",
        ("Ship unit 4.1", "open"),
    )
    task_id = cur.fetchone()[0]
    conn.commit()
print(f"Inserted task {task_id}")

For asyncpg in an async service, pass ssl="require" and supply the same host, port, and credentials. Never build SQL strings with f-strings, use parameter placeholders as shown above.

1.2 Connection Limits Are Fixed by RAM

The max_connections value is calculated from cluster RAM and is not user-configurable. The following table is the exact mapping enforced by the platform.

Of those, 11 connections are reserved, so your application pool must stay below the published number minus the reserved slots. Because this ceiling is fixed, you cannot solve "too many connections" by raising a server setting. You solve it with pooling, covered next.

2. Connection Pooling

There are no read replicas. Every read and every write hits the single primary instance, so an unbounded application that opens a connection per request will exhaust the fixed max_connections ceiling fast. Pooling is mandatory, not optional, and you have two layers to use together: an application-side pool and the managed PgBouncer pooler.

2.1 Application-Level Pool with SQLAlchemy

Cap the SQLAlchemy pool well under the cluster ceiling and enable pool_pre_ping so stale connections are recycled rather than handed to a request mid-failure.

from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+psycopg2://taskboard_app:<pw>@10.7.222.10:5432/taskboard",
    connect_args={"sslmode": "require"},
    pool_size=20,          # steady-state connections
    max_overflow=10,       # burst headroom, keep total well under the ceiling
    pool_pre_ping=True,
    pool_recycle=1800,
)

with engine.connect() as conn:
    rows = conn.execute(text("SELECT id, title FROM tasks WHERE status = :s"),
                        {"s": "open"}).fetchall()

If you run N application replicas, the cluster sees N * (pool_size + max_overflow) connections. Size the pool against the cluster total divided by your replica count, leaving margin.

2.2 Managed PgBouncer Pooler

PostgreSQL clusters offer a managed PgBouncer pooler. You enable it and choose the pool mode, the supported modes are transaction (default) and session. The pooler listens on port 6432 rather than the database port 5432.

# Route the app through PgBouncer: same host, pooler port 6432
engine = create_engine(
    "postgresql+psycopg2://taskboard_app:<pw>@10.7.222.10:6432/taskboard",
    connect_args={"sslmode": "require"},
    pool_size=10, max_overflow=5, pool_pre_ping=True,
)

Use transaction mode for typical web workloads so a backend connection is held only for the duration of a transaction, which multiplies the number of clients the fixed connection ceiling can serve. Session-scoped features such as prepared statements and SET that must persist across a session need session mode.

3. MariaDB and MongoDB Integration

PostgreSQL is one of several managed engines, and the connection model is consistent: private LAN, TLS enforced, fixed limits. The driver and the connection-limit math differ per engine.

3.1 MariaDB

MariaDB listens on port 3306. All client connections are TLS-encrypted and the server certificate is issued by Let's Encrypt, so a standard CA bundle validates it. Only Long-Term Support versions are offered, starting from 10.6 (for example 10.6 and 10.11). Replication is asynchronous only.

The cluster max_connections is 500, and each user is capped at 250 connections. That per-user cap is deliberate: because one user can take at most 250 of the 500 slots, a single runaway application user is far less likely to starve the whole cluster.

import mysql.connector

cnx = mysql.connector.connect(
    host="10.7.222.20",
    port=3306,
    database="taskboard",
    user="taskboard_app",
    password="<pw>",
    ssl_disabled=False,        # TLS is enforced server-side regardless
    pool_name="taskboard_pool",
    pool_size=20,
)
cur = cnx.cursor()
cur.execute("SELECT id, title FROM tasks WHERE status = %s", ("open",))
for task_id, title in cur.fetchall():
    print(task_id, title)
cur.close(); cnx.close()

Storage tops out at 2 TB per cluster, and a single instance cannot exceed 16 cores.

3.2 MongoDB

MongoDB clusters present a connection string in the form mongodb+srv://m-<id>.mongodb.<region>.ionos.com, which you typically read from a Terraform output rather than hardcoding. Supported versions are 6.0 and 7.0. Server-side connection pooling is not provided, so the driver-side pool in pymongo is your pool.

from pymongo import MongoClient

uri = "mongodb+srv://taskboard_app:<pw>@m-abc123.mongodb.de-txl.ionos.com"
client = MongoClient(
    uri,
    tls=True,
    maxPoolSize=50,
    serverSelectionTimeoutMS=5000,
    retryWrites=True,
)
db = client["taskboard"]
db.task_events.insert_one({"task_id": 42, "event": "created"})
print(db.task_events.count_documents({"task_id": 42}))

The maximum connection count scales with RAM. The following table is the enforced mapping.

Role assignment uses the built-in MongoDB roles such as read, readWrite, dbAdmin, and clusterMonitor. Grant readWrite scoped to the taskboard database for the application user rather than a cluster-wide admin role.

4. In-Memory DB (Redis) Caching

Because no managed engine exposes read replicas, the In-Memory DB cluster is the IONOS-native way to scale reads. It is Redis OSS 7.2 compatible and listens on port 6379, and TLS uses a Let's Encrypt certificate authority once you configure the instance to use it (TLS is not enabled by default, so enable it explicitly, as the redis-py example below does with ssl=True). A cluster has up to 5 nodes. The underlying Valkey engine defaults to a maximum of 10,000 client connections (maxclients).

The default eviction policy is allkeys-lru and persistence defaults to None, which is the correct posture for a pure cache: treat it as volatile and always be able to rebuild it from the source database.

4.1 Connecting with redis-py

import redis

r = redis.Redis(
    host="10.7.222.30",
    port=6379,
    password="<pw>",
    ssl=True,
    ssl_cert_reqs="required",
    socket_timeout=2,
    decode_responses=True,
)
r.set("health", "ok", ex=30)
print(r.get("health"))

4.2 Cache-Aside Pattern

Cache-aside is the default read path: check the cache, fall back to the database on a miss, then populate the cache. Set a TTL so stale entries expire even if no write invalidates them.

import json

def get_task(task_id, r, engine):
    key = f"task:{task_id}"
    cached = r.get(key)
    if cached:
        return json.loads(cached)              # cache hit
    with engine.connect() as conn:
        row = conn.execute(
            text("SELECT id, title, status FROM tasks WHERE id = :id"),
            {"id": task_id}).mappings().first()
    if row:
        r.set(key, json.dumps(dict(row)), ex=300)   # populate with 5 min TTL
    return dict(row) if row else None

4.3 Write-Through Pattern

Write-through updates the database and the cache in the same operation so reads never serve a stale value after a write. Write the database first, and only update the cache once the commit succeeds.

def update_task_status(task_id, status, r, engine):
    with engine.begin() as conn:                # commits on context exit
        conn.execute(text("UPDATE tasks SET status = :s WHERE id = :id"),
                     {"s": status, "id": task_id})
    r.set(f"task:{task_id}", json.dumps({"id": task_id, "status": status}),
          ex=300)

Redis fits TaskBoard in two more places: session storage and caching the task-list query results that would otherwise hammer the single primary on every page load.

5. Recovery: PITR and the Backup Service Gap

Recovery for managed databases is the database's own point-in-time recovery, not the Backup Service. The Backup Service does not back up managed DBaaS, so you must not assume your tasks data is captured by a backup unit. For logical exports beyond the PITR window, schedule pg_dump or the MariaDB dump tool from application code or a cron job and store the output in Object Storage.

PostgreSQL and MariaDB both default to a 7-day point-in-time-recovery window on v1 clusters, but on their v2 APIs retention is configurable from 1 to 365 days via backup.retentionDays, so check your cluster's API version and retention setting before assuming a hard 7-day window. PITR runs through the REST API. The base path for PostgreSQL is https://api.ionos.com/databases/postgresql, and a restore is an in-place operation, the database is unavailable while it runs.

5.1 Triggering PostgreSQL PITR via API

curl -X POST \
  "https://api.ionos.com/databases/postgresql/clusters/${CLUSTER_ID}/restore" \
  -H "Authorization: Bearer ${IONOS_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "recoveryTargetTime": "2026-06-04T09:30:00Z"
  }'

The recoveryTargetTime is an ISO-8601 timestamp and is non-inclusive. Restore constraints to code around: only one backup can be restored at a time, the cluster must be AVAILABLE before you trigger a restore, you can only restore from the same or an older major version, and a restore can move the database to another region. The platform recommends at least 4 GB RAM during the restore, which you can scale back down afterward.

API Reference Quick Card

Key API endpoints for managed database integration:

Base URL: https://api.ionos.com/databases/postgresql (region-specific hosts apply to In-Memory DB and MariaDB) Authentication: Authorization: Bearer <token>

Code Lab

Objective: Connect the TaskBoard API to PostgreSQL and the In-Memory DB cache, insert a task, cache the read, and verify TLS.

Prerequisites:

• IONOS Cloud account with API token
• A running PostgreSQL cluster and In-Memory DB replica set on a shared private LAN
• Python 3.10+ with psycopg2-binary, sqlalchemy, and redis installed
• The cluster primaryInstanceAddress and the In-Memory DB node address

Step 1: Verify TLS to PostgreSQL

PGPASSWORD=$PW psql "host=10.7.222.10 port=5432 dbname=taskboard user=taskboard_app sslmode=require" -c "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

Expected output:

 ssl
-----
 t

Step 2: Create the tasks table

PGPASSWORD=$PW psql "host=10.7.222.10 sslmode=require dbname=taskboard user=taskboard_app" \
  -c "CREATE TABLE IF NOT EXISTS tasks (id SERIAL PRIMARY KEY, title TEXT, status TEXT);"

Expected output:

CREATE TABLE

Step 3: Insert a task from Python

from sqlalchemy import create_engine, text
engine = create_engine("postgresql+psycopg2://taskboard_app:<pw>@10.7.222.10:5432/taskboard",
                       connect_args={"sslmode": "require"}, pool_size=5, pool_pre_ping=True)
with engine.begin() as c:
    tid = c.execute(text("INSERT INTO tasks (title, status) VALUES (:t, 'open') RETURNING id"),
                    {"t": "Lab task"}).scalar()
print("task id:", tid)

Expected output:

task id: 1

Step 4: Connect to the cache

import redis
r = redis.Redis(host="10.7.222.30", port=6379, password="<pw>", ssl=True,
                ssl_cert_reqs="required", decode_responses=True)
print(r.ping())

Expected output:

True

Step 5: Cache the read (cache-aside)

import json
key = f"task:{tid}"
if not r.get(key):
    with engine.connect() as c:
        row = c.execute(text("SELECT id, title, status FROM tasks WHERE id = :id"),
                        {"id": tid}).mappings().first()
    r.set(key, json.dumps(dict(row)), ex=300)
print(r.get(key))

Expected output:

{"id": 1, "title": "Lab task", "status": "open"}

Step 6: Confirm the second read is a cache hit

print("cached:", r.ttl(key), "seconds remaining")

Expected output:

cached: 300 seconds remaining

Validation Checklist:

• [ ] psql reports ssl = t, confirming TLS is active
• [ ] Task row inserted and returned an id via SQLAlchemy
• [ ] r.ping() returns True over TLS
• [ ] Second read returns the value from Redis, not PostgreSQL

Cleanup:

PGPASSWORD=$PW psql "host=10.7.222.10 sslmode=require dbname=taskboard user=taskboard_app" -c "DROP TABLE tasks;"
# Flush the lab cache key
redis-cli -h 10.7.222.30 -p 6379 -a "$PW" --tls DEL task:1

Common Pitfalls

Developer mistakes to avoid with managed database integration:

1. Opening a connection per request and exhausting the ceiling

   • Problem: Under load the application throws FATAL: too many connections for role or sorry, too many clients already.
   • Why it happens: max_connections is fixed by cluster RAM (for example 1000 above 8 GB), 11 slots are reserved, and there are no read replicas to spread the load. An unpooled app multiplies connections by replica count.
   • Fix: Bound the pool well under the ceiling and route through PgBouncer in transaction mode on port 6432:

   create_engine(url, pool_size=20, max_overflow=10, pool_pre_ping=True)
   

2. Assuming the Backup Service protects your database data

   • Problem: A bad migration drops a table and there is no backup unit to restore from.
   • Why it happens: The Backup Service does not back up managed DBaaS. Developers assume one backup tool covers everything.
   • Fix: Rely on database PITR (7-day default window, configurable 1-365 days on the v2 API via backup.retentionDays) for recent recovery and schedule logical dumps for anything older:

   pg_dump "host=10.7.222.10 sslmode=require dbname=taskboard" | gzip > taskboard-$(date +%F).sql.gz
   

3. Disabling TLS to "make it work"

   • Problem: Connection fails locally so a developer reaches for sslmode=disable.
   • Why it happens: A missing or outdated CA bundle fails certificate validation, and the fast workaround looks like turning TLS off.
   • Fix: TLS cannot be disabled by the client on PostgreSQL (default mode is prefer and cannot be disabled), so fix the CA chain instead. PostgreSQL chains to ISRG Root X1, MariaDB and In-Memory DB use Let's Encrypt. Update the system CA bundle and keep sslmode=require.

Summary

You can now connect production application code to every IONOS managed database engine with TLS enforced and connections pooled, and you can recover data through the right mechanism. The connection model is consistent across engines: private LAN, encrypted transport, and a fixed connection ceiling that you respect with pooling rather than fighting with a server flag. The In-Memory DB cache is your read-scaling tool because none of the database engines expose read replicas.

For TaskBoard specifically, PostgreSQL holds the tasks, Redis caches reads and sessions, and recovery is database PITR plus your own scheduled dumps. Build those connection helpers once, with pooling and TLS baked in, and reuse them across every service in the application.

Key Points:

• Managed databases are reached over the private LAN with TLS enforced, never a public endpoint
• max_connections is fixed by cluster RAM and is not user-configurable, so pooling is mandatory
• There are no read replicas, the In-Memory DB cache is the IONOS-native read-scaling solution
• PgBouncer in transaction mode on port 6432 multiplies the clients a fixed ceiling can serve
• The Backup Service does not back up DBaaS, recovery is database PITR (7-day default, configurable 1-365 days on the v2 API) plus your own dumps

Important Terminology:

• PITR: Point-in-time recovery, restoring a cluster to an ISO-8601 timestamp within the retention window (7-day default; 1-365 days configurable on the v2 API) via the REST API
• PgBouncer: The managed PostgreSQL connection pooler on port 6432, supporting transaction and session pool modes
• Cache-aside: A read pattern that checks the cache, falls back to the database on a miss, then populates the cache with a TTL
• Write-through: A write pattern that updates the database and the cache in the same operation to avoid stale reads
• primaryInstanceAddress: The private-LAN address of a database cluster's primary instance that the application connects to

Next Steps

Continue Learning: Unit 4.2: Object Storage Integration

Related Topics:

• Unit 4.3: Event Streaming Integration
• Unit 4.4: AI Model Hub Integration