Hey, it's correct to put EXISTS and NOT EXISTS in one WHERE Clause?

Hey, it's correct to put EXISTS and NOT EXISTS in one WHERE Clause?

Yes, why not

Yes, why not

Ah ok thanks ahahah

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

С индексами все гуд, на одну 10гб таблицу приходится 90гб индексов

С индексами все гуд, на одну 10гб таблицу приходится 90гб индексов

стоит призадуматься, если у вас индексы весят больше чем 50% от размера таблицы не избыточны ли они? возможно, вы не используете мощь многоколоночных индексов (и бесплатный поиск по первым колонкам индекса слева направо)

Вот именно) поэтому если есть обычный индекс по дате, чтобы не добавлять всякий ещё функциональных индексов

Вот именно) поэтому если есть обычный индекс по дате, чтобы не добавлять всякий ещё функциональных индексов

так ваша "функция" будет использовать обычный индекс по столбцу с датой (который уже, судя по всему, существует). в чём проблема то?)

Всяких много, но многоколоночные не подходят под сложные сортировкид

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

у меня складывается ощущение что вам вообще нужен clickhouse, но это уже совсем другая история :)

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

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

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

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

Поле + что-то = :параметр1 Так пишут в разработке

?

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

Ну так и почему бы не попробовать создать функцию?

Проект очень большой, этих функцийфункций очень много

Проект очень большой, этих функцийфункций очень много

И что? А если бы возможно было создать синтаксис, то чем бы это было легче? Т.е. чем "поле_даты NEW_BETWEEN age1 AND age2" существенно отличается от "date_age_between(поле_даты, age1, age2)" в этом отношении?

примерно те же усилия займёт научить разработчиков читать планы

я вот всё думаю, как сделать тесты на планы, типа "если в плане два секскана, то переписывай"

я вот всё думаю, как сделать тесты на планы, типа "если в плане два секскана, то переписывай"

Два секскана по табличкам. В одной 100 записей, в другой 200... Пусть переписывают. :P

ну да

если генератор адекватных прод-данных не получается написать, то как ещё? :)

Это —- инструмент снятия дампов. А варианты —- pg_basebackup (или аналогичные сторонние решения), и затем решения, основанные на WAL (архвирование, репликация). В некоторых случаях могут и filesystem snapshots подойти (если fs это умеет).

можете пояснить про pg_dump? хочу для себя прояснить чем он действительно плох для создания резервных копий. просто в документации написано - pg_dump — это программа для создания резервных копий базы данных PostgreSQL. Она создаёт целостные копии, даже если база параллельно используется. Программа pg_dump не препятствует доступу других пользователей к базе данных (ни для чтения, ни для записи).

можете пояснить про pg_dump? хочу для себя прояснить чем он действительно плох для создания резервных копий. просто в документации написано - pg_dump — это программа для создания резервных копий базы данных PostgreSQL. Она создаёт целостные копии, даже если база параллельно используется. Программа pg_dump не препятствует доступу других пользователей к базе данных (ни для чтения, ни для записи).

Тем, что "резервные копии" [сами по себе] —- бесполезны (и я не шучу, т.е. подумайте над этим утверждением).

Т.е. backup —- это не самоцель, а только _средство_ достижения нужных вам RTO/RPO.

Тем, что "резервные копии" [сами по себе] —- бесполезны (и я не шучу, т.е. подумайте над этим утверждением).

но как средство переноса данных с одного кластера на другой (естественно с отключением нагрузки) имеет же право на жизнь ?

и еще я много где слышал упоминания, о том что возможны ситуации, когда по неведомым причинам, дамп оказывался поврежден, и бесполезен, без каких либо ошибок в процессе снятия, бывает такое?

но как средство переноса данных с одного кластера на другой (естественно с отключением нагрузки) имеет же право на жизнь ?

Да, конечно, для однократного переноса (если у вас нет ограничений по времени), они _отчасти_ подходят.

Да, конечно, для однократного переноса (если у вас нет ограничений по времени), они _отчасти_ подходят.

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

и еще я много где слышал упоминания, о том что возможны ситуации, когда по неведомым причинам, дамп оказывался поврежден, и бесполезен, без каких либо ошибок в процессе снятия, бывает такое?

