SQL *

SQL vs NoSQL: как выбрать архитектуру БД для мобильного приложения

SQL (Structured Query Language) — это язык запросов, которые мы используем для работы с реляционными базами данных. У таких БД жесткая структура в виде таблиц. Вся информация там хранится в столбцах и строках. Структурированность — одно из главных преимуществ SQL баз данных.

NoSQL — нереляционные БД, у них нет жесткой структуры. Скажем, у нас есть класс — пользователи. У каждого из них есть ID, имя, фамилия и проч. Эти данные помещаются в отдельный файл (а не в таблицу), и взаимодействие происходит только с этим файлом. Вы уже не можете забрать какое-то одно поле и работаете только с этим классом.

Чтобы выбрать между SQL и NoSQL, нужно исходить из задач бизнеса.

• Если вы делаете маленькое приложение на узкую аудиторию или MVP, можно смело выбирать NoSQL. Такие базы быстрые, с ними проще работать, они легко масштабируются. А если проект растет как на дрожжах — NoSQL можно быстро переписать на SQL.

• Если вы делаете средний по объему проект, лучше SQL. Хотя, если очень хочется, можно и NoSQL. Но тут есть особенности: выбирайте NoSQL, только если у вас есть налаженные ивенты, налаженная имплементация баз данных и опыт работы с NoSQL.

• Если вы делаете энтерпрайз, лучше выбрать SQL. Представим, что в каком-то крупном маркетплейсе 10 тысяч человек оплатили покупки, но не получили товары, потому что транзакция данных прошла некорректно. Цена ошибки тут слишком высока — поэтому SQL.

Больше подробностей — в нашем блоге.

Google Firebase сдался и добавил в свои сервисы SQL базу данных (облачную PostgreSQL) в форме Firebase Data Connect.

Пока в виде preview сервис можно попробовать бесплатно. Потом собираются брать плату и за саму базу, и за API доступа к ней.

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

Работа с PostgreSQL: настройка и масштабирование — открытый учебник

Держите ценный контент — открытый учебник от А. Ю. Васильева aka leopard по Postgres. 

Нереально полезно, здесь раскрывается множество важных моментов, связанных с этой СУБД

📎 Учебник

А здесь большой список лучших бесплатных учебников SQL.

🖥 Мне было сегодня лет, когда я узнал, что в 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 |

+------+------+

Подробнее.

А здесь я нашел большую бесплатную книгу по PostgreSQL 16, советую почитать ее всем, кто работает с базами данных.

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

• SQL Murder Mystery — вам предстоит стать шпионом Джаусом и использовать SQL для поиска загадочного убийцы;

• Playground.tensorflow -  браузерная игра, от tensorflow есть  в которой можно настраивать и обучать нейросеть

• CryptoZombies —  Игра разделена на уроки, где вы, используя язык программирования Solidity, создадете свою первую веб-игру 3.0, собирая армию зомби;

• Screeps — игра, которая похожа на Dwarf Fortress, но здесь для управления колонией нужно писать код;

• Flexbox Froggy — милая игра про жаб, которая научит вас работе с CSS flexbox. Для полного погружения рекомендуется играть в определенной среде;

• Checkio — отличное упражнение для программистов на Python и TypeScript. Сборник интересных интерактивных игр, решение которых требует написания кода.

• Еще 6 прикольных игр для изучения программирования, devops, linux, python.

Склонение месяцев в SQL

Недавно в моем канале о SQL мы обсуждали как сколонять месяцы в SQL

Вроде мелочь, а вполне-таки реальный кейс

В общем, необходимо вносить в договор дату в формате: "30 мая 2099 г." 

Эта дата хранится в БД, ее нужно просто подгрузить и нормально отформатировать. 

Для этого можно обращаться к месяцу с помощью: LOWER(DATENAME(month, @Tdate)), однако в таком случае получается месяц в именительном падеже - "май".

И тут полезно вспомнить, что форматирование даты с правильными склонениями месяца работает из коробки:

SELECT FORMAT(GETDATE(), 'dd MMMM yyyy', 'ru-RU')

-- 30 мая 2099

Состоялся релиз мажорной версии открытого масштабируемого решения для кластеризации баз данных MySQL — Vitess 19. Исходный код проекта опубликован на GitHub под лицензией Apache License 2.0.

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

