Как организовать построчное чтение из очень большой, неизменяемой таблицы?

Question

[hoxnox]

Имеется очень большая неизменяемая таблица (в память не влезет). Необходимо организовать построчное чтение данной таблицы с разных хостов из C\C++ кода (хотя код не важен).
Например на сервере имеем

id
===
1
4
6
87

Хост 10.0.0.1 делает запрос, получает 1. Хост 10.0.0.3 делает два запроса, получает 4, 6. И т.д.

PS: На данный момент реализован демон, который висит на стороне сервера, делает запросы вида «SELECT id FROM table ORDER BY(id) OFFSET K LIMIT N», чтобы наполнять внутренний буфер, из которого раздает ответы запрашивающим хостам. С определенного K вся эта балалайка начинает тормозить.

Не может быть, чтобы такая простая задача не имела простого решения штатными средствами Postgres…

UPD: Была идея в демоне выполнять команду «COPY table(id) to STDOUT» и «подсасывать» из нее данные в буфер. Но есть страх, что postgres оборвет выполнение этой команды на «самом интересном месте» по какой-либо причине.

Comments

[asm0dey]

А это самое «1», «4» итд еще понадобятся когда-нить? А то может можно добавить, например колонку boolean toDelete и в коде демона сделать что-нить а-ля if(offset >=n) delete from...?

[strib]

А что должно произойти когда таблица закончится?

[hoxnox]

strib, Ничего. Приложения на хостах выполняют процедуру остановки.

Answer 1

[ToSHiC]

Можно в вашем демоне открыть курсор для запроса SELECT id FROM table ORDER BY(id) и вычитывать оттуда пачками и раздавать клиентам. В апи есть все нужные функции.

OFFSET тормозит, потому что каждый раз надо делать запрос, сортировать, скипать кучу строк и т.д. Курсоры идеально подходят для описанной задачи, при наличии демона.

Comments

[hoxnox]

ToSHiC Наконец-то нашел в документации то, что требовалось. Просто нужно действовать в рамках одной транзакции и использовать DECLARE CURSOR и FETCH. Правильно я понимаю? Товарищ strib наверное говорил о том же?

[hoxnox]

ToSHiC так как можно работать только в рамках одной транзакции, то обязательно придется использовать трехзвенную архитектуру с демоном. Поэтому решение EndUser с последовательностью тоже имеет место, потому как не требует дополнительных прослоек между БД и хостами.

[strib]

Да, да. В рамках одной транзакции.
Простите, дела навалились, не раписал подробнее.

Answer 2

[Александр Коротков]

Ещё можно делать так:

SELECT id FROM table WHERE id > последний_прочитанный_в_прошлый_раз_id ORDER BY(id) LIMIT N

Тогда при сканировании по индексу не придется каждый раз пропускать кучу записей, а можно будет начать сразу с нужного места.

Comments

[hoxnox]

smagen я полагал, что OFFSET работает быстрее. Думал, что OFFSET+LIMIT работает как курсор.

[Александр Коротков]

Если сканирование идет по индексу, то при OFFSET K LIMIT N будут просканированы первые K+N записей, из них K пропущены, а N отданы клиенту. При WHERE сразу находится нужное место в индексе и сканирование начинается с него.

Я в свое время написал статью про пагинацию в постгресе. Она, конечно, не совсем на ваш вопрос отвечает, но тема OFFSET vs WHERE там раскрыта.

Answer 3

[strib]

Курсор не поможет?

Comments

[hoxnox]

Насколько я понимаю, курсор используется только в pl/pgsql процедурах и не является отдельной сущностью. Как с его помощью организовать вышеописанное?

Answer 4

[EndUser]

По аналогии с оракулом рекомендую поковырять триггеры вместо представления www.depesz.com/2010/10/16/waiting-for-9-1-triggers-on-views/

Comments

[EndUser]

Кроме того можно использовать счётчик вроде
create sequence seq_ids 1
select id from ids offset nextval('seq_ids') limit 1

