Pull to refresh

Comments 139

обычно ORM поддерживают только смежные фичи… возможно из-за этого uber перешел обратно на MySQL)
Убер перешел на MySQL потому что им нужна была не RDBMS, а платформа для реализации своей key-value базы: https://eng.uber.com/schemaless-part-one/

Очень важная, на мой взгляд, вещь, которая есть в постгресе и нет в мускуле — транзакционный DDL. Экономит просто нереальное кол-во нервных клеток. Constraints тоже классная и нужная вещь, которую в мускуле имплементировали только на уровне интерфейса (сюрприз новичкам) — вызывает лишь недоумение. Ну и конечно умение генерировать series. Ну и в целом постгрес рулит — уж очень он функциональный и продуманный.

В MySQL 8 уже перешли на транзакционный DDL.


С трудом представляю себе, как они это сделали: в исходниках MySQL системные словари прибиты к MyISAM гвоздями с незапамятных времен. Явно пришлось переработать огромные залежи очень старого кода.

Ну, насколько я понял, от myisam было решено отказаться. А так, это хорошая новость. Кстати, я один не понимаю такую систему версионирования: 5.7.х -> 8.0.0? :)

Убрали первую цифру. Как Java или Linux Kernel.
UFO landed and left these words here

В Оракле нет транзакционного DDL. (Если есть, подскажите как включить, Oracle после PostgreSQL — это адская боль и мучение)

Включить нельзя, вы правы, его там нет.
Constraints которую в мускуле имплементировали только на уровне интерфейса (сюрприз новичкам) — вызывает лишь недоумение.
скажите, кратко, что за проблемы с Constraints в MySQL? что значит на уровне интерфейса?
Я так понимаю это неработающие CHECK
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 просто тихо создано не будет

Ага, надо в constraint писать. Там какая-то проблема "курицы и яйца" у них в этом месте.

В каком контексте в статье употребляется слово «хайповость», «хайповая технология»?
Что оно отражает?
Ну, это что-то модное. То, о чем все говорят на конференциях. То, что все используют в новых проектах и т.д. Нелегаси
Спасибо за ответ.
Гугление выдало в том числе значение — пирамида (с англ. HYIP — High Yield Investment Program) имеющая не самый положительный смысл.
Также трудно PG назвать новым проектом.
На конференции по PG конечно будут говорить о PG. Но это одна конференция.

Хотелось бы комментарий автора статьи услышать.
Я и есть автор статьи. Ее просто опубликовали в рамках блога конференции pgday

http://www.urbandictionary.com/define.php?term=hype
все идет к тому, что мускул и постгрес станут похожими на mssql аж 2005 (!) года выпуска

У них, кроме всего прочего, есть одно огромное преимущество перед MS SQL Server'ом — не надо платить 800 килорублей за лицензию.
первое и главное, на мой взгляд, преимущество — низкий порог вхождения. а килорубли проблема бизнеса, не моя

С такой логикой можно далеко зайти. "Написал O(N^3) алгоритм, работает, а что на сервера надо много килорублей — проблема бизнеса".

Так сейчас частенько так и делают. Если затраты на оптимизацию выше стоимости железа помощнее, то почему нет.
Правильно ли я понял, что ваше нежелание/неумение разбираться должно стоить бизнесу 800k? Кроме того, PL/pgSQL дался мне немного проще, чем T-SQL, как и нюансы администрирования СУБД. Или вы вхождением называете умение запускать Management Studio и жать в ней кнопочки в правильном порядке?
UFO landed and left these words here

