💻Открытая книга «PostgreSQL 16 изнутри

⏩Эта книга для тех, кого не устраивает работа с базой данных как с черным ящиком. Если вы хотите во всем разобраться сами — книга отлично вам подойдёт.

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

📎 PDF

@sqlhub

Читать полностью…

💻Внутренности PostgreSQL: как добавить новую функцию

⏩При разработке пача для PostgreSQL иногда требуется добавить новую функцию, чтобы ее можно было вызывать из SQL.
Собственно, об этом и идёт речь в статье.

⏩Сразу можно отметить 2 момента. Во-первых, некоторые функции имеет смысл добавлять не в ядро системы, а поместить в отдельное расширение — либо стороннее, либо идущее вместе с PostgreSQL и живущее в каталоге /contrib/. Во-вторых, pg_proc.dat является удобной точкой входа для изучения внутренностей PostgreSQL. Также файл бывает полезен, когда вы примерно понимаете, какую функцию ищите, но не знаете ее название.

📎 Статья

@sqlhub

Читать полностью…

💻Большое ли преимущество использования IN вместо нескольких OR в SQL?

▶️Скажем, какой из этих 2 примеров более эффективен?
Этот:

SELECT *
FROM table
WHERE property IN ('a','b','c')

или вот этот:

SELECT *
FROM table
WHERE property='a' OR property='b' OR property='c'

▶️Если говорить про абстрактный SQL - никакой разницы нет. Есть много способов попросить достать одни и те же данные. Впрочем, с точки зрения читаемости человеком in явно выигрывает из-за компактности.

А если рассматривать конкретные реализации - то различия могут быть. Например, postgresql строит разные планы для IN и OR:

explain select * from bigtable where id = 1 or id = 3 or id=4;
                                    QUERY PLAN                                    
----------------------------------
 Bitmap Heap Scan on bigtable  (cost=13.34..25.34 rows=3 width=12)
   Recheck Cond: ((id = 1) OR (id = 3) OR (id = 4))
   ->  BitmapOr  (cost=13.34..13.34 rows=3 width=0)
         ->  Bitmap Index Scan on bigtable_pkey  (cost=0.00..4.45 rows=1 width=0)
               Index Cond: (id = 1)
         ->  Bitmap Index Scan on bigtable_pkey  (cost=0.00..4.45 rows=1 width=0)
               Index Cond: (id = 3)
         ->  Bitmap Index Scan on bigtable_pkey  (cost=0.00..4.45 rows=1 width=0)
               Index Cond: (id = 4)

explain select * from bigtable where id in (1,3,4);
                                   QUERY PLAN                                   
----------------------------------
 Index Scan using bigtable_pkey on bigtable  (cost=0.44..17.37 rows=3 width=12)
   Index Cond: (id = ANY ('{1,3,4}'::integer[]))

Как можно заметить, IN был переписан в другую форму и оценён дешевле, чем несколько эквивалентных OR.
Какие-то другие СУБД может быть переписывают оба запроса в идентичное представление, или же переписывают IN в группу OR. Вопросы производительности необходимо рассматривать только в рамках какой-то конкретной реализации.

Вот такие дела

@sqlhub

Читать полностью…

💻 Статический анализ структуры базы данных

Такой анализ потенциальных проблем в базе данных имеет очевидные преимущества:

⏩анализ можно проводить в любой среде (dev/test/prod) и даже на пустой БД, где нет данных (бэкап структуры без данных)

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

⏩статический анализ может быть встроен в CI как элемент повышения качества разработки

Встраивать такого рода проверки в CI — это однозначно полезная практика. Разовая инвестиция в настройку конвейера исключит спектр потенциальных проблем с расхождением данных.

Подробнее об этом тут:
📎 Статья

@sqlhub

Читать полностью…

💻Исправление клиентской кодировки PostgreSQL с LATIN1 на UTF8

▶️Итак, реальный кейс. На сайте был один баг, который проявлялся при попытке прикрепления к сообщениям файлов, названия которых были на кириллице. В данном случае сайт выдавал ошибку 503. Это происходило несмотря на то, что кодировка базы данных была в UTF8. Тогда как Django выдавал следующее сообщение:

'latin-1' codec can't encode characters in position 55-64: ordinal not in range(256)

