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

Question

[mkone112]

Работаю над проектом на django, в качестве бд используется postgres. Бд содержит тысячи таблиц, многие из таблиц могут содержать десятки миллиардов записей.
Проблема - count() выполняется по полчаса.
Я использовал приблизительный подсчет записей вроде:

SELECT reltuples FROM pg_class WHERE relname = "table"

Но результат в 300 раз меньше реального - это слишком большая погрешность.
Есть ли способ, быстро подсчитать число записей, без уменьшения скорости записи?

Comments

[Ромзес Панагиотис]

Бд содержит тысячи таблиц

OMG

[Ромзес Панагиотис]

mkone112
А в таблицах, случаем, не данные временных серий находятся?
Банально, индекс на поле relname добавлен?

[mkone112]

Ромзес Панагиотис, не понял. pg_class - системная таблица, какая разница, как она там вообще реализована?

[Ромзес Панагиотис]

А, упустил этот момент. Не имеет значения что в pg_class, конечно. Речь таки о ваших таблицах, а не системных.

[mkone112]

Ромзес Панагиотис, имеет значение. Перечитайте вопрос. Какой индекс вообще, о чем вы?
https://habr.com/ru/post/490320/
но даже если бы индекс помог - у нас на проде подобные изменения бд могут занимать несколько дней - мне никто не согласует остановку бизнеса даже на сутки.

[Ромзес Панагиотис]

mkone112,
про индекс уже проехали.
А вот это имелось в виду:

А в таблицах, случаем, не данные временных серий находятся?

[mkone112]

Ромзес Панагиотис,

А в таблицах, случаем, не данные временных серий находятся?

Не понял.

[Ромзес Панагиотис]

mkone112,
это когда используется колонка timestamp и какие-то дополнительные колонки с данными. Например, таблица с логированием действий пользователя: время, ID пользователя и действие.

[mkone112]

Ромзес Панагиотис, там есть timestamp.

[Ромзес Панагиотис]

mkone112, спасибо. Это то, что и хотел узнать.

[RazdoR]

А если так

SELECT (reltuples/relpages*pg_relation_size(:'tablename')/8192)::bigint FROM pg_class WHERE oid = :'tablename'::regclass

[Ромзес Панагиотис]

mkone112,
А какой вообще вариант использования подсчета всех записей? Это где-то фигурирует в отчетах, в админке?
Как часто приходится производить подсчет?

[mkone112]

Ромзес Панагиотис, в отчетах и админке, как-то так вышло что оно используется в бизнес процессах.

[Uno]

Интересно, почему пал выбор на постгре?
Все кого я слышал говорят что он только для прототипирования полезен, а при любом масштабировании это огромная головная боль
боюсь, может дороже выйти обслуживать на длительной дистанции чем переехать на что-то более адекватное
может таки задумаетесь о переходе?

[Everything_is_not_so_bad]

Uno, это оффтопик. Нет смысла убеждать того, от кого не зависит решение.

[mkone112]

Uno,

боюсь, может дороже выйти обслуживать на длительной дистанции чем переехать на что-то более адекватное
может таки задумаетесь о переходе?

Насколько длинной? Проекту уже 12 лет минимум. Пока проблемы как-то решались.

[Uno]

Роман Мирр, комменты для того и комменты. Может и зависит откуда мне знать
mkone112, говоря о длительной дистанции в таком ключе больше имеется ввиду скорость развития, нежели чем просто разница в таймштампах
может 12 лет простаивало все и только последние два месяца вы на проблемы наткнулись

[Vitsliputsli]

Uno, любая СУБД имеет свои недостатки, любую сложную БД тяжело масштабировать.
Но что не так с PostgreSQL? И что следует выбирать для нагруженных проектов? Что более адекватное?

[Uno]

Vitsliputsli, почему ко мне эти вопросы?

Все кого я слышал говорят что он только для прототипирования полезен, а при любом масштабировании это огромная головная боль

я ж откуда знаю) если бы знал то писал бы в ответ

[FanatPHP]

очередной вопрос из серии "как мне добраться на другой берег?" с уточнением "на плавсредства начальство денег не выделяет, надо плыть голым, со связанными руками и ногами и кляпом во рту"

Ты бы хоть в заголовке всё это своё садо-мазо описывал

[mkone112]

FanatPHP, все что требуется - в вопросе уже описано, даже с избытком.

[FanatPHP]

Речь не про вопрос, а про заголовок.
потому что ответ на вопрос в заголовке - это select count(*)

Answer 1

[tested life]

Не факт, но может это поможет? А еще комменты там почитай.

Comments

[mkone112]

Видел это. Там есть оверхед на запись.

Answer 2

[Boris Köln]

SELECT reltuples FROM pg_class WHERE relname = "table"

Это и есть самый быстрый способ.
Погрешность можно (нужно) уменьшить, если сделать VACUUM или ANALYZE. Подробнее см. https://www.postgresql.org/docs/9.2/row-estimation...

Comments

[mkone112]

А если count выполняется скажем пару часов - сколько времени уйдет на analyze?

[Boris Köln]

mkone112, если count без join / group by выполняется пару часов - надо менять и программистов, и сервер, и саму БД на принципиально другую.

[mkone112]

