Как читать таблицу на 100 млн строк и не положить ни память, ни базу

[dkfbm]

Мне одному кажется, что необходимость делать выборки по 10М строк есть признак оч. хренового дизайна всей системы? Ну допустим, удалось допилить базу так, что она отдаст и не грохнется – дальше-то что? Потом это ведь и в коде как-то прожевать надо будет. Как, и главное – зачем? В нормально спроектированной системе такая задача не должна появляться в принципе.

[fonartur]

Для бэкенда приложения согласен — там 10 млн строк в одном запросе означают проблему. Но это ETL: задача — перенести данные из рабочей базы в аналитическое хранилище. Первичная загрузка, бэкфилл, полная перезагрузка из источников вроде 1С, где нет надёжного updated_at, — всё это неизбежно требует прочитать таблицу целиком.

И «прожёвывать» её целиком никто не будет: она идёт кусками по ~29 МБ через чтение → преобразование → запись, и память воркера зависит от числа потоков, а не от размера таблицы. Об этом, собственно, и статья.

Если интересно, как это устроено в коде, можно посмотреть в репозитории: https://github.com/Denvic-Tech/dvt

[dkfbm]

задача — перенести данные из рабочей базы в аналитическое хранилище

Честно говоря, не вижу существенной разницы: если об аналитике задумались только когда в базе скопилось 10М заказов – это и есть хреновое проектирование. А хоть репликация там есть? А бэкапы?

[Freeman_RU]

Целая статья для описания как читать базу по страницам?

[VitaminND]

Сдается мне, Вы двигаетесь к Трино или Спарку.

[akamalov781]

По открытому вопросу про границы без OFFSET. Квадратичная стоимость у вас не из-за OFFSET как такового, а из-за того, что каждый запрос стартует с начала индекса. Два варианта, которые ложатся на все шесть диалектов без ветвления по СУБД.

Первый — один проход с оконной функцией: SELECT id FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM orders) t WHERE rn % 76924 = 0. Это один скан индекса за O(N) вместо N²/2, и ROW_NUMBER есть везде из вашего списка: PostgreSQL, MySQL 8+, MS SQL, Oracle, SQLite 3.25+, ClickHouse. На 10 млн строк это секунды, а не минуты.

Второй, если оконные функции по какой-то причине нельзя — keyset-цепочка: SELECT id FROM orders WHERE id > :prev ORDER BY id LIMIT 1 OFFSET 76923. Запросов по-прежнему 130, но каждый пропускает ровно k строк от предыдущей границы, а не от начала, итого те же O(N). Минус — последовательность, плюс — никаких окон и один SQL на всех.

И третье, уже чисто для PostgreSQL: раз вы и так берёте reltuples вместо COUNT(*), границы можно брать из pg_stats.histogram_bounds — они бесплатны и приближённы, но у вас уже есть предохранитель max_rows_per_partition, который ловит перекос. Для задачи «кусок влезает в память» точность квантилей до строки и не нужна.