Проблема заключалась в том, что кодировка сервера была первоначально выставлена в LATIN1. То есть следующие команды давали следующий вывод:

postgres=# show server_encoding; server_encoding ----------------- LATIN1(1 row) postgres=# show client_encoding; client_encoding ----------------- LATIN1(1 row) postgres=# \encoding  LATIN1

Выполнение команды SET CLIENT_ENCODING TO 'utf8'; не давало результата, после выхода из psql кодировка возвращалась в LATIN1 .


▶️Исправление. Исправление данного бага свелось к настройке кодировки самого сервера. Поскольку в первую очередь сервер русскоязычный, то для исправления была произведена установка русскоязычных пакетов локалей.

sudo apt-get install language-pack-ru

Выполнение процесса русификации

sudo update-locale LANG=ru_RU.UTF-8

И перезагрузка сервера

sudo reboot

После этого кодировка клиента сервера стала UTF8

postgres=# show server_encoding; server_encoding ----------------- LATIN1(1 row) postgres=# show client_encoding; client_encoding ----------------- UTF8(1 row) postgres=# \encoding  UTF8

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

@sqlhub

Читать полностью…

💻PostgreSQL: исполнение запроса из Bash-скрипта

⏩Для выполнения запроса в PostgreSQL от имени пользователя 'postgres' используйте команду:

sudo -u postgres psql -d dbname -c "SELECT * FROM table;"

Здесь dbname — это название вашей БД, table – имя таблицы.
Разумеется, используем права суперпользователя sudo для переключения пользователя на postgres, psql является инструментом командной строки PostgreSQL, а ключ -c нужен для выполнения конкретного запроса.

⏩Для более аккуратного форматирования вывода можно использовать ключ -t, который выводит данные построчно, и -X для того, чтобы .psqlrc не обрабатывался командой psql.

⏩Подобное выполнение SQL-запросов прямо из оболочки может быть особенно актуальным, если вы настраиваете какую-нибудь систему по ssh.
Пишите, кстати, если сталкивались с такими кейсами

@sqlhub

Читать полностью…

💻Коллекция готовых SQL-запросов для PostgreSQL

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

В том числе здесь:
⏩Обсуждаются различные функции и операторы для выполнения запросов и модификации данных

⏩Рассматриваются способы разбиения больших таблиц на N тысяч записей и распараллеливания запросов

⏩Обсуждаются особенности сравнения record и NULL и способы быстрого получения количества записей в большой таблице

⏩Рассматриваются рекурсивные запросы, модификация пользовательских данных (UPSERT) и журналирование изменений таблицы

⏩Рассматриваются модификации схемы данных (DDL) и способы добавления ограничений таблицы и изменения ограничений внешнего ключа без блокирования таблицы

📎 Коллекция

@sqlhub

Читать полностью…

💻Исследователи обнаружили, что GitHub Copilot генерирует уязвимые SQL-запросы (да и не только SQL-запросы)

⏩Не так давно исследователи компании Snyk, специализирующейся на безопасности, рассказали, что ИИ-помощник GitHub Copilot генерирует уязвимый код, если в проекте уже есть такой. Это связано с тем, что нейросеть просто анализирует кодовую базу, в том числе и плохие практики.

⏩Snyk провела эксперимент, чтобы доказать способность GitHub Copilot генерировать уязвимый код на основе такого же в проекте. На 1 этапе исследователи попросили ИИ-помощника сгенерировать SQL-запрос и получили это:

// create query to match input with the description or product name
var query = em.createQuery("SELECT p FROM Product p WHERE LOWER(p.description) like  OR lower(p.productName) like :input", Product.class);

Это качественный и безопасный запрос с именованными параметрами, что делает невозможным использование инъекций.

⏩После этого в соседнем файле проекта самостоятельно написали уязвимый SQL-запрос и снова попросили нейросеть написать код. Во второй раз GitHub Copilot сгенерировал следующий фрагмент:

// create query to match input with the description or product name
String query = "Select * from Product where lower(description) like '%" + lowerInput + "%' OR lower(product_name) like '%" + lowerInput + "%'";

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

Факторы, усугубляющие использование GitHub Copilot:

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

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

— Устаревшие шаблоны. GitHub Copilot может предлагать фрагменты, которые в сообществе уже признаны неверными и содержащими ошибки.