Oracle после PostgreSQL — боль. Транзакционного DDLя нет, sqlplus — жалкое подобие консольного клиента, вообще не чета psql'ю (а есть куда более продвинутые, pgcli, например), типов данных из коробки — раз два и обчёлся (CLOB'ы — это вообще тихий ужас). Многие запросы пишутся через жопу (уже спустя год с содроганием вспоминаю вездесущие SELECT FROM DUAL).
Возможно, это дело привычки. С Постгресом я уже много лет работаю, а с Oracle год повеселился (и, надеюсь, больше ни-ни).
Но есть и плюсы: многие вещи в Оракле есть из коробки. Полнотекстовый поиск? Пожалуйста (+$100500), геометрия/география? Вот тебе бесплатный Oracle Locator и платный Oracle Spatial (нам, кстати, хватило бесплатного).
Но берёшь PostgreSQL, добавляешь в него по вкусу PostGIS, pg_pathman, мониторинг хороший (тут можно попиарить okmeter), приправляешь каким-нибудь barman'ом — и становится даже лучше.

Скорее всего вы не человек, могущий реально защитить диплом инженера-программиста в части, как минимум, техникой экономического обоснования.
У бакаларвских дипломов больше нет обязательной экономической части.

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

Не надо грязи. Я сейчас живу на MS SQL 2005. Это боль. Не такая большая, как 2000, но ощутимая.
а вы с чем сравниваете, боль по сравнению с чем?
Если можно, несколько пунктов которые в другой СУБД лучше.
Например — нет кастомных типов, нет enum, нет логического типа. Мне уже по ночам снятся CASE WHEN expr = 1 THEN.
Синтаксис T-SQL… не так очевиден, как PL/pgSQL. Триггеры в постгресе пишутся намного проще.

Нет юникода, как я забыл! Основная причина того, что мы до сих пор на 1251 — невозможность utf8 в субд.
sql 2005 снят с поддержки.
пользовательские типы были в 2008, я с ними работал, функциональные возможности типов не помню.
тип bit есть, не знаю с какой версии.
юникодные NCHAR NVARCHAR был, но на данный момент я не помню какая связь с collation
> sql 2005 снят с поддержки.

Прочтите исходный комментарий ветки.

> тип bit есть

Я не хочу bit. Я хочу булевый. Вы знаете разницу между ними?

> юникодные NCHAR NVARCHAR

Это не тот юникод. Совсем не тот, поверьте.
Я не хочу bit. Я хочу булевый. Вы знаете разницу между ними?
я не уверен, хотелось бы узнать, наверно какое то удобство в чем то
CASE WHEN 2>1 THEN bit_field=1 ELSE bit_field=0 END

Булев (логический) тип — это то, что возвращают операторы сравнения, также его принимают и возвращают логические операторы.


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.

ага, действительно гадость
С тремя возможными значениями:
An integer data type that can take a value of 1, 0, or NULL.
Это не тот юникод. Совсем не тот, поверьте.
Можете сказать что не так с юникодом?
UTF16 это проблема для какого то клиента? Для PHP?
Если вам доставляет извращённое удовольствие регулярно перекодировать в utf8 и обратно — я рад за вас. Мне не доставляет ни малейшего. Очень весело, например, собирать json. Да и разбирать часто не менее весело.
Вам видимо кажется что я прикалываюсь, но нет, мне действительно интересно какие у кого проблемы с MS SQL. Я MS SQL с PHP не использовал, потому о проблемах с кодировкой юникода слышу в первые. Я предполагал что можно задавать кодировку, с которой подключаешься к MS SQL.
Причем тут PHP? Этот язык, к вашему сведению, вообще понятием «кодировка» не оперирует. Для него что request, что response — просто набор байт. И не более того.

Вам прямо говорят, что 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, пункт четвертый.

Значит, нужен слой, который это перекодирование сделает сам, вот и все.

MSSQL это головная боль для админов.

А у админов-то какие с ним проблемы?..

Насчет dba админов не знаю, а для сисадминов обыкновенных™ mssql на порядок удобнее в плане бэкапов/восстановления. А остальное их не касается.
В чем именно похожими?
Слишком общая фраза, так же общо отвечу: у 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) — это просто для экономии места, чтобы не хранить никакое значение, которое тут все равно служебное и смысла не несет.

