Ефективне проєктування бази даних PostgreSQL та типи даних

1. Проєктування ефективних таблиць бази даних

При проєктуванні таблиці бази даних важливо вибирати типи даних, які є представницькими та компактними:

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

Приклад: Вік

Вік людини зазвичай знаходиться в межах від 0 до 150 років. Тому, замість використання INTEGER або BIGINT, які займають більше місця, ефективніше використовувати SMALLINT.

CREATE TABLE people (  
 id SERIAL PRIMARY KEY,  
 name VARCHAR(100),  
 age SMALLINT  
);

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

2. Розуміння числових типів даних PostgreSQL

Числові типи vs типи з плаваючою комою

У PostgreSQL можна вибрати між числовими та типами з плаваючою комою в залежності від точності, необхідної для вашої програми:

• Числові типи (NUMERIC, DECIMAL) є точними і можуть обробляти дуже великі значення, але вони повільніші для арифметичних операцій.
• Типи з плаваючою комою (REAL, DOUBLE PRECISION) швидші, але наближені значення, що робить їх придатними для ситуацій, коли незначна втрата точності є прийнятною.

Приклад числового типу:

Тип NUMERIC(PRECISION, SCALE) дозволяє визначити загальну кількість цифр (PRECISION) та кількість десяткових знаків (SCALE).

SELECT 12345::NUMERIC(5, 3); -- Результат: 123.450

Якщо ви спробуєте вставити значення, яке перевищує визначену точність, PostgreSQL видасть помилку:

SELECT 125345::NUMERIC(5, 3); -- Помилка: Переповнення числового поля

Приклад типу з плаваючою комою:

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

SELECT 6.0::float4 * (3.0 / 10.0) as float,  
 6.0::NUMERIC * (3.0 / 10.0) as numeric  

Вивід:  
+------------------+-------+  
|float |numeric|  
+------------------+-------+  
|1.7999999999999998|1.8 |  
+------------------+-------+

3. Зберігання грошей у PostgreSQL

Використання типу даних money

PostgreSQL має вбудований тип даних money, який зручний для зберігання валютних значень у стандартному форматі (долари США). Однак він має деякі обмеження:

Переваги:

• Простота використання та відображення значень з доларовим знаком.
• Може приймати різні числові значення у форматах, таких як VARCHAR, INT, та DECIMAL в операторі INSERT.

CREATE TABLE account (  
 id SERIAL PRIMARY KEY,  
 amount MONEY  
);  

INSERT INTO account (amount)  
VALUES ('$123.00'),  
 ('$23.99'),  
 (22.22),  
 (19.99),  
 (1200);

Недоліки:

• Втрати точності: При виконанні математичних операцій може втрачатися точність. Це не є ідеальним для фінансових обчислень з високою точністю.
• Обробка валют: Валюта визначається налаштуванням lc_monetary (за замовчуванням en_US.utf8 для доларів США), що може спричинити плутанину, якщо потрібно працювати з кількома валютами. Зміна lc_monetary змінює формат відображення, але не перетворює фактичні значення.

Альтернативні підходи:

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

-- Зберігаємо $199.99 як 19999 центів  
CREATE TABLE account (  
 id SERIAL PRIMARY KEY,  
 amount INTEGER -- Сума зберігається в центрах  
);

Якщо ви працюєте з кількома валютами, найкраще зберігати окрему колонку currency для їх відмінності:

CREATE TABLE account (  
 id SERIAL PRIMARY KEY,  
 amount INTEGER, -- У центрах  
 currency CHAR(3) -- наприклад, 'USD', 'EUR', 'AED'  
);

Special Values: Infinity і -Infinity

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

SELECT '-inf'::float; -- Результат: -Infinity
SELECT 'inf'::float + 'inf'::float; -- Результат: Infinity
SELECT 'inf'::float - 'inf'::float; -- Результат: NaN (Not a Number)
```

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

5. Перетворення типів у PostgreSQL

PostgreSQL пропонує потужну функціональність перетворення типів (type casting), що дозволяє конвертувати значення між різними типами даних.

Приклад:

SELECT '100'::INTEGER, '23.99'::NUMERIC, pg_typeof('100'::INT2);

Це перетворює рядок '100' на тип INTEGER, а рядок '23.99' — на тип NUMERIC. Ви також можете перевірити тип перетворення за допомогою функції pg_typeof():

SELECT pg_typeof('100'::INT2); -- Результат: smallint

6. Функція PGCOLUMNSIZE

PostgreSQL має функцію PG_COLUMN_SIZE(), яка може бути корисною, коли вам потрібно зрозуміти розмір зберігання різних типів даних. Це важливо для оптимізації проєктування таблиць і продуктивності запитів.

Приклад:

SELECT PG_COLUMN_SIZE(200::int2) AS int2,  
 PG_COLUMN_SIZE(3000) AS int4,  
 PG_COLUMN_SIZE(299.99) AS numeric,  
 PG_COLUMN_SIZE(2922139.21199) AS numeric;

Вивід:

+----+----+-------+-------+  
|int2|int4|numeric|numeric|  
+----+----+-------+-------+  
|2 |4 |10 |14 |  
+----+----+-------+-------+

Це допоможе вам оцінити, чи використовуєте ви занадто великий тип даних для певного значення.

Висновок

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

• Завжди вибирайте компактні та представницькі типи даних для ваших колонок.
• Розгляньте можливість використання NUMERIC для фінансових даних і INTEGER для грошових значень у найменших одиницях (наприклад, центрах).
• Використовуйте перетворення типів (type casting) та PGCOLUMNSIZE, щоб оптимізувати ваші запити та розуміти, як типи даних впливають на зберігання.

З урахуванням цих моментів, ви будете готові до розробки надійних та високопродуктивних схем PostgreSQL.

Перекладено з: Efficient PostgreSQL Database Design and Data Types