Будьте аккуратнее, в общем)

@sqlhub

Читать полностью…

🖥 Загрузка SQL-таблиц в Pandas DataFrames позволяет анализировать и предварительно обрабатывать данные, используя богатый функционал pandas.

Чтобы загрузить таблицу SQL в pandas DataFrame, передайте соединение с базой данных, полученное от SQLAlchemy Engine, методу pandas read_sql.

@sqlhub

Читать полностью…

🖥 Jless —   полезный инструмент для просмотра JSON-файлов в командной строке.

Отображение данных в удобном виде — с подсветкой синтаксиса для отдельных элементов объекта. Инструмент позволяет обрабатывать и расширять массивы.

Поддерживает полнотекстовый поиск и использование регулярных выражений. 

▪Github
▪Docs

@sqlhub

Читать полностью…

🖥 Полезная шпаргалка SQL

Это руководство охватывает широкий спектр тем, начиная от основных команд SQL и заканчивая продвинутыми темами.

Материао сопровождается практичесикми примерами, которые помогут вам лучше усвоить материал.

@sqlhub

Читать полностью…

SCDB: простая Open Source БД типа «ключ — значение»

Представляем SCDB, то есть Solid-Core-Database. Этот проект еще находится на ранних стадиях разработки и задуман для решения задач и проблем разработчиков.

Что такое SCDB?

▪️scdb  — легковесная, простая и скоростная подсистема хранения данных типа «ключ — значение». Сделана она по примеру архитектуры хранилища Bitcask, эффективной при операциях записи.
▪️Основной принцип проектирования scdb и Bitcask  — журналируемые файловые системы. Здесь все изменения добавляются в конец журнала, а записи очень быстрые, так как выполняются последовательно. При таком построении, помимо скорости, обеспечивается целостность данных: даже в случае отказа системы они легко восстанавливаются благодаря этому методу с возможностью только добавлять.
▪️В scdb реализован процесс объединения лог-файлов. Со временем вместе с записываемыми приложением данными увеличивается и количество лог-файлов. Объединением лог-файлов предотвращается фрагментация, поддерживается производительность. Для данных это как домашняя уборка, после которой хранилище остается организованным и эффективным.
▪️Созданная для беспроблемного хранения и извлечения пар «ключ — значение», SCDB придется кстати разработчикам приложений с быстрой записью данных и простым, но надежным хранилищем.

Пример:

📌 Читать дальше

@sqlhub

Читать полностью…

🖥 Как вы решаете, какой тип базы данных использовать?

Сегодня существуют сотни и даже тысячи баз данных, таких как Oracle, MySQL, MariaDB, SQLite, PostgreSQL, Redis, ClickHouse, MongoDB, S3, Ceph и т. д. Как выбрать архитектуру для своей системы? Вкратце я могу сказать следующее:

🔹Реляционные базы данных. С их помощью можно решить практически любую задачу.
🔹In-memory СУБД это система управления базами данных, которая хранит информацию непосредственно в оперативной памяти.
🔹Базы данных временных рядов. Хранят и управляют данными с временными метками.
🔹Графовая база данных. Подходит для сложных отношений между неструктурированными объектами.
🔹Документное хранилище. Хорошо подходят для больших неизменяемых данных.
🔹Хранилище широких колонок. Обычно используются для больших данных, аналитики, отчетности и т. д., где требуются денормализованные данные.

Очевидно, что тут не описаны все типы баз данных.

Есть ли что-то еще, что вы часто используете, и почему вы выбрали именно эту технологию?

@sqlhub

Читать полностью…

🖥 Поиск строк в одной таблице, отсутствующих в другой, в Oracle Database с помощью

t1 MINUS t2

Любые дубликаты строк только в T1 исключаются из результатов

В 21c добавлен MINUS ALL, который возвращает избыточные дубликаты.

Также добавлен синоним MINUS - EXCEPT, соответствующий стандарту #SQL

Oператор MINUS используется для возврата всех строк первого запроса SELECT, не возвращаемых вторым SELECT.

@sqlhub

Читать полностью…

Новый год — старт для ваших новых карьерных возможностей 🎄
6 февраля приглашаем на Зимнюю ярмарку вакансий, которая пройдёт офлайн и онлайн. Это классная возможность стать частью IT-сообщества Яндекса и познакомиться с теми, кто создаёт сервисы с многомиллионной аудиторией.