для меня это неожиданное поведение, в ms sql 2016 null колонки попадают в индекс и значения двух колонок (null, null) заблокируют вставку следующей пары (null, null), так же как и например
(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.

Гм… А что произойдет, если я при ANSI_NULLS ON сделаю UNIQUE INDEX на a.v из примера выше (после того, как зачистил не null-дубликаты)? Все равно придется where not null для индекса дописывать?

хоть ANSI_NULLS ON хоть OFF, на UNIQUE INDEX это не влияет
в ms sql 2016 так
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-ами?

То есть получается, что в MSSQL:
1) при сравнении с константой NULL результат зависит от ANSI_NULLS,
2) при сравнении значений полей NULL-ы всегда не равны,
3) но в UNIQUE INDEX NULL-ы всегда равны.


Я все верно усвоил?


Мой мозг. :) Зачем, зачем они так сделали?

И эти люди запрещают нам ковыряться в носу! :-) (В смысле, критикуют strict_mode в mysql).

А как тогда в MS SQL делать уникальность опционального email-а (пусть даже безо всяких deleted)?
а мне вот наоборот интересно, как в PG или MySql делать индекс по комбинации где одна колонка заполнена, а другая null

Вообще, как правило, вы не должны этого хотеть, ведь NULL — это не значение, а отсутствие значения.


А если очень хочется, можно, конечно, сделать индекс вида (col1, is_null(col2)). В постгресе напрямую, в mysql — тем же трюком с virtual columns, только наоборот.

Хм, проверил — вы правы, в уникальном индексе в MS SQL может быть только 1 NULL. Осталось понять, каким образом я запомнил противоположное?..

в 2000 вроде было другое поведение, я уже не помню точно, возможно зависит от SET ANSI_NULLS ON или еще от чего нибудь

ANSI_NULLS можно менять в любое время, а потому эта настройка не может влиять на структуру индекса.

Чисто в теории на структуру индекса может не влиять, а на поведение может. Ожидания могут быть нарушены, но поведение может быть детерминировано.
а как вы узнали что оно действительно не хранит значений?

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


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) выглядит логичным ничего не хранить. Это скорее уже предположение.

Для nullable char(0) логичным выглядит хранить один бит вне зависимости от наличия индекса, но есть подозрение, что в большинстве случаев это экономия на спичках., даже с индексом.

Я выбирал из char(0) и enum-а с одним возможным значением. char(0) показался эстетичнее, плюс дополнительное соображение о логичности хранения только одного бита (enum-то наверняка всегда хранится как integer).

Согласно стандарту ANSI SQL, NULL-значение не может быть равно никакому другому, даже другому NULL (похожим образом ведет себя NaN в IEEE 754). Поэтому ключи, содержащие хотя бы 1 NULL, всегда считаются уникальными.


Так было сделано для того, чтобы можно было накладывать ограничение уникальности на опциональные поля, иначе в таких ограничениях было бы мало смысла.

Про enum в кастомных типах забыли: https://www.postgresql.org/docs/current/static/datatype-enum.html
Это один из самых частых вопросов от свитчеров.
а есть какая нормальная IDE к PgSql?
pgAdmin что то не впечатлил.
Есть что то бесплатное с аналогом Database Diagram в Management studio?
Есть DataGrip и он хорош. Но не бесплатен.

Меня вполне устраивает Database Tools, который поставляется с практически всеми JetBrains-овскими IDE.

Попробуйте поставить pgAdmin 3, потому что pgAdmin 4 — какое-то невнятное убожество, которое умеет только тормозить и жрать память, потому что написано "на вебе".

Listen / Notify

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

Мы используем. Связали так php и golang, отправляется уведомление при изменении данных, чтоб бэкенд подхватил. Работает как часы, очень удобно.
golang подписан, а php отправляет уведомления?
Да. PHP меняет данные, сохраняет, отправляет в golang «эй, смотри тут данные обновились».
и сколько соединений с pg держит голанг?
Два. Одно для общения с самой базой, одно для Listen.
Если соединение прервалось и восстановилось, в этот период может что-то продолбаться, так ведь?

