Comments 139
Очень важная, на мой взгляд, вещь, которая есть в постгресе и нет в мускуле — транзакционный DDL. Экономит просто нереальное кол-во нервных клеток. Constraints тоже классная и нужная вещь, которую в мускуле имплементировали только на уровне интерфейса (сюрприз новичкам) — вызывает лишь недоумение. Ну и конечно умение генерировать series. Ну и в целом постгрес рулит — уж очень он функциональный и продуманный.
В MySQL 8 уже перешли на транзакционный DDL.
С трудом представляю себе, как они это сделали: в исходниках MySQL системные словари прибиты к MyISAM гвоздями с незапамятных времен. Явно пришлось переработать огромные залежи очень старого кода.
Constraints которую в мускуле имплементировали только на уровне интерфейса (сюрприз новичкам) — вызывает лишь недоумение.скажите, кратко, что за проблемы с Constraints в MySQL? что значит на уровне интерфейса?
The CHECK clause is parsed but ignored by all storage enginesда это кончено жесть
Имеет место такая жесть, да.
Но это относится только к check, foreign keys итд работают.
foreign keys итд работают.
Да и те как-то хитро сделаны
MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification.
create table testfk (
parent_id int not null references testbase(id)
) engine=innodb;
Вот так сделать синтаксически верно, а FK просто тихо создано не будет
Что оно отражает?
Гугление выдало в том числе значение — пирамида (с англ. HYIP — High Yield Investment Program) имеющая не самый положительный смысл.
Также трудно PG назвать новым проектом.
На конференции по PG конечно будут говорить о PG. Но это одна конференция.
Хотелось бы комментарий автора статьи услышать.
С такой логикой можно далеко зайти. "Написал O(N^3) алгоритм, работает, а что на сервера надо много килорублей — проблема бизнеса".
Oracle после PostgreSQL — боль. Транзакционного DDLя нет, sqlplus — жалкое подобие консольного клиента, вообще не чета psql'ю (а есть куда более продвинутые, pgcli, например), типов данных из коробки — раз два и обчёлся (CLOB'ы — это вообще тихий ужас). Многие запросы пишутся через жопу (уже спустя год с содроганием вспоминаю вездесущие SELECT FROM DUAL).
Возможно, это дело привычки. С Постгресом я уже много лет работаю, а с Oracle год повеселился (и, надеюсь, больше ни-ни).
Но есть и плюсы: многие вещи в Оракле есть из коробки. Полнотекстовый поиск? Пожалуйста (+$100500), геометрия/география? Вот тебе бесплатный Oracle Locator и платный Oracle Spatial (нам, кстати, хватило бесплатного).
Но берёшь PostgreSQL, добавляешь в него по вкусу PostGIS, pg_pathman, мониторинг хороший (тут можно попиарить okmeter), приправляешь каким-нибудь barman'ом — и становится даже лучше.
Вы забыли про возможность запускать под нормальными системами, а не под вендой.
Если можно, несколько пунктов которые в другой СУБД лучше.
Синтаксис T-SQL… не так очевиден, как PL/pgSQL. Триггеры в постгресе пишутся намного проще.
Нет юникода, как я забыл! Основная причина того, что мы до сих пор на 1251 — невозможность utf8 в субд.
А с utf-8-то что не так?..
пользовательские типы были в 2008, я с ними работал, функциональные возможности типов не помню.
тип bit есть, не знаю с какой версии.
юникодные NCHAR NVARCHAR был, но на данный момент я не помню какая связь с collation
Прочтите исходный комментарий ветки.
> тип bit есть
Я не хочу bit. Я хочу булевый. Вы знаете разницу между ними?
> юникодные NCHAR NVARCHAR
Это не тот юникод. Совсем не тот, поверьте.
Я не хочу bit. Я хочу булевый. Вы знаете разницу между ними?я не уверен, хотелось бы узнать, наверно какое то удобство в чем то
Булев (логический) тип — это то, что возвращают операторы сравнения, также его принимают и возвращают логические операторы.
bit — это целочисленный тип с двумя возможными значениями.
В SQL нельзя написать WHERE foo, если foo имеет тип bit. Приходится писать WHERE foo=1. В обратную же сторону получается еще хуже — вместо SELECT a<b as cmp нужно писать SELECT case when a<b then 1 else 0 end as cmp.
Это не тот юникод. Совсем не тот, поверьте.Можете сказать что не так с юникодом?
Вам прямо говорят, что JSON, например, это UTF-8. Независимо от языка.
Ничего подобного, JSON может быть в любой кодировке. Стандарт вообще ничего не говорит про бинарное представление JSON, только про текстовое.
A JSON text is a sequence of tokens formed from Unicode code points that conforms to the JSON value grammar.
— Final draft of the TC39 "The JSON Data Interchange Format" standart, пункт четвертый.
Значит, нужен слой, который это перекодирование сделает сам, вот и все.
Слишком общая фраза, так же общо отвечу: у SQL Server даже 2017 нет многих возможностей, которые в PostgreSQL были уже давно.
Самый распространенный вариант частичного индекса (email + not deleted) в mysql можно изобразить через виртуальные столбцы. Синтаксис виртуальных столбцов несколько отличается в mariadb и oracle mysql (где они появились позднее), но суть та же. Покажу на примере mariadb.
CREATE TABLE users (
...
email varchar(255),
is_deleted boolean NOT NULL DEFAULT FALSE,
_null_if_deleted char(0) AS (IF(is_deleted, NULL, '')) PERSISTENT,
...
);
CREATE UNIQUE INDEX uniq_users_email ON users(email, _null_if_deleted);Трюк в виртуальном char(0) поле, которое может иметь всего 2 значения — пустая строка и null. Если пользователь удален, то там будет null, и в итоге строка в uniq_users_email участвовать не будет.
Не подумайте, я вовсе не хочу превратить топик в оправдания mysql-я, я люблю постгрес и использую его практически во всех проектах, где я принимаю решение о выборе базы данных с самого начала. Но очень часто приходится работать с проектами, которые уже сделаны на mysql и выбора нет.
то там будет null, и в итоге строка в uniq_users_email участвовать не будета почему? из за слова PERSISTENT?
непонятно как char(0) при null должен исключаться от попадания в индекс
Из-за слова NULL. NULL-значения не участвуют в unique index. В случае индекса по нескольким полям — если хотя бы одно из них NULL. char(0) — это просто для экономии места, чтобы не хранить никакое значение, которое тут все равно служебное и смысла не несет.
(null, 1) заблокируют вставку следующей пары (null, 1)
вот можно поиграться
USE [test]
GO
/****** Object: Table [dbo].[users] Script Date: 6/23/2017 1:31:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
[id] [int] NOT NULL,
[email] [varchar](255) NULL,
[is_deleted] [bit] NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [test]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [uniq_users_email] Script Date: 6/23/2017 1:31:32 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [uniq_users_email] ON [dbo].[users]
(
[email] ASC,
[is_deleted] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Это какое-то очень странное поведение, NULL не является уникальным значением, один NULL не равен другому.
В ANSI SQL это четко прописано. В этом весь смысл NULL-а.
А как тогда в MS SQL делать уникальность опционального email-а (пусть даже безо всяких deleted)?
CREATE UNIQUE INDEX IX_users_email ON users(email) WHERE email IS NOT NULLЯ так и думал. :)
С точки зрения ANSI SQL это полная жесть, конечно. "Уникальность" NULL-а ломает кучу классических SQL-паттернов, ради которых NULL и задуман таким, какой он в ANSI SQL.
«Уникальность» NULL-а ломает кучу классических SQL-паттернова можно огласить весь список? я предпалагаю что ms sql как то должен их обойти без особых проблем
Полагаю, что для обхода везде придется дописывать where field is not null.
Скажем, поиск дубликатов:
> create table a (id serial, v int);
> insert into a (v) values (1), (null), (2), (null), (1);
> select * from a a1, a a2 where a1.v = a2.v and a2.id > a1.id;
+----+------+----+------+
| id | v | id | v |
+----+------+----+------+
| 1 | 1 | 5 | 1 |
+----+------+----+------+
1 row in set (0.00 sec)В запросах это решается настройкой ANSI NULLS:
SET ANSI_NULLS ONПричем установить ее можно глобально для базы, тогда не нужно будет писать эту строку каждый раз (хотя все равно пишут, для независимости от настроек базы).
Неустранимыми "особенностями" обладают лишь те объекты, которые хранятся в базе — индексы и, может быть, ограничения. Индексированные виды при этом внезапно работают в режиме ANSI_NULLS.
select * from a a1, a a2 where a1.v = a2.v and a2.id > a1.id;
id v id v
1 1 5 1
SET ANSI_NULLS on/off ничего не меняет
А, точно. ANSI_NULLS влияет только на интерпретацию сравнений с константой NULL.
А вот выше говорят про настройку ANSI_NULLS. Я верно понимаю, что такой результат выборки получается при ANSI_NULLS ON, а если сделать OFF, то вылезут еще строчки с NULL-ами?
Нет, я там ошибся. Жаль, нельзя себе минус поставить...
А как тогда в MS SQL делать уникальность опционального email-а (пусть даже безо всяких deleted)?а мне вот наоборот интересно, как в PG или MySql делать индекс по комбинации где одна колонка заполнена, а другая null
Хм, проверил — вы правы, в уникальном индексе в MS SQL может быть только 1 NULL. Осталось понять, каким образом я запомнил противоположное?..
Насчет хранения не уверен, но, как минимум, для выборки по индексу не надо читать ничего из данных:
MariaDB [test]> create table foo (c0 char(0), index (c0));
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> insert into foo values ('');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> explain select c0 from foo;
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | foo | index | NULL | c0 | 1 | NULL | 1 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)"If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index."
Как точно узнать, что совсем не хранится, я не знаю. :) Но в случае char(0) выглядит логичным ничего не хранить. Это скорее уже предположение.
Согласно стандарту ANSI SQL, NULL-значение не может быть равно никакому другому, даже другому NULL (похожим образом ведет себя NaN в IEEE 754). Поэтому ключи, содержащие хотя бы 1 NULL, всегда считаются уникальными.
Так было сделано для того, чтобы можно было накладывать ограничение уникальности на опциональные поля, иначе в таких ограничениях было бы мало смысла.
Это один из самых частых вопросов от свитчеров.
pgAdmin что то не впечатлил.
Есть что то бесплатное с аналогом Database Diagram в Management studio?
Меня вполне устраивает Database Tools, который поставляется с практически всеми JetBrains-овскими IDE.
Попробуйте поставить pgAdmin 3, потому что pgAdmin 4 — какое-то невнятное убожество, которое умеет только тормозить и жрать память, потому что написано "на вебе".
Listen / Notify
Я пока что не пробовал это в продакшене, поэтому не знаю, применимо ли это на практике (если кто использовал, поделитесь плиз опытом в комментариях).
Мы используем. Связали так php и golang, отправляется уведомление при изменении данных, чтоб бэкенд подхватил. Работает как часы, очень удобно.
А в схеме, когда много-много подписчиков, каждый из них должен будет постоянно держать соединение с базой, чтобы не пропустить сообщение. Но соединение с базой в посгресе — это дорого. Вот это меня беспокоит
Если не соединяться с базой — как с ней общаться? :) Как и с любым сервисом.
Мы же используем этот механизм для реалтайма, то есть оповестить клиента настолько быстро насколько возможно. Это удобней чем городить огороды с rpc/http апи или ещё чем… с базой и так соединены оба.
Не, проблема в том, что если, скажем, отрестартить гошный сервис, то часть уведомлений потеряется.
В последних постгресах есть API для декодирования WAL, и на гитхабе можно найти декодеры в json, protobufs и все такое. Возможно, это было бы более надежным решением, да и notify никакие не нужны — все само прилетит. Насколько я понимаю, отфильтровать только нужное можно тоже на уровне декодера.
Тогда вам нужны очереди. Если хочется странного оставаться в рамках Постгреса, то посмотрите в сторону PgQ, возможно, что это оно (сам не работал). А вообще, лучше не мучит мозг, а взять что-нибудьMQ: RabbitMQ, там, тысячи их.
Идея с WAL decoder мне нравится тем, что можно получить функциональность RethinkDB, оставаясь в рамках постгреса.
Еще не пробовал на практике, но скоро собираюсь — на горизонте маячит подходящая задачка.
Ни sequelize, ни postgres ошибок и предупреждений не выдает, и на сложных запросах спокойно и валидно возвращает не фронтенд некорректные данные. При этом в тестах (на более менее простых запросах) всё ок.
У меня проблема обнаружилась на втором месяце разработки, когда уже была написана жирная бизнес-логика и в ход пошли трехэтажные запросы. Стоило нескольких бессонных ночей.
Ишью открыта с 2014 года и выглядит так, будто это не критическая проблема, а мелкая недоработка.
Простого решения нет. Мне пришлось пересобрать postgres из исходников, установив NAMEDATALEN = 1024. Но это плохое решение: во-первых, со слов разрабов, уже с NAMEDATALEN = 128 есть падение производительности postgres; во-вторых умеючи можно и 1024 превысить и не заметить.
Напишите, а ишью создали?
Я был бы рад даже не увеличению параметра, а хотя бы нормальной ошибке при превышении.
Вообще, интересная ситуация. Я всё понимаю, open-source, никто ничего никому не должен, возьми и сделай, или хотя бы заранее читай все issue. Всё так. Но я всё равно как-то офигел, что такое возможно в 10k звездном проекте с 400 тысячами загрузок в месяц. Представляете, это была моя инициатива строить проект на node стеке, и тут, спустя два месяца работы, я понимаю, что в решении есть один «несущественный недостаток»: из базы приходят не все данные. Три дня и ночи искал у себя в коде баг. Потом нашел открытую ишью от 2014 года. Ещё три дня просто молчал и познавал дзен.
Ресурсов стал есть мама не горюйКак настроете так и будет жрать. На мой взгляд он экономнее MySQL.
Вот не знаю даже, что вы с Постгресом такое сделали. Он из коробки настроен на очень экономное использование памяти, так, чтобы нормально работать на машинках класса Raspberry Pi и хуже. Ему специально надо «гайки ослаблять», чтобы разрешить использовать все ресурсы и раскрыть производительность по полной. Для старта возьмите PgTune, а уже потом есть мануалы по том, как его тюнить.
Да, в PostgreSQL мне понравились индексы по выражению.
Но есть и небольшая ложка дёгтя: если взять два поля, объединить их в range и построить индекс, то он будет срабатывать только когда в запросе тоже используются range.
То есть WHERE int8range(col1, col2) @> $value будет работать эффективно, а WHERE col1 <= $value AND $value < col2 — нет. Это не очень удобно при использовании ORM.
А это со всеми функциональными индексами так: ускоряются только запросы с условием с точно таким же выражением (и одним из поддерживаемых данным индексом операторов в условии ещё, сверяйтесь в документации — btree и gist ускоряют разные запросы!).
SELECT count(*) FILTER (WHERE age > 20) AS old, count(*) FILTER (WHERE age <= 20) AS young FROM users;
И чем это принципиально лучше такого?
SELECT SUM(IF(age>20,1,0)) AS old, SUM(IF(age<=20,1,0)) AS young FROM users;
Не смешно.
Брюнетка/блондинка…
Ещё есть плюсы?
Ну там используются partial индексы, например?
К сожалению, нет, ни в том, ни в другом варианте:
- count и filter
EXPLAIN ANALYZE SELECT count(*) FILTER (WHERE age < 18) AS young FROM people; -> Seq Scan on people (cost=0.00..212803.95 rows=10999995 width=4) (actual time=0.060..646.134 rows=11000000 loops=1) sum с условием. В PostgreSQL нет
IF(function if(boolean, integer, integer) does not exist), поэтому вместо этого используетсяCASE:
EXPLAIN ANALYZE SELECT (SUM(CASE WHEN age < 18 THEN 1 ELSE 0 END)) AS young FROM people; -> Seq Scan on people (cost=0.00..212803.95 rows=10999995 width=4) (actual time=0.042..674.906 rows=11000000 loops=1)
- Однако если
WHEREприсобачить уже в конце выражения, послеFROM, только тогда используются:
EXPLAIN ANALYZE SELECT count(*) FROM people WHERE age < 18; -> Index Only Scan using young_people on people (cost=0.43..55783.71 rows=1952507 width=0) (actual time=0.043..153.447 rows=1925242 loops=1)
Таблица для проверки:
CREATE TABLE people (id serial, name varchar, age int, primary key (id));
INSERT INTO people (name, age) SELECT md5(random()::text)::varchar AS name, (random() * 100)::int AS age FROM generate_series(1, 1000000); -- достаточно большая таблица, чтобы планировщик захотел заморочиться с индексами
CREATE INDEX young_people ON people (age) WHERE age < 18;
VACUUM ANALYZE people;На самом деле неправильно вас понял (перепутал покрывающие с частичными индексами), но ответа это не меняет.
Используйте вот такую конструкцию, если хотите оба значения и использовать индекс (и то, только при условии, что оба фильтра отсекают большое количество записей):
SELECT (SELECT count(*) FROM people WHERE age < 18) AS young, (SELECT count(*) FROM people WHERE age >= 18) AS too_old; QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=29282.67..29282.68 rows=1 width=16) (actual time=160.145..160.145 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Aggregate (cost=5369.82..5369.84 rows=1 width=8) (actual time=23.040..23.040 rows=1 loops=1)
-> Index Only Scan using people_age on people (cost=0.42..4936.65 rows=173270 width=0) (actual time=0.016..15.298 rows=175201 loops=1)
Index Cond: (age < 18)
Heap Fetches: 0
InitPlan 2 (returns $1)
-> Aggregate (cost=23912.83..23912.83 rows=1 width=8) (actual time=137.100..137.100 rows=1 loops=1)
-> Seq Scan on people people_1 (cost=0.00..21846.00 rows=826730 width=0) (actual time=0.010..100.024 rows=824799 loops=1)
Filter: (age >= 18)
Rows Removed by Filter: 175201
Planning time: 0.065 ms
Execution time: 160.170 msОбратите внимание, что во втором случае планировщик всё равно предпочёл полное сканирование, потому что колонок в таблице мало, а второе условие отбирает бо́льшую часть записей в таблице и профита лезть в индекс нет.
1) второй вариант это всё же хак — нам нужно количество, а мы считаем сумму нулей и единичек, да ещё пользуясь нестандартной функцией, а оконные функции, в том числе агрегирующие часть стандарта, насколько я знаю
2) если нужны и обычные оконные функции, то второй вариант с ними будет выглядеть неконсистентно.
Возможности PostgreSQL для тех, кто перешел с MySQL