Skip to content

Database

Database⚑

Що таке бази даних і для чого вони потрібні?⚑

База даних - це організована структура для зберігання та управління інформацією. Вона дозволяє зберігати, організовувати, шукати та забезпечувати доступ до даних з різних джерел та для різних цілей.

Під базою даних зазвичай розуміють систему керування базами даних. Але варто розрізняти базу даних — сховище даних, та СКБД — засоби для роботи з базою даних.

Бази даних служать для

  • Збереження різноманітних даних, які можуть бути структурованими.
  • Організації даних у логічних структурах, таких як таблиці, колекції ключ-значення.
  • Забезпечення швидкого доступу до даних через індекси та оптимізовані запити.
  • Забезпечення безпеки даних шляхом управління правами доступу.
  • Можливості спільної роботи над даними багатьох користувачів.
  • Виконання аналітичних завдань та генерація звітів.

Які типи баз даних існують?⚑

  • Реляційні бази даних (РБД): Цей тип баз даних використовує таблиці для зберігання даних, де кожен рядок представляє запис, а кожна колонка - поле даних. Такі бази даних добре підходять для зберігання структурованих даних та встановлення зв'язків між ними.
  • Документ-орієнтовані бази даних: Вони зберігають дані у вигляді документів, таких як JSON або XML. Це корисно для зберігання неструктурованих даних або даних зі змінними полями.
  • Ключ-значення бази даних: Вони працюють на основі структури ключ-значення, де кожен запис зберігається за унікальним ключем. Це дозволяє швидко вибирати та зберігати дані.
  • Стовпчикові бази даних: Вони організовані за стовпцями, що допомагає оптимізувати запити на вибірку конкретних полів даних.
  • Графові бази даних: Вони засновані на графовій структурі, де дані представлені як вузли та зв'язки між ними. Такий підхід зручний для аналізу зв'язків та взаємодій.

Чим Реляційні БД відрізняються від NoSQL БД?⚑

Реляційні бази даних (РБД) та нереляційні бази даних (NoSQL) відрізняються за своєю структурою, способом зберігання та обробки даних.

  • Схема даних
  • РБД: Використовують жорстку, попередньо визначену схему даних з таблицями, колонками і типами даних, з визначеними відношеннями між ними.

  • NoSQL БД: Забезпечують гнучку схему даних, що дозволяє зберігати дані різного типу без жорсткої фіксованої структури.

  • Масштабованість

  • РБД: Масштабування може бути складнішим, особливо при горизонтальному масштабуванні.

  • NoSQL БД: Зазвичай пропонують легше горизонтальне масштабування завдяки спеціалізованій архітектурі.

  • Запити

  • РБД: Використовують мову запитів SQL для взаємодії з даними.

  • NoSQL БД: Використовують різні мови запитів або інтерфейси для операцій з даними.

  • Транзакції та консистентність

  • РБД: Забезпечують гарантії ACID для збереження цілісності даних.
  • NoSQL БД: Можуть пропонувати менш жорсткі гарантії цілісності, забезпечуючи вищу швидкість та масштабованість.

Застосування RDB (реляційних баз даних) та NoSQL⚑

Застосування RDB (реляційних баз даних) та NoSQL баз даних залежить від конкретних вимог проекту та характеристик даних.

Коли краще використовувати RDB

  • Структуровані дані
    • Якщо дані мають чітку структуру з фіксованими схемами і повинні відповідати певним типам даних.
  • Зв'язки між даними
    • Для проектів, де потрібні складні зв'язки між таблицями та забезпечення цілісності даних за допомогою зовнішніх ключів.
  • Транзакції
    • У випадках, коли важлива підтримка транзакцій ACID (атомарність, узгодженість, ізольованість, надійність) для забезпечення надійності операцій з даними.
  • Складні запити
    • Коли необхідно виконувати складні SQL-запити, такі як об'єднання таблиць (JOIN), агрегації та фільтрації даних.

Типові застосування — фінансові системи, системи обліку, ERP, де важливий цілісний і послідовний стан даних.

Коли краще використовувати NoSQL

  • Гнучкість схем
    • Якщо структура даних може змінюватися з часом і потрібна гнучкість у зміні схем без складних міграцій.
  • Великі обсяги даних
    • Для проектів, де необхідно зберігати та обробляти великі обсяги даних, особливо неструктурованих або напівструктурованих (наприклад, документи, JSON, графи)
  • Висока продуктивність і масштабованість
    • У випадках, коли потрібна висока продуктивність записів і читання даних, а також горизонтальне масштабування для розподілених систем.
  • Великі дані та аналітика
    • Для проектів, які потребують аналізу великих даних у реальному часі, таких як потоки подій, журнали, соціальні мережі.
  • Кешування
    • Коли потрібно швидко отримати доступ до даних, кешування даних за допомогою NoSQL може забезпечити швидший доступ до даних, ніж реляційна база даних.

NoSQL також часто використовують у високонавантажених системах, таких як соціальні мережі, аналітичні платформи, логування, системи реального часу.

Наведіть приклади РБД та NoSQL БД⚑

Приклади реляційних баз даних (РБД)

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server
  • Oracle Database
  • SQLite

Приклади баз даних типу NoSQL

  • MongoDB
    • Орієнтована на документи NoSQL база даних, яка зберігає дані у вигляді документів (наприклад, JSON) з гнучкою схемою.
    • MongoDB легко масштабується горизонтально та підтримує індексування даних, що забезпечує швидкий доступ до них.
    • Для зберігання інформації використовується тип даних BSON (Binary JSON). BSON – це бінарний формат для представлення JSON-подібних документів. BSON забезпечує більш ефективне зберігання даних, ніж JSON.
  • Cassandra
    • Ключ-значення база даних, яка підходить для великих обсягів даних та горизонтального масштабування.
  • Redis (REmote DIctionary Server)
    • Швидка база даних типу ключ-значення, яка використовується для кешування та зберігання швидкозмінних даних.
    • Redis швидко обробляє дані завдяки тому, що всі дані зберігаються у пам'яті.

