PostgreSQL *

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

Цель статьи - поделиться практиками, которые я применил для защиты данных при поднятии собственного VPS в публичной сети. Всегда, когда твой IP открыт всему Миру напоказ и доступен извне внутренних контуров сети - это означает, что любой прохожий может устроить тебе неприятности: похитить твои данные, завладеть твоим сервером и сломать твое замечательное приложение. Я буду приводить пример атаки, показывать, как ее можно заметить и после этого будем разбирать возможные способы защиты.

На одной конференции мне задали вопрос (спасибо Александру!): как сделать стрим в PostgreSQL? Представьте, что имеется bytea и вы к нему хотите что-то дописать. Люди столкнулись с тем, что на это в PostgreSQL  тратится гигантское время  и растет WAL-трафик. 

Расскажу, что с этим возможно сделать — это будет еще один пример оптимизации TOAST (о чем я недавно писал), на на этот раз — для быстрой записи потока бинарных данных. На самом деле мой коллега, Никита Глухов, за несколько часов сделал расширение, которое «вылечило» проблему, и мы даже успели рассказать про это на сессии блиц-докладов на PGConf.Online 2021.

В PostgreSQL есть два типа данных: JSON и JSONB. Первый формат является текстовым хранилищем, в котором json хранится "as is",  второй — бинарным, в нем ключи отсортированы  (сначала по длине ключа, а потом по его названию), дубликаты удалены, а пробелы удалены.

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

Однако у постгреса есть серьёзная проблема, которая сказывается и на производительности JSONB  — это TOAST, и о ней я говорил в первой части. Сегодня я расскажу о том, как мы улучшили JSONB для того, чтобы существенно повысить его производительность.

Приветствую, уважаемые хаброжители!

Так как занимаюсь переводом кода с MS SQL в Postgre SQL с начала 2019 года, то решил продолжить сравнение этих двух СУБД.

В прошлой публикации мы рассматривали отличия в быстродействии MS SQL и PostgreSQL для 1C.

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

Начнем рассмотрение с сопоставления типов.

Недавно мне пришлось объяснять это нашим братьям меньшим на работе, и я решил написать текст, который может пригодиться. В конце вы найдете ссылку на полезный скрипт для MSSQL, а также Postgres и MySQL.

В идеальном мире, если в таблице миллион записей, а разных значений например всего 100K, то на каждое значение приходится по 10 записей. Но что делать, если в список ваших значений затесалось особое значение, например, NULL, пробел или 'n/a'? Для SQL optimizier это головная боль. Для вас тоже.

Картинка иллюстрирует людей со значением 'n/a' в поле SSN

Для систем управления бизнесом часто приходится решать очень похожий класс задач по вычислению количества уникальных объектов на произвольном временном интервале. В контексте CRM это могут быть "пользователи, обращавшиеся на горячую линию на прошлой неделе", "контрагенты, оплатившие за последние 30 дней" или "потенциальные клиенты, с кем был контакт в этом квартале".

Искать в большом количестве фактов «уники» — всегда сложно и долго, если их достаточно много. Если интервалы фиксированы (календарные месяц/квартал/год), можно материализовывать такие агрегаты заранее. А если интервал — произвольный, как тогда эффективно найти ответ?

Большая часть оптимизаций запросов к базам PostgreSQL может выполняться "механически", следуя разного рода маркерам в плане выполнения запроса, которые подскажут, что и как можно ускорить. Но "глубинные" переработки алгоритма, вроде описанных в статье про DBA-детектив, требуют от разработчика детального понимания используемой структуры логических связей.

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

А ведь она уже и так находится "под ногами" в момент анализа плана запроса - надо только лишь удобно увидеть ее!

Подробное описание всего процесса включения ПО в реестр программного обеспечения минкомсвязи от частного лица на личном опыте.

Я разрабатываю библиотеку для работы с Entity Attribute Value (репозиторий), сокращенно EAV (структура базы данных для хранения произвольных данных). В конце прошлой статьи я спросил у вас о чём мне ещё надо написать, вы попросили показать пример использования и сделать замеры быстродействия. Про замеры быстродействия статья была, эта будет о примере использования.

