Verbindungspools, Backups und Berechtigungen

Kurz gesagt: Eine Datenbank hinter einer laufenden Website braucht einen Pool, der offene Verbindungen wiederverwendet, Backups, aus denen man wirklich wiederherstellen kann, und einen Benutzer mit nur den nötigen Rechten.

Problem

Freitagabend

Der Shop macht eine Aktion, und innerhalb von fünf Minuten kommen 400 Leute. Die Seite bleibt stehen, und im Log steht:

FATAL:  sorry, too many clients already

Das Programm öffnet für jede Seitenansicht eine neue Verbindung zur Datenbank und schließt sie am Ende. Das Öffnen ist nicht umsonst: ein Weg übers Netzwerk, eine Anmeldung mit Passwort und in PostgreSQL ein neuer Prozess auf dem Server. Und der Server nimmt nur eine begrenzte Zahl von Verbindungen an (max_connections, standardmäßig 100). Vierhundert Seitenansichten gleichzeitig sind vierhundert Verbindungen.

Die Idee

Ein Verbindungspool

Ein Pool hält ein paar Verbindungen offen. Eine Seitenansicht nimmt eine freie, benutzt sie und gibt sie zurück, und die nächste nimmt dieselbe. Das Öffnen wird einmal bezahlt, und die Datenbank sieht nie mehr Verbindungen als die Größe des Pools.

Hier ist ein kleiner Pool mit drei Verbindungen, von Hand geschrieben, damit du siehst, was er tut. borrow gibt eine freie Verbindung, und wenn keine da ist, öffnet es eine neue, aber nie mehr als drei. giveBack gibt sie zurück:

Java

static final int MAX = 3;
static final Deque<Connection> free = new ArrayDeque<>();
static int opened = 0;

static Connection borrow() throws SQLException {
    if (!free.isEmpty()) return free.pop();
    if (opened == MAX) throw new SQLException("der Pool ist leer");
    opened++;
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    setup(conn);
    return conn;
}

static void giveBack(Connection conn) {
    free.push(conn);
}

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (!rs.next()) return "kein solches Produkt";  // die Verbindung wird nie zurückgegeben
            String name = rs.getString("name");
            giveBack(conn);
            return name;
        }
    }
}

for (int id : new int[] { 1, 99, 5, 98, 97, 7 }) {
    try {
        System.out.println("Produkt " + id + ": " + productName(id));
    } catch (SQLException e) {
        System.out.println("Produkt " + id + ": Fehler: " + e.getMessage());
    }
}
System.out.println("offene Verbindungen: " + opened + ", frei: " + free.size());

C#

const int Max = 3;
var free = new Stack<SqliteConnection>();
var opened = 0;

foreach (var id in new[] { 1, 99, 5, 98, 97, 7 })
{
    try
    {
        Console.WriteLine($"Produkt {id}: {ProductName(id)}");
    }
    catch (InvalidOperationException e)
    {
        Console.WriteLine($"Produkt {id}: Fehler: {e.Message}");
    }
}
Console.WriteLine($"offene Verbindungen: {opened}, frei: {free.Count}");

SqliteConnection Borrow()
{
    if (free.Count > 0) return free.Pop();
    if (opened == Max) throw new InvalidOperationException("der Pool ist leer");
    opened++;
    var conn = new SqliteConnection("Data Source=:memory:");
    conn.Open();
    Setup(conn);
    return conn;
}

void GiveBack(SqliteConnection conn) => free.Push(conn);

string ProductName(int id)
{
    var conn = Borrow();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT name FROM products WHERE id = $id";
    cmd.Parameters.AddWithValue("$id", id);
    var name = cmd.ExecuteScalar();
    if (name is null) return "kein solches Produkt";  // die Verbindung wird nie zurückgegeben
    GiveBack(conn);
    return (string)name;
}

Python

MAX = 3
free = []
opened = 0


def borrow():
    global opened
    if free:
        return free.pop()
    if opened == MAX:
        raise RuntimeError("der Pool ist leer")
    opened += 1
    conn = sqlite3.connect(":memory:")
    setup(conn)
    return conn


