Database Scheme Table PostgreSQL

Статья основана на одиннадцатом видео из 30 тем курса SQL c 0 от Аристова Евгения.

Табличное пространство

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

Объекты – всё, что материально хранится: таблицы, индексы и т.д.

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

Также существуют глобальные объекты, которые находятся в единственном каталоге pg_catalog (например, pg_class) и относятся ко всем базам данных в кластере.

Рассмотрим хранение объектов.

В классическом pg_default есть каталог с PostgreSQL, каталог base, идентификатор базы данных (глобальный).

В каталог pg_tblspc можно смонтировать любой другой каталог – там будет ссылка на физический диск. Это нужно для распараллеливания нагрузки.

Важно, что один объект может быть расположен только в одном месте (табличном пространстве). При этом перенос возможен, но это физический перенос файла (понадобится эксклюзивная блокировка).

Варианты оптимизации ТП:

  • Отдельные объекты (БД, таблицы) разместить в разных ТП, расположенных на разных дисках, дисковых массивах, таким образом распараллелить нагрузку
  • Смонтировать часть оперативной памяти часть как файловую систему, создать ТП и держать там, например, материализованные представления, временные таблицы, индексы – то, что не страшно потерять при перезагрузке сервера. Хранить таким образом оригинальные данные нельзя
  • Установить локально на сервер ssd диск под такие же некритичные данные

Устройство PostgreSQL

Рассмотрим устройство с логической точки зрения.

На виртуальной машине можно развернуть несколько кластеров PostgreSQL (кластер – отдельно запущенный инстанс на каком-то порту). Внутри каждого кластера может быть несколько БД, а внутри каждой БД несколько схем. В каждой схеме свои Relation (таблицы, индексы и другие объекты). Количество возможных кластеров зависит от ресурсов. БД, схемы, Relation и табличные пространства относятся к DDL – data definition language – язык, описывающий схему хранения данных.

Database

Относится к DDL.

Является контейнером самого верхнего уровня в любом кластере. Нельзя создать объект, не принадлежащий какой-то базе данных.

По умолчанию в любом кластере есть как минимум 3 БД:

  • postgresql (по умолчанию работаем с ней)
  • template0
  • template1

Присутствует и на логическом, и на физическом уровне. То есть каждой базе данных соответствует каталог.

postgres

  • Первая база данных для регулярной работы. Имеет такое имя, так как автоматически подключается по имени пользователя.
  • Создается по умолчанию
  • Желательно не использовать (создавать свои БД для работы), но и не удалять – иногда она нужна для различных утилит

template0

  • Предназначен для восстановления из резервной копии
  • По умолчанию нет прав на connect (можно изменить, но категорически не рекомендуется)
  • Лучше всего не создавать в ней никаких объектов

template1

  • Используется как шаблон для создания новых баз данных
  • В нем имеет смысл делать некие действия, которые не хочется делать каждый раз при создании новых баз данных. Например, create extension или create schema
  • Лучше не создавать объектов, так как для других
  • пользователей это будет неочевидно
  • Лучше сделать свой шаблон для создания других БД (template2, template3 и т.д.)

Создание базы данных

CREATE DATABASE

В TEMPLATE можно выбрать шаблон из существующих. С помощью IS_TEMPLATE можно сделать свою базу данных шаблоном. Есть возможность запретить подключения ALLOW_CONNECTIONS (используется в шаблонах).

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

CREATE DATABASE aristov_tech - создание БД 

Scheme

Схема – контейнер второго уровня. По умолчанию используется схема PUBLIC.

Схема – логическое разделение БД на сегменты.

В разных схемах можно создавать объекты с одинаковыми именами.

Создание схемы

CREATE SCHEMA

CREATE SCHEMA eugene;

Если схема не указано явно, то объект ищется во всех схемах.

Для отображения таблиц схемы:

\dt eugene.* (указано имя схемы - eugene)

Table

CREATE TABLE

Создание объекта внутри конкретной схемы:

CREATE TABLE eugene.test (i int);
\d+ eugene.test

Пример создания таблицы warehouse:

DROP TABLE IF EXISTS warehouse CASCADE;
CREATE TABLE warehouse (
id serial UNIQUE,
name text NOT NULL DEFAULT '',
kolvo int NOT NULL DEFAULT 0,
price decimal NOT NULL DEFAULT 0.0
);

Сначала удаляем таблицу, но только если она существует. С помощью CASCADE удаляются также все зависящие таблицы. Рекомендуется использовать очень аккуратно, так как удаляться все зависящие таблицы, что может повлечь потерю значительного количества связанных данных. Также нельзя удалять и заново создавать объекты при необходимости отредактировать что-либо (например, изменить тип поля), так как данные при этом не сохранятся. Нужно использовать команду ALTER.

  • id – последовательность, уникальное поле
  • name – текстовое, не может быть NULL, по умолчанию пустое
  • kolvo – целое, не может быть NULL, по умолчанию 0
  • price – вещественное, не может быть NULL, по умолчанию 0.0