Яка різниця між BASE і ACID?⚑

BASE: Це підхід до дизайну систем управління базами даних, який ставить акцент на доступність та швидкодію. BASE розшифровується як "Basically Available, Soft state, Eventually consistent" (базова доступність, нестійкий стан, консистентна в кінцевому рахунку).

  • Під базовою доступністю розуміється такий підхід до проектування додатка, за якого збій в деяких вузлах призводить до відмови в обслуговуванні лише для незначної частини сеансів, зі збереженням доступності в більшості випадків.
  • Нестійкий стан передбачає можливість пожертвувати довгостроковим зберіганням стану сеансів (такого як проміжні результати виборок, інформація про навігацію, контекст), зосереджуючись при цьому на фіксації оновлень лише критичних операцій.
  • Узгодженість у кінцевому підсумку, яка трактується як можливість суперечливості даних у деяких випадках, але за умови забезпечення узгодженості протягом практично прийнятного часу.

Таким чином, згідно з підходом BASE, система може тимчасово допускати неузгодженість даних для забезпечення високої доступності та швидкості в операціях. Це особливо важливо в розподілених системах, де збереження строгої консистентності може призвести до затримок.

ACID: Це набір властивостей, які визначають надійність транзакцій у системах управління базами даних. ACID розшифровується як "Atomicity, Consistency, Isolation, Durability" (Атомарність, Цілісність, Ізоляція, Довговічність). Ці властивості гарантують, що транзакції виконуються надійно: вони атомарні (або повністю виконуються або взагалі не виконуються), зберігають цілісність даних, ізолюються одна від одної та є стійкими до відмов.

Отже, різниця полягає в підході: ACID надає пріоритет стабільності, цілісності та гарантії цілісності даних, тоді як BASE більше акцентується на доступності, швидкості та готовності системи працювати навіть за умови відмов.

Яку базу даних використовувати у проекті?⚑

Якщо структура даних має складні зв'язки і треба виконувати складні SQL-запити з багатьма джоінами, краще вибрати реляційну базу даних, таку як PostgreSQL або MySQL.

Якщо ж треба працювати з великим обсягом неструктурованих або змінюваних даних, краще вибрати NoSQL. MongoDB добре підходить для сховища документів, а Redis чи Cassandra - для швидкого зберігання та доступу до даних типу key-value.

Іншим важливим критерієм є масштабованість. NoSQL бази даних зазвичай дозволяють легше масштабувати систему горизонтально, додавши нові вузли при зростанні обсягу даних

Links

Яка різниця між PostgreSQL і MySQL⚑

Історія розробки MySQL і PostgreSQL. MySQL почав розроблятися ще в 90-х роках. Внутрішній випуск відбувся в 1995 році. Тоді розробкою MySQL займалися декілька компаній. Починаючи з 2010 року компанія Oracle володіє проектом MySQL та розробляє нові версії.

PostgreSQL трохи раніше, в 1986 році, почав розроблятися в Каліфорнійському університеті. Над проектом працювали понад 8 років, але потім він був розділений на комерційну базу даних IIlustra та вільний проект Postrgesql.

Особливості зберігання даних. В MySQL для зберігання даних у таблицях використовуються різні двигуни. Двигун не має впливу на синтаксис запитів та їх виконання. Є підтримка MyISAM, InnoDB, MEMORY, Berkeley DB. Їхньою основною відмінністю є спосіб запису даних на диск та методи зчитування. PostgreSQL працює лише на двигуні зберігання даних. Таблиці організовані як об'єкти, а дії виконуються за допомогою об'єктно-орієнтованих функцій.

Стандарти SQL. SQL - це стандартизована мова виконання запитів, яка використовується як в MySQL, так і в PostgreSQL. Цей стандарт має кілька версій і був розроблений ще в 1986 році.

MySQL підтримує не всі функції та можливості SQL. Це зроблено для того, щоб працювати з MySQL було просто та зручно. Але якщо для проекту необхідне яке-небудь розширення, розробники можуть додати його, не жертвуючи стандартом.

PostgreSQL підтримує всі нові стандарти SQL, через що цей проект досить складний.

Можливості обробки даних. MySQL під час виконання запиту завантажує всю відповідь сервера в пам'ять клієнта. У випадку великих обсягів це не завжди зручно. Функціональності Postgresql ширші, ніж у Mysql. Наприклад, в Postgresql за допомогою курсора можна переміщувати отримані дані. Надається лише вказівник, а вся відповідь зберігається в пам'яті сервера баз даних. Цей вказівник можна зберігати між сеансами. PostgreSQL підтримує регулярні вирази у запитах, рекурсивні запити та успадкування таблиць.

Продуктивність MySQL і Postgresql. MySQL завжди був орієнтований на велику продуктивність, тоді як Postgresql був спрямований на багато налаштувань та стандартів. Але з часом ця ситуація змінилася, і Postgre став більш продуктивним.

Для організації роботи з базою даних в MySQL використовується таблиця InnoDB. Це означає, що MySQL буде значно швидше за Postgre при використанні первинного ключа.

Стосовно Postgresql, вся заголовкова інформація таблиць розміщується в оперативній пам'яті. Можна застосовувати кілька індексів до однієї таблиці для більшої зручності. Взагалі PostgreSQL працює швидше, крім ситуацій із використанням первинних ключів.

