Пул от връзки, резервни копия и права

Накратко: Базата на работещ сайт има нужда от пул, който преизползва отворените връзки, от резервни копия, от които наистина се възстановява, и от потребител само с нужните права.

Проблем

Петък вечер

Магазинът пуска промоция и за пет минути идват 400 души. Сайтът спира и в лога пише:

FATAL:  sorry, too many clients already

Програмата отваря нова връзка с базата за всяка страница и я затваря накрая. Отварянето не е безплатно: път по мрежата, вход с парола и в PostgreSQL нов процес в сървъра. А сървърът приема ограничен брой връзки (max_connections, по подразбиране 100). Четиристотин страници наведнъж са четиристотин връзки.

Идеята

Пул от връзки

Пулът държи няколко отворени връзки. Страницата взима свободна, ползва я и я връща, а следващата страница взима същата. Отварянето се плаща веднъж, а базата никога не вижда повече връзки, отколкото е размерът на пула.

Ето малък пул от три връзки, написан на ръка, за да се види какво прави. borrow дава свободна връзка, а ако няма, отваря нова, но не повече от три. giveBack я връща:

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("пулът е празен");
    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 "няма такъв продукт";  // връзката не се връща
            String name = rs.getString("name");
            giveBack(conn);
            return name;
        }
    }
}

for (int id : new int[] { 1, 99, 5, 98, 97, 7 }) {
    try {
        System.out.println("продукт " + id + ": " + productName(id));
    } catch (SQLException e) {
        System.out.println("продукт " + id + ": грешка: " + e.getMessage());
    }
}
System.out.println("отворени връзки: " + opened + ", свободни: " + 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($"продукт {id}: {ProductName(id)}");
    }
    catch (InvalidOperationException e)
    {
        Console.WriteLine($"продукт {id}: грешка: {e.Message}");
    }
}
Console.WriteLine($"отворени връзки: {opened}, свободни: {free.Count}");

SqliteConnection Borrow()
{
    if (free.Count > 0) return free.Pop();
    if (opened == Max) throw new InvalidOperationException("пулът е празен");
    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 "няма такъв продукт";  // връзката не се връща
    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("пулът е празен")
    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 "няма такъв продукт"  # връзката не се връща
    give_back(conn)
    return row[0]


for product_id in [1, 99, 5, 98, 97, 7]:
    try:
        print(f"продукт {product_id}: {product_name(product_id)}")
    except RuntimeError as e:
        print(f"продукт {product_id}: грешка: {e}")
print(f"отворени връзки: {opened}, свободни: {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("пулът е празен");
  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 "няма такъв продукт"; // връзката не се връща
  giveBack(db);
  return row.name;
}

for (const id of [1, 99, 5, 98, 97, 7]) {
  try {
    console.log(`продукт ${id}: ${productName(id)}`);
  } catch (e) {
    console.log(`продукт ${id}: грешка: ${e.message}`);
  }
}
console.log(`отворени връзки: ${opened}, свободни: ${free.length}`);

Виж го

Изтичане на връзка

Функцията за името на продукта има два изхода. Когато продуктът го има, връща връзката. Когато го няма, излиза с return по-рано и връзката остава взета:

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 "няма такъв продукт";  // връзката не се връща
            String name = rs.getString("name");
            giveBack(conn);
            return name;
        }
    }
}

Изход

продукт 1: Бял хляб
продукт 99: няма такъв продукт
продукт 5: Прясно мляко
продукт 98: няма такъв продукт
продукт 97: няма такъв продукт
продукт 7: грешка: пулът е празен
отворени връзки: 3, свободни: 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 "няма такъв продукт";  // връзката не се връща
    GiveBack(conn);
    return (string)name;
}

Изход

продукт 1: Бял хляб
продукт 99: няма такъв продукт
продукт 5: Прясно мляко
продукт 98: няма такъв продукт
продукт 97: няма такъв продукт
продукт 7: грешка: пулът е празен
отворени връзки: 3, свободни: 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 "няма такъв продукт"  # връзката не се връща
    give_back(conn)
    return row[0]

