введение в СУБД   к корпоративным СУБД

Система управления базами данных PostgreSQL

История развития MySQL и PostgreSQL

История MySQL начинается в 1979 г., у ее истоков стояла небольшая компания во главе с Monty Widenius. В 1996 г. появился первый релиз 3.11 под солярис с публичной лицензией. Затем MySQL была портирована под другие операционные системы, появилась специальная коммерческая лицензия. В 2000 г., после добавления интерфейса, аналогичного Berkeley DB, база стала транзакционной. Примерно тогда же была добавлена репликация. В 2001 г. в версии 4.0 был добавлен движок InnoDB к уже имеющемуся MyISAM, в результате чего появилось кеширование и возросла производительность. В 2004 г. вышла версия 4.1, в которой появились подзапросы, парциальная индексация для MyISAM, юникод. В версии 5.0 в 2005 г. появились хранимые процедуры, курсоры, триггеры, представления (views). В MySQL развиваются коммерческие тенденции: в 2009 г. MySQL стала торговой маркой компании Oracle.

История постгрес началась в 1977 г. с базы данных Ingress.

В 1986 г. в университете Беркли, Калифорния, она была переименована в PostgreSQL.

В 1995 г. постгрес стала открытой базой данных. Появился интерактивный psql.

В 1996 г. Postgres95 была переименована в PostgreSQL версии 6.0.

У постгреса несколько сотен разработчиков по всему миру.

Архитектура MySQL и PostgreSQL

PostgreSQL – унифицированный сервер баз данных, имеющий единый движок – storage engine. Постгрес использует клиент-серверную модель.

Для каждого клиента на сервере создается новый процесс (не поток !). Для работы с такими клиентскими процессами сервер использует семафоры.

Клиентский запрос проходит следующие стадии.

  1. Коннект.
  2. Парсинг: проверяется корректность запроса и создается дерево запроса (query tree). В основу парсера положены базовые юниксовые утилиты yacc и lex.
  3. Rewrite: берется дерево запросов и проверяется наличие в нем правил (rules), которые лежат в системных каталогах. Всякий раз пользовательский запрос переписывается на запрос, получающий доступ к таблицам базы данных.
  4. Оптимизатор: на каждый запрос создается план запроса – query plan, который передается исполнителю – executor. Смысл плана в том, что в нем перебираются все возможные варианты получения результата (использовать ли индексы, джойны и т.д.), и выбирается самый быстрый вариант.
  5. Выполнение запроса: исполнитель рекурсивно проходит по дереву и получает результат, используя при этом сортировку, джойны и т.д., и возвращает строки. Постгрес – обьектно-реляционная база данных, каждая таблица в ней представляет класс, между таблицами реализовано наследование. Реализованы стандарты SQL92 и SQL99.

Транзакционная модель построена на основе так называемого multi-version concurrency control (MVCC), что дает максимальную производительность. Ссылочная целостность обеспечена наличием первичных и вторичных ключей.

MySQL имеет два слоя – внешний слой sql и внутренний набор движков, из которых наиболее часто используется движок InnoDb, как наиболее полно поддерживающий ACID.

Реализован стандарт SQL92.

С модульной точки зрения код MySQL можно разделить на следующие модули.

  1. Инициализация сервера.
  2. Менеджер коннектов.
  3. Менеджер потоков.
  4. Обработчик команд.
  5. Аутентификация.
  6. Парсер.
  7. Кеш.
  8. Оптимизатор.
  9. Табличный менеджер.
  10. Движки (MyISAM, InnoDB, MEMORY, Berkeley DB).
  11. Логирование.
  12. Репликация.
  13. Сетевое API.
  14. API ядра.

Порядок работы модулей следующий: сначала загружается первый модуль, который читает опции командной строки, конфиг-файлы, выделяет память, инициализирует глобальные структуры, загружает системные таблицы и передает управление менеджеру коннектов.

Когда клиент подсоединяется к базе, управление передается менеджеру потоков, который создает поток (не процесс!) для клиента, и проверяется его аутентификация.

Клиентские запросы в зависимости от их типа на верхнем уровне обрабатываются четвертым модулем (dispatcher). Запросы будут залогированы 11-м модулем. Команда передается парсеру, проверяется кеш. Далее запрос может попасть в оптимизатор, табличный модуль, модуль репликации, и т.д. В результате данные возвращаются клиенту через менеджер потоков.

Организация СУБД PostgreSQL

Как и любая СУБД PostgreSQL должна обслуживать запросы sql и возвращать результаты клиентским приложениям. Для этого механизм СУБД оптимизатор запросов принимает решение об оптимальном использовании ресурсов и строит план запроса. При этом он может опереться на используемых механизмы ускорения работы (индексов, кэшей в памяти, ресурсам процессора, данные статистики и т.д.).
Результаты запросов возвращаются клиентским подключениям