Підтримувані типи даних. MySQL і Postgresql мають схожий набір типів, який, звісно, має свої відмінності. В Postgres типи більш різноманітні і є власні типи полів для певних видів даних, яких, наприклад, немає в MySQL.

Links

Які типи зв'язків бувають в БД?⚑

Типи зв'язків визначають способи, якими дані взаємодіють у базі даних.

Один до Одного (One-to-One) - в цьому типі зв'язку кожен запис в одній таблиці пов'язаний з одним і лише одним записом в іншій таблиці. Цей зв'язок використовується тоді, коли об'єкти в обох таблицях пов'язані взаємно один до одного. Типовий приклад - User і UserProfile.

Один до Багатьох (One-to-Many) - у цьому типі зв'язку кожен запис в одній таблиці може бути пов'язаний з багатьма записами в іншій таблиці. Наприклад, один користувач може мати багато замовлень.

Багато до Багатьох (Many-to-Many) - у цьому типі зв'язку кожен запис в одній таблиці може мати зв'язок з багатьма записами в іншій таблиці, і навпаки. Цей тип зв'язку вимагає введення додаткової таблиці, яка з'єднує записи обох таблиць. Наприклад автори і книги. Один автор може мати багато книг, і одна книга може мати багато авторів.

Сам до Себе (Self-Referencing) - це спеціальний тип зв'язку, коли записи в одній таблиці можуть мати зв'язок з іншими записами в цій же таблиці. Наприклад, в таблиці "Користувачі" може бути поле "Батько", яке посилається на інший запис у тій же таблиці, створюючи ієрархію користувачів.

Що таке індекси? Навіщо вони потрібні? Як вони працюють?⚑

Summary

Індекси - це структури даних у базах даних, які допомагають прискорити пошук і фільтрацію даних. Вони створюються для певних полів таблиць і дозволяють базі даних здійснювати швидший доступ до конкретних рядків.

Індекси потрібні для покращення продуктивності запитів, особливо коли потрібен пошук або фільтрація даних за певними полями. Вони допомагають зменшити кількість рядків, які база даних повинна перевірити, щоб знайти відповідні результати.

Індекси працюють шляхом створення додаткової структури даних, яка має відображення на значення поля та посилання на відповідний рядок у таблиці. При пошуку даних за індексованим полем база даних спочатку переходить до відповідного запису в індексі, а потім знаходить відповідний рядок у таблиці.

Індекси не є безкоштовними. Вони займають додатковий обсяг місця на диску і впливають на швидкість вставки, оновлення і видалення даних. Тому їх слід створювати обдумано, зважаючи на типи запитів, які будуть використовуватися в проекті.

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

У PostgreSQL індекси реалізовані різними способами. Наприклад, B-дерева є одним з найпоширеніших типів індексів. Вони дозволяють швидкий пошук значень і забезпечують ефективну підтримку різних операцій порівняння, таких як менше, більше, дорівнює тощо.

Links

Які популярні типи індексів існують?⚑

  • B-дерево (B-Tree) індекс: Це один з найпоширеніших типів індексів. B-дерева використовуються для оптимізації пошуку даних у впорядкованих полях, таких як числа та рядки. Вони дозволяють виконувати операції порівняння (менше, більше, дорівнює) дуже швидко.
  • Хеш-індекс: Хеш-індекси використовують хеш-функції для швидкого пошуку даних. Вони підходять для запитів з точним збігом значень. Однак, вони не так ефективні для діапазонних запитів або запитів, які використовують оператори порівняння.
  • GIN (Generalized Inverted Index) індекс: Цей тип індексу використовується для повнотекстового пошуку та інших варіантів пошуку, які вимагають обробки великої кількості слів або термінів.
  • GiST (Generalized Search Tree) індекс: GiST індекси призначені для роботи зі складними даними, такими як географічні дані, діапазони дат та інші. Це збалансоване дерево пошуку, точно так само, як і розглянуте раніше B-дерево. Але B-дерево застосовне лише до тих типів даних, для яких має сенс операція порівняння та є можливість упорядкування. Проте PostgreSQL дозволяє зберігати і такі дані, для яких операція упорядкування не має сенсу, наприклад, геодані та геометричні об'єкти.
  • SP-GiST (Space-Partitioned Generalized Search Tree) індекс: Цей тип індексу використовується для роздільних даних, які можна розбити на області. Наприклад, для географічних даних це можуть бути координати на карті. SP-GiST схожий із GiST, але він дозволяє створювати незбалансовані дерева. Такі дерева можуть бути корисними при розбитті множини на об'єкти які не перетинаються. Літери SP означають space partitioning.

Links

В яких випадках використовується CREATE INDEX CONCURRENTLY⚑

Опція CONCURRENTLY при створенні індексів використовується для того, щоб дозволити доступ до таблиці під час процесу створення індексу. Це допомагає створювати індекси для великих таблиць, коли потрібно забезпечити доступ 24/7, оскільки звичайне створення індексу викликає блокування, яке може впливати на доступність системи та продуктивність. Ось деякі випадки, коли використання CREATE INDEX CONCURRENTLY є доцільним:

Використання CONCURRENTLY може зайняти більше часу порівняно зі звичайним створенням індексу, але це дозволяє уникнути блокування таблиці.

CREATE INDEX CONCURRENTLY index_name ON table_name (column_name);

Що таке seq-scan? Коли він виконується, навіть якщо є індекси?⚑

Seq-scan (sequential scan) — це метод доступу до даних у таблиці, який використовується в системах управління базами даних (наприклад, PostgreSQL). Під час seq-scan сервер бази даних читає всі рядки таблиці послідовно, незалежно від того, чи є індекси для цієї таблиці.