Изход

продукт 1: Бял хляб
продукт 99: няма такъв продукт
продукт 5: Прясно мляко
продукт 98: няма такъв продукт
продукт 97: няма такъв продукт
продукт 7: грешка: пулът е празен
отворени връзки: 3, свободни: 0

JavaScript

function productName(id) {
  const db = borrow();
  const row = db.prepare("SELECT name FROM products WHERE id = ?").get(id);
  if (row === undefined) return "няма такъв продукт"; // връзката не се връща
  giveBack(db);
  return row.name;
}

Изход

продукт 1: Бял хляб
продукт 99: няма такъв продукт
продукт 5: Прясно мляко
продукт 98: няма такъв продукт
продукт 97: няма такъв продукт
продукт 7: грешка: пулът е празен
отворени връзки: 3, свободни: 0

Три търсения на продукт, който го няма, и пулът е празен. Шестата заявка е напълно нормална, но не получава връзка. Истинският пул не дава грешка веднага: чака свободна връзка (в HikariCP до 30 секунди), и страницата виси. Затова изтичането се вижда като „сайтът е бавен“, а после „сайтът не отговаря“.

Идеята

Връщането е във finally

Поправката е връзката да се връща, по какъвто и път да излезе функцията:

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") : "няма такъв продукт";
        }
    } finally {
        giveBack(conn);
    }
}

Изход

продукт 1: Бял хляб
продукт 99: няма такъв продукт
продукт 5: Прясно мляко
продукт 98: няма такъв продукт
продукт 97: няма такъв продукт
продукт 7: Сирене
отворени връзки: 1, свободни: 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 ?? "няма такъв продукт";
    }
    finally
    {
        GiveBack(conn);
    }
}

Изход

продукт 1: Бял хляб
продукт 99: няма такъв продукт
продукт 5: Прясно мляко
продукт 98: няма такъв продукт
продукт 97: няма такъв продукт
продукт 7: Сирене
отворени връзки: 1, свободни: 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 "няма такъв продукт"
    finally:
        give_back(conn)

Изход

продукт 1: Бял хляб
продукт 99: няма такъв продукт
продукт 5: Прясно мляко
продукт 98: няма такъв продукт
продукт 97: няма такъв продукт
продукт 7: Сирене
отворени връзки: 1, свободни: 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 : "няма такъв продукт";
  } finally {
    giveBack(db);
  }
}

Изход

продукт 1: Бял хляб
продукт 99: няма такъв продукт
продукт 5: Прясно мляко
продукт 98: няма такъв продукт
продукт 97: няма такъв продукт
продукт 7: Сирене
отворени връзки: 1, свободни: 1

Шест заявки с една връзка: всяка я връща и следващата я взима. С истинските библиотеки не пишеш пул сам. Взимаш връзка от пула и я затваряш както в първия урок на модула, а затварянето я връща в пула, вместо да я затвори наистина:

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);  // веднъж, при старта

try (Connection conn = pool.getConnection()) {  // close() я връща в пула
    // заявките
}

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);  // веднъж, при старта

await using (var conn = await dataSource.OpenConnectionAsync())  // Dispose я връща в пула
{
    // заявките
}

Python

password = os.environ["SHOP_DB_PASSWORD"]
engine = create_engine(  # веднъж, при старта
    f"postgresql+psycopg://shop_app:{password}@localhost/shop",
    pool_size=5,
    max_overflow=10,
)

with engine.connect() as conn:  # краят на with я връща в пула
    ...  # заявките

JavaScript

const pool = new pg.Pool({  // веднъж, при старта
  host: "localhost",
  database: "shop",
  user: "shop_app",
  password: process.env.SHOP_DB_PASSWORD,
  max: 10,
});

const client = await pool.connect();
try {
  // заявките
} finally {
  client.release();  // връща я в пула
}

По-точно, за любопитните

Тези примери не се пускат в проверката на сайта, защото искат PostgreSQL и пакетите на пуловете. Размерите по подразбиране са: HikariCP 10, Npgsql 100, SQLAlchemy 5 (и още 10 при нужда), pg 10.

