Получение топ-10 самых крупных счетов в реальном времени

6. Получение топ-10 самых крупных счетов в реальном времени

Условие задачи:
Необходимо организовать получение в режиме реального времени 10 самых крупных счетов.
Под крупными счетами понимаются счета с наибольшим балансом или остатком средств. Решение должно позволять быстро получать актуальный топ-10 при частом изменении данных, например при поступлении новых платежей, списаний или обновлении баланса.
Нужно продумать способ хранения, обновления и выборки данных так, чтобы список крупнейших счетов всегда был максимально актуальным и получался с минимальной задержкой.

Спойлеры к решению

Подсказки

Основной источник правды — таблица accounts, где хранится актуальный balance.
Чтобы быстро получать топ-10, нужен индекс по balance в порядке убывания.
Запрос должен использовать ORDER BY balance DESC LIMIT 10.
Баланс должен обновляться при каждом платеже, списании или пополнении.
Для денежных значений нужно использовать NUMERIC, а не FLOAT.
Для конкурентных переводов желательно блокировать изменяемые счета через SELECT ... FOR UPDATE.
MATERIALIZED VIEW для строго real-time хуже, потому что её нужно обновлять отдельно.

Решение

CREATE TABLE clients (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL
);

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL,
    account_number VARCHAR(50) NOT NULL UNIQUE,
    balance NUMERIC(14, 2) NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'RUB',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_accounts_client
        FOREIGN KEY (client_id)
        REFERENCES clients(id),

    CONSTRAINT chk_accounts_balance_non_negative
        CHECK (balance >= 0)
);

CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    from_account_id INTEGER,
    to_account_id INTEGER,
    amount NUMERIC(14, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_payments_from_account
        FOREIGN KEY (from_account_id)
        REFERENCES accounts(id),

    CONSTRAINT fk_payments_to_account
        FOREIGN KEY (to_account_id)
        REFERENCES accounts(id),

    CONSTRAINT chk_payments_amount_positive
        CHECK (amount > 0),

    CONSTRAINT chk_payments_accounts_different
        CHECK (
            from_account_id IS NULL
            OR to_account_id IS NULL
            OR from_account_id <> to_account_id
        )
);

CREATE INDEX idx_accounts_balance_desc
ON accounts (balance DESC, id);

Запрос для получения 10 самых крупных счетов:

SELECT 
    id,
    client_id,
    account_number,
    balance,
    currency
FROM accounts
ORDER BY balance DESC, id
LIMIT 10;

Пример обновления баланса при переводе между счетами:

BEGIN;

SELECT id
FROM accounts
WHERE id IN (:from_account_id, :to_account_id)
FOR UPDATE;

UPDATE accounts
SET 
    balance = balance - :amount,
    updated_at = CURRENT_TIMESTAMP
WHERE id = :from_account_id;

UPDATE accounts
SET 
    balance = balance + :amount,
    updated_at = CURRENT_TIMESTAMP
WHERE id = :to_account_id;

INSERT INTO payments (
    from_account_id,
    to_account_id,
    amount
)
VALUES (
    :from_account_id,
    :to_account_id,
    :amount
);

COMMIT;

Идея решения: актуальный остаток хранится в accounts.balance, а быстрый доступ к крупнейшим счетам обеспечивает индекс idx_accounts_balance_desc. При запросе топ-10 база может идти по индексу от самых больших балансов и брать только первые 10 строк, вместо полного сканирования и сортировки всей таблицы.

Для строгого real-time лучше не использовать отдельную таблицу top_accounts, потому что её придётся постоянно синхронизировать. Надёжнее хранить актуальные балансы в accounts и получать топ через индексированный запрос.

Если нагрузка очень высокая, можно дополнительно держать кэш в Redis Sorted Set, где score — это баланс, а member — account_id. Но PostgreSQL всё равно должен оставаться источником правды, а Redis — только быстрым read-cache.

Документация PostgreSQL: B-tree индекс подходит для отсортированных значений, а индексы могут использоваться для ORDER BY, особенно вместе с LIMIT. ([postgresql.org][1])