def give_back(conn):
    free.append(conn)


def product_name(product_id):
    conn = borrow()
    row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
    if row is None:
        return "kein solches Produkt"  # die Verbindung wird nie zurückgegeben
    give_back(conn)
    return row[0]


for product_id in [1, 99, 5, 98, 97, 7]:
    try:
        print(f"Produkt {product_id}: {product_name(product_id)}")
    except RuntimeError as e:
        print(f"Produkt {product_id}: Fehler: {e}")
print(f"offene Verbindungen: {opened}, frei: {len(free)}")

JavaScript

const MAX = 3;
const free = [];
let opened = 0;

function borrow() {
  if (free.length > 0) return free.pop();
  if (opened === MAX) throw new Error("der Pool ist leer");
  opened++;
  const db = new DatabaseSync(":memory:");
  setup(db);
  return db;
}

function giveBack(db) {
  free.push(db);
}

function productName(id) {
  const db = borrow();
  const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
  if (row === undefined) return "kein solches Produkt"; // die Verbindung wird nie zurückgegeben
  giveBack(db);
  return row.name;
}

for (const id of [1, 99, 5, 98, 97, 7]) {
  try {
    console.log(`Produkt ${id}: ${productName(id)}`);
  } catch (e) {
    console.log(`Produkt ${id}: Fehler: ${e.message}`);
  }
}
console.log(`offene Verbindungen: ${opened}, frei: ${free.length}`);

Sieh es dir an

Eine verlorene Verbindung

Die Funktion für den Namen eines Produkts hat zwei Ausgänge. Wenn das Produkt existiert, gibt sie die Verbindung zurück. Wenn nicht, kehrt sie vorzeitig zurück, und die Verbindung bleibt belegt:

Java

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (!rs.next()) return "kein solches Produkt";  // die Verbindung wird nie zurückgegeben
            String name = rs.getString("name");
            giveBack(conn);
            return name;
        }
    }
}

Ausgabe

Produkt 1: Weißbrot
Produkt 99: kein solches Produkt
Produkt 5: Frischmilch
Produkt 98: kein solches Produkt
Produkt 97: kein solches Produkt
Produkt 7: Fehler: der Pool ist leer
offene Verbindungen: 3, frei: 0

C#

string ProductName(int id)
{
    var conn = Borrow();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT name FROM products WHERE id = $id";
    cmd.Parameters.AddWithValue("$id", id);
    var name = cmd.ExecuteScalar();
    if (name is null) return "kein solches Produkt";  // die Verbindung wird nie zurückgegeben
    GiveBack(conn);
    return (string)name;
}

Ausgabe

Produkt 1: Weißbrot
Produkt 99: kein solches Produkt
Produkt 5: Frischmilch
Produkt 98: kein solches Produkt
Produkt 97: kein solches Produkt
Produkt 7: Fehler: der Pool ist leer
offene Verbindungen: 3, frei: 0

Python

def product_name(product_id):
    conn = borrow()
    row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
    if row is None:
        return "kein solches Produkt"  # die Verbindung wird nie zurückgegeben
    give_back(conn)
    return row[0]

Ausgabe

Produkt 1: Weißbrot
Produkt 99: kein solches Produkt
Produkt 5: Frischmilch
Produkt 98: kein solches Produkt
Produkt 97: kein solches Produkt
Produkt 7: Fehler: der Pool ist leer
offene Verbindungen: 3, frei: 0

JavaScript

function productName(id) {
  const db = borrow();
  const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
  if (row === undefined) return "kein solches Produkt"; // die Verbindung wird nie zurückgegeben
  giveBack(db);
  return row.name;
}

Ausgabe

Produkt 1: Weißbrot
Produkt 99: kein solches Produkt
Produkt 5: Frischmilch
Produkt 98: kein solches Produkt
Produkt 97: kein solches Produkt
Produkt 7: Fehler: der Pool ist leer
offene Verbindungen: 3, frei: 0

