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

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

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

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

    p.p.s. когда народ перестанет делать так и начнет пользоваться именованными параметрами?
    t.id=\'" + str(m_list[i][1]) + '\'
    Ответ написан
    Комментировать
  • Есть ли разница для скорости работы БД при установке типа text, а не varchar 128?

    @rPman
    varchar/text/bytea в postgres используют одну и ту же технологию хранения и производительность будет напрямую зависеть от реального размера строк и от оптимизаций
    https://habr.com/ru/company/tensor/blog/498292/
    Ответ написан
    Комментировать
  • Как лучше хранить и пересылать время: как полную дату или только время?

    @rPman
    храни и передавай как число секунд, огромное количество готовых функций, во всех языках, не подходит только для исторических дат (все что старее unixtime 1970г, во всех остальных случаях - удобно, просто, быстро.

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

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

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

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

    Отделяй модуль/место сбора оперативных данных от их анализа, например делай две базы, отличающиеся как по месту размещения так и по типу (например оперативные данные можно просто собирать в ram, с космическими скоростями, без sql отдельным приложением-демоном), а аналитику собирать паралельно и периодически, под задачу.
    Ответ написан
    1 комментарий
  • Можно ди обойти ошибку "duplicate key value violates unique constraint" при обновлении?

    @rPman
    duplicate key value violates unique constraint
    google translate:
    повторяющееся значение ключа нарушает уникальное ограничение

    ничего не напоминает?

    один из индексов типа unique в таблице не позволяет, значение после обновления будет повторяться
    Ответ написан
  • Как хранить изображение в бд?

    @rPman
    Все зависит от проекта
    Есть ситуации, когда ссылки на изображения не хранятся в базе данных а размещены только в шаблонах и html файлах, которые так же являются файлами (т.е. полностью вне базы данных)... не всегда такой подход удобен (особенно при обслуживании базы, например поиск файлов на которые никто не ссылается) но он самый оптимальный, ведь нет оверхеда по работе с базой данных.

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

    Просто помните - файловая система это тоже база данных, современные файловые системы очень эффективно обрабатывают запросы, с индексацией по части ключа (путь к файлу - каталоги в нем)... а некоторые, типа btrfs/zfs/xfs через снапшоты предлагают встроенный аналог транзакций как многие sql базы данных (только пользоваться нужно с умом этим).
    Ответ написан
    Комментировать
  • Как узнать общее количество посещений моего сайта?

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

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

    Сторонние скрипты от рекламных сетей дадут готовую информацию, но не забывай, таким образом ты отдаешь своих пользователей (их приватную информацию) 'на растерзание' им...
    Ответ написан
    Комментировать
  • Какую базу выбрать для bigdata?

    @rPman
    Так как автор молчит про особенность своей задачи, значит можно предположить что угодно? например write once read many базы данных? с запросом только данных по временному интервалу?

    Пили самописное что-нибудь на основе файлов.

    Современная файловая система, если это не какой-нибудь fat, - это отличная key-value база данных, причем самая быстрая из возможных, но без инструментов индексирования (кроме поиска по имени, если дробить его по подкаталогам то не будет лишних накладных расходов, например на обслуживание), а так как у автора временные ряды, раскидать по файлам-каталогам соответственно временным интервалам (дни или часы), разбив данные по еще какому либо признаку, если нужна фильтрация по нему, можно получить искомый результат фактически забесплатно (нечего там кодить). Например, если тебе нужны редкие транзакции (атомарно менять большой объем данных не ломая чтение) то какой-нибудь btrfs представит этот функционал за бесплатно на основе снапшотов.

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

    Некоторые сложности может создать задача частых запросов на чтение, в этом случае нужно физически разнести голову массива данных и основное хранилище (например голова на ssd все остальное на hdd), а перенос проводить в момент наименьшей нагрузки, ну само собой можно и все на ssd если денег хватает, просто когда такой поток данных, сразу терабайты мерещатся
    Ответ написан
    Комментировать
  • Корректна ли будет такая организация Docker-контейнеров?

    @rPman
    Зачем ты разделяешь части приложения по доккер образам?

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

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

    так что ответ, хватит одного максимум двух образов
    Ответ написан
  • Можно ли в postgres записывать данные на время?

    @rPman
    для такой задачи в базу данных записывают не boolean, а время на момент записи, а в select запросе делают
    select now()-start_time<=3 часа from ....
    Ответ написан
    Комментировать
  • Как защититься от двойного списания в многопоточном приложении?

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

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

    более красиво, вместо одного поля, заводи специальную таблицу - текущие сделки, где в соответствии со статусом вычисляй этот заблокированный баланс (статусы сделка начата, сделка совершена или сделка обломилась), это актуально как раз на тот случай, когда сделка совершается достаточно долго чтобы не проводить ее в пределах транзакции базы данных (а то попадет пользователь на момент обслуживания к примеру перезапуск бд на обновление, и его транзакция пропадет), к тому же эта таблица у тебя уже точно есть, только статусов побольше добавить
    Ответ написан
    Комментировать
  • Индексация идёт уже 2 недели, в чем у меня ошибка?

    @rPman
    значит узкое место почти наверняка диск.

    Пальцем в небо, файловая система на которой таблеспейсы лежат какая? случайно не cow (btrfs/zfs/xfs)? с ними отвратительно работают базы данных, так как частые записи в файл генерируют сильную фрагментацию. В этом случае перед тяжелой обработкой хотя бы дефрагментируй файлы базы и отключи cow фичу на таблеспейсах

    неплохим тюнингом может оказаться (на выбор):
    * разместить базу в ram диске (буквально, залить на сервер в облаке, обработать данные, залить назад, работая напрямую с таблеспейсами, но версия софта должна совпадать до последней цифры)
    * разместить базу целиком на ssd (даже если это будет потребительский и дешевый)
    * добавить в систему ssd кеш для hdd с помощью например bcache (включенный на запись), правда для линейной обработки базы это может дать мало пользы, но вообще это неплохой способ на порядок поднять производительность за дешево (в одном месте я использовал фичу virtualbox со снапшотами в файл, есть и у kvm, когда последующие записи шли не на исходный образ а на другой диск, и он ssd)
    * разместить таблеспейс для индексов (а может и каждую таблицу отдельно) на другом физическом устройстве (hdd, ssd или даже в ram), требования к размеру тут обычно низкие, ключевое слово - исключить последовательные чтения/записи на одно устройство.
    * разместить журнал (например ext4) на ssd диск (хватит пары гигабайт) или по хардкору даже отключить его (очень опасно, можно получить кашу из данных при сбое питания, но как временное решение пока идет долгая операция, при наличии всех бакапов, оправдано) - наименьшая оптимизация, но при частых мелких записях это заметно
    Ответ написан
  • Как проще и правильнее перенести одну запись из бэкапа?

    @rPman
    Если бакап достаточно свежий, можно сравнить бакапы (построчно каждую таблицу но эта операция достаточно простая и скрипт можно сгенирировать на основании структуры, вытаскивая ее из ddl, да тут структура то и не нужна), если искать разницу что именно удалено (то что есть в бакапе но нет в текущей базе, проверку делать по полю id (тот что primary key, ddl нужен именно чтобы выявлять составные), базы можно развернуть на одном сервере) то можно получить вполне готовый срез записей, просмотреть их глазками будет гораздо проще чем всю базу целиком.

    посмотри pgdiff

    Чем меньше расстояние в изменениях между бакапом и текущей базе, тем меньше лишних записей получится в результате.
    Ответ написан
    Комментировать
  • Как сделать поиск с лишним словом в postgresql?

    @rPman
    замени слово которое не важно на символ % и условие должно быть where field like 'xxx % yyy'
    Ответ написан
  • Как синхронизировать базы данных Test и Production?

    @rPman
    Правильно - исключить 'ручную' работу в процессе переноса релизных изменений в продакшен базу.
    Т.е. задача разработчика делать скрипты, которые будут приводить базу данных предыдущей версии к следующей, при этом это не только обновление структуры (это кстати можно сделать автоматически, сравнив базы разных версий, гуглить ddl diff, к примеру для postgres это pgadmin shema diff) но и данные, например наполнение новых полей данными или к примеру в старой версии поле было текстовое в 'свободном формате', а в новой на его основе целая структура (вырожденный пример - был адрес текстовой строкой а стал чуть ли не целым ФИАС).

    Цель - процесс установки обновления может выглядеть так: делаем реплику продакшн -> накатываем обновление -> проводим тестирование -> если все хорошо - повторить на продакшн, остановив ее, иначе отправляем багрепорт разработчикам. Причем все это скриптами автоматически (само собой если для тестирования есть таковые).

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

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

    @rPman
    Хеш индекс для базы данных не требует уникальности хеша (т.е. это может быть 2-байтовый хеш с 65536 количеством значений для базы в миллион строк на каждое значение будет много коллизий), это механизм нужен больше чтобы поделить трудоемкость сканирования всей таблицы на возможное количество значений хеша, т.е. в индексе для записей с равным хешем запрос пройдет по всем таким (если у хеша коллизия для id равным 1 и 10 то в таблице индекса у этого хеша будет 2 записи и при поиске нужной обе они будут просканированы).

    тут или тут чуток про это есть
    Ответ написан
  • Позволяют ли партишены делать такое?

    @rPman
    Вставка данных без индексов должна работать максимально быстро, у вас что, на чтение очень много запросов?
    p.s. диск хоть ssd?

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

    table_base_latest куда бы попадали данные только за последние 365 дней, а остальные а остальные складирвоались без в какой-нибудь table_base_tail который бы содержал все оставшиеся данные?
    на сколько я знаю штатный инструмент это не умеет, ведь данные придется как то перемещать между партициями

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

    Имхо 'овчинка не стоит выделки', точнее очень мало случаев когда геморой реализации даст адекватное повышение производительности.
    Ответ написан
    4 комментария
  • Как правильно работать с большим количеством данных?

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

    Без индексов скорее всего никуда, если в запросе идет фильтрация по полю, то если не создать на него индекс, этот запрос и будет создавать нагрузку

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

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

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

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

    p.s. диск на сервере hdd или ssd? если не хватает денег на большой ssd, настрой маленький ssd как кеш к медленному hdd (linux bcache), даже когда данные полностью не влезают в кеш, это дает заметный прирост, потому что hdd начинает менее случайно двигаться, плюс добавляется буферизация записи (данные записываются на ssd и лежат там пока hdd не освободится), так же дает эффект ускорения записи вынос журнала файловой системы (если ext4) на ssd (требуется пара гигабайт от силы).

    Осторожно, если высокие требования к надежности хранения, кеширующий ssd в режиме записи лучше пусть будет хотя бы raid1, так как иначе при смерти ssd починить данные, размазанные по диску и кешу будет очень сложно. Для кеша только для чтения это не так критично (при смерти ssd сервер просто упадет, не убивая данные).

    Но мне больше нравится резервирование на лету (настроить онлайн репликацию, пусть и на слабый, бакап сервер)
    Ответ написан
    1 комментарий
  • Как упаковать Postgress в бинарник?

    @rPman
    смени postgres на sqlite

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

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