Default применяется, когда пользователь не задал значение при добавлении записи

Рекомендуется все поля делать NOT NULL, так как с NULL полем сложнее работать (NULL != NULL). Для работы с NULL используется is NULL. Пустая строка – ” – это отсутствие символа, а NULL – несуществующее значение. При этом ”=” (пустая строка равна пустой строке).

Создание таблицы sales:

DROP TABLE IF EXISTS sales;
CREATE TABLE sales(
id serial PRIMARY KEY,
kolvo int NOT NULL,
summa numeric NOT NULL DEFAULT 0.0,
fk_warehouse int references warehouse(id) ON DELETE CASCADE,
salesDate date default current_date
);
  • id – последовательность, уникальное поле
  • kolvo – целое, не может быть NULL
  • summa – вещественное, не может быть NULL, по умолчанию 0.0
  • fk_warehouse – внешний ключ на таблицу warehouse
  • salesDate – дата, по умолчанию текущая дата

Вставим записи в разные таблицы и просмотрим результат:

Проверим, что будет, если попробовать вставить запись с повторным id:

INSERT INTO warehouse(id, name) VALUES (1, 'soap');

Сопоставим продажи с товарами:

Обратите внимание, что при создании таблиц не была указана схема, поэтому они были созданы в PUBLIC. Рекомендовано создавать своих схемы данных, использовать public считается неправильным.

Relation

Виды RELATION в БД

r = ordinary table
Кроме простых таблиц, также существуют и другие отношения:
i = index,
S = sequence,
v = view,
m = materialized view,
c = composite type,
t = TOAST table,
f = foreign table

Каждому Relation соответствует отдельный файл на диске.

Просмотр конфигурационных файлов (подробнее в статье):

show hba_file;
show config_file;
show data_directory;

Создадим объект и просмотрим его расположение:

CREATE TABLE test5(i int);
SELECT pg_relation_filepath('test5');

Посмотрим пример создания своего табличного пространства.

Создадим своё табличное пространство в домашнем каталоге пользователя postgres каталоге:

sudo su postgres
cd ~
mkdir temp_tblspce
CREATE TABLESPACE ts location '/var/lib/postgresql/temp_tblspce';

Напоминаю, что табличное пространство можно смонтировать на любо диск, распараллелив таким образом работу.

Перемещение (физическое) объекта в другое ТП:

CREATE TABLE test2 (i int) TABLESPACE pg_default;

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

Просмотр физического размера базы:

SELECT pg_database_size('app');

Для упрощения восприятия можно вывести число в отформатированном виде:

SELECT pg_size_pretty(pg_database_size('app'));

Просмотр размера индексов:

SELECT pg_size_pretty(pg_indexes_size('test2'));

Просмотр default табличного пространства:

SELECT count(*) FROM pg_class WHERE reltablespace = 0;

Просмотр схем по умолчанию (в данной переменной указана очередность поиска объектов):

SHOW search_path;

Рекомендуется всегда указывать схему, в которой работаете, чтобы не было разночтений

Constraint или ограничения

Рекомендуется всегда использовать NOT NULL

UNIQUE внутри себя содержит уникальный индекс

PRIMARY KEY требует внимательной работы с каскадами

CHECK – проверки. Например, цена больше нуля. Рекомендуется отдавать на бэкенд.

Подробнее об ограничениях здесь.

Именование объектов

Необходимо давать интуитивно понятные имена объектам

PostgreSQL автоматически текст приводит к нижнему регистру.

Два популярных варианта именования:

  • camelCase -> camelcase
  • snake_case -> snake_case

Можно, но крайне не рекомендуется использовать кавычки (сохраняется регистр, можно использовать русский и пробелы) – тогда имя будет сохранено как в оригинале: “camelCase” -> “camelCase” и всегда придётся использовать кавычки и то же написание.

DEFAULT vs IDENTITY

Классический SERIAL -> INT NOT NULL DEFAULT(nextval(‘test_i_seq’))

CREATE TABLE color (
    color_id SERIAL,
    color_name VARCHAR NOT NULL
);

Начиная с 10 версии рекомендуется использовать IDENTITY

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

Отличие заключается в том, что IDENTITY можно использовать для единственной таблицы, а SERIAL – как сквозную нумерацию в разных таблицах.

Оба способа основаны на SEQUENCE.

Презентация и исходники к статье доступны на github.

12 из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.

Комментарии

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

18 + четыре =