А в схеме, когда много-много подписчиков, каждый из них должен будет постоянно держать соединение с базой, чтобы не пропустить сообщение. Но соединение с базой в посгресе — это дорого. Вот это меня беспокоит
По поводу соединения — не задумывался пока.
Если не соединяться с базой — как с ней общаться? :) Как и с любым сервисом.
Ну в случае с классической очередью, можно хоть раз в час подсоединяться, проверять, нет ли чего нового, и отсоединяться. А тут прям привязан к соединению
Ну мы тут говорим про очереди или про pub/sub? :) Если всё что нужно — передать сообщение чтоб клиент его рано или поздно получил (например раз в час проверяет) — почему бы просто не записать в табличку, а потом оттуда прочитать?
Мы же используем этот механизм для реалтайма, то есть оповестить клиента настолько быстро насколько возможно. Это удобней чем городить огороды с rpc/http апи или ещё чем… с базой и так соединены оба.
Записать в табличку, а потом прочитать, плохо, как минимум, работает, когда читателей много, писатель о них не знает.
pg_notify можно сразу из тригерной процедуры делать для реалтайма, однако есть свои минусы
Яизначально хотел делать нотифи по сохранению строки в таблицу, но потом решил что будет проще просто делать ещё один SQL запрос из PHP, больше контроля и проще отладка для меня как для программиста (а совсем не DBA).

Не, проблема в том, что если, скажем, отрестартить гошный сервис, то часть уведомлений потеряется.


В последних постгресах есть API для декодирования WAL, и на гитхабе можно найти декодеры в json, protobufs и все такое. Возможно, это было бы более надежным решением, да и notify никакие не нужны — все само прилетит. Насколько я понимаю, отфильтровать только нужное можно тоже на уровне декодера.

Ну мы тут не атомные ракеты программируем…

Тогда вам нужны очереди. Если хочется странного оставаться в рамках Постгреса, то посмотрите в сторону PgQ, возможно, что это оно (сам не работал). А вообще, лучше не мучит мозг, а взять что-нибудьMQ: RabbitMQ, там, тысячи их.