Изменения и дополнения в Vitess 19:

• прекращение поддержки MySQL 5.7. Разработчики советуют пользователям выполнить обновление до MySQL 8.0, используя Vitess 18, прежде чем переходить на Vitess 19. Однако Vitess 19 по-прежнему будет поддерживать импорт из MySQL 5.7;

• добавлены новые метрики для консолидации потоков и версия сборки в /debug/vars, чтобы обеспечить более глубокое понимание и отслеживаемость;

• улучшена совместимость запросов, реализована поддержка операций удаления из нескольких таблиц, новый запрос SHOW VSCHEMA KEYSPACES и несколько других улучшений синтаксиса SQL, которые расширяют совместимость Vitess с MySQL;

• поддержка отсрочки попыток переключения в случае блокировки. Поддержка принудительного отключения;

• улучшение процесса инкрементного резервного копирования: поддержка имён резервных копий и пустых резервных копий.

«Следуя тенденции последних трёх лет, новая версия Vitess быстрее предыдущей во всех тестах, которые мы отслеживаем в Arewefastyet. Мы исправили несколько проблем с производительностью, доработали интерфейс и код», — пояснили разработчики, порекомендовав изучить документацию проекта и список исправлений.

«Циан» перенесла за 6 недель более 500 микросервисов, 2 монолитных приложения и 500 ТБ своих данных на платформу Yandex Cloud без перерывов в работе сервиса, включая Terraform и Salt.

Миграция проходила в рамках совместной работы специалистов «Циан» и поддержки Yandex Cloud. На облачную платформу они перенесли системы управления базами данных, очереди сообщений и поисковую систему. Данные поступают в объектное хранилище. Для эффективной работы с микросервисами компания использует Yandex Managed Service for Kubernetes.

Технологический стек компании включает Python, C#, Node.js. Работа с данными велась в PostgreSQL, MSSQL, Cassandra, Elasticsearch; приём и отправка сообщений — в RabbitMQ и Kafka.

Подготовительный этап миграции в Yandex Cloud занял 4 месяца. Из них 2 месяца ушло на проверку осуществимости миграции.

За полтора месяца миграции микросервисы и один монолит перенесли в Yandex Managed Service for Kubernetes, а монолит на Windows IIS — на виртуальные машины Compute Cloud. Для хранения данных используют объектное хранилище Object Storage, для гибкой работы с документоориентированными моделями данных — Managed Service for MongoDB, для эксплуатации и администрирования реляционных баз данных — Managed Service for PostgreSQL и Managed Service for MySQL.

Чтобы избежать даунтаймов, пришлось сделать утилиту для миграции микросервисов, развернуть несколько кластеров на Vanilla Kubernetes, синхронизатор эндпоинтов, перейти на нереплицируемые SSD для работы MSSQL.

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

Тут представлена попытка заNULLить значение, если оно равно чему-то.

Но ведь в PostgreSQL есть функция nullif, которая делает ровно то же самое:

NULLIF(значение1, значение2)

Функция NULLIF выдаёт значение NULL, если значение1 равно значение2; в противном случае она возвращает значение1. Это может быть полезно для реализации обратной операции к COALESCE. В частности, для примера, показанного выше:

SELECT NULLIF(value, '(none)') ...

В данном примере если value равно (none), выдаётся null, а иначе возвращается значение value.

То есть в примере выше стоит переписать короче и понятнее:

, nullif(sdate, '1900-01-01') sdate
, nullif(mdate, '1900-01-01') mdate

В своей лекции про "сложные" SELECT я уже рассказывал про возможности оператора CASE, а еще раньше - про возможности оптимизации выполнения запросов с его помощью.

Но иногда он вовсе не нужен! Обратите внимание на картинку сверху...

Посмотрим на использованный тут синтаксис CASE:

CASE
  WHEN условие THEN результат
  [WHEN ...]
  [ELSE результат]
END

Или еще конкретнее:

CASE
  WHEN условие THEN TRUE -- [условие IS TRUE]
  ELSE FALSE             -- [условие IS FALSE, IS NULL]
END

Хм... То есть результат этого CASE эквивалентен значению условия с точностью до NULL!

При обращении условия в NULL такой CASE вернет FALSE, но этого же поведения можно добиться с помощью coalesce:

coalesce(условие, FALSE)

Но если мы говорим о конкретном примере с условием EXISTS, то уж оно-то точно никак не может принимать значение NULL! Значит, coalesce-обертка нам тут не требуется и эту часть запроса можно сократить до одного лишь условия, без всяких CASE:

EXISTS(
  SELECT
    NULL
  FROM
    _inforg20687 t15
  WHERE
    t15._fld1329 = 0::numeric AND
    t15._fld20688rref = t6._idrref AND
    t15._fld20689_type = '\\010'::bytea AND
    t15._fld20689_rtref = '\\000\\000\\001\\010'::bytea AND
    t15._fld20689_rrref = t4._fld6883rref
)

В общем, пишите меньше SQL-кода - и ваши запросы "будут мягкими и шелковистыми"!

Вышел релиз легковесной СУБД, оформленной в виде подключаемой библиотеки, SQLite 3.45. Код проекта распространяется как общественное достояние (public domain) и может использоваться без ограничений и безвозмездно в любых целях. Финансовую поддержку разработчиков SQLite осуществляет специально созданный консорциум, в который входят такие компании, как Bentley, Bloomberg, Expensify и Navigation Data Standard.

Основные изменения:

• все SQL‑функции для работы с форматом JSON переписаны и переведены на новый внутренний формат дерева разбора JSONB, который сериализируется и может храниться в БД для исключения повторного разбора при использовании значений JSON;

• в виртуальную таблицу FTS5, применяемую для полнотекстового поиска, добавлена опция tokendata, позволяющая использовать собственные токенизаторы;

• по умолчанию включена оптимизация SQLITE_DIRECT_OVERFLOW_READ, при которой overflow‑страницы, размер которых больше стандартного размера страницы b‑tree, читаются из файла напрямую, минуя кэш;

• в планировщике запросов повышена эффективность оптимизации транзитивных ограничений (transitive constraint) и улучшено игнорирование индексов, которые признаны низкокачественными при выполнении операции ANALYZE;

• в интерфейсе командной строки улучшено отображение содержимого в кодировке UTF-8 на платформе Windows. Обеспечено автоматическое определение использования CLI‑интерфейса при воспроизведении скриптов «.dump» и внесение соответствующих изменений в настройки.

Источник: OpenNET.

В начале января 2024 года состоялся релиз реляционной СУБД Firebird 5.0, разработка которой началась в 2016 году. Четвёртая версия проекта вышла в июне 2021 года.

Проект Firebird продолжает развитие исходного кода СУБД InterBase 6.0, открытого в 2000 году компанией Borland.

СУБД Firebird распространяется под свободной лицензией MPL. Проект поддерживает стандарты ANSI SQL, в том числе такие возможности, как триггеры, хранимые процедуры и репликацию. Бинарные сборки СУБД Firebird 5.0 доступны для Windows (x86, x64), Linux (x86, x64, ARM32, ARM64), macOS (x64) и Android (x86, x64, ARM32, ARM64, Embedded).

Ключевые изменения в Firebird 5.0:

• реализована возможность выполнения операций в многопоточном режиме;

• добавлена поддержка частичных индексов;

• добавлена возможность обновления БД до актуальной промежуточной версии хранилища (ODS — On-Disk-Structure) на лету (inline update) без создания и восстановления из резервной копии;

• реализован кэш скомпилированных SQL-выражений, обслуживаемый автоматически (устаревающие записи очищаются по мере необходимости);

• добавлен интерфейс для профилирования SQL и PSQL, позволяющий оценивать время выполнения каждого запроса, накапливать статистику о числе запросов и выявлять проблемы с производительностью;

• проведена оптимизация производительности копирования блобов;

• добавлена полная поддержка синтаксиса определения строк, описанного в стандарте SQL.

Источник: OpenNET.

Портал DB-Engines обновил рейтинг популярности СУБД и присудил звание СУБД 2023 года проекту PostgreSQL, который за год продемонстрировал наибольших рост популярности из 417 отслеживаемых систем. Второе место досталось облачной платформе Databricks (за год поднялась с 19 на 17 место), а третье место занял движок Google BigQuery (поднялся с 21 на 19 место).