В посгресі вибір між "використовувати" і "не використовувати" індекс проводиться планувальником і іноді він може вирішити, що робити IndexScan безглуздо, наприклад, якщо таблиця дуже маленька (але не тільки в цьому випадку).

Коли виконується seq-scan, навіть якщо є індекси

  • Запит сканує значну частину таблиці - якщо запит повертає великий відсоток рядків таблиці, сервер може вирішити, що використання seq-scan швидше, ніж доступ через індекси. Індекси підходять для вузьких вибірок, але якщо потрібно зчитати майже всі дані, seq-scan стає вигіднішим.
  • Індекси не охоплюють умову фільтрації - якщо у запиті є умова фільтрації, яка не відповідає існуючому індексу (наприклад, фільтрація за полем, яке не індексоване), база даних змушена виконувати seq-scan.
  • Маленький розмір таблиці - для дуже маленьких таблиць використання seq-scan може бути швидшим, ніж доступ через індекси, оскільки читання даних напряму із таблиці уникає додаткових накладних витрат на пошук у індексі.
  • Інкрементальні оновлення таблиці - якщо таблиця має багато нещодавно доданих/змінених рядків (через механізм MVCC у PostgreSQL), сервер може обрати seq-scan, щоб уникнути накладних витрат на підтримку актуальності індексів. Регулярне виконання ANALYZE та VACUUM допомагає оптимізатору краще оцінювати розмір таблиці та вибирати правильний план.
  • Складні функції або вирази - якщо запит використовує функції або складні обчислення у WHERE, і індекс не покриває ці обчислення (наприклад, LOWER(column) = 'value' без створеного індексу для LOWER(column)), індекси не будуть використані.
  • Налаштування параметрів оптимізатора - у PostgreSQL параметри random_page_cost і seq_page_cost впливають на вибір оптимізатора. Якщо seq_page_cost низький (або рівний за замовчуванням), оптимізатор може віддавати перевагу seq-scan.

Що таке транзакція? Що таке ACID?⚑

Транзакція - це послідовність дій, які виконуються в базі даних або іншій системі з метою забезпечення консистентності даних та виконання операцій атомарно (тобто вони повинні бути виконані цілісно або не виконуватися взагалі - все або нічого). Іншими словами, транзакція - це група дій, які мають бути виконані як єдине ціле. Транзакція є робочою одиницею роботи з базою даних (надалі - БД).

Транзакція - це внесення змін у БД. Наприклад, якщо ми створюємо, змінюємо або видаляємо запис, то ми виконуємо транзакцію. Дуже важливо контролювати транзакції для гарантування цілісності. Крім того, транзакції дозволяють відновлювати базу даних до попереднього стану в разі виникнення помилки або аварії.

Основні концепції (властивості) транзакції описуються абревіатурою ACID

  • Atomicity - Атомарність
  • Consistency - Узгодженість
  • Isolation - Ізоляція
  • Durability - Довговічність

Атомарність Атомарність гарантує, що транзакція або виконується повністю або зовсім не виконується. Якщо одна з операцій у послідовності не буде виконана, то вся транзакція буде відмінена. Тут вводиться поняття "відкату" (rollback). Тобто всередині послідовності відбуватимуться певні зміни, але в результаті всі вони будуть скасовані ("відкочені"), і в результаті користувач не побачить жодних змін. У випадку вкладених транзакцій, rollback відбувається до найближчого SAVEPOINT.

Наприклад, якщо гроші списалися з одного рахунку, але не надійшли на інший через помилку, вся операція скасовується і гроші залишаються на першому рахунку.

Узгодженість Узгодженість означає, що будь-яка завершена транзакція (транзакція, яка досягла завершення транзакції - end of transaction) фіксує лише допустимі результати. Система має перебувати в узгодженому, несуперечливому стані до початку дії транзакції і по її завершенню. При цьому вона може перебувати в неузгодженому стані протягом виконання транзакції, проте ця неузгодженість не буде видимою за межами транзакції завдяки іншим властивостям — атомарності та ізольованості.

Наприклад, при переведенні коштів з рахунку на рахунок, кошти можна спочатку зняти з першого рахунку, після чого нараховувати на другий. Відповідно, після зняття коштів, але до їх нарахування система перебуває в неузгодженому стані: коштів немає на жодному з рахунків. Але після завершення транзакції повна сума перебуватиме на другому (або першому у випадку скасування транзакції) рахунку.

Ізоляція Кожна транзакція повинна бути ізольована від інших, тобто її результат не повинен залежати від виконання інших паралельних транзакцій. На практиці, ізоляції важко досягнути, тому тут вводиться поняття "рівні ізоляції" (транзакція ізолюється не повністю).

Наприклад дві людини одночасно намагаються змінити баланс на одному рахунку. Ізольованість гарантує, що одна транзакція не вплине на іншу.

Довговічність Довговічність означає, що після успішного завершення транзакції її результати зберігаються у базі даних, навіть якщо відбувається збій системи. Це означає, що дані не втрачаються і залишаються доступними після будь-яких помилок, таких як збій живлення.

Наприклад після того, як ви завершили переказ грошей, нова інформація про баланси зберігається і залишається доступною, навіть якщо сервер бази даних раптово вимкнеться. Коли сервер перезапуститься, дані будуть відновлені, оскільки транзакцію було завершено.

Links

Які команди управління транзакціями ви знаєте⚑

Для управління транзакціями використовуються наступні команди

  • COMMIT: Зберігає зміни
  • ROLLBACK: Відкочує (скасовує) зміни
  • SAVEPOINT: Створює точку, до якої група транзакцій може відкотитися
  • SET TRANSACTION: Розміщує ім'я транзакції.