Что вас ждёт
🔸 Вакансии для стажёров
Узнаете о новых вакансиях и задачах, а также сможете лично пообщаться с командами и подать заявку туда, где понравится больше всего.
🔸 Полезные лекции
Узнаете о технологиях и кейсах из практики от экспертов Яндекса. Лекции пройдут по основным направлениям стажировки: бэкенд, фронтенд, мобильная разработка, аналитика и машинное обучение.
🔸 Нетворкинг со стажёрами Яндекса
Узнаете, как они проходили отбор, как готовились и чем планируют заниматься дальше. Сейчас в Яндексе одновременно стажируются более 600 человек — им есть что рассказать.

Как попасть на Зимнюю ярмарку вакансий
Чтобы попасть на ярмарку, нужно пройти предварительный отбор — решить задачи на Яндекс Контесте до 31 января включительно. Мы проверим решения, оценим анкеты и позовём лучших на день стажёра в Москве. Приглашения придут до 2 февраля включительно.

🍭 Закрытое шоу
Тех, кто решит все задачи из Контеста, мы пригласим на вечернее закрытое шоу «Всё в плюсе». Это соревнование между различными бизнес-группами Яндекса, в которых примут участие и стажёры, и топовые разработчики. Знакомство с различными сервисами, нетворкинг с действующими стажёрами, вечеринка с кавер-группой и диджеем — это то, что ждёт вас на шоу.
Узнать подробности и подать заявку — https://yandex.ru/yaintern/intern-day.

Читать полностью…

💻Утилита стратегического мониторинга PostgreSQL

⏩Основная задача стратегического мониторинга — предоставление количественной информации о наиболее ресурсоемких активностях СУБД. Такая информация очень полезна для анализа нагрузки СУБД, например, при проведении нагрузочного тестирования.

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

⏩Видео будет интересено всем, кто работает с СУБД Postgres — администраторам баз данных, инженерам нагрузочного тестирования и разработчикам приложений, непосредственно взаимодействующих с базами данных Postgres.
Enjoy)

📎 YouTube
📎 Презентация

@sqlhub

Читать полностью…

💻Параллелизм базы данных в PostgreSQL

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

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

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

📎 Подробнее о параллелизме БД можно почитать тут, рекомендую. Вот ещё неплохая статья в тему

@sqlhub

Читать полностью…

⚡️ Бесплатный видеокурс. SQL практикум на базе MySQL

Спроектируем базу данных популярного мессенджера и научимся писать запросы к ней (4.5 часа качественного видеоматериала).

💡 Курс

@sqlhub

Читать полностью…

💻Как получить текстовый дамп базы данных Postgres в Heroku

Для создания дампа базы данных PostgreSQL в текстовом формате на Heroku выполните два действия:

1️⃣Создайте и загрузите резервную копию:

   heroku pg:backups:capture && heroku pg:backups:download
   

2️⃣Преобразуйте загруженный файл в SQL-формат с помощью pg_restore:

   pg_restore -f mydb.sql latest.dump
   

Здесь mydb.sql — это конечный файл с вашими данными. До начала работы убедитесь, что у вас установлен Postgres для использования команды pg_restore.

🔘Вот в принципе и всё, больше деталей и похожих кейсов тут

@sqlhub

Читать полностью…

💻Как создать процедуру в PostgreSQL?

⏩Итак, для создания процедуры в PostgreSQL используется язык PL/pgSQL, который является расширением языка SQL. Вот пример создания простой хранимой процедуры в PostgreSQL:

CREATE OR REPLACE FUNCTION 'имя_процедуры(параметры)' RETURNS 'тип_результата' AS $$
DECLARE
    -- переменные_локальные тип_переменной
BEGIN
    -- Тело процедуры
    -- Может содержать SQL запросы, управляющие конструкции и другие операторы
END;
$$ LANGUAGE plpgsql;

⏩Пример простой процедуры, которая выводит текстовое сообщение:

CREATE OR REPLACE FUNCTION hello_world()
RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

⏩После выполнения этого запроса, процедура hello_world будет создана в вашей базе данных. Чтобы вызвать эту процедуру, используйте следующий запрос:

SELECT hello_world();

Таким образом, вы можете создавать и вызывать процедуры в PostgreSQL с помощью PL/pgSQL.