"Повреждён" и "бесполезен" —- совсем разные вещи. Повреждён он не должен быть, если вы не нактнулись на какой-то bug. Дело в том, что pg_dump-ом можно "снять" далеко не все базы (а только достаточно простые), т.е. снимает он _не всё_ (см. pg_dumpall... а некоторые вещи не снимает и вовсе :( ).

"Повреждён" и "бесполезен" —- совсем разные вещи. Повреждён он не должен быть, если вы не нактнулись на какой-то bug. Дело в том, что pg_dump-ом можно "снять" далеко не все базы (а только достаточно простые), т.е. снимает он _не всё_ (см. pg_dumpall... а некоторые вещи не снимает и вовсе :( ).

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

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

И это —- не нормальный backup, а просто действия в стиле "а, нам повезёт, а user-ы подождут". :(

И это —- не нормальный backup, а просто действия в стиле "а, нам повезёт, а user-ы подождут". :(

Я таки прошу прощения, но поднять из backup-а - это тоже из "а юзеры подождут".

И это —- не нормальный backup, а просто действия в стиле "а, нам повезёт, а user-ы подождут". :(

ну бухгалтер будет ждать и не роптать, раз сама все испортит) с архивацией wal ей конечно придется ждать меньше

Я таки прошу прощения, но поднять из backup-а - это тоже из "а юзеры подождут".

Вопрос в том, _сколько_ они подождут (RTO). Для pg_dump "ответ" —- "а хрен его знает". :(

ну бухгалтер будет ждать и не роптать, раз сама все испортит) с архивацией wal ей конечно придется ждать меньше

Ну если у вас ни простой системы, ни повторное выполнение своей работы (другими пользователеми) ничего не стоят... то почему бы и нет. ;)

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

Не только —- ещё права, настройки индивидуальных баз... (см. документацию pg_dumpall).

спасибо, за пояснения. мне важно было развеять миф про внезапные "непригодные" дампы.

Наткнулся на неплохую систему мониторинга "pgwatch2" от Cybertec (Collector(GO)+InfluxDB+Grafana). Кто ни будь уже успел познакомиться? https://github.com/cybertec-postgresql/pgwatch2

Кто нибуть использовал? Или посоветуйте, чем мониторить нагрузку ( сервер на винде) .

Кто нибуть использовал? Или посоветуйте, чем мониторить нагрузку ( сервер на винде) .

Mamonsu вроде умеет и на windows тоже

подскажите, пожалуйста, только камнями сразу не кидайте, получил то что есть в наследство. Есть БД порядка 5 ТБ в ней фактически 2 таблички и куча индексов версия 9.3. есть ли смысл переделывать индексы? как оценить их "размер"? при перестроении индексов табличка будет залочена? filestorage=# \di+ List of relations Schema | Name | Type | Owner | Table | Size | Description —------+-------------------------+-------+-------------+-------------+---------+------------- public | file_data_pkey | index | filestorage | file_data | 500 MB | public | file_header_created | index | filestorage | file_header | 326 MB | public | file_header_key_key | index | filestorage | file_header | 1756 MB | public | file_header_pkey | index | filestorage | file_header | 337 MB | public | ix_file_header_category | index | filestorage | file_header | 684 MB | public | ix_file_header_data_key | index | filestorage | file_header | 1260 MB |

подскажите, пожалуйста, только камнями сразу не кидайте, получил то что есть в наследство. Есть БД порядка 5 ТБ в ней фактически 2 таблички и куча индексов версия 9.3. есть ли смысл переделывать индексы? как оценить их "размер"? при перестроении индексов табличка будет залочена? filestorage=# \di+ List of relations Schema | Name | Type | Owner | Table | Size | Description —------+-------------------------+-------+-------------+-------------+---------+------------- public | file_data_pkey | index | filestorage | file_data | 500 MB | public | file_header_created | index | filestorage | file_header | 326 MB | public | file_header_key_key | index | filestorage | file_header | 1756 MB | public | file_header_pkey | index | filestorage | file_header | 337 MB | public | ix_file_header_category | index | filestorage | file_header | 684 MB | public | ix_file_header_data_key | index | filestorage | file_header | 1260 MB |

4Tb индексов в 5tb базе?

спасибо, за пояснения. мне важно было развеять миф про внезапные "непригодные" дампы.

Если вы обрабатываете ошибки pg_dump, и не натыкаетсь на bug-и PostgreSQL (или чего-то ещё... см. например ongoing thread про fsync в linux ;) ), то вы должны получить dump на какой-то snapshot. Если база достаточно "проста" (в ней нет того / с ней не связано то, что pg_dump не снимает), этот дамп "пригоден". Далее, строго говоря, если он снят с default options, он подходит только для recovery (а не для ETL, например), но можно снимать и ACID-дампы (см. —serializable-deferrable).

Если вы обрабатываете ошибки pg_dump, и не натыкаетсь на bug-и PostgreSQL (или чего-то ещё... см. например ongoing thread про fsync в linux ;) ), то вы должны получить dump на какой-то snapshot. Если база достаточно "проста" (в ней нет того / с ней не связано то, что pg_dump не снимает), этот дамп "пригоден". Далее, строго говоря, если он снят с default options, он подходит только для recovery (а не для ETL, например), но можно снимать и ACID-дампы (см. —serializable-deferrable).

спасибо

подскажите, пожалуйста, только камнями сразу не кидайте, получил то что есть в наследство. Есть БД порядка 5 ТБ в ней фактически 2 таблички и куча индексов версия 9.3. есть ли смысл переделывать индексы? как оценить их "размер"? при перестроении индексов табличка будет залочена? filestorage=# \di+ List of relations Schema | Name | Type | Owner | Table | Size | Description —------+-------------------------+-------+-------------+-------------+---------+------------- public | file_data_pkey | index | filestorage | file_data | 500 MB | public | file_header_created | index | filestorage | file_header | 326 MB | public | file_header_key_key | index | filestorage | file_header | 1756 MB | public | file_header_pkey | index | filestorage | file_header | 337 MB | public | ix_file_header_category | index | filestorage | file_header | 684 MB | public | ix_file_header_data_key | index | filestorage | file_header | 1260 MB |

Табличка не лочится при create index ... concurently

4Tb индексов в 5tb базе?

нет по сути там данные должны быть на 5ТБ,но посмотрев на индексы таким же вопросом озадачился

current_database | schemaname | tablename | tbloat | wastedbytes | iname | ibloat | wastedibytes —----------------+------------+------------------+--------+-------------+---------------------------------+--------+------------— filestorage | public | file_header | 1.0 | 119078912 | file_header_key_key | 0.8 | 0 filestorage | public | file_header | 1.0 | 119078912 | ix_file_header_category | 0.3 | 0 filestorage | public | file_header | 1.0 | 119078912 | ix_file_header_data_key | 0.6 | 0 filestorage | public | file_header | 1.0 | 119078912 | file_header_pkey | 0.1 | 0 filestorage | public | file_header | 1.0 | 119078912 | file_header_created | 0.1 | 0 filestorage | public | file_data | 1.0 | 30760960 | file_data_pkey | 0.7 | 0

нет по сути там данные должны быть на 5ТБ,но посмотрев на индексы таким же вопросом озадачился

Нужность индексов не определяется их размером, совсем. Т.е. вам нужно подробнее смотреть (например, не дублируют ли индексы друг друга, используются ли они в запросах).

т.е. места они не занимают? как я понял один индекс на 1.2 ТБ

от этого хочу понять как оценить размер БД без учета индексов и понять есть ли возможность уменьшить размер перестройкой индекса как вариант

т.е. места они не занимают? как я понял один индекс на 1.2 ТБ

Место они занимают, конечно... только вот _зачем_ вам уменьшать размер?

имею виду может индекс разросся и его имеет смысл перестроить или вообще удалить

Место они занимают, конечно... только вот _зачем_ вам уменьшать размер?

необходимо освбодить какой-то % места ибо БД растет и % утилизцаии увеличивается + при уменьшении размера без последствий производительности положительно скажется на бэкапе и восстановлении БД

Добрый день, суммирую значения одного столбца, но мне нужно до суммирования округлить все значения, подскажите пожалуйста как это сделать? пытался так SELECT SUM(ROUND(votes)) AS sum_id FROM "addresses"

имею виду может индекс разросся и его имеет смысл перестроить или вообще удалить

Так у вас вроде и bloat относительно небольшой... у вас есть проблемы с backup-ом? А (несколько отклоняясь от темы) вы не рассматривали возможность upgrade (на 10.3, например)?

Так у вас вроде и bloat относительно небольшой... у вас есть проблемы с backup-ом? А (несколько отклоняясь от темы) вы не рассматривали возможность upgrade (на 10.3, например)?

это задача следущего шага. сначала надо получить адекватный бэкап + реплику, потом уже будем думать об обновлении. т.е. индекс 1.2 ТБ для такого количества данных это нормально?

Добрый день, суммирую значения одного столбца, но мне нужно до суммирования округлить все значения, подскажите пожалуйста как это сделать? пытался так SELECT SUM(ROUND(votes)) AS sum_id FROM "addresses"

ceil - помогло

relname | rows_in_bytes | num_rows | number_of_indexes | unique | single_column | multi_column —-----------+---------------+-------------+-------------------+--------+---------------+------------— file_header | 14 MB | 1.42432e+07 | 5 | Y | 5 | 0 file_data | 5741 kB | 5.87868e+06 | 1 | Y | 1 | 0

tablename | indexname | num_rows | table_size | index_size | unique | number_of_scans | tuples_read | tuples_fetched —-----------+-------------------------+-------------+------------+------------+--------+---------------- -+-------------+---------------- file_data | file_data_pkey | 5.87868e+06 | 884 MB | 500 MB | Y | 3908284 | 3483628 | 3483621 file_header | file_header_created | 1.42432e+07 | 2981 MB | 326 MB | N | 0 | 0 | 0 file_header | file_header_key_key | 1.42432e+07 | 2981 MB | 1756 MB | Y | 977119 | 361000 | 360995 file_header | file_header_pkey | 1.42432e+07 | 2981 MB | 337 MB | Y | 360995 | 371903 | 360995 file_header | ix_file_header_category | 1.42432e+07 | 2981 MB | 684 MB | N | 0 | 0 | 0 file_header | ix_file_header_data_key | 1.42432e+07 | 2981 MB | 1260 MB | N | 1954238 | 1954238 | 1954238

подскажите, как увеличить количество слотов для подключению к бд?)