MQ все разные, но не знаю ни одной, которая позволяла бы воркеру может полность нового, может год назад поратовшего( штатно и быстро получить все пропущенные события с конкретного… момента

Идея с WAL decoder мне нравится тем, что можно получить функциональность RethinkDB, оставаясь в рамках постгреса.


Еще не пробовал на практике, но скоро собираюсь — на горизонте маячит подходящая задачка.

Дорого устанавливать соединение. Держать установленное соединение разве дорого?
Оффтопик: Есть информация о том, что самая распространенная ORM для node.js (Sequelize.js, 400k загрузок в месяц) некорректно работает с postgres. Писать статью?
Конечно! А если кратко, то в чем суть проблемы?
В postgres есть hardcoded переменная, NAMEDATALEN = 64. Которая, среди прочего накладывает ограничение на длину идентификатора (названия таблиц, алиасов, полей) 64-1=63 символа. Если писать запросы руками, этого более чем достаточно. Однако ORM, при запросах с JOIN, для вложенных таблиц создает алиас вида ParenttablenameChildtablename, склеивая названия таблиц, запросто превышая этот предел длины.

Ни sequelize, ни postgres ошибок и предупреждений не выдает, и на сложных запросах спокойно и валидно возвращает не фронтенд некорректные данные. При этом в тестах (на более менее простых запросах) всё ок.

У меня проблема обнаружилась на втором месяце разработки, когда уже была написана жирная бизнес-логика и в ход пошли трехэтажные запросы. Стоило нескольких бессонных ночей.

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

Простого решения нет. Мне пришлось пересобрать postgres из исходников, установив NAMEDATALEN = 1024. Но это плохое решение: во-первых, со слов разрабов, уже с NAMEDATALEN = 128 есть падение производительности postgres; во-вторых умеючи можно и 1024 превысить и не заметить.
Это ишью надо в посгресе открывать имхо. Ну что такое 63 символа — смех один. Я тоже напарывался на это ограничение, и не в orm (генерили автоматом то ли хранимки, то ли юзеров, я забыл)
В маиллистах постреса поднимали эту тему некоторое время назад. Тогда увеличили с 32 до 64, отметив что дальнейшее увеличение дает performance degrade.
Я был бы рад даже не увеличению параметра, а хотя бы нормальной ошибке при превышении.
я вот не понимаю причины использование в данном orm таких имён, можно ведь как в рельсах t1, t2, t3… алиасы использовать.
Изначальных мотиваций две: вроде как проще дебажить сгенерированный SQL и проще парсить ответ (напрямую) в дерево. Я, было, хотел написать патч, но, честно говоря, просто зарылся коде и, за выделенное себе на это время — не осилил. Как-то там тяжело всё, целая экосистема и надо с умом подойти, чтобы сделать красиво.

Вообще, интересная ситуация. Я всё понимаю, open-source, никто ничего никому не должен, возьми и сделай, или хотя бы заранее читай все issue. Всё так. Но я всё равно как-то офигел, что такое возможно в 10k звездном проекте с 400 тысячами загрузок в месяц. Представляете, это была моя инициатива строить проект на node стеке, и тут, спустя два месяца работы, я понимаю, что в решении есть один «несущественный недостаток»: из базы приходят не все данные. Три дня и ночи искал у себя в коде баг. Потом нашел открытую ишью от 2014 года. Ещё три дня просто молчал и познавал дзен.
В 2013 postgres был еще нормальной СУБД. Сейчас же надобавляли всякой ерунды ненужной. Пользоваться стало неудобно. Ресурсов стал есть мама не горюй. Стабильно раз в сутки приходится по cron его перегружать. Про репликации я вообще молчу. Как обычно, хорошее долго на рынке не держится. Даже подозрение закрадывается, а не конкурентов ли с проприетарной стороны баррикад это проделки. С нынешним впечатлением и речи не может быть о переходе с MySQL. Однозначное «нет».
А можно конкретнее, какая ненужная ерунда вам мешает?
Судя по комменту, автору мешал autovacuum. :D
Рестарт БД по крону — это 100% маркер, что человек делает что-то ну совсем не правильное.
Вы меня пугаете. Где можно почитать как правильно делать запросы к БД, чтобы не нужно было рестартовать СУЬД по крону.
Чет я не понимаю, к чему вы клоните. Зачем вам рестартить базу?
Ресурсов стал есть мама не горюй
Как настроете так и будет жрать. На мой взгляд он экономнее MySQL.

Вот не знаю даже, что вы с Постгресом такое сделали. Он из коробки настроен на очень экономное использование памяти, так, чтобы нормально работать на машинках класса Raspberry Pi и хуже. Ему специально надо «гайки ослаблять», чтобы разрешить использовать все ресурсы и раскрыть производительность по полной. Для старта возьмите PgTune, а уже потом есть мануалы по том, как его тюнить.

Спасибо за информацию! очень интересная статья!)

Да, в PostgreSQL мне понравились индексы по выражению.
Но есть и небольшая ложка дёгтя: если взять два поля, объединить их в range и построить индекс, то он будет срабатывать только когда в запросе тоже используются range.


То есть WHERE int8range(col1, col2) @> $value будет работать эффективно, а WHERE col1 <= $value AND $value < col2 — нет. Это не очень удобно при использовании ORM.

Так индекс по range, почему он должен использоваться для операций типа "<"?

Потому что мне так хочется. Написано-то одно и то же. Вот пусть база сама догадается, что можно использовать индекс на range.

Когда база начнет догадываться у нас работы не останется :)
Насколько я понимаю — не одно и то же. Вы уверены, что результаты будут одинаковы для разных типов col*?

А это со всеми функциональными индексами так: ускоряются только запросы с условием с точно таким же выражением (и одним из поддерживаемых данным индексом операторов в условии ещё, сверяйтесь в документации — 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 индексы, например?

К сожалению, нет, ни в том, ни в другом варианте:


  1. 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)
  2. 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)
    

  3. Однако если 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) если нужны и обычные оконные функции, то второй вариант с ними будет выглядеть неконсистентно.

Only those users with full accounts are able to leave comments. Log in, please.