Назначение библиотеки

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

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

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

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

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

Всем привет.

Я разрабатываю библиотеку для работы с Entity Attribute Value (репозиторий), сокращенно EAV (модель базы данных для хранения произвольных данных). В конце прошлой статьи я спросил у вас о чём мне ещё надо написать, вы попросили показать пример использования и сделать замеры быстродействия.

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

Фишка библиотеки в том что бы работать не с представлением построенном на базовых таблицах EAV, а работать с небольшой частью этих данных записанных в отдельное материализованное представление или в отдельную таблицу.

В Новогодние каникулы я сделал замеры производительности и хочу с вами поделиться результатами

Что будем измерять ?

Чтение:

Время вычитывания всех позиций категории

Время формирования параметров фильтрации

Время фильтрации

Запись:

Время добавления новой характеристики (атрибута)

Время добавления новой товарной позиции (модели)

Время обновления товарной позиции

Топы

1. pgAdmin;
2. Navicat;
3. DBeaver;
4. HeidiSQL;
5. Datagrip;
6. OmniDB;
7. Beekeeper Studio;
8. TablePlus;
9. QueryPie;
10. SQLGate;
11. Retool.

На самом деле, еще 9 часов заняла загрузка данных в PostgreSQL, но обо всем по порядку.
Ничто не предвещало грозы — у Заказчика упал сервер и всего-то предполагалось поднять Оракл ;-)

Что я и сделал.

Но ВНЕЗАПНО оказалось, что по договору нужна миграция а дедлайн через 3 дня и все заверте...

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

О роли формата JSON в эволюции реляционных баз данных я недавно рассказал на двух конференциях — HighLoad++ и Saint HighLoad++ 2021. А также о том, что мешает эффективно использовать JSONB (бинарный JSON)  и как с этим можно бороться.

Сегодня посмотрим на особенности работы с TOAST — отдельным хранилищем для длинных записей.  Начну с проклятия TOAST для JSON, а в следующей части расскажу, как это можно использовать в PostgreSQL, и за счет чего получится повысить производительность JSONB.

В начале месяца я прочитал доклад про индексы в базах данных для Saint P Ruby Community и буквально через несколько дней жизнь не замедлила подкинуть мне показательный пример работы индексов, планировщика баз данных и важности обновления СУБД.

Итак, дано: большая таблица projects с кучей (ненужных) индексов, в том числе обычный BTree-индекс по числовой колонке forks_count. У неё есть связь по has_one с таблицей project_dependencies с функциональным GIN-индексом по полю packages в колонке data, в котором поле находится JSON-объект с названиями NPM-пакетов в ключах и их версиями в значениях (куда ж сейчас без джаваскрипта?):

Задача: вытащить примеры проектов, имеющих в зависимостях определённый пакет, показать пользователю наиболее популярные. Показателем популярности и ценности как раз будет количество форков — как правило это какие-то уже раскрученные публичные стартер-проекты.

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

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

С середины прошлого века мы работаем над реляционными базами данных. И они прекрасны. Но сейчас все чаще любят использовать NoSQL всех видов и мастей. И они иногда неплохо ложатся и затыкают собой какое-то мелкое место в проекте. Если я ценю свои данные и мне нужна какая-то надежность, то мне нужны ACID гарантии. Если это всего лишь кеш, данные из которого нужны чтобы ускорить приложение то я с радостью возьму Redis или аналоги. Ведь если он упадет или данные рассогласуются я смогу их восстановить из нормальной базы.

Перед Новым годом мы организовали тайного санту. Для упрощения процесса задумались о боте. Да, мы нашли на просторах гитхаба различные варианты, но решили не лишать себя праздничного веселья от создания бота на коленке. Меня зовут Вильданов Ринат, я python-разработчик в Технократии, и я расскажу, что мы наделали. Возможно, описание нашего пути поможет и вам.

Написали свежий Ansible-плейбук? Отлично. Осталось-то всего ничего, ровно самая малость: установить нужные Python-зависимости на целевые хосты. Именно такой путь предлагается авторами инструмента. Но является ли он единственно возможным, или есть варианты?