👩💻 Типы данных в PostgreSQL: как они устроены и почему их выбор имеет значение
Одна из самых распространенных ошибок при проектировании базы данных это выбирать типы данных по принципу "лишь бы поместилось". На самом деле тип данных определяет не только допустимые значения, но и способ их хранения, объем занимаемой памяти, производительность запросов и размер индексов. PostgreSQL предоставляет широкий набор встроенных типов данных, каждый из которых предназначен для решения определенных задач. В этом посте коротко, постарался описать, основные типы которые встречаются в постгре.
Тут кста пост про то как создали постгре 🖥
❗️ Целочисленные типы
Для хранения целых чисел PostgreSQL предоставляет три основных типа:
SMALLINT
INTEGER
BIGINT
➖ SMALLINT занимает 2 байта и хранит значения в диапазоне от -32 768 до 32 767.
➖ INTEGER (или сокращенно INT) занимает 4 байта и является наиболее часто используемым типом. Его диапазон составляет от -2 147 483 648 до 2 147 483 647.
➖ BIGINT занимает 8 байт и используется, когда диапазона INTEGER недостаточно, например для очень больших идентификаторов или счетчиков.
Типы SERIAL, BIGSERIAL и SMALLSERIAL не являются отдельными типами данных. Это специальная запись, которая автоматически создает последовательность (SEQUENCE) и задает значение по умолчанию для соответствующего целочисленного столбца.
При выборе типа рекомендуется использовать минимально достаточный размер. Это позволяет уменьшить объем хранения данных и размер индексов, хотя в современных системах влияние на производительность обычно менее заметно, чем влияние правильно построенных индексов и структуры запросов.
❗️ Числа с фиксированной точностью
Для хранения значений, где важна абсолютная точность, используются типы NUMERIC и DECIMAL.
price NUMERIC(10,2)
Первый параметр определяет максимальное количество цифр, второй, количество цифр после десятичной точки.
Например:
99999999.99
Главное преимущество NUMERIC это возможность хранить десятичные числа с заданной точностью без ошибок представления, характерных для чисел с плавающей точкой. В отличие от типов с плавающей точкой, каждое значение хранится с заданной точностью.
Именно поэтому все финансовые расчеты, бухгалтерские операции и денежные суммы рекомендуется хранить в NUMERIC. Следует учитывать, что операции над такими числами требуют больше вычислений и обычно выполняются медленнее, чем над целыми числами.
❗️ Числа с плавающей точкой
В PostgreSQL используются типы:
REAL
DOUBLE PRECISION
Также поддерживается запись
FLOAT(p)
где значение p определяет необходимую точность. В зависимости от нее постгре автоматически использует либо REAL, либо DOUBLE PRECISION. Если p ≤ 24, используется REAL, иначе (до 53) будет DOUBLE PRECISION. Эти типы реализованы в соответствии со стандартом IEEE 754. Число хранится в виде знака, экспоненты и мантиссы, благодаря чему поддерживается очень широкий диапазон значений. Однако далеко не каждую десятичную дробь можно представить точно в двоичной системе.
Например:
SELECT 0.1 + 0.2;
может вернуть
0.30000000000000004
Это нормальное поведение чисел с плавающей точкой, а не ошибка СУБД. Поэтому REAL и DOUBLE PRECISION подходят для инженерных расчетов, статистики и научных вычислений, но не для хранения денежных сумм.
❗️ Символьные типы
Наиболее часто используются три типа:
CHAR(n)
VARCHAR(n)
TEXT
➖ CHAR(n) хранит строки фиксированной длины. Если фактическая длина меньше указанной, PostgreSQL дополняет значение пробелами до нужного размера.
Такой тип имеет смысл использовать только тогда, когда длина значения всегда одинакова, например для кодов стран или сокращений.
➖ VARCHAR(n) хранит строки переменной длины и дополнительно проверяет, что значение не превышает указанное ограничение.
➖ TEXT также хранит строки переменной длины, но без ограничения размера.
Интересная особенность PostgreSQL заключается в том, что TEXT и VARCHAR используют практически одинаковый механизм хранения. На практике различий в производительности между ними практически нет. Единственное отличие состоит в том, что VARCHAR(n) выполняет дополнительную проверку максимальной длины строки при записи данных. Именно поэтому многие разработчики PostgreSQL предпочитают использовать TEXT, если ограничение длины не является бизнес-требованием.
❗️ Дата и время
Для хранения временных данных PostgreSQL предоставляет несколько специализированных типов:
DATE
TIME
TIMESTAMP
TIMESTAMPTZ
➖ DATE хранит только календарную дату.
➖ TIME содержит только время суток.
➖ TIMESTAMP хранит дату и время без информации о часовом поясе.
➖ TIMESTAMPTZ (Timestamp with Time Zone) хранит момент времени с учетом часового пояса. Внутри PostgreSQL такие значения преобразуются в UTC, а при выводе отображаются в часовом поясе текущей сессии.
Оба типа TIMESTAMP занимают 8 байт и представлены как количество микросекунд относительно внутренней эпохи PostgreSQL. Благодаря этому операции сравнения, сортировки и вычисления интервалов выполняются очень эффективно.
❗️ Логический тип
Для хранения булевых значений используется тип
BOOLEAN
Он занимает 1 байт и поддерживает значения:
TRUE
FALSE
NULL
PostgreSQL также понимает различные формы записи при вводе значения, например 1, 0, yes, no, on, off, автоматически преобразуя их в логические значения.
❗️ Бинарные данные
Для хранения произвольной последовательности байтов используется тип:
BYTEA
Он подходит для хранения изображений, документов, архивов и других бинарных объектов.
Для очень больших файлов постгре также предоставляет механизм Large Objects, который позволяет хранить данные отдельно от основной таблицы и работать с ними потоково, но на самом деле он используется значительно реже, чем BYTEA, и обычно нужен лишь для действительно крупных объектов.
❗️ Специализированные типы PostgreSQL
Одно из преимуществ постгри это наличие типов данных, отсутствующих во многих других СУБД.
➖ UUID - используется для хранения глобально уникальных идентификаторов (пост про юиды тут 😎)
id UUID
UUID занимает 16 байт и позволяет генерировать уникальные идентификаторы без обращения к последовательностям.
➖ JSON и JSONB
JSON
JSONB
JSON хранит исходный текст документа.
JSONB преобразует данные во внутренний бинарный формат, позволяющий создавать индексы и выполнять быстрый поиск по содержимому.
В большинстве случаев именно JSONB является предпочтительным выбором.
➖ ARRAY - PostgreSQL поддерживает массивы как полноценный тип данных.
tags TEXT[]
scores INTEGER[]
Это позволяет хранить несколько значений в одном поле без создания отдельных таблиц для хранения небольших коллекций значений, хотя применять массивы следует только тогда, когда это действительно оправдано моделью данных.
➖ ENUM - Позволяет определить собственный тип с фиксированным набором значений. Например:
CREATE TYPE order_status AS ENUM (
'new',
'paid',
'shipped',
'completed'
);
Такой подход обеспечивает контроль допустимых значений на уровне базы данных.
Тип данных в PostgreSQL определяет значительно больше, чем допустимый формат значения. Он влияет на внутреннее представление данных, объем хранения, размер индексов, скорость выполнения запросов и возможности оптимизатора.
Чем точнее выбран тип данных на этапе проектирования схемы, тем эффективнее будет работать база данных при росте объема информации. Именно поэтому грамотное проектирование БД начинается не с написания запросов, а с выбора подходящих типов данных.
#SQL #Типы_данных #PostgreSQL
📱 Подписаться на канал
💻 Курс автора по SQL DDL
👩💻Nodus
Одна из самых распространенных ошибок при проектировании базы данных это выбирать типы данных по принципу "лишь бы поместилось". На самом деле тип данных определяет не только допустимые значения, но и способ их хранения, объем занимаемой памяти, производительность запросов и размер индексов. PostgreSQL предоставляет широкий набор встроенных типов данных, каждый из которых предназначен для решения определенных задач. В этом посте коротко, постарался описать, основные типы которые встречаются в постгре.
Тут кста пост про то как создали постгре 🖥
❗️ Целочисленные типы
Для хранения целых чисел PostgreSQL предоставляет три основных типа:
SMALLINT
INTEGER
BIGINT
➖ SMALLINT занимает 2 байта и хранит значения в диапазоне от -32 768 до 32 767.
➖ INTEGER (или сокращенно INT) занимает 4 байта и является наиболее часто используемым типом. Его диапазон составляет от -2 147 483 648 до 2 147 483 647.
➖ BIGINT занимает 8 байт и используется, когда диапазона INTEGER недостаточно, например для очень больших идентификаторов или счетчиков.
Типы SERIAL, BIGSERIAL и SMALLSERIAL не являются отдельными типами данных. Это специальная запись, которая автоматически создает последовательность (SEQUENCE) и задает значение по умолчанию для соответствующего целочисленного столбца.
При выборе типа рекомендуется использовать минимально достаточный размер. Это позволяет уменьшить объем хранения данных и размер индексов, хотя в современных системах влияние на производительность обычно менее заметно, чем влияние правильно построенных индексов и структуры запросов.
❗️ Числа с фиксированной точностью
Для хранения значений, где важна абсолютная точность, используются типы NUMERIC и DECIMAL.
price NUMERIC(10,2)
Первый параметр определяет максимальное количество цифр, второй, количество цифр после десятичной точки.
Например:
99999999.99
Главное преимущество NUMERIC это возможность хранить десятичные числа с заданной точностью без ошибок представления, характерных для чисел с плавающей точкой. В отличие от типов с плавающей точкой, каждое значение хранится с заданной точностью.
Именно поэтому все финансовые расчеты, бухгалтерские операции и денежные суммы рекомендуется хранить в NUMERIC. Следует учитывать, что операции над такими числами требуют больше вычислений и обычно выполняются медленнее, чем над целыми числами.
❗️ Числа с плавающей точкой
В PostgreSQL используются типы:
REAL
DOUBLE PRECISION
Также поддерживается запись
FLOAT(p)
где значение p определяет необходимую точность. В зависимости от нее постгре автоматически использует либо REAL, либо DOUBLE PRECISION. Если p ≤ 24, используется REAL, иначе (до 53) будет DOUBLE PRECISION. Эти типы реализованы в соответствии со стандартом IEEE 754. Число хранится в виде знака, экспоненты и мантиссы, благодаря чему поддерживается очень широкий диапазон значений. Однако далеко не каждую десятичную дробь можно представить точно в двоичной системе.
Например:
SELECT 0.1 + 0.2;
может вернуть
0.30000000000000004
Это нормальное поведение чисел с плавающей точкой, а не ошибка СУБД. Поэтому REAL и DOUBLE PRECISION подходят для инженерных расчетов, статистики и научных вычислений, но не для хранения денежных сумм.
❗️ Символьные типы
Наиболее часто используются три типа:
CHAR(n)
VARCHAR(n)
TEXT
➖ CHAR(n) хранит строки фиксированной длины. Если фактическая длина меньше указанной, PostgreSQL дополняет значение пробелами до нужного размера.
Такой тип имеет смысл использовать только тогда, когда длина значения всегда одинакова, например для кодов стран или сокращений.
➖ VARCHAR(n) хранит строки переменной длины и дополнительно проверяет, что значение не превышает указанное ограничение.
➖ TEXT также хранит строки переменной длины, но без ограничения размера.
Интересная особенность PostgreSQL заключается в том, что TEXT и VARCHAR используют практически одинаковый механизм хранения. На практике различий в производительности между ними практически нет. Единственное отличие состоит в том, что VARCHAR(n) выполняет дополнительную проверку максимальной длины строки при записи данных. Именно поэтому многие разработчики PostgreSQL предпочитают использовать TEXT, если ограничение длины не является бизнес-требованием.
❗️ Дата и время
Для хранения временных данных PostgreSQL предоставляет несколько специализированных типов:
DATE
TIME
TIMESTAMP
TIMESTAMPTZ
➖ DATE хранит только календарную дату.
➖ TIME содержит только время суток.
➖ TIMESTAMP хранит дату и время без информации о часовом поясе.
➖ TIMESTAMPTZ (Timestamp with Time Zone) хранит момент времени с учетом часового пояса. Внутри PostgreSQL такие значения преобразуются в UTC, а при выводе отображаются в часовом поясе текущей сессии.
Оба типа TIMESTAMP занимают 8 байт и представлены как количество микросекунд относительно внутренней эпохи PostgreSQL. Благодаря этому операции сравнения, сортировки и вычисления интервалов выполняются очень эффективно.
❗️ Логический тип
Для хранения булевых значений используется тип
BOOLEAN
Он занимает 1 байт и поддерживает значения:
TRUE
FALSE
NULL
PostgreSQL также понимает различные формы записи при вводе значения, например 1, 0, yes, no, on, off, автоматически преобразуя их в логические значения.
❗️ Бинарные данные
Для хранения произвольной последовательности байтов используется тип:
BYTEA
Он подходит для хранения изображений, документов, архивов и других бинарных объектов.
Для очень больших файлов постгре также предоставляет механизм Large Objects, который позволяет хранить данные отдельно от основной таблицы и работать с ними потоково, но на самом деле он используется значительно реже, чем BYTEA, и обычно нужен лишь для действительно крупных объектов.
❗️ Специализированные типы PostgreSQL
Одно из преимуществ постгри это наличие типов данных, отсутствующих во многих других СУБД.
➖ UUID - используется для хранения глобально уникальных идентификаторов (пост про юиды тут 😎)
id UUID
UUID занимает 16 байт и позволяет генерировать уникальные идентификаторы без обращения к последовательностям.
➖ JSON и JSONB
JSON
JSONB
JSON хранит исходный текст документа.
JSONB преобразует данные во внутренний бинарный формат, позволяющий создавать индексы и выполнять быстрый поиск по содержимому.
В большинстве случаев именно JSONB является предпочтительным выбором.
➖ ARRAY - PostgreSQL поддерживает массивы как полноценный тип данных.
tags TEXT[]
scores INTEGER[]
Это позволяет хранить несколько значений в одном поле без создания отдельных таблиц для хранения небольших коллекций значений, хотя применять массивы следует только тогда, когда это действительно оправдано моделью данных.
➖ ENUM - Позволяет определить собственный тип с фиксированным набором значений. Например:
CREATE TYPE order_status AS ENUM (
'new',
'paid',
'shipped',
'completed'
);
Такой подход обеспечивает контроль допустимых значений на уровне базы данных.
Тип данных в PostgreSQL определяет значительно больше, чем допустимый формат значения. Он влияет на внутреннее представление данных, объем хранения, размер индексов, скорость выполнения запросов и возможности оптимизатора.
Чем точнее выбран тип данных на этапе проектирования схемы, тем эффективнее будет работать база данных при росте объема информации. Именно поэтому грамотное проектирование БД начинается не с написания запросов, а с выбора подходящих типов данных.
#SQL #Типы_данных #PostgreSQL
📱 Подписаться на канал
💻 Курс автора по SQL DDL
👩💻Nodus