подскажите, как увеличить количество слотов для подключению к бд?)

https://www.postgresql.org/docs/current/static/runtime-config-connection.html#GUC-MAX-CONNECTIONS

подскажите, как увеличить количество слотов для подключению к бд?)

слотов репликации?

tablename | indexname | num_rows | table_size | index_size | unique | number_of_scans | tuples_read | tuples_fetched —-----------+-------------------------+-------------+------------+------------+--------+---------------- -+-------------+---------------- file_data | file_data_pkey | 5.87868e+06 | 884 MB | 500 MB | Y | 3908284 | 3483628 | 3483621 file_header | file_header_created | 1.42432e+07 | 2981 MB | 326 MB | N | 0 | 0 | 0 file_header | file_header_key_key | 1.42432e+07 | 2981 MB | 1756 MB | Y | 977119 | 361000 | 360995 file_header | file_header_pkey | 1.42432e+07 | 2981 MB | 337 MB | Y | 360995 | 371903 | 360995 file_header | ix_file_header_category | 1.42432e+07 | 2981 MB | 684 MB | N | 0 | 0 | 0 file_header | ix_file_header_data_key | 1.42432e+07 | 2981 MB | 1260 MB | N | 1954238 | 1954238 | 1954238

Подождите... а где здесь терабайты?