Drei Abfragen nach einem Produkt, das es nicht gibt, und der Pool ist leer. Die sechste Anfrage ist völlig normal, bekommt aber keine Verbindung. Ein echter Pool scheitert nicht sofort: Er wartet auf eine freie Verbindung (bis zu 30 Sekunden in HikariCP), und die Seite hängt. Deshalb zeigt sich ein Leck erst als „die Seite ist langsam“ und dann als „die Seite antwortet nicht“.

Die Idee

Das Zurückgeben gehört in finally

Die Lösung: Die Verbindung wird zurückgegeben, egal wie die Funktion verlassen wird:

Java

static String productName(int id) throws SQLException {
    Connection conn = borrow();
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM products WHERE id = ?")) {
        ps.setInt(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            return rs.next() ? rs.getString("name") : "kein solches Produkt";
        }
    } finally {
        giveBack(conn);
    }
}

Ausgabe

Produkt 1: Weißbrot
Produkt 99: kein solches Produkt
Produkt 5: Frischmilch
Produkt 98: kein solches Produkt
Produkt 97: kein solches Produkt
Produkt 7: Weißkäse
offene Verbindungen: 1, frei: 1

C#

string ProductName(int id)
{
    var conn = Borrow();
    try
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT name FROM products WHERE id = $id";
        cmd.Parameters.AddWithValue("$id", id);
        return cmd.ExecuteScalar() as string ?? "kein solches Produkt";
    }
    finally
    {
        GiveBack(conn);
    }
}

Ausgabe

Produkt 1: Weißbrot
Produkt 99: kein solches Produkt
Produkt 5: Frischmilch
Produkt 98: kein solches Produkt
Produkt 97: kein solches Produkt
Produkt 7: Weißkäse
offene Verbindungen: 1, frei: 1

Python

def product_name(product_id):
    conn = borrow()
    try:
        row = conn.execute("SELECT name FROM products WHERE id = ?", (product_id,)).fetchone()
        return row[0] if row else "kein solches Produkt"
    finally:
        give_back(conn)

Ausgabe

Produkt 1: Weißbrot
Produkt 99: kein solches Produkt
Produkt 5: Frischmilch
Produkt 98: kein solches Produkt
Produkt 97: kein solches Produkt
Produkt 7: Weißkäse
offene Verbindungen: 1, frei: 1

JavaScript

function productName(id) {
  const db = borrow();
  try {
    const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
    return row ? row.name : "kein solches Produkt";
  } finally {
    giveBack(db);
  }
}

Ausgabe

Produkt 1: Weißbrot
Produkt 99: kein solches Produkt
Produkt 5: Frischmilch
Produkt 98: kein solches Produkt
Produkt 97: kein solches Produkt
Produkt 7: Weißkäse
offene Verbindungen: 1, frei: 1

Sechs Anfragen mit einer Verbindung: Jede gibt sie zurück, und die nächste nimmt sie. Mit den echten Bibliotheken schreibst du keinen Pool selbst. Du nimmst eine Verbindung aus dem Pool und schließt sie wie in der ersten Lektion des Moduls, und das Schließen gibt sie an den Pool zurück, statt sie wirklich zu schließen:

Java

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
config.setUsername("shop_app");
config.setPassword(System.getenv("SHOP_DB_PASSWORD"));
config.setMaximumPoolSize(10);
HikariDataSource pool = new HikariDataSource(config);  // einmal, beim Start

try (Connection conn = pool.getConnection()) {  // close() gibt sie an den Pool zurück
    // die Abfragen
}

C#

var password = Environment.GetEnvironmentVariable("SHOP_DB_PASSWORD");
var url = $"Host=localhost;Database=shop;Username=shop_app;Password={password};Maximum Pool Size=20";
await using var dataSource = NpgsqlDataSource.Create(url);  // einmal, beim Start

await using (var conn = await dataSource.OpenConnectionAsync())  // Dispose gibt sie an den Pool zurück
{
    // die Abfragen
}

