💻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

💻Как создать процедуру в 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

💻Исправление клиентской кодировки 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

💻Как получить текстовый дамп базы данных 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

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

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

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

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

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

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

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

@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

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

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

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

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

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

@sqlhub

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

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

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

📎 Статья

@sqlhub

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

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

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

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

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

@sqlhub

🌟 Lightning Studio: Альтернатива Google Colab от создателей PyTorch Lighting

⏩Lightning Studio представляет собой передовую облачную платформу для проектирования и тестирования моделей машинного обучения, разработанную создателями PyTorch Lightning. Эта платформа обеспечивает пользовательский опыт, сравнимый с "iPhone" в сфере инструментов ML, благодаря своей интуитивной простоте и одновременно впечатляющей мощности. Она объединяет инструменты машинного обучения в единый интерфейс, избавляя от необходимости переключения между разными приложениями. Это значительно упрощает процесс создания масштабируемых приложений и конечных точек AI.

⏩Одна из множества функций Lightning Studio способствует повышению производительности разработки на 60%, так как она позволяет легко переходить с работы на CPU на GPU, сокращая таким образом до 80% времени, обычно тратимого на разработку.

⏩Каждому пользователю Lightning Studio предоставляется 22 бесплатных GPU-часа в месяц на базе T4, с опцией покупки дополнительного времени по мере необходимости. Вы будете платить только за время активного использования GPU. Кроме того, для работы над более требовательными проектами вы можете увеличить свои ресурсы, арендовав до 8 GPU A100.

📎 Lightning Studio

@sqlhub

💻SQL-запрос для группировки и взятия уникальных значений

▶️Итак, есть табличка:

Number  Item
------------
202     Book
202     Cartoon
202     Book
207     Book
205     Elephant
207     Elephant
208     Book
209     Biggy
209     Smoke
203     Smoke

Нужно сгруппировать по number и вывести уникальные item для него, наподобие:

202 Book Cartoon
207 Book Elephant
205 Elephant
208 Book
209 Biggy Smoke
203 Smoke

Как это можно сделать?


▶️В целом ничего сложного, с этим справится вот такой запрос:

   SELECT Number,
          group_concat (DISTINCT Item separator ' ')
     FROM TABLE
 GROUP BY Number

@sqlhub

💻Настройка обслуживания PostgreSQL. Основное и простое

⏩Обслуживание БД — это не единственный вопрос, который следует решать администратору СУБД. И при этом не самый первый, если речь идет о сопровождении сервера.

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

— Настроили под себя оптимальным образом настройки PostgreSQL, например, с помощью сервиса pgtune.

— Решены все инфраструктурные вопросы в части работы сети, железа, дисков и так далее.

— Вы понимаете как работает PostgreSQL в части модификации строк, как работает версионирование, что такое “мертвые” строки и так далее.

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

@sqlhub

🖥 Знаете ли вы, что в MySQL есть невидимые столбцы!

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

CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
 INSERT INTO t1 (col1, col2) VALUES(1, 2), (3, 4);

SELECT * FROM t1;
+------+
| col1 |
+------+
|    1 |
|    3 |
+------+

SELECT col1, col2 FROM t1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+

https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

@sqlhub