Ранее PostgreSQL уже признавался СУБД года в 2020, 2018 и 2017 годах. В 2022 году и 2021 году это звание было закреплено за СУБД Snowflake, а в 2019 его получило MySQL, в 2016 - Microsoft SQL Server, в 2015 - Oracle, в 2013 и 2014 годах - MongoDB.

По методике расчёта рейтинг СУБД напоминает рейтинг языков программирования TIOBE и учитывает популярность запросов в поисковых системах, число результатов в поисковой выдаче, объём обсуждений на популярных дискуссионных площадках и в соцсетях, число вакансий в агентствах по найму персонала и упоминаний в профилях пользователей.

Что касается распределения СУБД в рейтинге, PostgreSQL продолжает занимать 4 место, несмотря на наибольший во всем рейтинге рост популярности - 34.11 балла. Рост популярности также демонстрирует проект Databricks и Snowflake. C 8 на 7 место поднялось решение Elasticsearch, а с 33 на 29 - СУБД Firebird, c 44 на 37 - ClickHouse, с 62 на 50 - Prometheus, с 48 на 42 - OpenSearch, с 85 на 76 - TimescaleDB.

Значительное снижение популярности в 2023 году наблюдается у MySQL, Microsoft SQL Server, MongoDB, Redis и SQLite.

Источник: OpenNET.

Если вдруг вам понадобилось базу IP2Location перевести из DECIMAL-представления IP-адресов в "родной" для PostgreSQL тип inet, то для IPv4-адресов все будет тривиально:

'0.0.0.0'::inet + ipnum::bigint

А вот для преобразования числа к формату IPv6-адреса придется проявить немного изобретательности:

• "математически" разбиваем число на 8 двухбайтовых сегментов по (2 ^ 16) ^ i

• каждое значение преобразуем в шестнадцатиричную систему счисления и добиваем лидирующими нулями

• склеиваем сегменты через двоеточие и кастуем к inet

array_to_string(ARRAY(
  SELECT
    lpad(to_hex(trunc(
      ipnum % (2::numeric(39,0) ^ ((i + 1) * 16)) / (2::numeric(39,0) ^ (i * 16))
    )::integer), 4, '0')
  FROM
    generate_series(7, 0, -1) i
), ':')::inet

В принципе, после этого мы можем "свернуть" ip_from и ip_to в подсеть, не обращая внимания на исходный формат:

inet_merge(ip_from, ip_to) subnet

А если проиндексируем эти подсети с помощью gist...

CREATE INDEX ON country_inet USING gist(subnet inet_ops);

... то сможем по индексу быстро определять принадлежность произвольного IPv4/IPv6-адреса подсетям с помощью соответствующих операторов примерно таким запросом:

SELECT
  *
FROM
  country_inet
WHERE
  subnet >> '8.8.8.8' AND
  country <> '-'
ORDER BY
  masklen(subnet) DESC
LIMIT 1;

❓100 Вопросов по Машинному обучению (Machine Learning) - Вопрос_10

🔠Вопрос_10: Что такок Tarantool и как он устроен ? (Часть_3)

1. Транзакции: В более новых версиях Tarantool была добавлена поддержка механизма транзакций. Транзакции позволяют группировать несколько операций в единую атомарную операцию, что обеспечивает целостность данных.

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

t.me/DenoiseLAB (Еесли вы хотите быть в курсе всех последних новостей и знаний в области анализа данных)

❓100 Вопросов по Машинному обучению (Machine Learning) - Вопрос_10

🔠Вопрос_10: Что такок Tarantool и как он устроен ? (Часть_2)

1. Replication: Tarantool предлагает механизм репликации, который позволяет создавать реплики базы данных для обеспечения отказоустойчивости и масштабируемости. Репликация Tarantool основана на механизме репликации мастер-слейв (master-slave) и поддерживает асинхронное и синхронное реплицирование.

2. Sharding: Tarantool поддерживает горизонтальное масштабирование с помощью шардинга данных. Шардинг позволяет распределить данные по нескольким узлам-серверам, что позволяет обрабатывать большие объемы данных и повышает производительность.

3. Индексы: Tarantool предоставляет различные типы индексов для оптимизации запросов и обеспечения быстрого доступа к данным. Включая хеш-индексы, деревья и индексы, основанные на отсортированных списках.

   t.me/DenoiseLAB (Еесли вы хотите быть в курсе всех последних новостей и знаний в области анализа данных);