Python

password = os.environ["SHOP_DB_PASSWORD"]
engine = create_engine(  # einmal, beim Start
    f"postgresql+psycopg://shop_app:{password}@localhost/shop",
    pool_size=5,
    max_overflow=10,
)

with engine.connect() as conn:  # das Ende von with gibt sie an den Pool zurück
    ...  # die Abfragen

JavaScript

const pool = new pg.Pool({  // einmal, beim Start
  host: "localhost",
  database: "shop",
  user: "shop_app",
  password: process.env.SHOP_DB_PASSWORD,
  max: 10,
});

const client = await pool.connect();
try {
  // die Abfragen
} finally {
  client.release();  // gibt sie an den Pool zurück
}

Genauer, für Neugierige

Diese Beispiele werden von der Prüfung der Seite nicht ausgeführt, weil sie PostgreSQL und die Pool-Pakete brauchen. Die Standardgrößen: HikariCP 10, Npgsql 100, SQLAlchemy 5 (und bei Bedarf 10 mehr), pg 10.

Ein größerer Pool bedeutet keine schnellere Seite. Die Datenbank hat so viele Kerne, wie sie hat, und 200 Abfragen gleichzeitig warten nur aufeinander. Der Leitfaden von HikariCP rät, mit etwa der doppelten Kernzahl des Datenbankservers anzufangen. Läuft die Seite auf fünf Servern mit je 20 Verbindungen, sieht die Datenbank 100. Wenn es zu viele Verbindungen sind, kommt ein eigener Pool wie PgBouncer vor PostgreSQL.

Die Idee

Ein Backup

Eines Tages wird die Datenbank beschädigt: eine Festplatte, eine falsche Abfrage, eine Migration mit einem Fehler. Eine Kopie ist der einzige Weg zurück. In SQLite schreibt VACUUM INTO die ganze Datenbank im laufenden Betrieb in eine neue Datei, und ATTACH öffnet diese Datei als zweite Datenbank auf derselben Verbindung:

SQLDatenbank: Shop

VACUUM INTO '/sicherung-2026-10-25-0300.db';
ATTACH '/sicherung-2026-10-25-0300.db' AS sicherung;

SELECT
  (SELECT COUNT(*) FROM orders) AS orders_now,
  (SELECT COUNT(*) FROM sicherung.orders) AS orders_in_copy;

VACUUM INTO '/sicherung-2026-10-25-0300.db'; ATTACH '/sicherung-2026-10-25-0300.db' AS sicherung; SELECT (SELECT COUNT(*) FROM orders) AS orders_now, (SELECT COUNT(*) FROM sicherung.orders) AS orders_in_copy;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Hier siehst du, wozu die Kopie gut ist. Ein Skript löscht aus Versehen die Bewertungen des Kaffees, und die Kopie von vor einer Minute bringt sie zurück:

SQLDatenbank: Shop

VACUUM INTO '/sicherung-2026-10-25-1415.db';
DELETE FROM reviews WHERE product_id = 17;

ATTACH '/sicherung-2026-10-25-1415.db' AS sicherung;
INSERT INTO reviews
SELECT * FROM sicherung.reviews WHERE product_id = 17;

SELECT id, product_id, rating FROM reviews WHERE product_id = 17;

VACUUM INTO '/sicherung-2026-10-25-1415.db'; DELETE FROM reviews WHERE product_id = 17; ATTACH '/sicherung-2026-10-25-1415.db' AS sicherung; INSERT INTO reviews SELECT * FROM sicherung.reviews WHERE product_id = 17; SELECT id, product_id, rating FROM reviews WHERE product_id = 17;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Führ das Feld ein zweites Mal aus, und VACUUM INTO bleibt stehen, weil die Datei schon existiert. Im echten Leben ist es genauso: Jede Kopie ist eine neue Datei mit Datum und Uhrzeit im Namen.

Genauer, für Neugierige