Команди управління транзакціями використовуються тільки для DML команд: INSERT, UPDATE, DELETE. Вони не можуть бути використані під час створення, зміни або видалення таблиці.

Links

Що таке рівні ізоляції транзакцій. Які вони бувають⚑

Ізоляція у сенсі ACID означає, що конкурентно виконувані транзакції ізольовані одна від одної - вони не можуть заважати одна одній. Класичні підручники з баз даних розуміють під ізоляцією серіалізованість (serializability). Тобто кожна транзакція виконується так, наче вона єдина в усій базі. БД гарантує, що результат фіксації транзакцій такий самий, як якщо б вони виконувались послідовно (по одній за раз), хоча насправді вони можуть виконуватись конкурентно.

При паралельному виконанні транзакцій можливі такі проблеми

  • втрачене оновлення (lost update) — при одночасній зміні одного блоку даних різними транзакціями, одна із змін втрачається;
  • "брудне" читання (dirty read) — читання даних, які додані чи змінені транзакцією, яка потім не підтвердиться (відкотиться);
  • неповторюване читання ( non-repeatable read) — при повторному читанні в рамках однієї транзакції, раніше прочитані дані з'являються зміненими;
  • фантомне читання (phantom reads) — одна транзакція в ході свого виконання декілька разів вибирає множину рядків за одними і тими ж критеріями. Інша транзакція в інтервалах між цими вибірками додає чи видаляє рядки чи змінює стовпці деяких рядків, що використовується в критеріях вибірки першої транзакції, і успішно закінчується. В результаті отримаємо, що одні і ті ж вибірки в першій транзакції дають різні множини рядків.

Рівень ізольованості транзакцій — значення, що задає рівень, при якому в транзакції дозволяються неузгоджені дані, тобто ступінь ізольованості однієї транзакції від іншої. Більш високий рівень ізольованості підвищує точність даних, але при цьому може знижуватись кількість транзакцій, що виконуються паралельно. З іншого боку, більш низький рівень ізольованості дозволяє виконувати більше паралельних транзакцій, але знижує точність даних.

У стандарті SQL описується чотири рівні ізоляції транзакцій

  • Read uncommited (Читання незафіксованих даних)
  • Read committed (Читання зафіксованих даних)
  • Repeatable read (Повторюване читання)
  • Serializable (Серіалізований)

Read Uncommitted Найнижчий рівень ізоляції. Транзакція може читати дані, які ще не були зафіксовані іншою транзакцією. Це може призводити до "брудного читання" (dirty reads), коли транзакція працює з даними, які можуть бути відкочені.

Read Committed Транзакція бачить лише ті зміни, які вже зафіксовані іншими транзакціями. Це запобігає "брудному читанню", але не вирішує проблему "неповторюваного читання" (non-repeatable reads), коли один і той самий запит повертає різні результати в межах однієї транзакції.

Реалізація завершеного читання може ґрунтуватися на одному з двох підходів: блокуванні або версійності.

  • Блокування даних, що читаються та змінюються.
    • Полягає в тому, що пишуча транзакція блокує змінні дані для читаючих транзакцій, що працюють на рівні read committed або вищому, до свого завершення, перешкоджаючи таким чином «брудному» читанню, а дані, що зчитуються, які блокуються транзакцією, звільняються одразу після завершення операції SELECT (отже ситуація «неповторного читання» може виникати на даному рівні ізоляції).
  • Збереження декількох версій рядків, що змінюються паралельно.
    • При кожній зміні рядка СУБД створює нову версію цього рядка, з якої продовжує працювати транзакція, що змінила дані, в той час як будь-якій іншій «читає» транзакції повертається остання зафіксована версія. Перевага такого підходу в тому, що він забезпечує більшу швидкість, оскільки запобігає блокуванню. Однак він вимагає, порівняно з першим, значно більшої витрати оперативної пам'яті, яка витрачається на зберігання версій рядків.

Repeatable Read Забезпечує, що дані, прочитані транзакцією, залишаються незмінними до її завершення. Вирішує проблему "неповторюваного читання", але не запобігає "фантомному читанню" (phantom reads), коли нові рядки додаються іншими транзакціями і впливають на результати.

Serializable Найвищий рівень ізоляції. Гарантує, що транзакції виконуються так, ніби вони були послідовними, тобто одна за одною. Усуває всі згадані проблеми, включаючи "фантомне читання", але може значно знизити продуктивність через блокування ресурсів.

За замовчуванням у PostgreSQL рівень ізоляції Read Committed. Такий рівень ізоляції завжди дозволяє бачити зміни внесені успішно завершеними транзакціями в паралельно відкритих транзакціях, що залишилися. У транзакції, що працює на цьому рівні, запит SELECT (без FOR UPDATE/SHARE) бачить лише дані, які були зафіксовані до початку запиту; він ніколи не побачить незафіксованих даних чи змін, внесених у процесі виконання запиту паралельними транзакціями. По суті, запит SELECT бачить знімок бази даних у момент початку виконання запиту. Однак SELECT бачить результати змін, внесених раніше в цій транзакції, навіть якщо вони ще не зафіксовані. Також треба зауважити, що два послідовні оператори SELECT можуть бачити різні дані навіть у рамках однієї транзакції, якщо якісь інші транзакції зафіксують зміни після виконання першого SELECT.

Вкладені транзакції⚑

Вкладеними називаються транзакції, виконання яких ініціюється з тіла вже активної транзакції.