слотов репликации?

ошибка уже перетёрлась, как опять появится- скажу, че-то с superuser связано

хотя коннекты все не от суперюзера

Подождите... а где здесь терабайты?

5.1T base/

слотов репликации?

remaining connection slots are reserved for non-replication superuser connections

Подождите... а где здесь терабайты?

filestorage=# SELECT pg_size_pretty( pg_total_relation_size( 'file_data' ) ); pg_size_pretty —------------— 5143 GB

filestorage=# SELECT pg_size_pretty( pg_total_relation_size( 'file_data' ) ); pg_size_pretty —------------— 5143 GB

Ну а pg_relation_size(...) по fork-ам?

как замерить количество коннектов, текущих к БД

ps aux | grep postgres

добрый день хочу скопировать значения из одной таблицы в другую time psql -c "copy table to stdout" | psql -c "copy table from stdin" выдает такое ERROR: missing data for column "ranking" CONTEXT: COPY moz, line 19: "www.lifewire.com/what-is-sudo-2197466 what is H" выясняется что есть странное значение в одной строке как на скрине ниже как это можно экспейпить и вообще ахтунг пробовал уже и csv, то же самое

как замерить количество коннектов, текущих к БД

Или "SELECT COUNT(*) FROM pg_stat_activity;"

блин, реально лимит кончается периодически

20 коннектов, 4 воркера

надо вдвое поднять, количество воркеров оставлять?

4 ядра

Ну а pg_relation_size(...) по fork-ам?

не понял

не понял

Хмм... я правильно вижу, что вы показываете индексы размером по 2-3 ГИГАбайта, хотя SELECT pg_size_pretty( pg_total_relation_size( 'file_data' ) ); у вас 5 ТЕРАбайт? Поэтому, интересует: SELECT pg_size_pretty(pg_indexes_size('file_data')); SELECT pg_size_pretty(pg_relation_size('file_data', 'main')); —- на всякий случай