In PostgreSQL macht pg_dump eine Kopie einer Datenbank, und pg_basebackup mit einem WAL-Archiv erlaubt die Wiederherstellung auf einen genauen Zeitpunkt („auf 14:14, vor dem Skript“). In MySQL ist es mysqldump. Eine verbreitete Regel ist 3-2-1: drei Kopien, auf zwei Arten von Medien, eine davon an einem anderen Ort.

Probier es aus

Prüfen nach der Wiederherstellung

Eine Kopie, aus der du nie wiederhergestellt hast, ist nur eine Hoffnung. Nach jeder Wiederherstellung (zumindest in eine Testdatenbank) prüfst du, dass die Datenbank vollständig ist:

SQLDatenbank: Shop

PRAGMA integrity_check;

PRAGMA integrity_check;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

und wie viele Zeilen jede Tabelle hat, um sie mit den Zahlen vor der Kopie zu vergleichen.

Zeig für jede der sechs Tabellen des Shops ihren Namen (Spalte table_name) und ihre Zeilenzahl (Spalte row_count).

SQLDatenbank: Shop

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers;

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers;

Prüfen▶ Ausführen

Übung

Hol zurück, was gelöscht wurde, ohne zu verlieren, was neu ist

Die Kopie von gestern Nacht ist in die Tabelle reviews_2026_10_24 wiederhergestellt. Heute Morgen hat ein Skript Bewertungen aus reviews gelöscht, und danach hat ein Kunde eine neue Bewertung geschrieben. Hol die gelöschten Bewertungen aus der Kopie zurück, ohne die neue zu verlieren.

SQLDatenbank: Shop

SELECT * FROM reviews_2026_10_24 WHERE id NOT IN (SELECT id FROM reviews);

SELECT * FROM reviews_2026_10_24 WHERE id NOT IN (SELECT id FROM reviews);

Prüfen▶ Ausführen

Die Idee

Nur so viele Rechte, wie nötig

SQLite hat keine Benutzer: Wer die Datei öffnen kann, kann alles. Server-Datenbanken haben sie, und die Seite meldet sich mit einem eigenen Benutzer an, der nur kann, was er braucht:

CREATE ROLE shop_app LOGIN PASSWORD '…';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO shop_app;

Kein DROP, kein ALTER und kein Zugriff auf die anderen Datenbanken auf dem Server. Wenn jemand in die Seite einbricht, kann er so viel wie shop_app. Die Migrationen laufen mit einem anderen Benutzer, dem die Tabellen gehören, und beide Passwörter bleiben aus dem Code, wie in der ersten Lektion des Moduls.

Test

Teste dich

1. Die Seite öffnet für jede Seitenansicht eine neue PostgreSQL-Verbindung. Was passiert beim Ansturm?

Jede Seitenansicht zahlt fürs Öffnen, und über max_connections hinaus weist die Datenbank neue Verbindungen abNichts, die Datenbank ist schnellDie Datenbank legt von selbst einen Pool an

2. Der Pool hat 10 Verbindungen. Eine Funktion kehrt vorzeitig zurück, bevor sie ihre Verbindung zurückgibt. Was passiert?

Nichts, wenn die Abfrage ein SELECT warJeder solche Pfad verliert eine Verbindung, bis der Pool leer istDer Pool nimmt sie nach der Abfrage von selbst zurück

3. Woran erkennst du, dass ein Backup funktioniert?

Du hast die Datenbank daraus wiederhergestellt, und die Daten sind daDie Datei ist größer als nullDer Backup-Befehl hat keinen Fehler gemeldet

4. Welche Rechte braucht der Benutzer, mit dem sich die Seite anmeldet?

Alle, damit keine Abfrage scheitertSELECT, INSERT, UPDATE und DELETE auf seinen Tabellen, ohne DROP und ohne das Schema zu ändernNur SELECT

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: DELETE: Zeilen löschen · Aus dem Code mit der Datenbank verbinden · SQL-Injection und Parameter · Schema-Migrationen
• Aufgaben zum Thema: Die Datenbank im Programm