Для створення вкладеної транзакції користувачеві не потрібні жодні додаткові команди. Він просто розпочинає нову транзакцію, не закриваючи попередню. Завершення транзакції верхнього рівня відкладається до завершення вкладених транзакцій. Якщо транзакція самого нижчого (вкладеного) рівня завершена невдало і скасована, то всі транзакції верхнього рівня, включаючи транзакцію першого рівня, будуть скасовані. Крім того, якщо декілька транзакцій нижнього рівня завершилися успішно (але не були зафіксовані), однак на середньому рівні (не найвища транзакція) невдало завершилася інша транзакція, то згідно з вимогами ACID відбудеться відкат всіх транзакцій всіх рівнів, включаючи успішно завершені. Лише коли всі транзакції на всіх рівнях завершено успішно, відбувається фіксація всіх зроблених змін у результаті успішного завершення транзакції верхнього рівня.

Кожна команда COMMIT TRANSACTION працює лише з останньою розпочатою транзакцією. При завершенні вкладеної транзакції команда COMMIT застосовується до найбільш "глибокої" вкладеної транзакції. Навіть якщо в команді COMMIT TRANSACTION вказано ім'я транзакції вищого рівня, буде завершена транзакція, розпочата останньою.

Що таке тригери?⚑

Тригери - це збережені процедури, які автоматично (які користувач не викликає явно) виконуються (або "спрацьовують") при виникненні певних подій або умов в базі даних. Вони можуть бути використані для відстеження змін у таблицях, валідації даних перед вставкою або оновленням, автоматичного виконання певних операцій після виконання певних дій користувача та інші сценарії.

Тригери можуть бути визначені для подій, таких як вставка (INSERT), оновлення (UPDATE), видалення (DELETE) записів у таблицях, і вони можуть допомагати забезпечити цілісність даних та реалізувати бізнес-логіку в базі даних.

-- Example trigger in SQL to track changes in the "Clients" table
CREATE TRIGGER track_changes
AFTER INSERT OR UPDATE OR DELETE ON Clients
FOR EACH ROW
BEGIN
    -- Logging changes in the "Clients" table
    INSERT INTO Change_Log (Action, Timestamp, User, Changes)
    VALUES ('INSERT/UPDATE/DELETE', NOW(), CURRENT_USER, NEW);
END;

Що таке курсор і навіщо він потрібен⚑

Запит до реляційної бази даних зазвичай повертає кілька рядків (записів) даних, але програма за один раз обробляє лише один запис. Навіть якщо вона має справу одночасно з декількома рядками (наприклад, виводить дані у формі електронних таблиць), їх кількість все ще обмежена. Крім того, при модифікації, видаленні або додаванні даних робочою одиницею є рядок. У цій ситуації на передній план виступає концепція курсора, і в такому контексті курсор - вказівник на рядок.

Курсор в SQL - це область в пам'яті бази даних, яка призначена для зберігання останнього оператора SQL. Якщо поточний оператор - запит до бази даних, в пам'яті зберігається рядок даних запиту, який називається поточним значенням або поточним рядком курсора. Зазначена область в пам'яті має ім'я і доступна для прикладних програм.

Зазвичай курсори використовуються для вибору з бази даних певної підмножини збереженої в ній інформації. В кожний момент часу прикладною програмою може бути перевірений один рядок курсора. Курсори часто використовуються в операторах SQL, вбудованих у написані на мовах процедурного типу прикладні програми. Деякі з них неявно створюються сервером бази даних, тоді як інші визначаються програмістами.

У деяких випадках застосування курсора необхідно. Однак, якщо це можливо, цього слід уникати і працювати зі стандартними командами обробки даних: SELECT, UPDATE, INSERT, DELETE. Крім того, що курсори не дозволяють здійснювати операції зміни над всім обсягом даних, швидкість виконання операцій обробки даних за допомогою курсора помітно нижча, ніж у стандартних засобів SQL.

Що таке VACUUM в PostgreSQL⚑

VACUUM вивільняє простір, зайнятий "мертвими" кортежами. Під час звичайних операцій Postgres кортежі, видалені або застарілі в результаті оновлення, фізично не видаляються з таблиці; вони залишаються в ній до виконання команди VACUUM. Таким чином, періодично необхідно виконувати VACUUM, особливо для часто змінюваних таблиць.

Як виявити повільний SQL запит?⚑

  • Логування повільних запитів - У багатьох СУБД є можливість увімкнути логування повільних запитів. Наприклад, у MySQL можна налаштувати параметри slow_query_log та long_query_time, щоб записувати запити, які виконуються довше за заданий час.
  • Профілювання запитів - Використання інструментів, які надають детальну інформацію про виконання запиту. У PostgreSQL команда EXPLAIN або EXPLAIN ANALYZE дозволяє побачити план виконання запиту, оцінити використання індексів, кількість зчитувань та час виконання.
  • Інструменти моніторингу СУБД - Використання спеціалізованих інструментів, таких як pg_stat_statements у PostgreSQL або Performance Schema у MySQL, які відстежують та агрегують інформацію про виконувані запити.
  • Імітація навантаження - Використання інструментів для створення штучного навантаження (наприклад, Apache JMeter) дозволяє оцінити поведінку запитів у реальних умовах.

Що таке профілювання запитів?⚑

Профілювання запитів - це процес аналізу та оптимізації виконання запитів до бази даних.

Під час профілювання запитів, ви використовується "EXPLAIN", щоб отримати план виконання запиту. Цей план показує, як база даних обробляє запит, включаючи послідовність операцій та використані ресурси.

"EXPLAIN" допомагає ідентифікувати можливі "гарячі точки" в запиті. Це дає змогу розробникам виявити слабкі місця та можливість їх виправити, наприклад створити або оптимізувати індекс, переписати запит або змінити налаштування бази даних.

