Возможно, вы спрашиваете себя: «Почему PostgreSQL?» Ведь есть и другие варианты реляционных баз данных с открытым исходным кодом (в рамках этой статьи мы рассматривали MySQL, MariaDB и Firebird), так что же Постгрес может предложить такого, чего нет у них? В слогане PostgreSQL заявляется, что это «Самая продвинутая база данных с открытым исходным кодом в мире». Мы приведем несколько причин, почему Постгрес делает такие заявления.
В первой части этой серии мы поговорим о хранении данных — модели, структуре, типах и ограничениях размера. А во второй части больше сфокусируемся на выборке и манипуляциях с данными.
Модель данных
PostgreSQL не просто реляционная, а объектно-реляционная СУБД. Это даёт ему некоторые преимущества над другими SQL базами данных с открытым исходным кодом, такими как MySQL, MariaDB и Firebird.
Фундаментальная характеристика объектно-реляционной базы данных — это поддержка пользовательских объектов и их поведения, включая типы данных, функции, операции, домены и индексы. Это делает Постгрес невероятно гибким и надежным. Среди прочего, он умеет создавать, хранить и извлекать сложные структуры данных. В некоторых примерах ниже вы увидите вложенные и составные конструкции, которые не поддерживаются стандартными РСУБД.
Структуры и типы данных
Существует обширный список типов данных, которые поддерживает Постгрес. Кроме числовых, с плавающей точкой, текстовых, булевых и других ожидаемых типов данных (а также множества их вариаций), PostgreSQL может похвастаться поддержкой uuid, денежного, перечисляемого, геометрического, бинарного типов, сетевых адресов, битовых строк, текстового поиска, xml, json, массивов, композитных типов и диапазонов, а также некоторых внутренних типов для идентификации объектов и местоположения логов. Справедливости ради стоит сказать, что MySQL, MariaDB и Firebird тоже имеют некоторые из этих типов данных, но только Постгрес поддерживает их все.
Давайте рассмотрим подробнее некоторые из них:
Сетевые адреса
PostgreSQL обеспечивает хранение разных типов сетевых адресов. Тип данных CIDR (бесклассовая маршрутизация интернет домена, Classless Internet Domain Routing) следует соглашению для сетевых адресов IPv4 и IPv6. Вот несколько примеров:
- 192.168.100.128/25
- 10.1.2.3/32
- 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
- ::ffff:1.2.3.0/128
Также для хранения сетевых адресов доступен тип данных INET, используемый для IPv4 и IPv6 хостов, где подсети являются необязательными. Тип данных MACADDR может использоваться для хранения MAC-адресов для идентификации оборудования, таких как 08-00-2b-01-02-03.
У MySQL и MariaDB тоже есть INET функции для конвертации сетевых адресов, но они не предоставляют типы данных для внутреннего хранения сетевых адресов. У Firebird тоже нет типов для хранения сетевых адресов.
Многомерные массивы
Поскольку Постгрес — это объектно-реляционная база данных, массивы значений могут храниться для большинства существующих типов данных. Сделать это можно путём добавления квадратных скобок к спецификации типа данных для столбца или с помощью выражения ARRAY. Размер массива может быть задан, но это необязательно. Давайте рассмотрим меню праздничного пикника для демонстрации использования массивов:
-- создаем таблицу, у которой значения являются массивами
CREATE TABLE holiday_picnic (
holiday varchar(50) -- строковое значение
sandwich text[], -- массив
side text[] [], -- многомерный массив
dessert text ARRAY, -- массив
beverage text ARRAY[4] -- массив из 4-х элементов
);
-- вставляем значения массивов в таблицу
INSERT INTO holiday_picnic VALUES
('Labor Day',
'{"roast beef","veggie","turkey"}',
'{
{"potato salad","green salad","macaroni salad"},
{"chips","crackers"}
}',
'{"fruit cocktail","berry pie","ice cream"}',
'{"soda","juice","beer","water"}'
);
MySQL, MariaDB, и Firebird так не умеют. Чтобы хранить такие массивы значений в традиционных реляционных базах данных, придется использовать обходной путь и создавать отдельную таблицу со строками для каждого из значений массива.
Геометрические данные
Геоданные быстро становятся основным требованием для многих приложений. PostgreSQL уже давно поддерживает множество геометрических типов данных, таких как точки, линии, круги и многоугольники. Один из этих типов – PATH, он состоит из множества последовательно расположенных точек и может быть открытым (начальная и конечная точки не связаны) или закрытым (начальная и конечная точки связаны). Давайте рассмотрим в качестве примера туристическую тропу. В данном случае туристическая тропа — это петля, поэтому начальная и конечная точки связаны, и, значит, мой путь является закрытым. Круглые скобки вокруг набора координат указывают на закрытый путь, а квадратные — на открытый.
-- создаем таблицу для хранения троп
CREATE TABLE trails (
trail_name varchar(250),
trail_path path
);
-- вставляем тропу в таблицу,
-- для которой маршрут определяется координатами в формате широта-долгота
INSERT INTO trails VALUES
('Dool Trail - Creeping Forest Trail Loop',
((37.172,-122.22261666667),
(37.171616666667,-122.22385),
(37.1735,-122.2236),
(37.175416666667,-122.223),
(37.1758,-122.22378333333),
(37.179466666667,-122.22866666667),
(37.18395,-122.22675),
(37.180783333333,-122.22466666667),
(37.176116666667,-122.2222),
(37.1753,-122.22293333333),
(37.173116666667,-122.22281666667)));
Расширение PostGIS для PostgreSQL дополняет существующие свойства геометрических данных вспомогательными пространственными типами, функциями, операторами и индексами. Оно обеспечивает поддержку местоположения и поддерживает как растровые, так и векторные данные. Оно также обеспечивает совместимость с множеством сторонних геопространственных инструментов (защищённых авторским правом и с открытым исходным кодом) для отображения, отрисовки и работы с данными.
Заметьте, что в MySQL 5.7.8 и в MariaDB, начиная с версии 5.3.3, были добавлены расширения типов данных для поддержки стандарта географической информации OpenGIS. Эта версия MySQL и последующие версии MariaDB предлагают хранение типов данных, аналогичное штатным геоданным Постгреса. Тем не менее, в MySQL и MariaDB значения данных сначала должны быть сконвертированы в геометрический формат простыми командами перед тем, как будут вставлены в таблицу. Firebird на данный момент не поддерживает геометрические типы данных.
Поддержка JSON
Поддержка JSON в PostgreSQL позволяет вам перейти к хранению schema-less данных в SQL базе данных. Это может быть полезно, когда структура данных требует определённой гибкости: например, если в процессе разработки структура всё ещё меняется или неизвестно, какие поля будет содержать объект данных.
Тип данных JSON обеспечивает проверку корректности JSON, который позволяет использовать специализированные JSON операторы и функции, встроенные в Постгрес для выполнения запросов и манипулирования данными. Также доступен тип JSONB — двоичная разновидность формата JSON, у которой пробелы удаляются, сортировка объектов не сохраняется, вместо этого они хранятся наиболее оптимальным образом, и сохраняется только последнее значение для ключей-дубликатов. JSONB обычно является предпочтительным форматом, поскольку требует меньше места для объектов, может быть проиндексирован и обрабатывается быстрее, так как не требует повторного синтаксического анализа.
В MySQL 5.7.8 и MariaDB 10.0.1 была добавлена поддержка встроенных объектов JSON. Но, хотя существует множество функций и операторов для JSON, которые теперь доступны в этих базах данных, они не индексируются так, как JSONB в PostgreSQL. Firebird пока что не присоединился к тренду и поддерживает объекты JSON только в виде текста.
Создание нового типа
Если вдруг так случится, что обширного списка типов данных Постгреса вам окажется недостаточно, вы можете использовать команду CREATE TYPE, чтобы создать новые типы данных, такие как составной, перечисляемый, диапазон и базовый. Рассмотрим пример создания и отправки запросов нового составного типа:
-- создаем новый составной тип "wine"
CREATE TYPE wine AS (
wine_vineyard varchar(50),
wine_type varchar(50),
wine_year int
);
-- создаем таблицу, которая использует составной тип "wine"
CREATE TABLE pairings (
menu_entree varchar(50),
wine_pairing wine
);
-- вставляем данные в таблицу при помощи выражения ROW
INSERT INTO pairings VALUES
('Lobster Tail',ROW('Stag''s Leap','Chardonnay', 2012)),
('Elk Medallions',ROW('Rombauer','Cabernet Sauvignon',2012));
/*
выборка из таблицы с использованием имени колонки
(используйте скобки, отделяемые точкой от имени поля
в составном типе)
*/
SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type
FROM pairings
WHERE menu_entree = 'Elk Medallions';
Поскольку они не являются объектно-реляционными, MySQL, MariaDB и Firebird не предоставляют такую мощную функциональность.
Размеры данных
PostgreSQL может обрабатывать много данных. Текущие опубликованные ограничения перечислены ниже:
| Максимальный размер базы данных | Неограничен |
| Максимальный размер таблицы | 32 TB |
| Максимальный размер строки | 1.6 TB |
| Максимальный размер поля | 1 GB |
| Максимальное количество строк в таблице | Неограничено |
| Максимальное количество столбцов в таблице | 250-1600 в зависимости от типа столбца |
| Максимальное количество индексов в таблице | Неограничено |
В Compose [прим. пер.: организация, в которой трудится автор оригинальной статьи] мы автоматически масштабируем вашу инсталляцию, чтобы вам не приходилось волноваться о росте количества данных. Но, как известно любому администратору баз данных, стоит с опаской относиться к слишком большим и неограниченным возможностям. Мы советуем руководствоваться здравым смыслом при создании таблиц и добавлении индексов.
Для сравнения, MySQL и MariaDB печально известны ограничением размера строк в 65 535 байт. Firebird также предлагает всего лишь 64Кб в качестве максимального размера строки. Обычно объём данных ограничивается максимальным размером файлов операционной системы. Поскольку PostgreSQL умеет хранить табличные данные в множестве файлов меньшего размера, он может обойти это ограничение. Но стоит отметить, что слишком большое количество файлов может негативно сказаться на производительности. MySQL и MariaDB поддерживают большее количество столбцов в таблице (до 4,096 в зависимости от типа данных) и большие индивидуальные размеры таблицы, чем PostgreSQL, но необходимость превысить существующие ограничения Постгреса возникает лишь в крайне редких случаях.
Целостность данных
Постгрес стремится соответствовать стандарту ANSI-SQL:2008, отвечает требованиям ACID (атомарность, согласованность, изолированность и надежность) и известен своей ссылочной и транзакционной целостностью. Первичные ключи, ограничивающие и каскадные внешние ключи, уникальные ограничения, ограничения NOT NULL, проверочные ограничения и другие функции обеспечения целостности данных дают уверенность, что только корректные данные будут сохранены.
MySQL и MariaDB больше работают на то, чтобы соответствовать стандарту SQL с движками таблиц InnoDB/XtraDB. Теперь они предлагают опцию STRICT с использованием режимов SQL, которая устанавливает проверки корректности используемых данных. Несмотря на это, в зависимости от того, какой режим вы используете, недостоверные и даже урезанные без вашего ведома данные могут быть вставлены или созданы при обновлении. Ни одна из этих баз данных сейчас не поддерживает CHECK ограничения. Кроме того, у них существует множество особенностей в отношении ограничений ссылочной целостности по внешним ключам. В дополнение к вышесказанному, целостность данных может существенно пострадать в зависимости от выбранного движка хранения. MySQL (и fork MariaDB) не делают секрета из того, что променяли целостность и соответствие стандартам на скорость и эффективность.
Подводя итоги
У Постгреса множество возможностей. Созданный с использованием объектно-реляционной модели, он поддерживает сложные структуры и широкий спектр встроенных и определяемых пользователем типов данных. Он обеспечивает расширенную ёмкость данных и заслужил доверие бережным отношением к целостности данных. Возможно, вам не понадобятся все те продвинутые функции хранения данных, которые мы исследовали в этой статье, но, поскольку потребности могут быстро возрасти, есть несомненное преимущество в том, чтобы иметь всё это под рукой.
Если вам кажется, что PostgreSQL не соответствует вашим потребностям, или вы предпочитаете “стрелять от бедра”, тогда вам стоит обратить внимание на NoSQL базы данных, которые мы предлагаем в Compose, или подумать о других SQL базах данных, которые мы упоминали. У каждой из них есть свои преимущества. Compose твёрдо уверен, что очень важно выбрать правильную базу данных для конкретной задачи… иногда это означает, что нужно выбрать несколько баз данных!
Хотите больше Постгреса? Во второй части этой серии мы рассмотрим манипуляции с данными и поиск в PostgreSQL, включая функции виртуальных таблиц, возможности запросов, индексирование и расширения языка.
Комментарии (82)
alien007
29.04.2016 15:59Ни одна из этих баз данных сейчас не поддерживает CHECK ограничения
То есть как это? Check constraint даже в Sqlite есть, во встраиваемой БД.
miwa
29.04.2016 17:15+2Ни одна из этих баз данных сейчас не поддерживает CHECK ограничения
> То есть как это?
Вот-вот. Из-за таких вот такие формулировок статья выглядит мягко говоря маркетинговой чухней. Исходя из этого категорического утверждения, Firebird тоже не поддерживает CHECK-и, а пробегаясь дальше по тексту — так же не поддерживает ACID. Да, прямо об этом не написано, но формилируется именно так.
Или еще вот пассаж: то что у PostgreSQL поддерживаются большые строки — это большой плюс и вообще «фи» остальным, которые «только 64 кб». Стыдливо замалчивая при этом TEXT для mysql и BLOB SUB_TYPE 1 для firebird. Но зато рядом
как известно любому администратору баз данных, стоит с опаской относиться к слишком большим и неограниченным возможностям. Мы советуем руководствоваться здравым смыслом при создании таблиц и добавлении индексов
«Грустно все это»©
potapuff
29.04.2016 21:04+2Стыдливо замалчивая при этом TEXT для mysql
Сравните работу с TEXT у MySQL и Postgresql. У MySQL это настоящий сборник ограничений. Читаем документацию в PG:
The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values.
и
Tip: There is no performance difference among these three types [CHAR, VARCHAR, TEXT], apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column
miwa
29.04.2016 21:55В общем и целом я лямку за Firebird тяну если что :) Но упоминаю возможности всех серверов, так как пытаюсь быть более объективным, чем автор статьи. Честно признаюсь, что об ограничениях TEXT для MySQL не в курсе; для Firebird-овского BLOB SUB_TYPE 1 — в курсе.
При этом сравнить работу даже просто длинных строк — не так просто. Даже если выделить обсуждаемым СУБД одинаковые, допустим, виртуалки, как минимум встанут вопросы конфигурации серверов. Плюс отдельно — режимы работы: только вставка или вставка/обновление/удаление, количество последовальных чтений, количество одновременных чтений, количество одновременных чтений с одновременными же обновлениями/удалениями данных, то же самое — с проверкой достоверности (должен ли пользователь А видеть данные, которые пользователь Б начал удалять после того, как пользователь А начал их просматривать).
Возьметесь за такое сравнение? Я — нет, ибо недостаточно компетентен в MySQL/PostgreSQL для правильного написания теста под них.
hedgehog
29.04.2016 16:09+1По-моему с абзацем про размеры строк что-то не так.
Для сравнения, MySQL и MariaDB печально известны ограничением размера строк в 65,535 байт.
Честно, не могу понять о каком ограничении тут идёт речь.
Melkij
29.04.2016 16:23Речь про вот это ограничение: http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
Я знаю про эту особенность, так что для меня абзац выглядит корректно и понятно.hedgehog
29.04.2016 16:28То есть это проблема для тех, кому хранить строки в TEXT или BLOB религия не позволяет?
Melkij
29.04.2016 16:33+3Вроде того. Я не представляю ситуаций, когда строка данных без учёта типов blob и text не помещается в 64кб. Для этого надо делать что-то довольно странное.
dezconnect
29.04.2016 18:08Зачем странное, можно просто сериализовать набор объектов и сложить в базу, а потом оттуда прочитать и десериализовать ;)
Melkij
29.04.2016 19:36+2Ну так это органично blob будет, ну или text. На них лимит размера строки не распространяется. Надо пытаться именно в огромные varchar'ы писать или что-нибудь в этом духе.
Хотя вот про тип данных json в mysql честно не знаю. Может быть он тоже подвержен лимиту в 64кб, а может — его просто забыли на этой странице мануала упомянуть.potapuff
29.04.2016 21:11Почему 64Кб? В InnoDB размер строки не может превышать половину страницу -значит 32кб.
Melkij
29.04.2016 22:1964кб — общий лимит. Каждый storage engine может налагать свои более строгие ограничения.
Касательно именно innodb и его максимального размера строки — там вообще всё как-то очень сложно. Зависит от версии СУБД, ROW_FORMAT, innodb_page_size и примечаний в разных частях мануала. Но вы пропустили маленькую деталь:
The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row length for the default innodb_page_size of 16KB is about 8000 bytes. For an InnoDB page size of 64KB, the maximum row length is about 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.
http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
varchar в innodb не считается как максимальная длина строки.
jack128
29.04.2016 16:17+2Массивы в FB есть http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-bnrytypes.html#fblangref25-datatypes-array
lega
29.04.2016 16:20+1JSONB обычно является предпочтительным форматом, поскольку требует меньше места для объектов
Не подскажите сколько jsonb занимает в минимальном случае (пустое значение), т.е. сколько байт выделяется под jsonb в строке табличной части?
В mysql innodb для текста (пустое значение) это около 700-800 байт на строку таблицы.chersanya
29.04.2016 16:53Намного меньше, несколько байт пустой он занимает. И вообще оверхед небольшой.
lega
29.04.2016 17:00-1Есть какие-нибудь пруфы, ссылки на доку?
Ведь если в табличной части хранится минимум, то вся остальная часть (при не пустом) храниться в отдельном хранилище, а это обычно медленнее чем хранение в таблице напрямую.lega
29.04.2016 20:34+1Немного гугления и экспериментов показали что таблица с одной колонкой jsonb и значением "{}" (пустой словарь), занимают ~36 байт на строку.
ZOXEXIVO
29.04.2016 16:38+2Пусть сначала приведут в человеческое состояние весь свой кроссплатформенный UI в лице pgAdmin, репликацию, в потом уже участвуют в номинациях лучшая БД и.т. п
dovg
29.04.2016 16:45+2А что не так с репликацией?
Разве есть какие-то проблемы с pgAdmin? Да и вообще, зачем он нужен при живом psql?
Razaz
29.04.2016 17:02Я бы вот не отказался от шелла типа SSMS для PG… это было бы нереально круто. Понятно, что часть задачь решается через psql, но хочется иметь удобный интерфейс для колупания в бд, визуалиазции редактирования структуры.
dezconnect
29.04.2016 18:12+1А у других есть что-то приличное?
ZOXEXIVO
29.04.2016 19:09-3SQL Server Management Studio как пример. Есть проблемы, но продукт выглядит дорого.
Что мешает сделать свежий и приятный интерфейс в стиле Robomongo для того же PostgreSQL?
Отношение к продукту будет уже совсем другое у новичков. Сейчас же я ощущаю, что работаю с чем-то морально устаревшим.
К PostgreSQL претензий вообще нет никаких, но люди реально акцентируют внимание на специфичных функциональных вещах, не сделав человеческими вещи, от которых зависит тоже очень многое.
ZaEzzz
01.05.2016 17:42Вам ответит Джеф Раскин:
Представьте себе, что вы поднялись на борт сияющего шикарной отделкой авиалайнера, оснащенного просторными, комфортабельными кожаными креслами с целым набором встроенной аудио- и видеотехники; в буфете вас ожидают отличная еда и напитки. Вы садитесь в свое кресло и смотрите в большой, чисто вымытый иллюминатор. Со вздохом предвкушения особенно приятного полета вы протягиваете руку к шкафчику впереди вас, чтобы поглядеть, что там. Сначала вы достаете весьма объемистую бутылку любимого напитка, а затем буклет с описанием этого замечательного воздушного лайнера.
В то время как двери закрываются и идут приготовления к взлету, вы усаживаетесь поудобнее и начинаете читать. Из буклета вы узнаете, что интерьер самолета создан трудами самых лучших в мире дизайнеров, что повара из пятизвездочных отелей лично составляли меню и готовили блюда и что в группу разработчиков самолета не были включены инженеры-авиаконструкторы, поскольку всемирно признанные дизайнеры сделали внешний вид самолета таким, что и без того создается впечатление авиалайнера, способного летать во много раз быстрее, чем любой другой.
Еще в буклете мелким шрифтом сообщается, что путешествие на этом самолете нередко даже в хорошую погоду сопровождается болтанкой и что достаточно регулярно с ним случаются катастрофы. Если же перелет обойдется без этих инцидентов, то в целом, как обещают авторы, ваше путешествие будет комфортным и интересным.
Теперь звук закрывающихся дверей внезапно принимает угрожающее значение, вы теряете спокойствие и чувствуете, что попали в ловушку. Вы начинаете думать, что именно этот рейс обречен и что вы предпочли бы сейчас сидеть в более жестком кресле, без любимого напитка и даже без бокового иллюминатора, лишь бы только самолет был оборудован хорошей и надежной техникой.Razaz
01.05.2016 18:22Ну вы уж нагнали страху. SQL Server шикарная СУБД, ток цена кусается и блин нет JSON полноценного как в PG :)
SSMS особенно в шелле от 15 студии ооочень приятен и удобен для работы с СУБД. pgAdmin очень отстает :( Там хотя бы убрать MDI интерфейс — это уже бы облегчило работу.
Это мнение разработчика, а не администратора. Работаю и с PG и с MSSQL одновременно.ZaEzzz
02.05.2016 12:54Утрирование цитированием Раскина — это же весело :)
Ваши аргументы действительно имеют место быть и никто с ними не будет спорить, но у предыдущего оратора основной тезис: «Есть проблемы, но продукт выглядит дорого.» А-ля для БД крайне необходимо не улучшать работу с данными, а запилить красивый интерфейс))) То есть не быть дорогой и рабочей без нареканий, а только выглядеть такой))))
AndersonDunai
29.04.2016 17:04+17— PostgreSQL лучше, чем другие OpenSource СУБД.
— Чем?
— Чем другие OpenSource СУБД.
(из старого анекдота)
zip_zero
29.04.2016 18:12Ложка дёгтя: за 20 лет развития из коробки до сих пор нет (и мне очень этого не хватает):
1. распараллеливания одной SQL query по нескольким ядрам CPU;
2. хинтов в запросах (Oracle-like).kshvakov
29.04.2016 18:39+3«Распараллеливание» запросов будет в 9.6, подробнее можно у Hubert'a почитать depesz.com
В самом постгресе хинтов не будет, по крайней мере пока OptimizerHintsDiscussion, но есть http://pghintplan.osdn.jp/pg_hint_plan.htmlzip_zero
29.04.2016 18:59Согласен, pg_hint_plan — прикольная штука, использовал, но что удручает — это два момента:
— хинты могут использоваться только на «топовом» (внешнем) уровне запроса;
— на 9.5 работоспособность официально не подтверждена. Проверяйте, мол, сами, на свой страх и риск.
jacob1237
29.04.2016 18:43То есть Вы все эти 20 лет ждали эти две фичи, и за столь долгий срок даже не промелькнуло и мысли хоть как-то поучаствовать в разработке этого, без преувеличения, замечательного OpenSource продукта? =)
guai
29.04.2016 18:23-1Всё замечательно, когда проект работает с одним только постгресом, много плюшек, всё круто. Но чаще РСУБД воспринимают как взаимозаменяемые хранилища. И тут уже приходится писать код под минимальный набор фичей всех целевых СУБД. Тем более, если берем ОРМ.
Так что, чтоб эта вся радость пришла к нам с вами, придется убить мускуль. Прости, мускуль, ничего личного.rdruzyagin
29.04.2016 21:16+2Вот вы знаете, может, я просто не сталкивался с подобными проектами, но я очень подозрительно отношусь к продукту, которому надо рутинно менять одно хранилище на другое. Я имею ввиду какой-то сервис, который предоставляет услугу, считает деньги за эту услугу, разработчки каждый день решают «хотелки» от коммерции разной степени адекватности и т.д.
guai
29.04.2016 21:38+1Одно дело сервис, другое, когда надо встать на ту базу, которая есть у клиента. Бабки заплачены, спецы есть, стоит она у них настроена-обкатана. Оракл там какой-нибудь или мс сиквел сервер купили, да многих жаба задушит после этого халявным постгресом начать пользоваться. Ну а раз так, то берем ОРМ, и получаем все прелести разработки под минимальное множество фич субд.
rdruzyagin
29.04.2016 23:39+2Здесь вы правы. Но такой продукт будет либо очень «средний по больнице», и в процессе эксплуатации все равно придется лезть в недра или звать сантехников, которые умеют в них ковыряться) Грабли на нагрузках и объемах неизбежно повылазят уже после внедрения. А до тех пор — какая-то усредненная функциональность, более-менее решаемая ORM'ом с сомнительной степенью эффективности.
sentyaev
01.05.2016 02:09+3Но чаще РСУБД воспринимают как взаимозаменяемые хранилища
Попробую предположить, что вы занимаетесь разработкой «коробочных» продуктов и в вашем случае, скорее всего, приходится полагаться на ANSI SQL, чтобы работало везде.
Я вот занимаюсь разработкой backend'ов и ни разу не приходилось менять СУБД (даже в голову никому такое придти не могло). И база обычно используется на всю катушку.
ArchieOHare
29.04.2016 18:29Вот что точно доставляет неудобство в PostgreSQL, так это невозможность использования в запросе значений вычисляемых в этом же запросе полей. Поясню:
Select a as val1, b as val2, val1+val2 as sum_a_b From table;
[Err] ОШИБКА: колонка «val1» не существует
Tonkonozhenko
29.04.2016 18:37Решается вложенными селектами, но это действительно неудобно когда вложенность доходит уровней до 5.
PsyHaSTe
05.05.2016 20:03Это не неудобство Posgre, это общее свойство всех запросов, и объясняется очень просто: для обращения к алиасу нужно, чтобы он существовал в момент выполнения этого запроса. Т.к. все поля селекта присваиваются одновременно (можно считать, что каждой поле в своем потоке присваивается), то и алиаса не существует. По той же причине нельзя их в Where и OrderBy использовать — селект выполняется последним, и поэтому ОН может использовать алиасы, из, например, from, а наоборот — нет.
rdruzyagin
05.05.2016 22:37В order by можно, а вот в группировке, например, — нельзя.
Я подозреваю, что комментарий возник в связи с тем, что MySQL как раз-таки позволяет оперировать алиасами в group by. Я сам сталкивался с этой печалью имени MySQL-«архитекторов», когда надо было код портировать, в котом трехъэтажная «регулярка» была в виде алиаса условием для group by! Пришлось переписывать, конечно. В тупую Посгрес не дает таки вещи переносить, и это, в общем-то, хорошо.PsyHaSTe
06.05.2016 02:22+2Потому что это прописанно в стандарте SQL :) Вот выдержка из Ben-Gan I., Sarka D., Talmage R. — Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 — 2012
И про OrderBy тоже не забудем! :)
bizzonaru
29.04.2016 18:29А как происходит работа с индексами, с большими таблицами?
rdruzyagin
29.04.2016 21:14Поясните, свой вопрос, пожалуйста :) Про индексы можете почитать переводы наших статей про explain, там частично эта тема затрагивается. Про большие таблицы — на крупных инсталляциях проблемы с их обслуживанием могут возникнуть, зависит от совокупной ситуации на вашей базе (объем и количество таблиц, интенсивность записи и т.д.), но это тема для отдельной статьи про эксплуатацию.
boolive
30.04.2016 00:31Что если на все 1600 колонок целого типа повесить b-tree не уникальные индексы?
eviland
01.05.2016 18:06В теории вставка или модификация (смотря какая) каждой строки будет происходить в среднем в 1600 раз медленнее, чем без индексов. На деле, пока всё это будет помещаться в буферы — быстрее, а потом медленее.
vovik0134
01.05.2016 18:07+1Боюсь, что в такой постановке задачи, проблемы будут у любой РСУБД, т.к. они не ориентированы на таблицы с таким числом колонок. В рамках РСУБД такие таблицы обычно преобразовываются по модели Entity–attribute–value (EAV).
boolive
01.05.2016 23:47Тогда к чему возможность столько колонок иметь?
vovik0134
02.05.2016 19:19Количество колонок варьируется от типа данных на колонках. Поэтому можно иметь от 250 до 1600 колонок. Такая возможность обусловлена размерами страницы в 8 Кб.
С другой стороны, возможность заводить большое число колонок, никак не оправдывает дизайн схемы данных с большим количеством колонок в таблице в РСУБД. РСУБД для этого не предназначены. Для таких задач есть колоночные СУБД, например Cassandra или Teradata.
Camel
29.04.2016 18:29+6Когда слово берёт специалист, мне хочется услышать не только о сильных сторонах продукта, но даже в большей степени о слабых.
rdruzyagin
29.04.2016 21:12+4Это может показаться ответом наивного фанбоя, но я вот посидел честно и подумал о своем опыте интенсивной работы с ПГ в крупных проектах и не могу вспомнить чего-то такого масштабно плохого, от чего были большие боли и страдания, по сравнению с моим опытом от работы с MySQL и NoSQL решениями. Мелких придирок могу накидать, выше коллеги, например, писали про невозможность использовать alias сложносочиненного выражения в group by. Но я это считаю больше решением из разряда bad design. Такие вещи правильно отцепить в CTE / подзапрос.
Хотя мысль, конечно, любопытная, написать подобную статью и покопаться в шкафу со скелетами.
Вещи из разряда «GUI кривой» я не считаю недостатком базы и всех программистов приучаю работать с psql.
hydrobiont
29.04.2016 22:57+1Я могу накидать таких недостатков много десятков, тк давно и плотно работаю с postgresql. Но восновном это будут недостатки в сравнении с Oracle/DB2 а не с MySQL.
Сравнивая с MySQL таких недостатков исчезающе мало и они если честно даже близко не перекрывают достоинств.
Если говорить о технических моментах, где MySQL с некоторыми оговорками впереди, то это
1. использование O_DIRECT
2. Поддержка NUMA-архитектур (по крайней мере перкона-сервером)
С пунктом 1 у MySQL не без купороса и есть шанс что в PostgreSQL в ближайшие годы поддержка O_DIRECT будет имплементирована прямее. Тут важно еще понимать, что MySQL в силу устройства своего сообщества осилил выкинуть поддержку зоопарка ОС, а PostgreSQL пока нет — им такие вещи сейчас внедрять проще.
Пункт 2 скорее тянет на пол-пункта тк поддержка далека от совершенства. Стандарты PostgreSQL просто не позволят закомитить такую полуфичу в основной Postgres, (EDB или ПгПро конечно могут себе позволить, но хорошо от этого их продуктам не будет)denis_g
29.04.2016 23:25+5А можно, пожалуйста, увидеть Топ 10 по сравнению с Oracle/DB2? Не писькомерства ради, а просто интересно такие вещи знать. Можно в виде отдельной статьи ;)
kaamos
04.05.2016 11:23Привет, Илья! Да, это то, что мы успели обсудить за 5 минут общения на Percona Live. Но список отличий этими двумя пунктами не исчерпывается. Я собираюсь развернуто поговорить на эту тему на DevConf: http://devconf.ru/ru/offers/offer/127
Приходите/приезжайте. Думаю, достаточно интересный рассказ получится.hydrobiont
04.05.2016 11:38Алексей, привет!
Я бы кстати с удовольствием пришел и даже бы похоливарил в чем-то, но 17ого июня не осилю быть в мск. Может вы к нам на pgday.ru в июле десант высадите?;-)
kaamos
04.05.2016 11:24Я собираюсь подробно поговорить на эту тему на DevConf: http://devconf.ru/ru/offers/offer/127
fahreeve
29.04.2016 19:14+1Было бы неплохо, если бы вы еще и про оптимизацию PostgreSQL написали
zip_zero
29.04.2016 20:19+1Куда уж больше…
1. https://habrahabr.ru/post/203320/
2. https://habrahabr.ru/post/203386/
3. https://habrahabr.ru/post/203484/
jreznot
30.04.2016 08:43+1> это поддержка пользовательских объектов и их поведения…. Это делает Постгрес невероятно гибким и надежным.
Маркетинговая манипуляция мнением относительно надёжности
groaner
30.04.2016 11:27+1А почему всегда приводятся и сравниваются максимальные размеры, но никогда не приводятся минимальные? Ну например — сколько байт реально физически на диске будет занимать запись с единственным битовым полем в PostgreSQL и других сравниваемых СУБД? То есть, насколько велик overhead при использовании очень больших таблиц с очень короткими записями?
DrPass
30.04.2016 23:09Разве это имеет какое-то практическое значение? У вас все равно в реальной жизни не будет таблицы с единственным битовым полем. Там как минимум будет еще какой-либо первичный ключ. А если для какой-то задачи вам потребуется такая таблица без первичного ключа, значит, вы неправильно спроектировали хранилище данных.
groaner
30.04.2016 23:19Какая разница — ну добавьте еще 4 байта на первичный ключ. Я просто предельный случай взял. И в SQL Server, например, таблицы с короткими записями реально раз в пять меньше места занимают.
PsyHaSTe
05.05.2016 20:07Ну допустим 10 байт. Что изменилось? Не будете никогда использовать Postgree?
DrPass
> У Постгреса множество возможностей
Конечно, моё утверждение довольно субъективное, но ИМХО, «множество возможностей» — это просто особенность, а не конкурентное преимущество. В целом, конечно, неплохо, что на стороне СУБД есть возможность делать высокоуровневые операции, которые раньше выносились на приложение. Но это совсем не обязательно, и особых причин делать их именно на стороне СУБД как бы и нет. Т.е. разработчик, которому СУБД таких плюшек не даёт, и который это будет делать на уровне сервера приложений и/или клиента, не будет себя чувствовать хуже :)
MaksVasilev
Я думаю, что всё таки разработчик, пишущий аналог «возможностей» на уровне логики приложения, а не базы, в итоге будет себя чувствовать хуже, чем разработчик, который просто будет использовать эту возможность в базе. Предлагаю вам попробовать сравнить, например, производительность пространственных запросов геоданных PostgreSQL/PostGIS и «геоданных» в MySQL.
Для примера, можно взять не сложный запрос: автоматически расставить полигонам зданий адреса по признаку их географической вложенности в полигоны страны, области, города; исключив при этом дома внутри промзон и города площадью меньше заданного значения.
seregamorph
Вы ведь программист баз данных, верно?
Не соглашусь с Вами. Временами, сделать быстрее на БД — возможно, да и посмотреть наверно удобнее.
Только все это очень весело до того момента, пока проект не перевалит через некоторое пороговое значение своего размера и сложности. Потом начинаются неочевидные связи, черные ящики, вертикальное масштабирование и пр. Использование «множества возможностей» в итоге часто оборачиваются проблемами миграции на другие БД (например, при установке системы в банке, который имеет лицензию и поддержку альтернативной БД).
При всем моем уважении и любви к postgres — для меня это не более чем надежное ACID-хранилище, я сторонник логики в приложении почти всегда (за исключением разве что случаев обработки огромных массивов данных, чтобы не гонять их по сети).
MaksVasilev
Ну а всё таки, известны ли вам случаи манипуляции с пространственными данными на стороне клиента, а не на уровне геоданных в БД?
Есть сферы применения БД, где манипуляция с данными крайне проста, прозрачна и имеет высокую производительность, в случае работы на уровне БД, и геоданные — одна из таких сфер, как мне кажется.
seregamorph
С такими типами данных не работал.
И все же — ваше сообщение скорее про OLAP-область, не OLTP?
MaksVasilev
Зависит исключительно от задачи. Если задача на лету вытащить адрес из базы по клику мышкой на карте, то это чистый OLTP, а если посчитать суммарную статистику и нормализовать данные по каким то глобальным критериям, то это уже будет OLAP. Пример на 3 комментария выше — скорее ближе к OLAP, да, если производится заполнение свойств большому количеству объектов, а если это задача геокодинга и нужны свойства одного объекта, то это будет чистый OLTP.
seregamorph
Оффтоп: На каждый клик тащить что-то из базы? Не проще ли закачать это все добро в память (кеш)? О каком объеме данных идет речь?
MaksVasilev
Если брать полный набор данных, то около 40-80 ГБ для покрытия территории бывшего СССР или около 500-700 ГБ для всей планеты. Если брать только адресную информацию, примерно на порядок меньше.
0xd34df00d
Известны. В одном из NLP-движков, в разработке которого я участвовал, геоиндекс и геопоиск были (и даже были написаны мной).
MaksVasilev
Поясните пожалуйста, что такое NLP-движок, а то я теряюсь в выборе расшифровок. И можно ли увидеть реальный пример использования этого движка?
0xd34df00d
Natural language processing. Брал поисковые запросы на человекочитаемом языке, выплёвывал некоторую машинноориентированную интерпретацию.
Увидеть, к сожалению, нельзя.