[hoxnox]

EndUser, не понял — с какой стороны тут прикрутить представление и с какой целью?

[hoxnox]

EndUser, насчёт счётчика. Чем N select'ов вида «OFFSET K LIMIT 1» быстрее одного «OFFSET K LIMIT N»? При таком подходе не нужен демон — это плюс, но прироста скорости так не добиться.

[EndUser]

Ну… Давным давно то ли в Interbase, то ли в Oracle я исследовал возможность контролировать выборку.
В оракуле афаир это был trigger instead of view, но могу соврать. В борланде это была селект-процедура, которая построчно могла формировать, вычислять или подменять строки.

[EndUser]

Я не понял вашего задания. Если вы раздаёте диапазоны «по одному в руки», то limit 1 вполне логичная вещь. Или ваши хосты могут забирать по нескольку строк в одно касание?

[hoxnox]

EndUser, да, решение со счетчиком работает. Но проблема в скорости. Я же писал, что реализован демон, который работает также как ваш счетчик, но забирает во внутренний буффер сразу N строк. Проблема в том, что SELECT… OFFSET K LIMIT N сильно тормозит с определенного К.

[rPman]

храните где-либо последнее полученное значение id и добавляйте к запросу where id>@last_id

Answer 5

[hoxnox]

asm0dey, можно попробовать… Только без дополнительно колонки, а то добавление в нее значений — дело не быстрое. Просто удалять с опереденного n. Правда придется клиентам как-то сообщать, что надо немного потерпеть, пока идет обновление таблицы. А то они нетерпеливые и при отсутствии ответа выпадают с ошибкой.

Answer 6

[hoxnox]

EndUser, то есть идея в том, чтобы периодически формировать представление части таблицы, в качестве буффера для демона? Так получается? А в какой момент формировать представления заранее или по ходу дела? Если заранее, то их может быть очень много, их надо как-то создавать автоматически, именовать, объяснять демону как, в какой момент из какого представления читать.
Если по ходу, то время на формирование представления может быть настолько же большим, как и запрос со смещением.
И вообще будут ли представления работать быстро? Это тоже далеко не факт…

Короче с представлениями нужно исследовать. Слишком много вопросов…

Comments

[EndUser]

Я думаю представление со счётчиком выглядит нормально.
Но у вас опять-таки не понятно, идут ли хосты прямо в базу или архитектура трёхслойная (приклад-аппсервер-бдсервер). Если хосты идут прямо в базу, то использование, как я думаю, счётчика — прямое решение, бо счётчик придуман именно для генерации различных значений для различных клиентских процессов.
Если архитектура трёхслойная, то… можно и счётчиком, вообще говоря. Но тогда опять не ясна задача.
Если раздаются диапазоны (например, филиал просит пачку номеров бланков на будущую неделю), то это одно. Если вы формируете DDoS и каждый LOIC должен брать у центра значения по сто раз в секунду в реальном времени, то это совсем другое.

[EndUser]

Кистати, это ваше вот «С определенного K вся эта балалайка» — вам религия не позволяет primary key с последовательными значениями создать?
И что это вообще за таблица? Может она генерируется в процессе и проще генерировать налету по запросу?

[hoxnox]

EndUser, id — это пример, это не primary key, по своей природе он не может идти последовательно и быть заменен счетчиком либо какой-то генерирующей функцией. Иначе зачем мне эта таблица?!

Повторюсь. Счетчик в порядки и идея хорошая — можно было бы не реализовывать демон. Проблема в тормозах запроса «SELECT id From table OFFSET K LIMIT N» с определенного K. Или надо решить проблему этих тормозов или думать над другим решением…

[EndUser]

create table ids (pk serial primary key, id integer not null);

При вставке строки в ids ваше значение кладёте, как и раньше в id, а pk заполняется сам.

Зато он индексированный и выборка по нему будет быстрой.