В Windows управляет всем файл службы pg_ctl.exe (останавливает, запускает, перезапускает).

Сам движок базы – это postgres.exe.

Оптимизатор запросов мало подвержен управлению со стороны разработчиков, поэтому к нему вернемся лишь косвенно в разделе анализа быстродействия.
Зато управление дисковым пространством имеет весомое значение. Как и в любой СУБД, для баз данных PostgreSQL лучше использовать RAID 10 для баз данных и отдельный дисковый массив под логии. Применение STORAGE систем может также положительно сказаться на всем быстродействии. Еще одна фича, ускоряющая работу за счет расположения данных на разных дисках, это табличные пространства (tablespaces).

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

У PostgreSQL есть особенность в реализации хранения – это так называемый “кластер”. В данном случаи речь идет месте расположения каталога для баз данных. Плюс PostgreSQL  организует  единую  файловую структуру, в которой  отдельные файлы не соответствуют непосредственно таблицам или другим  объектам  базы  данных.

При установке вы указываете “кластер” (читай каталог).

При установке PostgreSQL создает системную базу postgres и базу template1 как шаблон настроек для всех новых баз. Обычно в Linux-среде  в каталоге /var/postgres/data находится некоторое количество служебных файлов для PostgreSQL, а в каталоге /var/postgres/data/base размещаются базы данных, каждая в своем отдельном каталоге.

К сожалению, реализованный в PostgreSQL способ хранения данных требует  дополнительного обслуживания  -  периодической  дефрагментации  базы  данных  командой VACUUM.Примечание. Материализованные запросы (представления пользователя) view платформой не используются.

Размеры базы данных

На данный момент (версия 9.1.2), в PostgreSQL имеются следующие ограничения:

Максимальный размер базы данных

Нет ограничений

Максимальный размер таблицы

32 ТБайт

Максимальный размер записи

1,6 ТБайт

Максимальный размер поля

1 ГБайт

Максимум записей в таблице

Ограничено размером таблицы

Максимум полей в таблице

250—1600, в зависимости от типов полей

Максимум индексов в таблице

Нет ограничений

Сильными сторонами PostgreSQL считаются:

Утилита администрирования баз данных PostgreSQL

pgAdmin - графическая оболочка проектирования и административная СУБД PostgreSQL  для *nix и системы Windows.

pgAdmin пишется на языке программирования  C и использует превосходный пакет разработчика платформы wxWidgets (когда-то wxWindows).

pgAdmin распространяется по лицензии PostgreSQL, т.е. также является свободным ПО.

PosgreSQL в 1С 8.0

1С:Предприятие 8 имеет некоторые особенности работы с СУБД PostgreSQL, связанные с использованием транзакционных блокировок:

режиме автоматического управления блокировками в транзакции используются табличные блокировки СУБД;

в режиме управляемых блокировок в транзакции используются блокировки записей и полей СУБД.

Управление соединениями с других компьютеров

По умолчанию, PostgreSQL разрешает только соединения на локальной машине через сокеты домена Unix или TCP/IP соединения. Для того, чтобы другие машины смогли подключиться к базе вы должны изменить в postgresql.conf, разрешить host-авторизация в файле $PGDATA/pg_hba.conf и перестартовать сервер.

Сравнительный Анализ PostgreSQL с MySQL

Заинтересовал меня СУБД PostgreSQL тремя моментами:

Ниже приводится таблица сравнения основных возможностей этих СУБД. Критерии сравнения выбирались сугубо из моих интересов/опыта и обзоров в инете.

MySQL

PostgreSQL

Ответственный за код

компания MySQL AB

разные разработчики

Сжатие данных при передаче

+

+

Поддержка модели ACID

+/- (InnoDB, Falcon)

+

Поддержка SQL команд: insert ignore / replace

+

-

Поддержка внешних ключей

+/- (InnoDB)

+

Репликации

+

+ (говорят в 8.4 стало все хорошо)

Под запросы

+

+

Полнотекстовые индексы

+ (MyISAM)

+

Частичное индексирование

-

+

Чистка после работы команд UPDATE и DELETE

не нуждается

VACUUM

Система привилегий

+

+/- (проще чем в MySQL)

Хранение таблиц в файлах

+

-

Хранение/обработка географических данных

+/-

+

Лицензирование

GNU GPL

BSD

MySQL PostgreSQL: сходство и различия

ACID-стандарт

