PostgreSQL: обеспечение уникальности записи с проверкой даты валидности

Как бы вы решали такую задачу? Предположим, есть таблица с купонами, и у купонов есть некая дата устаревания valid_until. Вам надо обеспечить такое ограничение (constraint) на уровне БД, чтобы у одного человека мог быть только один действующий купон.

Т.е., таблица изначально выглядит так:

CREATE TABLE coupons (
id bigint primary key generated by default as identity,
user_id bigint not null,
created_at timestamp not null,
valid_until timestamp not null
)

https://habr.com/ru/companies/karuna/articles/794468/

#db

👉 @database_info

PGlite

Это сборка WASM Postgres, упакованная в клиентскую библиотеку TypeScript, которая позволяет запускать Postgres в браузере, Node.js и Bun, без необходимости установки каких-либо других зависимостей. Его размер составляет всего 3,7 Мб в зажатом виде.

https://github.com/electric-sql/pglite

#db

👉 @database_info

UNION в SQL

Оператор UNION используется для объединения результатов двух или более запросов SELECT в один набор результатов. Команда UNION отличается от JOIN-операторов, которые объединяют столбцы из двух таблиц. UNION создает новую таблицу, помещая все строки из двух исходных таблиц в таблицу результатов и располагая эти строки друг над другом.

Далее приведены основные правила объединения наборов результатов двух запросов SELECT с помощью UNION:

- Количество и порядок столбцов должны быть одинаковыми во всех запросах.
- Типы данных соответствующих столбцов должны быть совместимы.
Если эти критерии выполняются, то таблицы совместимы с оператором UNION.

Синтаксис

Базовый синтаксис оператора UNION представлен следующим образом:

SELECT список_столбцов FROM таблица1
UNION SELECT список_столбцов FROM таблица2;

Чтобы лучше понять, как работает оператор UNION, предположим, что в таблицах employees и departments существуют некоторые гипотетические поля first_name и last_name. Обратите внимание, что эти поля на самом деле не существуют в демонстрационных таблицах.

Таблица employees
+----+------------+-----------+--------+
| id | first_name | last_name | salary |
+----+------------+-----------+--------+
| 1 | Ethan | Hunt | 5000 |
| 2 | Tony | Montana | 6500 |
| 3 | Sarah | Connor | 8000 |
| 4 | Rick | Deckard | 7200 |
| 5 | Martin | Blank | 5600 |
+----+------------+-----------+--------+

Таблица departments
+----+------------+-----------+----------+
| id | first_name | last_name | city |
+----+------------+-----------+----------+
| 1 | Maria | Anders | Berlin |
| 2 | Fran | Wilson | Madrid |
| 3 | Dominique | Perrier | Paris |
| 4 | Martin | Blank | Turin |
| 5 | Thomas | Hardy | Portland |
+----+------------+-----------+----------+


Выполним оператор UNION, чтобы объединить результаты двух запросов.

Следующая команда вернет имена и фамилии всех клиентов и сотрудников:

SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM customers;

После выполнения приведенной выше команды вы получите такой результат:

+---------------+--------------+
| first_name | last_name |
+---------------+--------------+
| Ethan | Hunt |
| Tony | Montana |
| Sarah | Connor |
| Rick | Deckard |
| Martin | Blank |
| Maria | Anders |
| Fran | Wilson |
| Dominique | Perrier |
| Thomas | Hardy |
+---------------+--------------+

Оператор UNION по умолчанию удаляет дублирующиеся строки из объединенного набора результатов. Вот почему приведенный выше запрос возвращает только 9 строк. Если вы заметили, имя Martin Blank встречается и в таблице employees, и в таблице customers.

Однако если вы хотите оставить дублирующиеся строки, используйте ключевое слово ALL, как показано ниже:

SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers;

#db

👉 @database_info

Подборка SQL-запросов для очистки данных

Удаляем дубликаты записей
DELETE FROM your_table
WHERE rowid NOT IN (
SELECT MAX(rowid)
FROM your_table
GROUP BY column1, column2, ...
);

Удаляем строки с NULL-значениями
DELETE FROM your_table
WHERE column1 IS NULL OR column2 IS NULL;

Заменяем NULL-значения на дефолтное выражение
UPDATE your_table
SET column1 = 'default_value'
WHERE column1 IS NULL;

Переводим текст в верхний регистр
UPDATE your_table
SET column1 = UPPER(column1);

Обрезаем лишнее
UPDATE your_table
SET column1 = TRIM(column1);

Конвертируем строки в формат дат
UPDATE your_table
SET date_column = TO_DATE(date_string, 'YYYY-MM-DD');

Извлекаем год/месяц/день
SELECT EXTRACT(YEAR FROM date_column) AS year,
EXTRACT(MONTH FROM date_column) AS month,
EXTRACT(DAY FROM date_column) AS day
FROM your_table;

#db

👉 @database_info

Pghoard

Сервис резервного копирования и восстановления PostgreSQL

- Автоматические периодические резервные копии
- Автоматическое резервное копирование журнала транзакций (WAL/xlog) (с использованием pg_receivexlog, archive_command или экспериментальной поддержки родного протокола репликации PG с walreceiver)
- Дополнительная поддержка автономного горячего резервного копирования
- Поддержка облачных объектных хранилищ (AWS S3, Google Cloud, OpenStack Swift, Azure, Ceph)
- Восстановление резервной копии непосредственно из объектного хранилища, в сжатом и зашифрованном виде
- Восстановление по времени (PITR)
- Инициализация нового резервного хранилища из резервных копий объектного хранилища, автоматически настроенного как реплицирующий горячий резерв

https://github.com/aiven/pghoard

#db

👉 @database_info

Dolt

Это база данных SQL, которую можно форкать, клонировать, бранчить, объединять, как репозиторий Git.

Подключайтесь к Dolt так же, как к любой базе данных MySQL, чтобы читать или изменять схему и данные. Функциональность контроля версий реализована в SQL с помощью системных таблиц, функций и процедур.

Или используйте Git-подобный интерфейс командной строки для импорта CSV-файлов, фиксации своих изменений, отправки их на удаленное хранилище или слияния изменений членов рабочей группы. Все команды, которые вы знаете для Git, работают точно так же и в Dolt.

https://github.com/dolthub/dolt

👉 @Githublib

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

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

https://dev.to/aws-heroes/dead-tuple-space-reused-without-vacuum-10fd

#db

👉 @database_info

Шардирование базы данных на пальцах

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

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

Существует два вида шардирования:

▪Вертикальное (по столбцам): каждый шард содержит часть столбцов массива и все связанные с ними строки данных.
▪Горизонтальное (по каким-либо критериям строки): каждый шард содержит одинаковые столбцы, но разные строки данных.

https://architecturenotes.co/database-sharding-explained/

#db

👉 @database_info