Що таке Deadlock? Як їх уникати?⚑

Deadlock (взаємоблокування) — це ситуація, коли дві або більше транзакції блокують одна одну, очікуючи на ресурси, які вже заблоковані іншими транзакціями. У такому стані транзакції не можуть завершитися, доки не буде втручання ззовні.

Дедлок може виникати, коли дві транзакції блокують одні й ті ж ресурси, але в різному порядку. Наприклад, одна транзакція блокує таблицю A і чекає таблицю B, тоді як інша транзакція блокує B і чекає A.

Можливі варіанти для уникнення Deadlock

  • Зменшення часу утримання блокувань - Мінімізація кількіості операцій між початком транзакції (BEGIN) і її завершенням (COMMIT або ROLLBACK).
  • Використання рівнів ізоляції - Вибір відповідного рівня ізоляції (наприклад, READ COMMITTED) може зменшити ймовірність виникнення взаємоблокувань.
  • Розбиття великих транзакцій - Якщо можливо, розбити великі транзакції на менші, щоб зменшити ризик блокування ресурсів.
  • Використання таймаутів для блокувань - Налаштування таймауту дозволяє уникнути затяжного очікування у випадку Deadlock. У PostgreSQL, наприклад, це параметр lock_timeout.

Нормалізація баз даних⚑

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

Нормалізація дозволяє розділити таблиці таким чином, що кожна таблиця містить лише ті атрибути, які взаємодіють з однією конкретною сутністю. Це виконується за допомогою розподілу таблиць та визначення зв'язків між ними.

Денормалізація баз даних - це процес об'єднання даних із кількох таблиць в одну або кілька денормалізованих таблиць для оптимізації продуктивності та спрощення запитів.

Денормалізації включає

  • Зменшення числа з'єднань таблиць, щоб покращити продуктивність під час виконання запитів.
  • Дублювання даних для спрощення та прискорення вилучення даних.
  • Використання агрегованих стовпців для попереднього обчислення агрегатних функцій.

Нормальні форми в базах даних визначають, наскільки добре структуровані таблиці бази даних щодо уникнення дублювання даних та забезпечення консистентності. Існують кілька рівнів нормальних форм, кожен з яких вказує на певний ступінь оптимізації та структурованості бази даних.

Існують такі рівні нормалізації: перша нормальна форма (1НФ), 2НФ, 3НФ, нормальна форма Бойса-Кодда (БКНФ), 4НФ, 5НФ. Але дотепер жодна з реляційних СКБД не надає належної підтримки усім п'яти нормальним формам. Це відбувається через жорсткі вимоги до продуктивності. Суть справи полягає в тому, що в повністю нормалізованій БД для виконання запиту треба з'єднати настільки багато таблиць, що продуктивність такої системи не зможе задовольнити користувачів. Тому на практиці використовують лише перші три рівня нормалізації – 1НФ, 2НФ, ЗНФ.

Види нормальних форм

  • Перша нормальна форма (1NF) утворює ґрунт для структурованої схеми бази даних
    • Кожна таблиця повинна мати первинний ключ: мінімальний набір колонок, які ідентифікують запис.
    • Відсутні групи атрибутів з однаковими за змістом значеннями, які повторюються у межах одного кортежу.
    • Атомарність - кожен атрибут повинен мати лише одне значення, а не множину значень. Наприклад, якщо у атрибуті «Прізвище, ім'я, по батькові» міститься прізвище, ім'я та по батькові читача, вимога неподільності не виконується
  • Друга нормальна форма (2NF)
    • Таблиця повинна бути у 1NF
    • Всі неключові стовпці повинні повністю залежати від первинного ключа.
  • Третя нормальна форма (3NF)
    • Таблиця повинна бути у 2NF
    • Усуває транзитивні залежності, тобто кожен неключовий атрибут повинен прямо залежати від первинного ключа, а не від інших неключових атрибутів. Це ще більше зменшує надмірність і підвищує цілісність.

Переваги нормалізації

  • Зменшення надмірності даних та забезпечення цілісності бази даних.
  • Поліпшення продуктивності під час оновлення даних.
  • Спрощення структури бази даних, що робить її легшою для розуміння та підтримки.

Недоліки нормалізації

  • Збільшення складності при вилученні даних із кількох пов'язаних таблиць.
  • Збільшення числа з'єднань таблиць при виконанні складних запитів, що може негативно позначитися на продуктивності.

Links

Партиціювання та шардування⚑

Партиціювання та шардування - це техніки, які дозволяють розбити велику базу даних на менші частини, щоб забезпечити більшу масштабованість та продуктивність.

Партиціювання (Partitioning) - це процес розділення таблиці так, що її дані розділяються на менші, більш керовані та ефективно оброблювані частини, які називаються "партиціями". Кожна партиція може бути розміщена на окремому диску або файловій системі, що дозволяє базі даних обробляти запити швидше, оскільки кожна партиція може бути оброблена незалежно від інших, отже обробляється менше даних. Також, можна створювати індекси на окремих партиціях, що полегшує оптимізацію запитів.

Партиціювання включає в себе наступні кроки

  • Визначення критеріїв партиціювання: це може бути дата, час, ідентифікатор або будь-який інший атрибут, що дозволяє логічно розділити дані.
  • Створення партицій: кожна партиція створюється відповідно до визначених критеріїв.
  • Розподіл даних: дані переносяться в відповідні партиції.

Шардування (Sharding) - це процес розділення таблиці на менші фрагменти - шарди, які можуть бути збережені на різних серверах. Однак, на відміну від партиціювання, шардування зазвичай використовується для розділення даних на різних серверах у різних локаціях. Це дозволяє збільшити доступність та надійність бази даних, оскільки в разі відмови одного сервера, інші сервери можуть продовжувати працювати.