Boris Korobkov,

и сервер, и саму БД на принципиально другую.

Это очень круто звучит. Вот только где взять столько бабла?

[mkone112]

Aleksandr-JS-Developer, считали уже - тут либо обходное решение, которое я и ищу, либо отказ от части функционала.

[galaxy]

А если count выполняется скажем пару часов - сколько времени уйдет на analyze?

А это к вам вопрос. Попробовали хотя бы раз?
И вообще, 300-кратная ошибка в оценке reltuples - это швах со статистикой какой-то. У вас тогда и с планированием запросов могут быть огромные проблемы. Вы автовакуум вообще что ли отключили?

[mkone112]

galaxy,

А это к вам вопрос. Попробовали хотя бы раз?

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

И вообще, 300-кратная ошибка в оценке reltuples - это швах со статистикой какой-то. У вас тогда и с планированием запросов могут быть огромные проблемы. Вы автовакуум вообще что ли отключили?

Вот тут к сожалению не подскажу.

[Boris Köln]

mkone112, необходимо сделать полный вакуум. Но он полностью блокирует таблицу. И в зависимости от количества данных и производительности сервера может работать как несколько минут, так и много часов. Это уже вопрос не к программисту, а сисадмину.

[mkone112]

Boris Korobkov,

необходимо сделать полный вакуум. Но он полностью блокирует таблицу. И в зависимости от количества данных и производительности сервера может работать как несколько минут, так и много часов.

Теперь я понял почему его не делают.

[galaxy]

mkone112,

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

почему он должен что-то положить? ANALYZE можно делать потаблично, начните с не очень больших таблиц, чтобы понять, сильно ли он загружает сервер. ANALYZE не блокирует параллельную запись/чтение.

[mkone112]

galaxy,

почему он должен что-то положить?

не знаю - поэтому и спрашиваю.

[foxyhunt]

После такого запроса бд может поплохеть,

Answer 3

[Ромзес Панагиотис]

TimescaleDB

Похоже, что вам нужна TimescaleDB - оптимизированная СУБД для временных серий.

Как раз вашу проблему должен решить данный запрос (в рамках использования TimescaleDB):

SELECT h.schema_name,
    h.table_name,
    h.id AS table_id,
    h.associated_table_prefix,
    row_estimate.row_estimate
   FROM _timescaledb_catalog.hypertable h
     CROSS JOIN LATERAL ( SELECT sum(cl.reltuples) AS row_estimate
           FROM _timescaledb_catalog.chunk c
             JOIN pg_class cl ON cl.relname = c.table_name
          WHERE c.hypertable_id = h.id
          GROUP BY h.schema_name, h.table_name) row_estimate
ORDER BY schema_name, table_name;

https://github.com/timescale/timescaledb/issues/525

И заодно проверьте другие TSDB.

Добавлено
Попробуйте через трюк с EXPLAIN:

CREATE FUNCTION row_estimator(query text) RETURNS bigint
   LANGUAGE plpgsql AS
$$DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
 
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;

https://www.cybertec-postgresql.com/en/postgresql-...
https://wiki.postgresql.org/wiki/Count_estimate
https://www.citusdata.com/blog/2016/10/12/count-pe...

Comments

[mkone112]

Замены бд не будет. Замена бд на таком проекте - это безумно дорого.

[Ромзес Панагиотис]

Да, я понимаю, что это головная боль. У вас варианты: или найти какой-то трюк, который будет приемлемо работать на PG или думать об альтернативных вариантах, одно из которых я предлагаю.

Хотя сам не работал с TimescaleDB, но там пишут, что оно работает как расширение PG и можно мигрировать данные в TimescaleDB hypertable.
https://docs.timescale.com/latest/getting-started/setup
https://severalnines.com/database-blog/how-enable-...

[mkone112]

Ромзес Панагиотис,

Да, я понимаю, что это головная боль.

Это не головная боль. Во первых я такие решения принимать не могу, во вторых это безумно дорого - дешевле купить новое здание побольше и забить их серверами помощнее.

[Евгений]

Неплохо кстати отработало
примерно 1.263878e+08, но мгновенно
и select count(*) 131445756, но за 35секунд

Answer 4

[Владимир Кудря]

Не знаю насколько адекватный вариант, но что если в принципе отвязаться от подсчёта из бд, а привязаться к созданиям и удалениям самих данный, типо если запись успешно добавлена то мы в отдельной табличке, созданной специально для хранения количества записей делаем инкремент числа, а если удаляем то декремент

Comments

[mkone112]

Это снизит скорость записи.

[Владимир Кудря]

mkone112, ну так это решается очередями

Answer 5

[Pretor DH]

Давайте анализировать. Гугл не в помощь в вашем случае.

How to Define an Auto Increment Primary Key in Pos...

При таких больших таблицах важно чтобы была непрерывность индекса.
Это значит, что удалять записи из таких таблиц дурной тон. А из этого следует, что количество записей равно значению следующего индекса. Значит достаточно узнать значение индекса.

Comments

[mkone112]

Это было первым что пришло мне в голову. Расхожения там приличные, в миллионы записей.

[Pretor DH]

Значит нужно восстановить непрерывность. Записать начальное корректное значение и дальше контролировать непрерывность.