Хмм... я правильно вижу, что вы показываете индексы размером по 2-3 ГИГАбайта, хотя SELECT pg_size_pretty( pg_total_relation_size( 'file_data' ) ); у вас 5 ТЕРАбайт? Поэтому, интересует: SELECT pg_size_pretty(pg_indexes_size('file_data')); SELECT pg_size_pretty(pg_relation_size('file_data', 'main')); —- на всякий случай

postgres-# /c filestorage postgres-# SELECT pg_size_pretty(pg_indexes_size('file_data')); ERROR: syntax error at or near "/" LINE 1: /l ^ postgres=# SELECT pg_size_pretty(pg_indexes_size('file_data')); ERROR: relation "file_data" does not exist LINE 1: SELECT pg_size_pretty(pg_indexes_size('file_data')); ^ postgres=# SELECT pg_size_pretty(pg_relation_size('file_data', 'main')); ERROR: relation "file_data" does not exist LINE 1: SELECT pg_size_pretty(pg_relation_size('file_data', 'main'))... упс

postgres-# /c filestorage postgres-# SELECT pg_size_pretty(pg_indexes_size('file_data')); ERROR: syntax error at or near "/" LINE 1: /l ^ postgres=# SELECT pg_size_pretty(pg_indexes_size('file_data')); ERROR: relation "file_data" does not exist LINE 1: SELECT pg_size_pretty(pg_indexes_size('file_data')); ^ postgres=# SELECT pg_size_pretty(pg_relation_size('file_data', 'main')); ERROR: relation "file_data" does not exist LINE 1: SELECT pg_size_pretty(pg_relation_size('file_data', 'main'))... упс

И что это за простыня неудач? ;) Покажите результаты, всё же... "\c filestorage", а не "/c filestorage" (так, на всякий случай).

? блин вечер усталость)

туплю сорри)

И что это за простыня неудач? ;) Покажите результаты, всё же... "\c filestorage", а не "/c filestorage" (так, на всякий случай).

postgres=# \c filestorage You are now connected to database "filestorage" as user "pg.krus". filestorage=# SELECT pg_size_pretty(pg_relation_size('file_data', 'main')); pg_size_pretty —------------— 896 MB (1 row) filestorage=# SELECT pg_size_pretty(pg_indexes_size('file_data')); pg_size_pretty —------------— 510 MB (1 row)

получается индексов не так и много 1.3 ГБ. пора идти читать матчасть)

там скорее всего пол базы - это разные предгрегированные данные для отчетов

какинить кубы ежедневно считающиеся

а старые не удаляются

можно сказать эта БД Архив вложений

бинариники чтоли?

current_database | schemaname | tablename | indexname | real_size | estimated_size | bloat_size | bloat_ratio —----------------+------------+------------------+---------------------------------+------------+----------------+------------+---------------------— filestorage | public | file_data | file_data_pkey | 524640256 | 353771520 | 170868736 | 32.5687428758802679 filestorage | public | file_header | file_header_created | 325541888 | 285712384 | 39829504 | 12.2348322806311180 filestorage | public | file_header | file_header_key_key | 1759387648 | 742834176 | 1016553472 | 57.7788228282480246 filestorage | public | file_header | file_header_pkey | 337223680 | 285712384 | 51511296 | 15.2751123527268310 filestorage | public | file_header | ix_file_header_category | 685588480 | 514277376 | 171311104 | 24.9874536981718246 filestorage | public | file_header | ix_file_header_data_key | 1255170048 | 857112576 | 398057472 | 31.7134297965657001

получается индексов не так и много 1.3 ГБ. пора идти читать матчасть)

Т.е., скорее всего, всё это TOAST.

это 100% TOAST

можете пояснить про pg_dump? хочу для себя прояснить чем он действительно плох для создания резервных копий. просто в документации написано - pg_dump — это программа для создания резервных копий базы данных PostgreSQL. Она создаёт целостные копии, даже если база параллельно используется. Программа pg_dump не препятствует доступу других пользователей к базе данных (ни для чтения, ни для записи).

1. дамп возьмет снапшот и будет его держать всё время работы, база может некисло распухнуть за это время. 2. дамп почему-то не уносит статистику 3. дамп долго накатывается 4. дамп бесполезен для PITR

1. дамп возьмет снапшот и будет его держать всё время работы, база может некисло распухнуть за это время. 2. дамп почему-то не уносит статистику 3. дамп долго накатывается 4. дамп бесполезен для PITR

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

1. дамп возьмет снапшот и будет его держать всё время работы, база может некисло распухнуть за это время. 2. дамп почему-то не уносит статистику 3. дамп долго накатывается 4. дамп бесполезен для PITR

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