По-голям пул не значи по-бърз сайт. Базата има толкова ядра, колкото има, и 200 заявки наведнъж просто чакат една друга. Ръководството на HikariCP препоръчва за начало около два пъти броя на ядрата на сървъра на базата. Ако сайтът е на пет сървъра с по 20 връзки, базата вижда 100. Когато връзките станат твърде много, пред PostgreSQL се слага отделен пул, например PgBouncer.

Идеята

Резервно копие

Базата ще се повреди някой ден: диск, грешна заявка, миграция с бъг. Копието е единственият път назад. В SQLite VACUUM INTO записва цялата база в нов файл, докато тя работи, а ATTACH отваря файла като втора база в същата връзка:

SQLбаза: магазин

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

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

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

▶ ИзпълниВърни заявкатаНова база

Ето защо копието си заслужава. Скрипт изтрива отзивите за кафето по грешка, а копието отпреди минута ги връща:

SQLбаза: магазин

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

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

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

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

▶ ИзпълниВърни заявкатаНова база

Пуснеш ли полето втори път, VACUUM INTO спира, защото файлът вече го има. Така е и в живота: всяко копие е нов файл с датата и часа в името.

По-точно, за любопитните

В PostgreSQL копие на една база прави pg_dump, а pg_basebackup с архив на WAL позволява възстановяване към точен момент („към 14:14, преди скрипта“). В MySQL е mysqldump. Честото правило е 3-2-1: три копия, на два вида носители, едно от тях на друго място.

Пробвай

Сверка след възстановяване

Копие, от което никога не си възстановявал, е само надежда. След всяко възстановяване (поне на пробна база) се проверява дали базата е цяла:

SQLбаза: магазин

PRAGMA integrity_check;

PRAGMA integrity_check;

▶ ИзпълниВърни заявкатаНова база

и колко реда има във всяка таблица, за да се сравни с това преди копието.

Покажи за всяка от шестте таблици на магазина името ѝ (колона table_name) и броя на редовете в нея (колона row_count).

SQLбаза: магазин

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

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

Провери▶ Изпълни

Упражнение

Върни изтритото, без да губиш новото

Снощното копие е възстановено в таблицата reviews_2026_10_24. Днес сутринта скрипт е изтрил отзиви от reviews, а после клиент е написал нов отзив. Върни изтритите отзиви от копието, без да изгубиш новия.

SQLбаза: магазин

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);

Провери▶ Изпълни

Идеята

Толкова права, колкото трябват

SQLite няма потребители: който може да отвори файла, може всичко. Сървърните бази имат, и сайтът влиза със свой потребител, който може само нужното:

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

Без DROP, без ALTER и без достъп до другите бази на сървъра. Ако някой пробие сайта, може толкова, колкото shop_app. Миграциите се пускат с друг потребител, собственик на таблиците, а паролата и на двамата стои извън кода, както в първия урок на модула.

Тест

Провери се

1. Сайтът отваря нова връзка с PostgreSQL за всяка страница. Какво се случва при голям наплив?

Всяка страница плаща отварянето, а след max_connections базата отказва нови връзкиНищо, базата е бързаБазата сама прави пул

2. Пулът е с 10 връзки. Една функция излиза с return, преди да върне връзката. Какво става?

Пулът си я взима сам след заявкатаНищо, ако заявката е била SELECTВсеки такъв път губи по една връзка, докато пулът не остане празен

3. Кога знаеш, че резервното копие става?

Когато командата за копие не е дала грешкаКогато файлът е по-голям от нулаКогато си възстановил базата от него и данните са там

4. Какви права трябват на потребителя, с който сайтът влиза в базата?

SELECT, INSERT, UPDATE и DELETE на таблиците му, без DROP и без промяна на схематаВсички, за да не спре някоя заявкаСамо SELECT

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: DELETE: изтриване на редове · Връзка с базата от код · SQL injection и параметрите · Миграции на схемата
• Задачи по темата: Базата в програмата