[hoxnox]

EndUser, по id у меня тоже индекс построен. serial c primary key работает быстрее bigint c default index для bigint?

[hoxnox]

EndUser, то есть если pk имеет тип serial и индекс primary key, а id тип bigint и индекс поумолчанию, то
SELECT id FROM table OFFSET K LIMIT N ORDER BY pk будет работать быстрее чем просто
SELECT id FROM tabke OFFSET K LIMIT N ORDER BY id?

[hoxnox]

Чувствую тупит из-за ORDER BY, но документация Postgres утверждает, что без него каждый раз будем получать случайные выборки из таблицы.

[EndUser]

Вам индекс по ID вообще нах не нужен. Это пассивные данные для вытягивания.
Индекс нужен только для поиска (и соединения таблиц).

Более того, он вам просто место занимает, тормозя работу.

А вам полагается упорядочить ваши id по какому-то признаку. На данный момент у вас признак упорядочивания отсутствует как таковой. Вам вообще чудом повезло, что строчки вываливаются в том порядке, в котором они вставлены. В общем случае никакая БД, никакой SQL стандарт не обязаны возвращать строки в таком порядке.

Я вам предлагаю ввести такой признак искусственно посредством «PK SERIAL». Это неразрывная последовательность, которую можно сравнивать с генератором счётчика. К тому же индексированная, что позволяет быстро вытаскивать данные из таблицы.

А если индекс будет кластеризованным, да ещё и (как он называется? забыл) с полным покрытием, тогда вообще быстрота будет максимальной с учётом БД.

Короче

create table ids (pk serial primary key, id integer not null);
insert into ids (id) values (ваше значение, незнаю как вы там заполняете таблицу);
create index idx_ids_pk_id on id(pk, id);
create sequence seq_ids 1;

В процессе работы:
select id from ids where pk >= nextval('seq_ids') limit {отсыпьте в граммах сколько вам надо};

В результате значения ID будут упорядочены жёстко согласно порядку их вставки в таблицу и гарантированно не будут путаться. Время поиска будет минимизировано, так как порядок значений индексирован. А если постгрес пользуется индексами с полным покрытием, то вытаскивать ваши данные он будет не глядя в таблицу вообще — так как данные уже есть в составном индексе.

Но это я всё теоретически. Сам я с постгресом не работал никогда.

[EndUser]

мля… если вытягивать больше одной строки, то надо вовремя увеличивать seq_ids, так как по умолчанию оно вырастет на единичку. в общем — учтите.

[EndUser]

Ну и order by pk тоже. Тут вы правы.

[EndUser]

И вообще, не используйте OFFSET — это алгоритм маляра Шлемеля.

[hoxnox]

EndUser, да ORDER BY я тупо забыл написать в вопросе — естественно использую. Попробую serial с primary key — может действительно получу выигрыш. Отпишусь как все прошло. Спасибо за решение с sequence.

[hoxnox]

Вместо OFFSET лучше использовать > и <? Это не будет медленней?! Я думал OFFSET и LIMIT — это внутренняя штука СУБД по которой он быстро может встать в середину таблицы и выплюнуть некоторое количество данных…

[EndUser]

OFFSET сам по себе медленный, как уже объяснил ToSHiC.
Арифметическое сравнение — родная стихия для индексов.

Да, OFFSET и LIMIT — синтаксический сахар, и надо понимать какие базовые операции под ним кроются.

Answer 7

[bugman]

Разрешите взглянуть на задачу под другим углом — может не писать свои велосипеды очереди, а воспользоваться уже готовыми?

А в общем случае нужна некая структура для хранения текущего состояния, либо внутренняя (напр. банально сдвигаемый по исходной табличке маркер), либо внешняя (доп. табличка). У каждой свои плюсы и минусы. Но как только в игру вступят такие аспекты как — контроль доставки / обработки, перепосыл, мониторинг текущего состояния посмотрите снова на первый абзац.