Стандарт ACID базируется на атомарности, целостности, изоляции и надежности. Эта модель используется для гарантии целостности данных. Реализуется это на основе транзакций. PostgreSQL полностью соответствует стандарту ACID. Для полной поддержки ACID в MySQL в конфиге нужно установить default-storage-engine=innodb.

Производительность (performance)

Базы данных часто оптимизируются в зависимости от окружения, в котором они работают. Обе базы имеют различные технологии для улучшения производительности. Исторически так сложилось, что MySQL начинала разрабатываться с прицелом на скорость, а PostgreSQL с самого начала разрабатывалась как база с большим числом настроек и соответствием стандарту. PostgreSQL имеет ряд настроек, которые повышают скорость доступа:

MySQL имеет частичную поддержку парциальных индексов в InnoDB. Если взять MySQL-ский движок ISAM, он оказывается быстрее на плоских запросах, при этом нет блокировок на инсерты, нет поддержки транзакций, foreign key.

Компрессия

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

MySQL-компрессия для разных движков частично поддерживается, частично нет, и это зависит от конкретной версии конкретного движка.

SMP

На мульти-процессорности PostgreSQL имеет преимущество над MySQL. Даже сами разработчики MySQL признают, что их движок в этом плане не так хорош.

Типы данных

MySQL: для хранения бинарных данных использует типы TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, которые отличаются размером (до 4 ГБ).

Character: четыре типа – TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

PostgreSQL: поддерживает механизм пользовательских данных с помощью команды CREATE TYPE, тип BOOLEAN, геометрические типы.

Character: TEXT (ограничение – max row size).

Для хранения бинарных данных есть тип BLOB, который хранится в файловой системе. Столбцы таблицы могут быть определены как многомерный массив переменной длины. Обьектно-реляционное расширение: структура таблицы может быть унаследована от другой таблицы.

Хранимые процедуры

И PostgreSQL , и MySQL поддерживают хранимые процедуры. PostgreSQL придерживается стандарта Oracle PL/SQL, MySQL – IBM DB2. MySQL поддерживает extend SQL для написания функций на языке C/C++ с версии 5.1. PostgreSQL: PL/PGSQL, PL/TCL, PL/Perl, SQL, C для написания хранимых процедур.

Ключи

И PostgreSQL , и MySQL поддерживают уникальность Primary Key и Foreign Key. MySQL не поддерживает check constraint плюс вторичные ключи реализованы частично. PostgreSQL: полная реализация плюс поддержка ON DELETE CASCADE и ON UPDATE CASCADE.

Триггеры

MySQL: рудиментарная поддержка. PostgreSQL: декларативные триггеры: SELECT, INSERT, DELETE, UPDATE, INSTEAD OF; процедурные триггеры: CONSTRAINT TRIGGER. События: BEFORE или AFTER на INSERT, DELETE , UPDATE.

Автоинкремент

MySQL: в таблице может быть только один такой столбец, который должен быть проиндексирован. PostgreSQL: SERIAL data type.

Репликации

Поддерживаются и в MySQL, и в PostgreSQL. PostgreSQL имеет модульную архитектуру, и репликация входит в отдельные модули:

Репликация в PostgreSQL основана на триггерах и более медленная, чем в MySQL. В ядро репликацию планируется добавить, начиная с версии 8.4.

В MySQL репликация входит в ядро и имеет две разновидности, начиная с версии 5.1:

Первый тип основан на логировании записей в бинарный лог, второй – на логировании изменений. Начиная с версии 5.5, в MySQL поддерживается так называемая полусинхронная репликация, при которой основной сервер (master) делает сброс данных на другой сервер (slave) при каждом коммите. Движок NDB делает полную синхронную двухфазную репликацию.

Транзакции

MySQL: только для для InnoDB. Поддержка SAVEPOINT, ROLLBACK TO SAVEPOINT. Уровни блокировки: table level (MyISAM). PostgreSQL: поддерживается плюс read committed и уровни изоляции. Поддержка ROLLBACK, ROLLBACK TO SAVEPOINT. Уровни блокировки: row level, table level.

Уровни привилегий

PostgreSQL: для пользователя или группы пользователей могут быть назначены привилегии.

Экспорт-импорт данных

MySQL: набор утилит для экспорта: mysqldump, mysqlhotcopy, mysqlsnapshot. Импорт из текстовых файлов, html, dbf. PostgreSQL: экспорт – утилита pg_dump. Импорт между базами данных и файловой системой.

Вложенные запросы

Есть и в MySQL, и в PostgreSQL, но в MySQL могут работать непроизводительно.

Индексация