Шардування буває вертикальне і горизонтальне

  • Горизонтальне шардування відбувається, коли рядки даних розподіляються між різними базами даних (шардами). Кожен шард містить унікальний набір рядків. Наприклад, один шард може містити дані про користувачів з іменами, що починаються на літери A-M, а інший шард - про користувачів з іменами на N-Z.
  • Вертикальне шардування відбувається, коли стовпці даних розподіляються між різними базами даних. Кожен шард містить унікальний набір стовпців. Наприклад, один шард може містити контактну інформацію користувача, а інший - інформацію про замовлення цього користувача.

Master-Slave⚑

Архітектура Master-Slave (тепер часто називається Primary-Replica, щоб уникнути застарілих термінів) використовується для горизонтального масштабування баз даних і забезпечення високої доступності. У цій моделі одна база даних виступає як Master, а інші як Slave. Використовується для підвищення продуктивності, коли операції читання значно переважають операції запису, або для забезпечення резервування даних. Ця архітектура є популярною для додатків із високими вимогами до масштабованості з великою кількістю запитів на читання.

Master (Primary) - основна база даних, яка приймає всі операції запису. Зміни, внесені до Master, реплікуються (передаються) до всіх Slave.

Slave (Replica) - це одна або більше копій Master, які синхронізуються через реплікацію. Slave обслуговують лише операції читання, що дозволяє розподілити навантаження на систему.

Реплікація може бути синхронною (запис на Master завершується тільки після оновлення Slave) або асинхронною (Master завершує операцію запису, не чекаючи синхронізації з Slave).

Переваги

  • Розвантаження Master - операції читання перенаправляються на Slave, що зменшує навантаження на Master.
  • Масштабованість - легко додати нові Slave для обробки більшого обсягу запитів на читання.
  • Резервування даних - у разі відмови Master, один із Slave може бути підвищений до Master.

Недоліки

  • Затримка реплікації - у разі асинхронної реплікації можуть виникати затримки, що призводять до читання застарілих даних зі Slave.
  • Складність управління - підтримка синхронізації між Master і Slave потребує додаткових ресурсів і інструментів.
  • Відсутність автоматичного переходу на новий Master - у разі відмови Master зазвичай потрібне ручне втручання для призначення нового Master.

Яка різниця між OLTP та OLAP?⚑

OLTP (Online Transactional Processing) and OLAP (Online Analytical Processing) — це два різних типи обробки даних, які використовуються в сфері інформаційних систем.

OLTP (Online Transactional Processing)

  • Призначення: OLTP призначений для операційної обробки даних. Коли виконуються багато транзакцій в реальному часі.
  • Тип операцій: OLTP обробляє прості операції вставки, оновлення та видалення даних. Наприклад, банківські транзакції, бронювання готелів, зміни паролів тощо.
  • Швидкість обробки: Відповіді мають бути дуже швидкими, з відгуком в мілісекундах.

OLAP (Online Analytical Processing)

  • Призначення: OLAP призначений для аналізу даних. Коли виконуються складні аналітичні розрахунки на великих обсягах даних.
  • Тип операцій: Використовується для глибокого аналізу даних, такого як добування знань, бізнес-інтелект, прогнозування продажів тощо.
  • Швидкість обробки: OLAP може працювати з великими обсягами даних, тож відповіді можуть бути повільними.

Сесія, курсор⚑

Сесія - це окремий сеанс зв’язку між клієнтом і сервером бази даних. Сесія починається при встановленні з'єднання з БД і закінчується після його завершення. У рамках однієї сесії клієнт може виконувати кілька запитів до бази. Для кожної сесії сервер виділяє ресурси, що може стати обмеженням при великій кількості підключень.

Курсор - це об'єкт, який використовується для виконання SQL-запитів і отримання результатів. Курсор дозволяє обробляти результати запиту поетапно, зменшуючи обсяг пам’яті, необхідної для зберігання даних у пам'яті клієнта.

Приклад роботи з курсором у Python (модуль psycopg2)

import psycopg2

connection = psycopg2.connect("dbname=test user=postgres password=secret")  # Connect to the database
cursor = connection.cursor()  # Create a cursor
cursor.execute("SELECT * FROM users")  # Execute a query

for row in cursor.fetchall():  # Fetch results
    print(row)

cursor.close()  # Close cursor and connection
connection.close()

Пул з'єднань, PgBouncer⚑

Пул з'єднань - це механізм повторного використання вже існуючих з'єднань із базою даних. Він дозволяє уникнути витрат на створення нових з'єднань при кожному запиті, що суттєво підвищує продуктивність додатка.

В Python пул з'єднань можна реалізувати за допомогою бібліотеки psycopg2 або ORM, як-от SQLAlchemy:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("postgresql://user:password@localhost/db", pool_size=10, max_overflow=5)  # Create a connection pool

Session = sessionmaker(bind=engine)  # Create a session
session = Session()

result = session.execute("SELECT * FROM users")  # Execute a query
for row in result:
    print(row)

session.close()  # Close the session

PgBouncer - це легкий проксі-сервер для PostgreSQL, який виконує функції пулінгу з'єднань. PgBouncer дозволяє зменшити навантаження на сервер бази даних, зменшуючи кількість одночасних з'єднань.

Він підтримує кілька режимів роботи

  • Session pooling: кожне з'єднання клієнта відповідає одній сесії сервера.
  • Transaction pooling: з'єднання використовується лише для однієї транзакції.
  • Statement pooling: з'єднання використовується для виконання одного SQL-запиту.