IS NOT NULL + OR

Иногда внутри SQL-запроса возникает необходимость проверить наличие/отсутствие NULL-значения в некотором наборе полей:

a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL

Но то же самое по смыслу условие можно записать гораздо короче с помощью функции coalesce:

coalesce(a, b, c) IS NOT NULL

Подробнее об особенностях работы со сложными выражениями можно прочитать в статье "PostgreSQL Antipatterns: вычисление условий в SQL".

IS NOT NULL + AND

Немного изменим условие - заменим OR на AND:

a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL

Тут нам поможет ROW-конструктор:

(a, b, c) IS NOT NULL

IS NULL + AND

Теперь заменим IS NOT NULL на IS NULL:

a IS NULL AND b IS NULL AND c IS NULL

Тут достаточно вспомнить из логики, что (A and B) эквивалентно not(not A or not B), а (A or B) - not(not A and not B), поэтому легко применяем not к варианту IS NOT NULL + OR:

coalesce(a, b, c) IS NULL

Или с помощью ROW-конструктора:

(a, b, c) IS NULL

Разница будет заключаться в том, что coalesce вычисляет выражения "лениво" (см. "«Ленивый сахар» PostgreSQL").

IS NULL + OR

Остался последний вариант:

a IS NULL OR b IS NULL OR c IS NULL

Тут мы можем "обратить" вариант IS NOT NULL + AND:

NOT (a, b, c) IS NOT NULL

Заметьте, что пара NOT тут "не сокращается", иначе получился бы предыдущий вариант.

❓100 Вопросов по Машинному обучению (Machine Learning) - Вопрос_10

🔠Вопрос_10: Что такок Tarantool и как он устроен ? (Часть_1)

✔️Ответ: Tarantool — это база данных с открытым исходным кодом и высокой производительностью, которая сочетает в себе функциональность базы данных и сервера приложений. Tarantool состоит из:

1. In-Memory и Disk Storage: Tarantool предлагает возможность хранения данных как в оперативной памяти (In-Memory), так и на диске (Disk Storage). Это позволяет обеспечить высокую скорость доступа к данным и сохранить данные на долгосрочное хранение.

2. Lua: Tarantool использует язык программирования Lua для создания хранимых процедур (stored procedures), триггеров и бизнес-логики. Lua обеспечивает гибкость и простоту внедрения пользовательского кода в базу данных.

3. NoSQL и Lua Spaces: Tarantool поддерживает гибкую модель данных, известную как Lua Spaces. Lua Spaces предоставляет простой способ хранения и извлечения данных, а также мощные возможности индексирования и поиска.

    t.me/DenoiseLAB (Еесли вы хотите быть в курсе всех последних новостей и знаний в области анализа данных)

Периодически в коде запросов и "заточенных" под них индексов наблюдаю примерно подобные куски:

coalesce("Фамилия", '') || ' ' || coalesce("Имя", '') || ' ' || coalesce("Отчество", '')

Понятно, что тут хотели обезопасить себя от заполненности любого из полей NULL-значением, чтобы случайно вся строка не заNULL'илась.

Правда, тут возникают некоторые артефакты в виде "висящих пробелов" типа ' Иван Иванович' или 'Иванов Иван '.

Но ведь есть решение изящнее и проще - функция concat_ws:

concat_ws(' ', "Фамилия", "Имя", "Отчество")

RTFM!

Недостойное поведение Oracle DB

Сегодня база данных Oracle, была уличена в недостойном поведении, а именно в неявном преобразовании пустых строк к NULL

Посмотрите сами и убедитесь:

create table null_test (
    id int,
    test varchar(64)
);

insert into null_test values (1, 'Test');
insert into null_test values (2, null);
insert into null_test values (3, '');

select id, test, case when test is null then 'test is NULL' else 'test NOT NULL' end isNULL
from null_test;


select id, test
from null_test
where test = '';

Результат:

+====+========+===============+
| ID | TEST   | ISNULL        |
+====+========+===============+
| 1  | Test   | test NOT NULL |
| 2  | (null) | test is NULL  |
| 3  | (null) | test is NULL  |
+----+--------+---------------+

+====+======+
| ID | TEST |
+====+======+

Даже SQLITE известная своим пренебрежением к типам данных, не позволяет себе таких вольностей.

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