Хэширование индексов: в MySQL– частичное, в PostgreSQL – полное. Полнотекстовый поиск: в MySQL– частичный, в PostgreSQL – полный. Парциальные индексы: в MySQL не поддерживаются, в PostgreSQL поддерживаются. Многостолбцовые индексы: в MySQL ограничение 16 столбцов, в PostgreSQL – 32. Expression-индексы: в MySQL– эмуляция, в PostgreSQL – полное. Неблокирующий create index: в MySQL – частичное, в PostgreSQL – полное.

Партиционирование (Partitioning)

MySQL поддерживает горизонтальное партиционирование: range, list, hash, key, композитное партиционирование. PostgreSQL поддерживает RANGE и LIST. Автоматическое партиционирование для таблиц и индексов.

Автоматическое восстановление после сбоев

MySQL: частичное для InnoDB – нужно вручную сделать backup. PostgreSQL: Write Ahead Logging (WAL).

Data Storage Engines

PostgreSQL поддерживает один движок – Postgres Storage System. В MySQL 5.1 их несколько:

InnoDB разрабатывается компанией InnoBase, являющейся дочерней компанией Oracle. В 6-й версии должны появиться два движка – Maria и Falcon. Falcon – движок, основанный на ACID-транзакциях.

Лицензирование

PostgreSQL: BSD (Berkeley Software Distribution) open source. MySQL: GPL (Gnu General Public License) или Commercial. MySQL – это open-source продукт. Postgres – это open-source проект.

Заключение

Подводя итоги, можно сказать следующее: MySQL и PostgreSQL – две наиболее популярные open-source базы данных в мире. Каждая база имеет свои особенности и отличия. Если вам нужно быстрое хранилище для простых запросов с минимальной настройкой, я бы порекомендовал MySQL. Если вам нужно надежное хранилище для большого объема данных с возможностью расширения, репликации, полностью соответствующее современным стандартам языка SQL, я бы предложил использовать PostgreSQL.

Источники

1. http://vlads.tom.ru/index.php?option=com_content&view=article&id=103:-pgadmin-iii-windows&catid=26:-postgresql&Itemid=47

2. http://v8.1c.ru/overview/postgresql.htm

3. http://proft.me/2010/05/4/sravnenie-postgresql-vs-mysql/

4. http://tweakers.net/reviews/649/1/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron-introduction-problems-of-modern-cores.html

5. http://www.ibm.com/developerworks/ru/library/os-mysql-postgresql/01/index.html?ca=drs-

6. http://www.postgresql.org/docs/8.1/interactive/supported-platforms.html

7. http://gis-lab.info/qa/postgis-install.html

7. http://postgresql.softsalad.ru/

8. http://www.kodges.ru/69979-postgresql-8-for-windows.html

введение в СУБД   к корпоративным СУБД

(время поиска примерно 20 секунд)

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

НОВОСТИ ФОРУМАФорум Рыцари теории эфира
Рыцари теории эфира
 13.06.2019 - 05:11: ЭКОЛОГИЯ - Ecology -> ПРОБЛЕМА ГЛОБАЛЬНОЙ ГИБЕЛИ ПЧЁЛ И ДРУГИХ ОПЫЛИТЕЛЕЙ РАСТЕНИЙ - Карим_Хайдаров.
12.06.2019 - 09:05: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> Проблема государственного терроризма - Карим_Хайдаров.
11.06.2019 - 18:05: ЭКСПЕРИМЕНТАЛЬНАЯ ФИЗИКА - Experimental Physics -> Эксперименты Сёрла и его последователей с магнитами - Карим_Хайдаров.
11.06.2019 - 18:03: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Андрея Маклакова - Карим_Хайдаров.
11.06.2019 - 13:23: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вячеслава Осиевского - Карим_Хайдаров.
11.06.2019 - 13:18: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Светланы Вислобоковой - Карим_Хайдаров.
11.06.2019 - 06:28: АСТРОФИЗИКА - Astrophysics -> К 110 летию Тунгуской катастрофы - Карим_Хайдаров.
10.06.2019 - 21:23: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Владимира Васильевича Квачкова - Карим_Хайдаров.
10.06.2019 - 19:27: СОВЕСТЬ - Conscience -> Высший разум - Карим_Хайдаров.
10.06.2019 - 19:24: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> ЗА НАМИ БЛЮДЯТ - Карим_Хайдаров.
10.06.2019 - 19:14: СОВЕСТЬ - Conscience -> РУССКИЙ МИР - Карим_Хайдаров.
10.06.2019 - 08:40: ЭКОНОМИКА И ФИНАНСЫ - Economy and Finances -> КОЛЛАПС МИРОВОЙ ФИНАНСОВОЙ СИСТЕМЫ - Карим_Хайдаров.
Bourabai Research Institution home page

Боровское исследовательское учреждение - Bourabai Research Bourabai Research Institution