@sqlhub

Читать полностью…

Yandex Open Source Jam — необычное мероприятие от Яндекса

Что вас ждет:

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

Встречаемся 23 апреля в Москве («Агломерат») и онлайн (трансляция на сайте). Переходите по ссылке для регистрации.

Читать полностью…

🖥 Допустим вы хотите получить минимальное или максимальное значения из индексированного столбца.

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

База данных Oracle знает об этом, поэтому может оптимизировать данный запрос:

INDEX FULL SCAN (MIN/MAX)

или

INDEX RANGE SCAN (MIN/MAX)

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

@sqlhub

Читать полностью…

🖥 Огромная шпаргалка с готовыми запросами SQL (SQLite)

▪ Смотреть

@sqlhub

Читать полностью…

🖥 SQL в Фокусе: Полное Руководство. 100 ключевых Вопросов с собеседований. Часть 1.

▪Читать

#junior

@sqlhub

Читать полностью…

Очное обучение Data Science в НИУ ВШЭ — старт 14 февраля

❔Кому подходит программа «Специалист по Data Science»?

✔Начинающим: Вы изучите основы программирования, математики, машинного обучения и работы с Big Data.
✔Специалистам не из IT: Освоите новые методы и инструменты для работы с данными в вашей области.
✔Программистам: Подтянете свои знания в алгоритмах, структурах данных и научитесь обучать нейронные сети.

За 1.5 года вместе с опытными преподавателями и практикующими специалистами вы:

✔Разберетесь в направлениях Data Science: Изучите программирование на Python, математику для анализа данных, прикладную статистику, классические ML-модели и нейросети, а также основы обработки текстов, звука и изображений.
✔Разовьете hard skills: Освоите SQL, Python, Jupyter Notebook, Spark, Tensorflow, Pytorch и другие инструменты.
✔Выполните 3 проекта: Напишете чат-бота, программу для машинного обучения и защитите финальный проект. Это хорошая возможность пополнить портфолио, особенно для начинающих.
✔Получите самые актуальные знания по специальности и сможете работать специалистом по Data Science в крупных IT-компаниях.

Обучение очное в кампусе НИУ ВШЭ в Москве.

Старт: 14 февраля
Стоимость: 58 125₽ раз в 2 месяца при рассрочке без переплат: 8 платежей. Или 465 000₽ за весь курс.
Полная программа и регистрация: по ссылке.

Реклама. НИУ ВШЭ.
ИНН 7714030726
Erid: 2SDnjd72JW4

Читать полностью…

@sqlhub

Читать полностью…

🖥 DuckDB

Теперь в DuckDB можно подключать базы данных MySQL, Postgres и SQLite в дополнение к базам данных, хранящимся в ее собственном формате.

Это позволяет считывать данные в DuckDB и перемещать их между этими системами удобным способом.

▪Подробнее
▪Github

@sqlhub

Читать полностью…

🔍 Manticoresearch

Manticore Search - это простая в использовании быстрая база данных с открытым исходным кодом для поиска. Хорошая альтернатива Elasticsearch. От других решений его отличает следующее:

Она очень быстрая и более экономичная, чем альтернативы, например, Manticore:

▪182 раза быстрее, чем MySQL для небольших датасетов
▪В 29 раз быстрее, чем Elasticsearch для анализа журналов
▪В 15 раз быстрее, чем Elasticsearch для небольших наборов данных
▪В 5 раз быстрее, чем Elasticsearch для средних датасетов
▪В 4 раза быстрее, чем Elasticsearch для больших датасетов

github.com/manticoresoftware/manticoresearch

@sqlhub

Читать полностью…

🔥 Разбор гигантских наборов данных JSON за считанные секунды с помощью JSONalyze, новейшего механизма запросов.

JSONalyze принимает JSON (например, ответ API) и преобразует его в таблицу SQLite.

Затем он выполняет точные SQL-запросы к этим данным, чтобы ответить на ваш запрос!

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

https://docs.llamaindex.ai/en/latest/examples/query_engine/JSONalyze_query_engine.html

@sqlhub

Читать полностью…

🖥 MongoDB для Python - NoSQL база данных.

https://www.youtube.com/watch?v=THXT_SGo6xs

@sqlhub

Читать полностью…