Ответы пользователя по тегу PostgreSQL
  • Как оптимизировать скорость обновления материализованного представления Postgres?

    @rPman
    Прекратить пользоваться json, разобрав его структуру хотя бы для тех данных, которые требуются независимо друг от друга, и разместить в таблицах. А то это тупо убивает любые методологии, которые созданы в postgres для ускорения работы с этими данными.

    Единственное, где сериализация данных в реляционных базах оправдана - это когда данные запрашиваются целиком из записи и имеют нерегулярную (непрогнозируемую/случайную/сложную) структуру.
    Ответ написан
  • Заполнять базу данных (postgres) через Excel, как?

    @rPman
    Если вы пользователь microsoft office, то берите microsoft access, это именно база данных и среда разработки (построитель запросов, редактор форм, visual basic), одно время это использовалось именно для таких целей, и есть интеграция с excell, (но первично формы в access)

    Как среда разработки и инструмент access в стагнации и сильно устарел, используйте сразу microsoft visual studio, с ее построителем форм, поддержкой баз данных, и все максимально удобно....

    С excel максимально не подходящий для этой задачи инструмент.

    P.s. О стоимости лицензий на все это упоминать думаю не надо?
    Ответ написан
    Комментировать
  • Как правильно прописать конфигурацию данные Postgres в Node js?

    @rPman
    имена переменных бери из строки:
    const connectionString = `postgresql://${process.env.POSTGRES_USER}:${process.env.POSTGRES_PASSWORD}@${process.env.POSTGRES_HOST}:${process.env.POSTGRES_PORT}/${database}`;

    Хост у тебя судя по скрину - localhost, в крайнем случае используй то что возвращает консоль по команде hostname

    чтобы понять что происходит, назначение ключей psql смотри в документации или по команде psql --help
    p.s. смотри внимательно сообщение об ошибке
    в особо извращенных случаях сервер может быть не локальным, а его адрес прописан у пользователя в домашнем каталоге в .pgpass или pg_service.conf
    Ответ написан
    Комментировать
  • 10к строк по базе размером 500кк?

    @rPman
    Избавляйся от like '%...%', это худший способ искать, он индексы не использует
    даже поиск с регулярными выражениями работает лучше (правда там лимит на размер строки)

    Если like используется чтобы искать части url, то храни в базе не целиком а разделенную по полям, в идеале чтобы поиск был на равенство (даже если база увеличится и станет сложнее, например тебе нужно искать по параметрам GET url, т.е. потребуется создать еще одну таблицу на них, скорость все равно будет хорошей)
    Ответ написан
    Комментировать
  • Что лучше, множество запросов на вставку в одной транзакции или один запрос на вставку всех значений?

    @rPman
    Эти два метода вставки не идентичны, в ситуациях с ошибками вставки данных (например ошибка уникальности индекса или любой другой constrainit) до окончания транзакции, в первом случае никакие данные не будут записаны, а во втором, будут записаны данные до возникновения ошибки. Понятно что если все завернуть в единую транзакцию и при возникновении ошибки эту транзакцию отменять, то тогда содержимое базы будет идентично. Но когда речь идет об нестандартных способах обработки ошибок, второй вариант может оказаться удобнее, ведь можно пожелать продолжить вставку данных, с пропуском ошибочных... когда как первый вариант такого не позволит (сообщение об ошибке может даже не сообщить идентификатор, с которым произошла проблема, а если и будет такая информация, то только по одной записи)

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

    Иначе, лучше второй вариант.
    Ответ написан
    Комментировать
  • Можно ли гарантировать надежность снапшота?

    @rPman
    Создание снапшота - атомарная операция (lvm/btrfs/zfs), с точки зрения восстановления базы данных из этого снапшота, это будет то же самое, как если бы вы нажали reset на компьютере, даже лучше - сняли все процессы сервера баз данных с помощью жесткого kill -9 $pid (SIGKILL, его не отловить) ведь записи на диск не прервутся.
    ВАЖНО, если база данных находится на одном томе! невозможно создать атомарно снапшот на нескольких томах. Вариант с запуском всей системы в виртуальной машине и созданием снапшотов ее средствами не рассматриваем, такой конфиг абсурден с точки зрения производительности.

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

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

    p.s. но я бы не рекомендовал такой способ все равно, особенно на постоянной основе.
    Для создания резервной копии на живой базе я рекомендую использовать вторую машину, на которую настроена репликация базы. В этом случае эту вторую базу можно остановить, снять снапшот, возобновить работу (чтобы репликация догнала master - меньше нагрузка на диски, меньше оперативной памяти и допустим слабый процессор) а снапшот спокойно копировать, не опасаясь каких-либо проблем.
    Данный способ нужно использовать на постоянной основе (мало того, требования к backup slave серверу значительно ниже чем рабочему master), и сам процесс создания копии никак не повлияет на работу исходной базы, когда как использование оригинальной базы, даже со снапшотом, значительно понизит ее производительность, так как копирование сильно нагружает дисковую подсистему.
    Ответ написан
    3 комментария
  • Какая версия PostgreSQL-ODBC-драйвера подойдет для Windows 7?

    @rPman
    тут смотрел?
    https://www.postgresql.org/ftp/odbc/releases/

    Если приложение x86 а ОС x64 то посмотри как это тут решили (внизу статьи), само собой подправь название dll под нужную
    Ответ написан
  • Как хранить изображения в PostgreSql?

    @rPman
    Базы данных не очень эффективны с BLOB-ами, большими объемами данных, вопрос даже не в скорости работы с ними, а в том что инструменты резервного копирования к примеру будут работать значительно медленее, чем если копировать файлы того же объема но размещенные на диске.

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

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

    Причина - большое (десятки тысяч) количество файлов в одном каталоге не совсем удобно в том плане, что многие утилиты резервного копирования (да и просто работа с файлами, получение списка или удаление с помощью bash rm, он до сих пор глючный и тормозит), поэтому вместо хранения файла 031432532341234123.jpeg делать подкаталоги 0314/3253/2341/234123.jpeg заранее расчитав количество уровней от прогнозируемого количества файлов.
    Ответ написан
    1 комментарий
  • Как добавить отношения "многие-ко-многим" между таблицами из разных баз данных?

    @rPman
    Если это сделано специально то не надо так делать.

    Одновременная работа с двумя базами данных лишает пользователя встроенного механизма транзакций и заставляет самому следить за согласованностью данных.

    Если прямо очень надо, то для начала нужно понять, какая сторона будет 'главной', кто будет выдавать идентификаторы и следить за их согласованностью. Если главного выбрать не получается, то идентификаторы можно создавать двумя способами:
    - генерация GUID, который специально разработан быть статистически уникальным, на чьей бы стороне он не был создан (128бит)
    - добавление в идентификатор объекта идентификатора ноды, алгоритмы бывают разные, самый простой, определить максимальное количество нод и выделить в идентификаторе несколько бит для их номера (например максимальное количество нод 256, это 8бит в идентификаторе, пусть будут младшие)
    Ответ написан
    Комментировать
  • Как исправить неправильное отображение данных в csv после экспорта?

    @rPman
    майкрософтовский эксель по умолчанию (когда ты открываешь csv файл из проводника) ожидает то ли DOS кодировку cp866 то ли windows cp1251 (это для кирилицы), в общем однобайтовую. Чтобы была возможность выбирать (у тебя utf8), нужно открыть пустой документ и из меню данные выбрать вставить из текста, там откроется диалоговое окно с возможностью выбирать (офис помнит импортированный файл и может его повторно перезагружать с сохраненными настройками).

    libreoffice calc диалоговое окно импорта открывает сразу при открытии .csv

    Еще, настоятельно рекомендую в качестве разделителя выбирать ';' или символ табуляции '\t'
    Ответ написан
    1 комментарий
  • Можно ли корректно забэкпить БД Postgres в Proxmox?

    @rPman
    Если в момент создания снапшота ты штатно останавливал 1c (и в идеале всю операционную систему) то снапшот будет консистентным

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

    Если используется паравиртуализация (скорее всего если гостевая ос - linux то она будет использоваться), то создание снапшота и его восстановление равнозначно снятие процесса через kill (причем скорее всего каким-нибудь ключом -BUS, так как сервера с критичным отношением к данным слушают обычный kill и доделывают критичные записи перед его исполнением), т.е. файловая система не будет нарушена.

    p.s. лучшим и самым оперативным бакапом является настройка master-master репликации базы данных (в твоем случае это pg) на резервную ноду, и при сбоях основной, резервная база будет максимально точной и рабочей базой, если реплицировать и файловую систему (средствами файловой системы или хранилища, так же master-master) на нее же, то ноду можно будет использовать как полноценный сервер, запуск которого - секунды (после чего можно чинить основной сервер, восстанавливать репликацию, при правильной настройке она догонит по изменениям теперь уже резервную базу, и теперь уже эта нода будет резервной к текущей рабочей, так меняя местами ноды можно получить непрерывную работу сервера, устойчивого к сбоям оборудования и сети)
    Ответ написан
    Комментировать
  • Как связать YandexGPT с базой данных?

    @rPman
    При наличии доступа к весам llm, мощностям для finetuning и переобучения, специалистам по нейронным сетям, можно сделать ряд сопроводительны нейронок, которые будут помогать принимать решение о дальнейших действиях, закодировать большое количество 'дорожек' из llm к формальным базам данных, и сделать задачу якобы решенной.

    По факту llm будет решать одну единственную задачу - текстовый интерфейс на естественном языке к интерфейсу к базе данных. Т.е. набор функционала и запросы, которые можно делать к базе нужно будет закодировать заранее.

    Если не углубляться
    - во первых, тебе нужно на основе llm сделать классификатор, который будет из текста пользователя понимать, intent - какое именно действие он желает сделать (для этого обучают нейронку на основе pretrained llm, возможно убирают последние слои, заменяя их на новые, веса которых нужно будет найти и обучить на основе подготовленной базы вопросов пользователей и соответствующих им реакций, с некоторой долей вероятности можно попробовать без обучения спрашивать у умной llm типа chatgpt4, к сожалению chatgpt3.5 и сравнимые с ней плохо с этим справляются, точнее это вопрос творческий и возможно вместо одного универсального промпта тут нужно готовить франкенштейна из большого количества разных, llm-ка от яндекса не подойдет).
    - во вторых, это сбор информации, отличный пример - человек спрашивает брать ли завтра зонтик, мало сети понять что для ответа на вопрос нужно знать какая будет погода завтра (а завтра это через сколько часов?), нужно понять где этот человек находится и возможно задать наводящий вопрос... это я еще молчу про контекст беседы, может пользователь обсуждает о том что взять с собой в поездку на следующей неделе, т.е. вот уже другое время и место (фраза взять с собой может подразумевать - положить в багаж).
    Я видел примеры промптов, с помощью которых из лога беседы можно вытягивать элементы информации (список этих элементов придется заранее забивать соответственно тому интерфейсу, который выбран как целевой на первом шаге), что то типа проанализируй, достаточно ли в данном тексте информации для ответа на вопрос, выделяя в промпте цель, собранную информацию и часть лога беседы, может сжатой через саморизацию с фильтром - нужную для данной задачи (этакая симуляция большого окна контекста).
    Вот тут у директора по развитию ии от яндекс спрашивали как раз

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

    p.s. С некоторой долей риска можно просить на ходу нейронку генерировать sql запросы на основе вопросов пользователя, результат будет с большими ошибками, чем умнее llm-ка тем сложнее эти ошибки будет обнаружить (в автоматическом режиме я имею в виду).

    upd. вот тут из гигачата сделали внутреннюю базу и как то интегрировали нейронку чтобы ею пользоваться, подробности не сообщили но в статье и комментариях есть ссылки на работы и технологии
    Ответ написан
    1 комментарий
  • Как ускорить выборку из базы данных асимметрично зашифрованных данных?

    @rPman
    Так как у тебя сравнение строгое, не нужно дешифровать поле, нужно зашифровать сравниваемое значение. С точки зрения запроса это константа, шифрование пройдет однократно а на поиск будет использоваться индексы.

    Подход шифрования выбран неправильно, вместо шифрования значений, нужно шифровать всю базу данных средствами ос. При запуске базы данных будет требовать пароль, но вся работа будет как не шифрованной базой
    Ответ написан
  • Как перенести базу данных из СУБД Postgresql в MySQL?

    @rPman
    Если ты программист, то написать программу в 10 строчек, копирующую содержимое заданной таблицы из одной базы в другую для тебя не должно быть проблемой. Как минимум на php с использованием PDO проблем не возникнет.

    Судя по тому что структуру таблиц ты уже перенес, заморачиваться с этим тебе не понадобится.

    p.s. экспортировать и импортировать данные в своем формате умеют некоторые универсальные GUI для баз данных, например та же WorkBench, там тоже можно сделать экспорт отдельно в виде insert-ов и отдельно структуру.

    p.p.s. ну перенесли вы данные, но данные это самое легкое, запросы тоже переносить нужно, а вот тут совместимость может быть слабая.
    Ответ написан
    Комментировать
  • Как в предварительном запросе передать переменной $ значение полей?

    @rPman
    Да, параметризовать можно только значения но не наименование полей и таблиц (мало того ты и prepared запрос так не сможешь сделать, с каждым новым значением $(column) будет новый запрос).

    Да, тебе придется валидировать значение в $(column) но это не сложно так как там допускаются только латинские буквы (оба регистра), цифры и подчеркивание. Конечно если ты не используешь идентификаторы_в_ковычках (там любые символы, но зачем тебе стрелять себе в ногу), ну в крайнем случае как разработчик ты знаешь что у тебя в запросе и какие допустимые наименования полей там могут быть
    Ответ написан
    1 комментарий
  • Какие таблицы работают эффективнее?

    @rPman
    На каждую таблицу будет заводиться индекс PK, если количество записей будет одинаковое (в смысле на каждое значение PK будет по одной записи в каждой таблице) - будет хуже, так как размер индекса у каждой таблице растет с ростом количества записей в ней, и много таблиц - много по факту отдинаковых индексов (но для разных таблиц он хранится), поэтому разделять по мелким узким таблицам имеет смысл только для разряженных данных, когда записи появляются только при наличии данных, причем чем больше таких отдельных таблиц тем больше должна быть 'разряженность' данных, чтобы это имело смысл.

    Так как объем ПЗУ значительно выше (при той же стоимости) чем ОЗУ (а требования к ней таковы что индексы желательны влезать в нее, хотя бы для оперативных запросов) то лучше забить на оптимизацию по занимаемому месту данными, чем увеличить кратно требования для оперативного хранения индексов.

    Поэтому - не дроби на большое количество таблиц, пусть будет меньше таблиц с большим количеством полей, но без фанатизма, если видишь что появляется куча условий not null когда как для нескольких таблиц это отрабатывается inner join, то тогда да (not exists лучше чем is null).

    p.s. в догонку про сериализацию данных в одно поле, когда одно поле БД хранит сразу несколько значений - это оправдано и даже рекомендуется, если эти данные не имеют смысл по отдельности, красивый пример ip-адрес и порт как настройки подключения - суть одни данные, и нет никакого смысла разделять их на разные поля базы, за исключением случаев когда возможно потребуется активная аналитика по отдельному полю (например по ip адресу) - 'активная' тут это значит нужно строить индексы и делать частые запросы а не разовый full scan бакэндом.

    Дробление данных на большое количество полей - повышает нагрузку на бакэнд (спорное утверждение, бывает наоборот, но зачастую да), правда для тех разработчиков, где такая оптимизация имеет смысл, такими вопросами не задаются, нужны действительно большие нагрузки.
    Ответ написан
    1 комментарий
  • Можно ли как-то отследить прогресс добавления колонки в БД?

    @rPman
    загляни в файловую систему и посмотри с какой скоростью растет tablespace файлы, так же можно с помощью запроса
    select pg_tablespace_size ('new_tablespace');
    так как в момент добавления колонки таблица копируется (это потому что default значение прописано) значит места должно потребоваться приблизительно в два раза больше чем было изначально, соответственно оценивай приблизительно по скорости роста файла

    https://habr.com/ru/company/yandex/blog/435880/
    Ответ написан
    Комментировать
  • Как лучше хранить денежные суммы в Postgres?

    @rPman
    Главный бонус использования 3 варианта - не будет важна поддержка (т.е. ее отсутствие) чисел с переменной фиксированной точностью на бакэнде. Например в php любой драйвер преобразует числа в double со всеми вытекающими от сюда проблемами.

    Недостаток - можно будет попасть на лимиты стандартных целых чисел (для 64-битных систем, например php), например если на tron ты попробуешь так реализовать работу с токеном usdd, то ты обнаружишь что его точность абсурдна - 18 знаков после запятой, и придется использовать соответствующие классы для работы с ним. Если не преобразовать в запросе тип поля в строку, тот же php pdo преобразует слишком большие целые числа в double

    p.s. bitcoin lightning network добавили пару значимых знаков, советую об этом тоже подумать
    Ответ написан
    2 комментария
  • Как написать SELECT запрос c добавлением столбца?

    @rPman
    читать про left join
    условие на null - поле is null это для фильтрации или использовании в case ... then ..., еще есть функция nullif

    либо в программе ноль проверяй средствами бакэнда или фронтэнда, это зависит от используемых языков ли фреймворков
    Ответ написан
  • Как используя минимум ресурсов, при помощи python, sql и библиотек psycopg2 и pandas составить запрос к базе postgre используя данные dataframe?

    @rPman
    Либо ты загружаешь все себе в память и там ковыряешься либо загружаешь всю или критичную для принятия решения информацию в одну базу (целевую или создав новую) и там ковыряешься

    все остальные варианты - некрасивые

    p.s. судя по коду тебе просто нужно взять идентификаторы из экселя и по ним найти записи в базе, попробуй собрать все идентификаторы в список и сделать огромный select ... where id in (...), кстати можно собирать некоторое их количества и делать блоками по к примеру 1000 записей

    p.p.s. когда народ перестанет делать так и начнет пользоваться именованными параметрами?
    t.id=\'" + str(m_list[i][1]) + '\'
    Ответ написан
    Комментировать