Возможности PostgreSQL, которых нет в MySQL, и наоборот / forpes.ru

Главная
Возможности PostgreSQL, которых нет в MySQL, и наоборот

Возможности PostgreSQL, которых нет в MySQL, и наоборот +123

12.10.2015 07:25

varanio 307 40178 Источник

Многие боятся переходить с «мускуля» на «посгрес» из-за того, что лишь смутно понимают, что это даст. Некоторых останавливает мысль, что наверно Postgres — это слишком сложная база и требует обучения. А также, что возможно чего-то придется лишиться в связи с переходом. Попробую немного прояснить ситуацию.

Вообще говоря, если кто-то боится сложности, то для начала можно сделать как все обычно делают: «втупую» перейти с MySQL на PostgreSQL, не используя новых возможностей. SQL — он и в Африке SQL, это не rocket science. При таком переходе ничего сложного (с т.з. программирования) для вас не будет. Ну кавычки другие, синтаксис чуть строже. Т.е. использовать pg как mysql с другими кавычками для начала, а дальше учиться по ходу пьесы.

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

Начнем с недостатков посгреса, которых наверно нет в мускуле.

Чтобы работать в продакшене с посгресом, его нужно хорошенько профессионально настроить. Если вы не выставите правильно shared_buffers, настройки автовакуумов и т.д., то на серьёзных нагрузках всё будет медленно работать. Особо бесит то, что для highload-проектов практически всегда нужен еще и pgbouncer (это сторонняя разработка), который копит коннекты в очереди и следит, чтобы к самому посгресу было ограниченное число коннектов. Странно, что это не встроено в сам postgres
Автовакуумы. Если говорить по простому, то чтобы достичь высоких показателей скорости записи/удаления, посгрес оставляет кучи мусора, которые потом чистят специально обученные демоны. Если неправильно настроить автовакуумы или с дуру вообще отключить, особенно на очень нагруженной базе, то место, занимаемое таблицами будет пухнуть, и рано или поздно или забьётся всё, что может забиться, или даже без опухания, база просто может встать колом и сказать, что кончились id транзакций. На каждой конференции есть 3-4 доклада о том, как кто-то героически бился с автовакуумом и победил.
До недавнего времени не было INSERT IGNORE и INSERT ON DUPLICATE KEY UPDATE. Начиная с 9.5 появились аналоги. Очень странно, что так долго тянули с реализацией такого нужного всем функционала.
В Mysql можно прямо в запросе оперировать переменными
```
	SELECT @x:=0;
        SELECT @x:=@x+1 FROM table;
```
В посгресе такого нет, по крайней мере я не нашел (напишите, если ошибся). Ну, то есть, можно конечно сделать хранимку, где можно делать вообще всё что угодно, но вот чтобы прямо так в запросе — вроде как нет.
Нет нормального аналога phpmyadmin. По сути, большинство известных мне крутых посгресистов работают с SQL в командной строке, к чему сложно привыкнуть поначалу. Нет, есть всякие pgMyAdmin и т.д., но каждый из них имеет какую-то ущербность. Возможно платные есть хорошие, я не проверял.
Mysql все знают, postgresql никто не знает. Поэтому новые проекты часто боятся начинать на postgresql, потому что надо будет поддерживать, да и вообще боязнь неизвестного. Есть целый класс php-программистов, для которых слово “база данных” и mysql — это одно и то же, слова-синонимы. Т.е. им как-то сложно вылезти из скорлупы, что ли.
Говорят, дешевые хостинги не очень любят postgresql, потому что его сложнее администрировать. Например, чтобы создать пользователя, который может логиниться к postgres, надо делать это в двух местах: выполнить sql-запрос и прописать в pg_hba.conf

Из недостатков по сравнению с mysql пока всё. Если что-то еще знаете конкретное, что есть в mysql и чего нет в postgresql — пишите в комментариях. Теперь плюшки, которые есть у postgresql:

CTE ( Common Table Expression)

Если объяснять по-простому, то подзапросы можно записывать отдельно, давая им имена, и все это в рамках одного запроса к БД. Например
```
WITH  subquery1 AS (
      SELECT ...  
      JOIN...
      JOIN...
     GROUP BY....

),
subquery2 AS (
      SELECT ...
      WHERE ....
)

SELECT * 
FROM subquery1
    JOIN subquery 2
           ON ...
```
Крайне полезная вещь для сверхсложных запросов, где без именованных подзапросов можно сломать весь свой головной мозг, колдуя с join-ами и скобками подзапросов. Там конечно куча нюансов есть по производительности, которые надо знать, но всё равно невероятно полезная вещь. Которой нет в MySQL. Кстати, подзапросы в CTE можно использовать рекурсивно, например, чтобы получить всё поддерево в таблице вида “id, parent_id”.
Работа с ip-адресами. Например, надо быстро определить город/страну по ip-адресу.

Тут надо сказать, что в посгресе есть кастомные типы данных и даже операторы, которые с этими типами работают. Некоторые можно делать самому, некоторые можно получить, поставив расширение к посгресу. Например, есть расширение ip4r, позволяющее делать примерно так:
```
-- создадим таблицу с ip-диапазонами
create table ip_ranges (
    ip_range ip4r
);
insert into ip_ranges
values 
('2.2.3.4-2.2.3.10'),
('1.2.0.0/16');
```
Теперь мы можем получить список диапазонов, которые пересекаются с заданным ip с помощью оператора &&:
```
test=> select * from ip_ranges where ip_range && '1.2.1.1';
  ip_range  
------------
 1.2.0.0/16
(1 row)
```
До кучи там есть и другие операторы: вхожение диапазонов один в другой и др. Чтобы поиск был очень быстрым, можно построить специальный индекс GIST:
```
CREATE INDEX ip_ranges_idx ON ip_ranges USING GIST (ip_range);
```
И всё будет просто “летать” даже на огромных объемах данных. Как такое сделать в mysql не представляю, может есть какой-то способ?
Разнообразные CONSTRAINTS, т.е. ограничения самой базы данных, обеспечивающие целостность. В MySQL также есть констрейнты UNIQUE, NOT NULL, FOREIGN KEY и и т.д. Но как насчет такого:

Модифицируем таблицу из предыдущего примера:
```
ALTER TABLE ip_ranges
   ADD CONSTRAINT ip_ranges_exclude
   EXCLUDE USING GIST(ip_range WITH &&);
```
Эта запись гарантирует, что в таблице только непересекающиеся друг с другом диапазоны ip. При попытке вставить диапазон, ip которого частично уже есть в таблице, будет ругань:
```
test=> insert into ip_ranges values ('1.2.3.4/32');
ERROR:  conflicting key value violates exclusion constraint "ip_ranges_exclude"
DETAIL:  Key (ip_range)=(1.2.3.4) conflicts with existing key (ip_range)=(1.2.0.0/16).
```
Точно также можно использовать, например, тип данных circle и проверять, чтобы в таблице хранились непересекающиеся круги. Кстати, некоторые геометрические типы и операции с ними встроены прямо в стандартную поставку: circle, box, polygon и т.д.

Еще полезный constraint:
```
create table goods (
    id bigint,
   price decimal(11,2),
     …
    
    check (price >= 0.01)
)
```
И вы никогда не вставите туда случайно товар с нулевой ценой. Разумеется, условия внутри check могут быть любые.
Киллер-фича последних версий посгреса — тип jsonb, позволяющий очень быстро искать по джейсонам. Не буду подробно останавливаться, потому что в каждой второй статье про это все уши прожужжали.

Так называемые “оконные функции”. Например, надо выдать для каждого сотрудника его зарплату, и среднюю зарплату по отделу в той же строке, без использования подзапросов и group by.

SELECT 
depname, 
empno, 
salary, 
avg(salary) OVER (PARTITION BY depname) 
FROM empsalary;

  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

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

Хранимые процедуры можно писать на разных языках: чистом sql, pl/pgsql (это язык, удобный для работы с SQL-базой, но медленноват), на javascript (pl/v8), на перле и еще бог знает на чем. Вы можете даже приделать к посгресу свой любимый язык, если владеете си и достаточно усидчивы. Подробно об этом рассказывалось на pgday. На мой взгляд, в postgresql всё не так уж гладко с языками в хранимках, но всяко в 100 раз лучше, чем в mysql.
Можно делать индексы не только по полям, но и по фунциям от них.
Репликация (Hot Standby) сделана по уму. Работает быстро и консистентно.
Скорость. По моим субъективным ощущениям, а я работал много лет с обеими базами, Postgresql в целом гораздо быстрее MySQL. В разы. Как на вставку, так и на чтение. Если правильно настроен, конечно.
Особенно это проявляется при выполнении сложных запросов, с которыми mysql просто не справляется, и надо городить временные таблицы.
строгость во всём. В mysql вроде бы только в 5.7 сделали строгий режим по умолчанию (я не проверял, это действительно так?). До этого можно было вставить в поле типа decimal(5,2) число больше положеннго, и в результате молча получить 999.99. Молчаливое обрезание строк и т.д. Таких приколов там тьмы. И это поведение по умолчанию. Postgresql костьми ляжет и будет ругаться, но не будет молча выполнять двусмысленный запрос.
Транзакционность. CREATE TABLE, ALTER TABLE и т.д., как и простые запросы, можно выполнить в одной транзакции или откатить транзакцию на середине, если что-то не так. Со временем просто не понимаешь, как раньше выкручивался на mysql без этого.
Полнотекстовый поиск из коробки. Там на мой взгляд немного непривычный для нормального человека синтаксис, но всё работает и не нужно подключать сбоку сторонние примочки типа sphinx.
Последовательности (sequences). В mysql есть только AUTO_INCREMENT на поле таблицы, который тикает по одному. В postgresql этот механизм живет отдельно от таблицы, что можно использовать для самых разных потребностей, кроме того можно их создавать зацикленными
Похоже, DBA считают главным преимуществом postgresql его транзакционную машину. Транзакции там встроены глубоко и хорошо, поэтому всё работает быстро и надежно, как на вставку, так и на чтение. В mysql система другая, там есть база, и есть отдельные движки (такие как: innodb, myisam и т.д.), причем движки не все транзакционные. Из-за этого разделения с транзакциями есть некоторые проблемы. Например, myisam не транзакционен вообще, innodb транзакционен, и обе таблицы можно использовать в одном запросе. Как при этом работает база я не берусь предсказать, наверно сложно и костыльно.
Субъективно в postgresql меньше багов. Уж не знаю, как они этого добиваются, но для меня это факт — очень стабильная и надежная система, даже на больших нагрузках и объемах данных.

Это мой первый пост на хабр (песочница), так что прошу критиковать сильно, но конструктивно.

Какие еще есть конкретные преимущества и недостатки этих баз? Пишите в комментариях.

Комментарии (307)

maximw
12.10.2015 11:07
#8609481
-3
Я думаю, вместо CTE в Mysql можно использовать View. Не так гибко, конечно, но похоже по функционалу.,
1. mird
  12.10.2015 11:13
  #8609485
  +5
  Хочу посмотреть как вы через вьюхи сделаете рекурсию.
  1. maximw
    12.10.2015 11:16
    #8609491
    +1
    Ну рекурсию, да. Я же не сказал, что это полный функциональный аналог. Речь шла про удобство записи сложных запросов, и тут вьюхи могут помочь.
1. baltazorbest
  12.10.2015 19:38
  #8610501
  +3
  Не сужусь говорить как View работает в postgres, но в mysql при больших объемах данных она работает медленно… Сужу по личному опыту.
  P.S. Еще одним не большим плюсом mysql как по мне является возможность использовать конструкции вида:
  SELECT field FROM table t JOIN database.table2 t2 ON t2.column_id = t.id… Когда я пробовал постгрес я так и не увидел как можно нормально работать сразу с 2 базами.
  P.S.S. Может кто посоветует литературу для старта в изучении postgres?
  1. vsergey
    13.10.2015 11:03
    #8611237
    для запросов из нескольких баз вы можете использовать dblink
    www.postgresql.org/docs/9.3/static/contrib-dblink-function.html
    
    baltazorbest
    13.10.2015 11:07
    #8611239
    Читал про dblink, но он не так очевиден для начинающего пользователя как в самом запросе указать просто базу и таблицу. Не сужусь сказать хорошо это или плохо, просто не привычно для mysql разработчика.
    
    Fesor
    13.10.2015 11:15
    #8611261
    +2
    Как по мне не привычнее делать запросы с участием таблиц из разных баз данных.
    
    zzashpaupat
    13.10.2015 13:00
    #8611465
    +3
    В Postgres чаще используют схемы в рамках одной базы, и между ними запросы ходят отлично. А вот в MySQL отказались от схем, но зато сделали возможными запросы между базами.
    При этом в Postgres можно сделать CREATE FOREIGN TABLE и делать запросы к другому серверу как к своей таблице, с версии 9.5 будет возможность сделать IMPORT FOREIGN SCHEMA.
    Не знаю, есть ли такие возможности в MySQL, глубоко не копал.
    
    VolCh
    16.10.2015 05:55
    #8615699
    В MySQL CREATE DATABASE и CREATE SCHEMA полные синонимы.
    
    Существует FEDERATED engine, позволяющая создавать в локальной базе таблицы, ссылающиеся на таблицы в других базах (инстансах, локальных или удаленных) MySQL. Обычно по умолчанию этот движок отключен, но вообще он есть. На версии 5.1 работал, скажем так, странно, а более новые не смотрел.
  1. BlessMaster
    15.10.2015 17:22
    #8615091
    +1
    Как тут уже написал zzashpaupat, внешний источник данных можно явно объявить через CREATE FOREIGN TABLE и делать запросы к двум базам таким образом. Как приятный довесок другой базой могут быть не только базы Postgres, но, и тот же MySQL, Redis, Mongo, MemCache, простые файлы на диске — всё, на что будет написан соответствующий враппер. Гуглить FDW.
    
    Вот: http://pgxn.org/tag/fdw/, если покопаться, даже к твиттеру есть.
    
    Но вообще, запросы к двум базам — это достаточно экстремальное занятие класса «100 избранных способов прострелить себе ногу», особенно, если мы используем СУБД чуть больше, чем хранилище для статей в любимом бложике и мы рассчитываем на согласованное состояние данных под нагрузками и в распределённых системах, это хозяйство требует хорошо прокачанных скиллов уровнем не ниже совета джедаев :-) Не уверен, что «начинающему пользователю» это «ну очень надо».
    
    baltazorbest
    15.10.2015 17:34
    #8615099
    Спасибо большое за ссылки. Крутой конечно механизм внешних источников.
    
    zzashpaupat
    15.10.2015 18:52
    #8615229
    На одном из прошлых проектов, вытаскивали справочные из сторонней СУБД через JDBC и запихивали в свою БД. Теперь понимаю, что можно было бы обернуть это дело через FDW и было бы куда проще.
    
    VolCh
    16.10.2015 06:05
    #8615701
    В мускуле схема с двумя базами одного инстанса позволяет держать согласованность и т. п. обычными средствами, просто где-то в REFERENCE указываем не table3.field4, а database2.table3.field4. По сути это просто нэймспэйс, позволяющий структурировать таблицы, вьюхи и т. п.
    
    varanio
    16.10.2015 09:30
    #8615777
    Ну тогда это аналог посгресового термина «схема»
    
    VolCh
    16.10.2015 11:45
    #8615939
    CREATE DATABASE и CREATE SCHEMA в мускуле полные синонимы :)
    
    zzashpaupat
    16.10.2015 11:56
    #8615969
    Они-то синонимы, но создается при этом база, а не схема.
    
    VolCh
    16.10.2015 14:34
    #8616189
    А что считать базой, а что схемой?

Obramko
12.10.2015 11:14
#8609487
+12
> Нет нормального аналога phpmyadmin. По сути, большинство известных мне крутых посгресистов работают с SQL в командной строке, к чему сложно привыкнуть поначалу. Нет, есть всякие pgMyAdmin и т.д., но каждый из них имеет какую-то ущербность. Возможно платные есть хорошие, я не проверял.

Есть pgAdmin III. Он хорош. А визуализация EXPLAIN-а — это вообще праздник.

> Транзакционность. CREATE TABLE, ALTER TABLE и т.д., как и простые запросы, можно выполнить в одной транзакции или откатить транзакцию на середине, если что-то не так.

А вот с TRUNCATE-ом не так все хорошо, что немного неочевидно. Увы.
1. varanio
  12.10.2015 11:24
  #8609507
  +1
  угу, есть еще c enum проблемы
  1. ekho
    12.10.2015 12:23
    #8609601
    Некоторые возможно уже решены сторонними разработками: dklab.ru/lib/dklab_postgresql_enum
  1. el777
    12.10.2015 12:39
    #8609663
    +7
    Использовал его во времена mysql, на постгресе как-то обходился без него.
    На самом деле, даже лучше без него — определяйте значение констант на уровне приложения. Иначе каждый раз при добавлении значения вам нужно будет alter базы. Зачем это в высоконагруженной системе?
    
    varanio
    12.10.2015 12:47
    #8609671
    Да, мы тоже стали без него обходиться. Иногда рядом создаем таблицу-справочник, чтобы в базе было понятно, что к чему.
    
    youROCK
    12.10.2015 13:40
    #8609805
    +1
    Ну кстати в Percona (и наверное уже в MySQL) альтер таблицы с ENUM не пересобирает таблицу целиком, если вы только добавляете новое значение поля.
  1. deemytch
    14.10.2015 22:28
    #8614001
    Я как раз первый раз собираюсь их использовать в проде. Можно ли подробности?
    
    varanio
    14.10.2015 22:45
    #8614013
    невозможно добавить еще одно значение в тип внутри транзакции.
    
    test=> begin; alter type mood add value 'test'; BEGIN ERROR: ALTER TYPE ... ADD cannot run inside a transaction block
1. gubber
  12.10.2015 12:36
  #8609655
  У PgAdmin, есть один существенный недостаток — не отображает clob-ы :(.
1. elderos
  12.10.2015 12:53
  #8609691
  +3
  Есть pgAdmin III. Он хорош.
  
  Он конечно лучше, чем консоль, но в нем есть много бяк. В первую очередь синхронные гуи, которые виснут намертво при первом же потерянном пакете/таймауте.
  1. Imposeren
    12.10.2015 13:23
    #8609775
    +2
    Да, когда последний раз им пользовался через двойной ssh туннель, то всё постоянно падало при первом удобном случае )=
    
    skobkin
    13.10.2015 13:53
    #8611587
    А еще он через ssh-подключение бекап не может сделать.
1. Anarchist
  12.10.2015 17:01
  #8610175
  Есть еще ru.wikipedia.org/wiki/PhpPgAdmin
  
  Но вообще, привыкнув к командной строке, я так и не смог перейти на GUI — всегда недостаточно функционала.

Evengard
12.10.2015 11:14
#8609489
+9
Посоветуйте хорошую статью по настройке pgbouncer-а, автоваккуума и проч. пожалуйста!
1. itcoder
  12.10.2015 12:32
  #8609639
  +2
  По поводу автоваккуума, посмотрите презентации Ильи Космодемьянского, он на последнем PgDay как раз про это рассказывал
  http://pgday.ru/ru/papers/31 Так же много интересного есть в видео записях того же летного PgDay 2015. p.s надеюсь организаторы мне не сломают руку за ссылку.
  1. varanio
    13.10.2015 07:14
    #8611043
    вообще, для серьёзного проекта надо нанять кого-то типа Ильи и его команды. Они посоветуют и по серверу, и по настройкам ОС, и по базе со всеми заморочками естественно, и скажут вам в невежливой форме, если вы пишете совсем неправильно код для работы с бд.
1. BlessMaster
  12.10.2015 15:41
  #8610003
  +2
  Вообще очень рекомендую вот эту замечательную книгу: http://postgresql.leopard.in.ua/
  Помогает понять и решить очень многое.

printercu

12.10.2015 11:17

#8609493

В pg_hba можно правила для всех добавить, потом только CREATE USER будет достаточно:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

itcoder
12.10.2015 11:28
#8609513
+11
не сочтите за рекламу, но альтернатива pgMyAdmin для Postgres есть в продуктах jetBreans, панельку Database если настроить,
в запросах работает и автодополнение функций, названий таблиц и полей, скорость написания запросов можно в разы повысить + сохраняется история и все под рукой.
1. format1981
  12.10.2015 11:52
  #8609559
  +1
  Еще можно pgAdmin использовать.
1. gaelpa
  12.10.2015 12:29
  #8609625
  +2
  + фича о которой почему-то не все знают: при просмотре таблицы по F4 можно перейти от строки на связанные с ней внешними ключами (в обе стороны) строки других таблиц.
  А по Ctrl+Q открывается попап со «сводкой» в виде этой строки и связанных с ней строк.
1. franzose
  13.10.2015 01:27
  #8610941
  Кстати, вопрос по Database в PhpStorm. Как сделать, чтобы уже созданные функции в редакторе показывали полный набор возвращаемых полей (когда возвращаем таблицу) вместо «пустого» set of record? И еще в передаваемых в функцию массивах вместо _int8 чтоб стоял указанный bigint[] и т.п…
1. Honeyman
  13.10.2015 02:00
  #8610971
  +2
  JetBrains начал выносить эту «панельку» в самостоятельный продукт, 0xDBE. Пока получается неплохо.
1. PHmaster
  14.10.2015 02:11
  #8612705
  Больше всего меня порадовал инструмент сравнения схем двух баз данных. Удобно, например, по необходимости сравнивать тестовую/девелоперскую базу с продакшеном. В результате выдается список запросов для обновления схемы продакшена. Миграции для ленивых, для небольших проектов, где нет времени/средств заморачиваться со специализированными инструментами или писать свои. Не всегда, правда, все сходу срабатывает без ручной доводки (ну например, если есть поле NOT NULL без дефолтного значения), но все же мне очень помогает.

bRUtality
12.10.2015 11:38
#8609531
Про менеджеры добавлю.
Лучший из бесплатных — PgAdmin, из платных — EMS SQL Manager for PosgreSQL.
Это по моему опыту.
1. Stepanow
  12.10.2015 11:48
  #8609549
  +1
  EMS безумно глючный. По моему опыту. Но более функционального действительно ничего не попадалось.
  1. bRUtality
    12.10.2015 11:51
    #8609557
    Кроме долгой прогрузки метаданных, на вскидку других претензий к нему не припомню.
  1. zelenin
    12.10.2015 13:28
    #8609789
    +2
    а Navicat? пользуюсь — нареканий нет. (уже ниже написали)
1. Pilat
  12.10.2015 12:24
  #8609607
  EMS просто никакой. А вот https://www.dbvis.com/ вполне рабочий. Да и сам PgAdminIII неплох.
1. Ualde
  12.10.2015 12:27
  #8609621
  +1
  По-моему, из бесплатных все же лучший HeidiSQL. А из платных брали лицензию Navicat Premium.
  1. bRUtality
    12.10.2015 13:52
    #8609827
    HeidiSQL что-то виснет у меня наглухо при попытке смотреть свойства мастер-таблицы. Подобного поведения не встречал в PgAdmin, ни в ValentinaStudio.
  1. faiwer
    12.10.2015 17:17
    #8610217
    Попробовал HeidiSQL для Postgre… Постоянно падает. А новая версия так при этом ещё и теряет все свои конфиги (оО). Правда я под wine-ом запускаю. Попробовал pgAdmin… Не глючит, да. Но как им вообще пользоваться, пока не очень понимаю. Правда в postgreSQL я ещё совсем чайник.
1. Sway
  12.10.2015 13:01
  #8609719
  Лучше Navicat Premium из платных я ничего не припомню. По крайней мере в нем есть всё, что нужно и оно вполне стабильно работает, если привыкнуть к некоторым глюкам. Пробовал многие другие — намного более глючные или нет какой-то информации/функционала для продвинутого использования.
  Из бесплатных — PgAdmin. Но, на сколько помню, он не умеет соединяться через ssh
  1. Urvin
    12.10.2015 16:34
    #8610093
    Умеет, конечно же.
    
    Sway
    12.10.2015 17:05
    #8610185
    Уже умеет =) Версия, которая шла с postgresql 9.2 не имела такого функционала (только что проверил, там по теме была только вкладка SSL). С того момента я его и не использовал.

kolu4iy
12.10.2015 11:41
#8609535
+2
А есть что почитать про автовакуумы? Мне в наследство достался zabbix на postgres, так autovacuum доставил мне много истинной боли…

chulim
12.10.2015 11:56
#8609563
+8
сталкивался с такой особенностью.
в pg нельзя изменить кодировку базы. т.е. против mysql-ного
```
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
```
в Postgres нужно сделать бекап -> создать новую базу с нужной кодировкой -> залить дамп -> грохнуть старую базу.

зы, возможно отстал от жизни, и данная проблема уже решена.
1. bRUtality
  12.10.2015 12:04
  #8609575
  +6
  Увы, не решена. Ставим себе в привычку указывать utf8 при создании новой базы.
1. Sway
  12.10.2015 12:54
  #8609693
  +8
  Такая ситуация вообще не должна происходить. Поэтому разработчики postgresql и не делают этот функционал. Он нужен в единичных случаях чтобы исправить косяк того, кто базу создавал. И это правильно. Накосячил — страдай. В следующий раз думать будешь перед тем как делать.
  В постгресе вообще всё максимально строго. И это правильно.
  1. VolCh
    12.10.2015 14:18
    #8609879
    +1
    Кодировка ладно, а вот коллэйт может меняться во время жизни приложения по объективным (независящим от разработчиков) причинам, например с украинского на русский.
    
    Sway
    12.10.2015 14:24
    #8609897
    +1
    Все-равно это остается редкой задачей. Можно разок и из дампа восстановить.
    
    galaxy
    12.10.2015 17:03
    #8610181
    Ну это-то как раз есть: www.postgresql.org/docs/9.4/static/collation.html
  1. vanxant
    12.10.2015 22:07
    #8610719
    +1
    Ну хз, хз. Есть исторические базы в однобайтовой кодировке. К которой, например, прикрутили новую версию софта, теперь в юникоде. И чего теперь?
    
    PHmaster
    14.10.2015 02:15
    #8612707
    Ну типичный единичный случай. Dump-Drop-Create-Restore. Это ж не каждый день делать придется, а один раз за жизнь проекта.
  1. resurection
    13.10.2015 00:37
    #8610897
    +1
    чтобы исправить косяк того, кто базу создавал. И это правильно. Накосячил — страдай.
    
    Может и ALTER TABLE тоже убрать? Чтобы все всегда сразу создавали правильную структуру или страдали.
    
    Sway
    13.10.2015 01:22
    #8610931
    ALTER TABLE вообще-то имеет кучу разных возможностей, которые нацелены в том числе на расширение существующего функционала, а не только на изменение. Кстати, на эту тему — колонку из varchar в int в postgresql переделать нельзя. Тут тот же принцип: накосячил — страдай.
    
    Давайте на пальцах прикинем частоту использования этих конструкций и их альтернативы:
    1. ALTER DATABASE databasename CHARACTER SET enc COLLATE collation — 0.01% (хотя меньше). Для этой задачи есть альтернативный способ решения без потери данных. Трудоемкость способа небольшая — около 4х команд (дамп, удаление бд, создание новой, восстановление из дампа), хотя затраченное время сильно зависит от объема БД. Вероятно, реализация этой команды будет затрачивать аналогичное количество времени.
    2. ALTER TABLE table и т.д. — 99.99%. Альтернативный способ слишком трудоемкий для задачи, которая так часто используется.
    
    Вывод: ALTER DATABASE databasename CHARACTER SET enc COLLATE collation — очень редко используемый функционал, требующий довольно сложной реализации (если бы было все так просто — давно бы уже сделали, не из вредности же разработчики не делают его).
    Приговор: нет смысла тратить время на его реализацию.
    
    Так понятнее моя точка зрения?
    
    galaxy
    13.10.2015 01:38
    #8610951
    Кстати, на эту тему — колонку из varchar в int в postgresql переделать нельзя
    
    Можно:
    
    alter TABLE table_name ALTER COLUMN column_name TYPE int USING column_name::int;
    
    Если, конечно, данные сконвертятся.
    
    Sway
    13.10.2015 01:51
    #8610965
    +1
    Хм… Похоже, мне пора освежить свои знания о postgresql…

medvoodoo
12.10.2015 12:17
#8609589
+3
Не написали про обязательное приведение типов при работе в постресе(если чар сравнивать с интом будет ошибка, нужно делать что-то типа char_value::int = int_value)

В select id, region from table1 group by region будет ошибка, т.к. id он однозначно выбрать не может.

Две особенности, с которыми столкнулся на заре моей любви к посгрес.

p.s. Хранимки на python рулят :)
1. varanio
  12.10.2015 12:19
  #8609593
  насчет group by, вроде как в mysql 5.7 сделали такое же поведение по умолчанию

Artiomtb
12.10.2015 12:26
#8609615
+11
Одно из преимуществ postgres — наличие GIN/GIST индексов, которые позволяют искать в текстовом поле по условию like '%some text%'.

Необходимо под суперпользователем активировать расширение:
```
CREATE EXTENSION IF NOT EXISTS pg_trgm;
```
Пример создания индекса для поля product_name таблицы products:
```
CREATE INDEX products_name_index ON products USING GIN (lower(product_name) gin_trgm_ops);
```
Теперь, при запросе
```
SELECT * FROM products where lower(product_name) like '%test%'
```
будет использоваться индекс products_name_index.

По личным замерам в таблице на 10 млн строк поиск без индекса выполнялся около 2с, с индексом — 0.1 сек.

P.S. можно вообще использовать тип поля citext, который хранит без учета регистра (полезная фича для хранения e-mail и т.д.). Тогда в индексе и запросе lower вообще не нужен.

maxru
12.10.2015 12:32
#8609637
-1
Нет нормального аналога phpmyadmin.

И не нужен, есть EMS SQL Manager.
Веб-морда к БД в продакшене это что-то с чем-то.

el777
12.10.2015 12:41
#8609665
+1
Из платных клиентов еще NaviCat хорош.
1. TerminusMKB
  13.10.2015 11:47
  #8611321
  Вот для себя так и решил. Для удобной работы с данными — Navicat, для администрирования, просмотра статистики и анализа планов запросов — pgAdmin

VolCh
12.10.2015 12:47
#8609673
+1
Кроме функциональных индексов хорошая фича — частичные индексы. А уж если часто нужно выполнять запросы типа… WHERE is_active = 1 AND func(field1, field2) = value то мускул отдыхает вообще. Или когда нужно ограничение на уникальность не глобально на всю таблицу, например, номер счёта должен быть уникальным только среди выставленных и не отмененных счетов, а в черновиках и отмененных может быть любым — на мускуле такую задачу решить можно только на уровне приложения, а база не поможет.

romy4
12.10.2015 12:48
#8609677
-9
INSERT IGNORE и INSERT ON DUPLICATE KEY UPDATE

я даже на мускуле отказался от этой фичи для сохранения неразрывности последовательности AUTOINCREMENT ID

Нет нормального аналога phpmyadmin.

pgadmin отвратетиелен, но чтобы подправить что-то или сделать проверочную выборку, вполне достаточно. Для полноценной работы есть пока что ничем не заменимый Navicat
1. SamDark
  12.10.2015 13:24
  #8609777
  +9
  А зачем вам неразрывность последовательности AUTOINCREMENT ID?
  1. romy4
    12.10.2015 18:52
    #8610437
    -7
    Для предварительной генерации чего-нибудь, когда знаешь, что выбирая WHERE id > 50 LIMIT 50 у тебя точно будут id с 51 по 100.
    
    Fesor
    12.10.2015 21:31
    #8610673
    +3
    Плохая это практика, завязываться на ID. Да и потом откатываем транзакцию и мы уже потеряли следующий ID.
    
    romy4
    13.10.2015 09:18
    #8611105
    -7
    На MyISAM нет транзакций.
    
    Минусующие — идите в жопу. Не зная задачи — не понимаете, почему выбран метод из «плохой практики».
    
    VolCh
    13.10.2015 10:53
    #8611217
    Транзакции могут быть на уровне приложения.
    
    Bozaro
    13.10.2015 11:37
    #8611303
    В контекте ACID, как минимум Durability требует поддержки со стороны СУБД.
    
    В случае с MyISAM, на сколько я помню, никаких гарантий сохранности данных при внештатной остановке MySQL вообще нет.
    
    Envek
    13.10.2015 14:14
    #8611629
    +3
    До тех пор, пока вы не объясните свою задачу и не обоснуете выбор «плохой практики», вас так и будут минусовать. За необоснованный выбор «плохой практики».

antage
12.10.2015 12:52
#8609689
+2
Чтобы работать в продакшене с посгресом, его нужно хорошенько профессионально настроить

Точно такая же ситуация и с mysql. Так что весьма странно записывать это в недостатки postgresql.
1. SamDark
  12.10.2015 13:25
  #8609781
  +1
  Ну не… MySQL из коробки заводится даже на самом ужасном железе. Если взять MariaDB, так и того лучше. Тюнить если и надо, то по минимуму.
  1. zelenin
    12.10.2015 13:34
    #8609797
    +2
    postgres аналогично заводится. речь о том, чтобы выжать максимум производительности по сравнению с mysql.
    
    SamDark
    12.10.2015 13:45
    #8609813
    У меня с дефолтами не влез по памяти на довольно ущербной железяке…
    
    zelenin
    12.10.2015 13:51
    #8609823
    +2
    крутится несколько проектов на самом дешевом тарифе от digitalocean. Еще ущербнее железяка?
    
    SamDark
    12.10.2015 15:22
    #8609959
    +2
    М… нет :)
  1. antage
    12.10.2015 13:44
    #8609809
    +7
    Ну так и postgresql заводится с настройками из коробки. На низких нагрузках любая база заводится из коробки. Но автор говорил про большие нагрузки:
    
    Если вы не выставите правильно shared_buffers, настройки автовакуумов и т.д., то на серьёзных нагрузках всё будет медленно работать.
    
    А для серьезных нагрузок на mysql нужно настраивать, как минимум, key_buffer (для myisam, по дефолту 8МБ), query_cache_size (кэш запросов запрещен по дефолту), innodb_buffer_pool_size (для innodb, по дефолту 8МБ), innodb_flush_log_at_trx_commit.

mtyurin
12.10.2015 12:57
#8609699
for update skip locked

mtyurin
12.10.2015 12:58
#8609707
+2
postgresql — никому не принадлежит, и полный порядок с комунити, нет разброда с форками
1. kaamos
  12.10.2015 13:29
  #8609791
  +6
  Михаил, я этот FUD довольно часто слышу от представителей Postgres Community, но никогда не мог добиться ответа на два вопроса:
  
  1. В чём собственно заключается «разброд»? Например, общее количество дистрибутивов Linux даже подсчёту не поддаётся, но никто не говорит, что это плохо (кроме адептов BSD, конечно ;). Говорят, что это хорошо, open source, экосистема, конкуренция и вот это всё.
  
  2. Где собственно «форки» MySQL. Единственным настоящим форком является MariaDB. Всё остальное (собственно, WebscaleSQL и Percona Server) являются скорее набором расширений, т.к. 1) постоянно и регулярно объединяют upstream со своими патчами и 2) предлагают патчи для включения в upstream.
  1. mtyurin
    12.10.2015 16:52
    #8610149
    -1
    так так, вы линукс то с майсиквелем не ровняйте)
    
    разброд:
    1) отсутствие тру бинарной репликации из-за архитектуры «плагабл» движков
    2) хватит первого
    
    форки — да ну ладно, их точно более двух, плюс движки. ну и «постоянно и регулярно объединяют upstream» как-то не очень звучит
    
    kaamos
    12.10.2015 17:13
    #8610205
    +3
    Извините, но это не ответ. Вы мне сейчас пересказываете доклад Олега Царёва, я бы рад поговорить про этот доклад, но это уход от темы.
    
    Мы обсуждаем разброд форков. Значит «разброд» — это отсутствие бинарной репликации. Допустим, но я стесняюсь спросить, а причём тут форки-то? Есть форки, где эта проблема решена?
    
    И собственно форки. Мы насчитали только MariaDB, но вообще их «точно больше двух»? Про регулярное обновление, как я могу вас убедить? Вот, например, Percona Server версии x.y.z выходит после релиза MySQL x.y.z не позднее, чем через месяц. И да, в нём содержится весь функционал MySQL x.y.z + все перконовские патчи. Это достаточно убедительно?
    
    mtyurin
    12.10.2015 18:31
    #8610393
    -3
    это ответ. в субд нет бинарной репликации.
    
    далее, что мешает перконе/марии не коммитить в оракл, или ораклу не принять патч перконы/марии, или другие сочетания. вот это убедительно.
    
    kaamos
    12.10.2015 18:35
    #8610407
    +3
    Мда. Я, признаться, был лучшего мнения. Бинарная репликация есть, конечно, но дело и не в этом. В чём «разброд форков» мы так и не выяснили. Ну и ладно.
    
    PS. Кому-то не принять патч от кого-то конечно никто не мешает. Так же как Fedora не обязана быть стопроцентной копией Debian или ArchLinux. Однако почему это не проблема для Linux, но вселенская проблема для MySQL, я тоже так и не понял.
    
    mtyurin
    12.10.2015 18:51
    #8610433
    -1
    так. форков точно не два. есть как минимум oracle / maria / percona — в подавляющем числе случаев это разные ветки. при этом это разные компании — они конкурируют, их цели вообще говоря могут пересекаться, а с учетом маневров оракла, это всё выглядит так себе. эти компании в том числе двигают разные движки (еще один привет бинарному логу).
    
    далее про линукс. вы путаете прикладное и системное по. процессоров и дисков на рынке еще больше. это нельзя сравнивать так просто, считаю пример не уместным. от системного по важен «интерфейс».
    
    а бинарная репликация — просто яркая особенность, одна из.
    
    kaamos
    12.10.2015 19:04
    #8610455
    +3
    Я уже объяснил, что MariaDB — это единственный «форк» MySQL, то есть проект, который развивается независимо. Ни Percona, ни WebscaleSQL форками не являются, они жёстко привязаны к Oracle MySQL (вы посмотрите что ли в мой профиль).
    
    > далее про линукс. вы путаете прикладное и системное по. процессоров и дисков на рынке еще больше. это нельзя сравнивать так просто, считаю пример не уместным. от системного по важен «интерфейс».
    
    Ничего не понял, но пожалуйста, примеры «форков» из прикладного ПО, которые почему-то никому не мешают: OpenOffice/LibreOffice, GNU Emacs/XEmacs/Aquamacs, Konqueror/WebKit/Chrome.
    
    Это, подчеркну, примеры тру форков, то есть когда-то код был общий, но после какой-то точки проекты развиваются независимо. Чего точно нельзя сказать про Percona и WebscaleSQL.
    
    Чувствую, мне пора писать первый пост на Хабр. «Как правильно критиковать MySQL». Его есть за что критиковать, но то, как вы это делаете — хочется обнять и плакать, честное слово. Нельзя критиковать, просто повторяя где-то услышанные мантры, надо ж разбираться в предмете.
    
    mtyurin
    12.10.2015 19:44
    #8610515
    -2
    теперь вы приводите примеры текстовых редакторов и броузеров, ок. ждем постов
    
    kaamos
    12.10.2015 20:02
    #8610557
    +3
    Хорошо, у меня персонально для Вас, уважаемый Михаил, есть пуленепробиваемый пример форков. Вот, говорят, список бывших и ныне живущих «форков» и дистрибутивов PostgreSQL: wiki.postgresql.org/wiki/PostgreSQL_derived_databases
    
    В этом контексте хотелось понять: почему форки PostgreSQL — это «полный порядок с комунити, нет разброда с форками», а форки MySQL — это таки «разброд», ужас, нет бинарной репликации, патчи не примут, звучит как-то не очень, выглядит так себе, и т.д.?
    
    mtyurin
    12.10.2015 20:10
    #8610577
    -2
    тааак, отлично, вернулись к базам от текстовых редакторов.
    
    то, что вы привели — это «derived forks and rebranded distributions» это не альтернативные ветки postgres а (bsd лайк лицензия располагает). никто не купил postgres (нечего покупать) и не стал в защиту делать maria_pg.
    
    kaamos
    12.10.2015 20:16
    #8610583
    +1
    Так вот, теперь, когда мы говорим на одном языке, MariaDB — это «derived fork». А WebscaleSQL и Percona Server — это «rebranded distributions». Что с ними не так и чем они отличаются от 43 СУБД из того списка?
    
    Да, PostgreSQL не принадлежит коммерческой организации. Но дальше-то что?
    
    mtyurin
    12.10.2015 20:27
    #8610613
    > MariaDB — это «derived fork»
    
    угу, вы это им расскажите, интересный будет разговор.
    
    mariadb.org/en/about
    «MariaDB An enhanced, drop-in replacement for MySQL»
    
    и отказ от innodb
    
    kaamos
    12.10.2015 20:40
    #8610621
    +2
    Я не вижу противоречий между «derived fork», «enhanced drop-in replacement». Первый описывает происхождение проекта, второй — краткое описание самого проекта.
    
    Никакого отказа от InnoDB в MariaDB нет. Можно использовать XtraDB или InnoDB на выбор. Где вы все эти сплетни собираете?
    
    mtyurin
    12.10.2015 20:55
    #8610629
    да какие сплетни? одно делается в замену другому: не как отнаследуюсь и что-то добавлю, а как замена.
    
    kaamos
    12.10.2015 21:01
    #8610635
    +4
    Ох, XtraDB — это «derived fork» и «ennhanced drop-in replacement» InnoDB. Унаследованный, да. Это я вам как разработчик XtraDB говорю.
    
    Кстати, у меня теперь отрицательная карма, какой-то поклонник PostgreSQL постарался. Я не знаю, как здесь всё работает, но по-моему посты с отрицательной кармой писать нельзя. Печаль…
    
    mtyurin
    12.10.2015 22:39
    #8610751
    разработчик XtraDB с минусовой кармой на хабре… ) хабр — это нечто, болотце порядочное)
    
    ну ок. вы, так сказать, лицо заинтересованное, ну ладно, допустим. возможно у вас там так принято считать и «drop-in replacement» это как бы без конфликтов.
    
    но! mariadb.com/kb/en/mariadb/using-innodb-instead-of-xtradb майсикуель прекрасен конечно и вот как это идет на пользу? мы не будем за стабильность, давай rps тут, а стабильность там, и не одно с другим, а одно за место другого?!
    
    kaamos
    12.10.2015 22:52
    #8610765
    Я не очень понял вопрос. Вот есть InnoDB. И есть XtraDB = InnoDB + delta. Разработчики MariaDB предлагают выбор: по умолчанию XtraDB, но если пользователь хочет, он может перейти на ванильный InnoDB одной строчкой в конфиге.
    
    Там не написано, что InnoDB — это только стабильность, а XtraDB — это только performance. Там перечислены теоретические причины, по которым этот выбор вообще может понадобиться.
    
    mtyurin
    12.10.2015 23:21
    #8610809
    -2
    теоретические, да. тем более интересно, если это касается top critical части субд, теоретические рассуждения на этот счет. может самолет и упадет, если быстро полетим, а может и нет, сложно сказать.
    
    kaamos
    12.10.2015 23:25
    #8610815
    +1
    Ну так и самолёты падают. И в PostgreSQL баги бывают, да? Я вот попользвался PostgreSQL 5 минут и сразу нашёл баг. Правда я знал где искать ;)
    
    mtyurin
    12.10.2015 23:34
    #8610831
    > попользвался PostgreSQL 5 минут и сразу нашёл баг
    
    «самолет упал» и вы не смогли восстановиться?
    какой баг?
    
    kaamos
    12.10.2015 23:57
    #8610853
    +3
    Да вот же:
    
    test=# CREATE TABLE d1 (d FLOAT); CREATE TABLE test=# INSERT INTO d1 VALUES (1.7976931348623157E+308); INSERT 0 1 test=# SELECT * FROM d1;; d ----------------------- 1.79769313486232e+308 (1 row) test=# INSERT INTO d1 VALUES (1.79769313486232e+308); ERROR: "179769313486232000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" is out of range for type double precision
    
    Это, чтобы было понятно, я записываю правильное FLOAT число. Оно сохраняется. Но при попытке его прочитать PostgreSQL печатает его неправильно. И сам же его не может импортировать.
    
    То есть, вставил число, сделал дамп базы, а дамп потом не импортируется. Круто? :) Я в курсе про extra_float_digits — оно исправляет одни случаи, и ломает другие. Мы в MySQL это давно проходили и исправили. Поэтому я знал, где копнуть :)
    
    mtyurin
    13.10.2015 00:20
    #8610883
    > я записываю правильное FLOAT число. Оно сохраняется.
    
    www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-FLOAT
    8.1.3. Floating-Point Types
    
    это «фича», всё описано, без всяких «может упадет»
    
    www.postgresql.org/docs/9.4/static/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS
    
    this is especially useful for dumping float data that needs to be restored exactly
    
    kaamos
    13.10.2015 00:33
    #8610891
    +4
    Да я понимаю, что «документировано». Но invalid data, как ни крути.
    
    И в MySQL этот test case работает без всякого подкручивания конфига А если в PostgreSQL я выставлю extra_float_digits, то получу правильные значения для одних чисел и «мусор» для других. И как быть, если чисел у меня много?
    
    И дело тут не в точности, а в том, что разработчики PostgreSQL не очень разобрались в предмете. И их ещё много чудных открытий ждёт.
    
    mtyurin
    13.10.2015 00:42
    #8610903
    +1
    invalid data — даже сессия не упала, не то что движок! и о потери базы целиком никто даже не говорит.
    
    > PostgreSQL не очень разобрались в предмете
    
    забавно) это мне говорит разработчик базы, которая с нулами не работает и делит на 0 по-тихому. и это только то, что на первом плане
    
    mtyurin
    13.10.2015 00:44
    #8610905
    а на счет флоатов — это как бы сразу скользкая тема, хранить их в базе в таком виде, да еще и на граничных величинах — весьма сомнительное занятие.
    
    mtyurin
    13.10.2015 00:38
    #8610899
    set extra_float_digits = 3 на сессию дампа и всё работает
    
    kaamos
    13.10.2015 01:19
    #8610929
    +1
    Увы и ах, печатать больше чем DBL_DIG (15) цифр нельзя. Почитайте определение DBL_DIG. То есть, extra_float_digits — это костыль, чтобы исправить один случай и сломать другие.
    
    mtyurin
    13.10.2015 01:49
    #8610959
    что же там ломается?
    
    а дамп по дефолту в pg юзает доп цифры
    http://www.postgresql.org/message-id/flat/20090909185152.GA7893@kehcheng.Stanford.EDU#20090909185152.GA7893@kehcheng.Stanford.EDU
    
    вот пример комунити — написал, разобрали
    
    kaamos
    13.10.2015 09:33
    #8611127
    Ну, то есть pg_dump вовремя подставляет костыль. Что конечно замечательно, но:
    
    1. невалидные данные с extra_float_digits остаются невалидными данными
    2. костыль остаётся костылём (подробнее ниже, там ещё один товарищ жаждет объяснений)
    3. одними дампами проблема не ограничивается. Ну, например, с дефолтным extra_float_digits:
    
    est=# create table d2(a float8, b float8); CREATE TABLE test=# insert into d2 values (2, 2.0000000000000004); INSERT 0 1 test=# select * from d2; a | b ---+--- 2 | 2 (1 row) test=# select a = b from d2; ?column? ---------- f (1 row)
    
    Может для PostgreSQL это нормально, но MySQL себе такого не позволяет.
    
    > вот пример комунити — написал, разобрали
    
    Мне вот интересно, вы действительно считаете, что в MySQL community нет списков рассылок, форумов и прочего, где пользователям отвечают на вопросы?
    
    galaxy
    13.10.2015 02:31
    #8610981
    То есть, вставил число, сделал дамп базы, а дамп потом не импортируется. Круто? :)
    
    Тестировали или умозрительно злорадствуете? Все импортируется.
    А здесь тоже тупой постгрес виноват:
    
    postgres=# select 2.1::real - 2; ?column? -------------------- 0.0999999046325684
    
    ?
    
    Мы в MySQL это давно проходили и исправили
    
    Ну расскажите нам, как и что вы исправили
    
    kaamos
    13.10.2015 10:12
    #8611173
    +2
    Ну расскажем, чего уж там.
    
    > Тестировали или умозрительно злорадствуете? Все импортируется.
    
    Да, мы с Михаилом уже выяснили, что pg_dump подставляет костыль. Но всё сложнее, см. ниже.
    
    > А здесь тоже тупой постгрес виноват:
    
    Нет, здесь виноват злой стандарт IEEE 754, который не имеет точного бинарного представления для десятичных чисел 2.1 или 0.1. Соответственно, мы видим округлённую бесконечную двоичную дробь. Причём в силу потери точности результат для ::real выглядит хуже, чем для ::float8. Но это речь не об этой проблеме.
    
    Проблема заключается в том, что стандарт IEEE 754 (точнее, выбранный формат данных) определяет максимальное число значимых цифр для каждого типа данных, для которых гарантируется преобразование без потерь из десятичного (строкового) представления во внутреннее (бинарное) и обратно. Для double precision aka float8 это 15 значимых десятичных цифр (константа DBL_DIG). Для чисел с бОльшим количеством значимых цифр преобразование туда-обратно может быть без потерь, а может быть и с потерями — это зависит от числа и деталей реализации, стандарт ничего не гарантирует.
    
    Вот когда PostgreSQL работает DBL_DIG (т.е. с extra_float_digits=0), то он может печатать невалидные данные в разных граничных случаях, или просто работать неинтуитивно с точки зрения приложений, как я показал на примерах.
    
    А когда он начинает печатать все числа с DBL_DIG + extra_float_digits, он нарушает стандарт. Это может выражаться как в относительно безобидных вещах, типа:
    
    test=# select 0.3::float8; float8 ---------------------- 0.299999999999999989
    
    (технически ответ верный, но мог бы показать более короткое эквивалентное представление: 0.3)
    
    или
    
    test=# select .1::float8; float8 ---------------------- 0.100000000000000006 (1 row)
    
    (здесь ответ верный до DBL_DIG значимых цифр, но потом идёт «мусор» в виде 006)
    
    Кстати, вот этот «мусор» за пределами 15 значимых цифр имеет неопределённое содержимое, т.е. зависит от реализации, ОС, версии libc, процессора и флагов компиляции.
    
    Этот мусор может иметь и более опасные последствия. Поскольку стандарт не гарантирует преобразование без потерь при печати чисел с числом десятичных цифр > DBL_DIG, данные могут искажаться при экспорте/импорте. Например, сделали дамп гео данных на Linux/Intel, а импортировали на Solaris/Sparc. Данные «чуть-чуть» уплылили. А потом ещё «чуть-чуть». А потом самолёты начинают падать ;)
    
    > Ну расскажите нам, как и что вы исправили
    
    MySQL для чисел с плавающей точкой работает так:
    
    1. Выбирает наиболее короткое представление («0.3» напечатается как «0.3», а не как «0.299999999999999989»)
    2. Избегает «мусора», т.е. любая напечатанная цифра является значимой и не зависит от ОС/libc/процессора и т.д. («0.1» напечатается как «0.1»
    3. Автоматически выбирается «безопасное» для преобразования без потерь количество десятичных цифр. Нет никаких костылей типа extra_float_digits. Если число напечатано, значит MySQL сможет его считать обратно при любой конфигурации, ОС, libc, процессора и флагов компиляции.
    
    Ну как-то так.
    
    Vayun
    13.10.2015 11:01
    #8611229
    Этот мусор может иметь и более опасные последствия. Поскольку стандарт не гарантирует преобразование без потерь при печати чисел с числом десятичных цифр > DBL_DIG, данные могут искажаться при экспорте/импорте.
    Стандарт гарантирует минимум 3 лишних цифры, но рекомендует не иметь таких ограничений вообще.
    
    Мусор никому не мешает, до тех пор пока «Conversion to an external character sequence must be such that conversion back using round to even will recover the original number.»
    
    kaamos
    13.10.2015 11:27
    #8611281
    +1
    > Мусор никому не мешает, до тех пор пока «Conversion to an external character sequence must be such that conversion back using round to even will recover the original number.»
    
    О том о речь. «До тех пор, пока», то есть undefined behavior, на которое PostgreSQL расчитывает при экпорте/импорте. И где-то как-то оно работает, пока не случится ой.
    
    Vayun
    13.10.2015 12:02
    #8611347
    «До тех пор, пока» это часть стандарта, так что никакого undefined behavior нет.
    
    Разница в том что если мы хотим видеть только достоверные десятичные цифры, то выводить надо DBL_DIG=15 цифр. Но в таком случае одно десятичное представление может иметь несколько бинарных аналогов.
    
    Если мы хотим однозначное соответствие между бинарным и десятичным представлениями, то выводить надо DBL_DIG+2=17 цифр.
    
    Никакого undefined behavior, все по стандарту, просто значение по умолчанию реализует одно поведение, если надо другое ставим extra_float_digits=2 и все.
    
    kaamos
    13.10.2015 12:47
    #8611445
    > Разница в том что если мы хотим видеть только достоверные десятичные цифры, то выводить надо DBL_DIG=15 цифр.
    
    Неверно. Для некоторых чисел можно, а иногда нужно выводить больше DBL_DIG цифр. См. исходный пример. А для некоторых чисел этого делать нельзя.
    
    > Если мы хотим однозначное соответствие между бинарным и десятичным представлениями, то выводить надо DBL_DIG+2=17 цифр.
    
    Не существует никакого однозначного соответствия для DBL_DIG+2=17 цифр. А PostgreSQL вообще использует DBL_DIG+3=18. Оно впрочем и для DBL_DIG не существует, это не о том.
    
    > Никакого undefined behavior, все по стандарту, просто значение по умолчанию реализует одно поведение, если надо другое ставим extra_float_digits=2 и все.
    
    Нет, не всё. Почитайте определение FLT_DIG/DBL_DIG. И ещё почему PostgreSQL сначала использовал extra_float_digits=2, а потом вдруг стал extra_float_digits=3 при экспорте. Тут ссылка уже была.
    
    Vayun
    13.10.2015 13:39
    #8611545
    Не существует никакого однозначного соответствия для DBL_DIG+2=17 цифр. А PostgreSQL вообще использует DBL_DIG+3=18. Оно впрочем и для DBL_DIG не существует, это не о том.
    Согласен, «однозначное соответствие» неудачно сказано. Имелось ввиду что из DBL_DIG+2=17 цифр можно однозначно получить оригинальное бинарное преставление (десятичное представление в этом случае не однозначное). Для double разницы между 17 и 18 нет (IEEE754 гарантирует правильное округление до 20 цифр включительно)
    
    Нет, не всё. Почитайте определение FLT_DIG/DBL_DIG. И ещё почему PostgreSQL сначала использовал extra_float_digits=2, а потом вдруг стал extra_float_digits=3 при экспорте. Тут ссылка уже была.
    
    Использует extra_float_digits=3 потому что FLT_DIG=6, а для однозначной конвертации надо 9 (что есть в IEEE754 кстати). По-хорошему нужны отдельные extra_float_digits для double и float.
    
    В общем, в Postgres решили что длина представления должна быть фиксированной. Поэтому она получается разной в зависимости от того хотим мы видеть только верные десятичные цифры (DBL_DIG) или хотим иметь возможность восстановить оригинальное бинарное представление (DBL_DIG+2). Оба свойства одновременно можно получить только используя представления переменной длины.
    
    Фиксированная длина, это возможно не оптимальное решение, и защищать я его не собираюсь. Моя критика была направлена на необоснованное использования термина «undefined behaviour», этого в Postgres нет.
    
    kaamos
    12.10.2015 23:05
    #8610779
    И отдельным комментом про подключаемые движки (раз пост мне не дают написать). Это безусловно усложняет архитектуру, но есть и плюсы. Разные движки заточены под разные цели, нельзя иметь один единственный «православный» движок, который оптимален на любых нагрузках.
    
    Например, сильной стороной InnoDB как и всех движков на B-Tree (включая PostgreSQL) является primary key lookups на полностью закешированных данных. На таких нагрузках вряд ли PostgreSQL сможет соревноваться с InnoDB, хотя было бы интересно проверить.
    
    Слабая сторона B-Tree движков — интенсивная запись в базу, особенно когда dataset не умещается в память. Особенно когда много индексов, или нужна компрессия. Для таких нагрузок какой-нибудь TokuDB покажет результаты, которые не снились ни InnoDB, ни PostgreSQL.
    
    Ещё раз, тезисно: 1) нет идеального способа хранить данные; 2) MySQL предлагает варианты с помощью движков, PostgreSQL — нет. 3) да, возможность иметь подключаемые движки имеет свою цену. Архитектура усложняется, сервер не всегда может «срезать углы», как если бы он работал с одним движком. Да, приходится дублировать журналы. Нет, это не так плохо, как рассказывает Олег в презентации :)
    
    varanio
    12.10.2015 23:29
    #8610823
    я не очень понимаю, при чем тут движки. Postgres не предлагает варианты с помощью движков, но он предлагает кучу видов индексов, не только b-tree (еще Hash, GiST, SP-GiST, GIN)
    К примеру, для задачи, где нужно много записи и индексы занимают много места — в 9.5 вводят индекс нового типа BRIN. Почти бесплатен при записи, но хуже на чтении.
    Кроме того, насколько я понимаю, в посгресе можно кластеризовать таблицу под конкретный индекс. Это то, что в innodb сделано для primary key
    
    kaamos
    12.10.2015 23:42
    #8610839
    +1
    Ну разные типы индексов — это только часть решения. В MySQL/InnoDB тоже предлагает специализированные индексы, хоть и в меньшем количестве.
    
    Hash, GiST, GIN вам никак не помогут при интенсивной записи. Если BRIN помогает — хорошо, но вот TokuDB умеет ещё компрессию. Причём алгоритмы компрессии (surprise!) тоже pluggable. Хочется сильнее сжимать но медленние — есть zlib. А хочется слабее, но быстрее — есть quicklz. А можно вообще свой алгоритм добавить. Как-то так. Это я ещё не касаюсь сильно специализированных движков типа NDB.
    
    По поводу кластерных индексов — если я правильно читаю документацию, в PostgreSQL это «одноразовая» операция. Можно кластеризовать готовые данные, но новые данные будут храниться опять как попало. В InnoDB данные кластеризованы всегда. А в TokuDB вообще можно иметь несколько кластерных ключей одновременно. Опять же, такое никак не сделать добавлением нового типа индекса, да?
    
    mtyurin
    13.10.2015 00:04
    #8610863
    ну в целом понятно, удел движков — кеши, ок
    
    mtyurin
    12.10.2015 23:31
    #8610829
    бррр… само по себе «подключаемое» это очень хорошо. ( постгрес весь максимально плагабл, но без ущерба (как минимум, без отсутствия контроля над) acid-у в узле.)
    
    но блин! если вы при этом получаете ограничение на утилизацию железа, тем что реплика не успевает проигрывать транзакциии! как же это если, это не ужасно?
    
    потом, давайте тут не будем сфеерическими тестами, я видел сотни тысяч rps на postgres и полную утилизацию железа. а майсиквел не может утилизировать мастер — реплика не поспевает. так какая мне разница какой бенч тогда на одном узле, надо бенчить скорость реплкики.
    
    kaamos
    12.10.2015 23:47
    #8610843
    +6
    Давайте я уже перескажу доклад Олега, чтобы мы больше к нему не возвращались:
    
    — в базе данных живут гномики;
    — в огороде бузина, а в MySQL — binary log
    — у нас в проекте тормозила репликация
    — у нас в проекте репликация CPU-bound, а значит у всех должна быть такой
    — мы взяли альфа-версию MySQL 5.7, которая должны была решить наши проблемы
    — но она не решила
    — почему — мы так и не разобрались
    — вопросы?
    
    Нет, правда.
    
    mtyurin
    12.10.2015 23:54
    #8610851
    да, всё верно. печально. и то что мария вместо решения всего этого предлагает еще больше rps (хотя cpu уже и так нет) но при этом теоретически больше краша (а еще страшнее, что без возможности восстановления) я называю «разброд»
    
    kaamos
    13.10.2015 00:02
    #8610861
    Я к тому, что из того доклада вообще ничего не понятно. Ни в чём конкретно была проблема, ни что было сделано для решения.
    
    mtyurin
    13.10.2015 00:06
    #8610869
    ничего не было сделано, где комунити? (разброд: это не мое, то не сё, а там оракл, поддержки нет, все хотят денег) у человека одна из крупнейших в европе систем была.
    
    kaamos
    13.10.2015 00:14
    #8610875
    +1
    А я откуда знаю, где комунити? С крупнейшей-то в европе системой можно было и у Перконы поддержку купить. Но нет
    
    mtyurin
    13.10.2015 00:29
    #8610887
    вот именно, хрен кто что будет развивать. плати бабки — вот это и разброд, другой подход к снаряду, люди по другому видят развитие программного продукта и место коммунити в нём.
    
    а по факту ничего бы перкона бы не сделала, деньги бы взяли, да, и что-то бы даже переделали (tokudb там еще муть какую-нибудь, чтобы все концы спрятать ), но репликация бы так и не работала. потому, что всё, мы так зарабатываем.
    
    kaamos
    13.10.2015 00:38
    #8610901
    Ну это уже какие-то фантазии пошли. «бы», да «кабы». Несерьёзный разговор.
    
    mtyurin
    13.10.2015 00:45
    #8610907
    а какой может быть серьезный, если его и не может быть… так как полноценного открытого коммунити нет
    
    kaamos
    13.10.2015 00:54
    #8610909
    Михаил, вы сначала вот сюда гляньте: http://db-engines.com/en/ranking
    
    А потом возвращайтесь, поговорим про community.
    
    mtyurin
    13.10.2015 00:59
    #8610911
    а вы потом удивляетесь, почему у вас на хабре минус в рейтинге? там плюс, тут минус)
    
    mtyurin
    13.10.2015 01:05
    #8610919
    +1
    как раз вы начали монгу щас встраивать, всё в тренде)
    
    mtyurin
    14.10.2015 11:39
    #8612975
    вы мне сначала задаете вопрос про списки рассылок, при этом потом удивляетесь, что комьюнити не подоспело и предлагаете за денежку что-то купить взамен.
    
    дак вот. и у кого мне покупать: перкона / оракл? и в чьи листы мне писать: мария / перкона / оракл / монга / токудб / etc etc? где ядро разработчиков сидит, в каком листе, у оракла или у марии?
    
    далее, что мне там обсуждать: как ФЛОАТЫ на РАВЕНСТВО = сравнивать или что бинарная репликация sql тредом обрабатывается?
    
    вот и думаю…
    
    kaamos
    14.10.2015 11:51
    #8613005
    +2
    Да напишите хоть в какой-нибудь уже.
    
    А так да, есть несколько компаний, которые поддерживают списки рассылки и форумы. Причём, в список рассылки Percona или MariaDB можно задавать вопросы не только по продуктам Перконы, но и по Oracle и MariaDB. И в этих списках отвечают на вопросы абсолютно бесплатно. Я лично отвечал.
    
    А ещё есть локальные группы пользователей. Например, московская. Со своим списком рассылки, где не очень большой трафик, но на вопросы отвечают довольно оперативно. Я лично отвечал тоже, да. Безвомездно, то есть даром.
    
    А теперь главный вопрос: в каком из списков рассылки можно почитать описание проблемы Олега? Ах, ну да, он же никуда и не писал. Но чему тогда удивляться?
    
    svetasmirnova
    14.10.2015 23:03
    #8614031
    +1
    А теперь главный вопрос: в каком из списков рассылки можно почитать описание проблемы Олега? Ах, ну да, он же никуда и не писал. Но чему тогда удивляться?
    
    Строго говоря проблема Олега довольно известна. Другое дело, что в чистом виде она встречается не так часто. Однако в поддержке мы подобное видим достаточно часто.
    
    Что мы советуем для текущих версий?
    
    Во-первых, единственный параметр, который не может здесь играть роли — это innodb_thread_concurrency. Все остальные performance-related параметры вполне tunable.
    
    Далее, если слейв read-only, можно отключить innodb_doublewrite_buffer, sync_binlog, xa транзакции, поставить innodb_flush_log_at_trx_commit в 0 и сделать ещё ряд подобных вещей, которые, строго говоря, не рекомендуются, так как могут привести к потере данных. Но так как данные у нас есть где-то ещё (а бинарные логи ~~нужно~~можно бэкапить при помощи mysqlbinlog --raw ..., а не рабочего слейва) — это можно пережить.
    
    Плюс есть ещё параметры типа innodb_io_capacity, которые нужно ставить в то, что написано в мануале (реальную скорость диска), а не от балды и innodb_flush_method, который таки лучше потестировать в конкретном окружении после того, как его выставили в O_DIRECT
    
    Это для слейва, с которого не читают.
    
    Для слейва, с которого активно читают, innodb_thread_concurrency тоже будет работать. Для запросов на чтение.
    
    kaamos
    14.10.2015 23:45
    #8614095
    Света, объясни мне, пожалуйста, как всё вышеперечисленное может повлиять на CPU-bound нагрузку, которую здесь неоднократно упомянули?
    
    svetasmirnova
    15.10.2015 01:02
    #8614185
    Параллельности тут не выжмешь. Зато можно снизить нагрузку на CPU. Те же disk writes — они требуют CPU. То есть будет работать также одно ядро, но будет работать меньше.
    
    kaamos
    14.10.2015 11:58
    #8613029
    > далее, что мне там обсуждать: как ФЛОАТЫ на РАВЕНСТВО = сравнивать или что бинарная репликация sql тредом обрабатывается?
    
    флоаты на равенство сравнивать можно. результаты вычислений нельзя. это вы слышали звон, но не очень в курсе где он.
    
    Вы хоть книжки почитайте по теме. Вот эта хорошая: «What Every Computer Scientist Should Know About Floating-Point Arithmetic, by David Goldberg».
    
    mtyurin
    14.10.2015 13:12
    #8613171
    > флоаты на равенство сравнивать можно. результаты вычислений нельзя. это вы слышали звон, но не очень в курсе где он.
    
    а как мне понять, какой флоат более результат, чем другой)
    
    и как там всё таки про рассылки, где правильная, можно всё таки пояснить? гугл мне как минимум дает майсиквеловские и мариишные листы
    
    kaamos
    14.10.2015 13:37
    #8613239
    > а как мне понять, какой флоат более результат, чем другой)
    
    вас вот что смутило в моём примере. PostgreSQL показывает два _разных_ бинарных флоата как одинаковые числа. Поэтому сравнение выдаёт 'false', что неинтуитивно. MySQL разные _бинарные_ числа всегда показывает разными. Поэтому и результат сравнения вопросов не вызывает.
    
    > и как там всё таки про рассылки, где правильная, можно всё таки пояснить? гугл мне как минимум дает майсиквеловские и мариишные листы
    
    Ну кому вы больше доверяете, туда и пишите. Возвращаясь к примеру с дистрибутивами Linux, вот есть у вас проблема с Linux kernel. Можно написать в LKML. А можно в список рассылки своего дистрибутива. А можно в местную группу пользователей. Или везде сразу.
    
    mtyurin
    14.10.2015 17:19
    #8613667
    > PostgreSQL показывает два _разных_ бинарных флоата как одинаковые числа
    
    чтобы понять, что значения «одинаковые» их надо сравнить. «одинаковость» — это не текстовое представление глазами сверять. ох, как с вами тяжело.
    
    когда мы работаем с такими сложными объектами как флоаты, ни о какой интуитивности (они на то и сложные, что не интуитивные) речи быть не может. особенно, когда эти флоаты поедут дальше и потом приедут обратно.
    
    > Ну кому вы больше доверяете
    
    kaamos
    14.10.2015 17:25
    #8613685
    > чтобы понять, что значения «одинаковые» их надо сравнить. «одинаковость» — это не текстовое представление глазами сверять. ох, как с вами тяжело.
    
    Весь пример демонстрировал, что PostgreSQL показывает одинаковые текстовые представления для разных бинарных чисел, а MySQL так никогда не делает. Ничего больше, ничего меньше. Все остальные следствия из этого простого факта вы выдумали сами и спорите сейчас с воображаемым собеседником.
    
    mtyurin
    14.10.2015 17:31
    #8613695
    он показывал, что они разные
    select a = b from d2;
    ?column?
    f
    
    false
    
    kaamos
    14.10.2015 17:38
    #8613707
    «select a = b» сравнивает бинарные представления. А текстовые представления в PostgreSQL одинаковые. А в MySQL бинарные числа разные, и текстовые представления разные. Так понятно?
    
    mtyurin
    16.10.2015 00:21
    #8615593
    да, одинаковые. надеюсь вы не всерьёз обсуждаете сравнение флоатов по текстовым представлениям. DBL_DIG, мы на второй круг. про «интуитивно для приложения» это вы рассказывайте кому-то другому.
    
    kaamos
    16.10.2015 10:35
    #8615825
    Вы лучше попробуйте ответить на вопросы зачем вообще нужен extra_float_digits и почему extra_float_digits=3 не включен по умолчанию, если это единственный режим, в котором PostgreSQL печатает корректные значения. Для себя ответить, не для меня. Я ответ знаю.
    
    mtyurin
    16.10.2015 11:54
    #8615961
    ок! за вас можно только порадоваться.
    
    а для тех, кто еще сомневается в своих знаниях, рекомендую раздел из одной, пожалуй лучшей в мире, документации:
    http://www.postgresql.org/docs/9.4/static/datatype-numeric.html#DATATYPE-FLOAT
    
    и выдержка про «inexact»
    
    The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.
    
    Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies.
    
    ну и смысл: связались с флоатами — вы значит крутые уже — сами знаете, что делаете, НЕТ там ничего «интуитивного» и всё может зависеть
    
    kaamos
    16.10.2015 12:13
    #8615989
    Да. Только это всё не о том, и никак не отвечает на заданные мною вопросы.
    
    mtyurin
    14.10.2015 17:25
    #8613687
    > Ну кому вы больше доверяете
    
    речь не о доверии, я может ваще ни кому не доверяю. речь о потенциальных конфликтах и невозможности смержить знания и вижин.
    
    а по поводу линукса — я просто беру lts ядро и дебиан, и воспринимаю это как то, что надо резервировать. известных багов fsync за всё время использования не наблюдал.
    
    zabivator
    14.10.2015 12:58
    #8613135
    +2
    Алексей, спасибо за краткий пересказ моего доклада, я буду это цитировать.
    
    kaamos
    14.10.2015 13:07
    #8613157
    Всегда пожалуйста. Прошу не рассматривать это как личный наезд, а как замечание к дальнейшему его улучшению. Текущую версию я слышал два раза и к сожалению это всё существенное, что я смог из неё вынести.
    
    zabivator
    14.10.2015 13:13
    #8613175
    Ну, если так, то странно почему не упомянут разбор различных типов журналов (логический/физический), их компаративный анализ, а также бенчмарк MySQL 5.7
    
    В причинах тормозов не разобрались — вы правы, однако у меня задачи в проекте стояли несколько отличные от «починить mysql».
    И я до сих пор очень хочу увидеть исследование LOGICAL_CLOCK в 5.7, которое:
    — показывает существенный прирост производительности репликации
    — указано железо и настройки
    — указаны числа в эксперименте
    
    Сейчас 5.7 выглядит странно — вроде есть крутая фича, которая должна давать практически линейный прирост, но по факту я его даже измерить не смог
    
    svetasmirnova
    13.10.2015 21:11
    #8612429
    Реплика не поспевает не из-за движков, а потому что по умолчанию у неё только один SQL thread. Соответственно InnoDB и прочие не могут полностью утилизировать CPU (при обработке одного лишь SQL thread-а).
    
    mtyurin
    14.10.2015 11:17
    #8612925
    вот я всё понимаю (на самом деле, видимо больше, чем говорю тут), но КАК могут быть связаны бинарная репликация с sql и cpu. оно потому и бинарная, что не sql.
    
    и как я понимаю — такая «особенность» — есть следствие многодвижковости в том числе
    
    svetasmirnova
    14.10.2015 11:37
    #8612969
    Я так понимаю, что вы под «бинарной» репликацией имеете в виду, что реплицируется transaction log движка? Как если бы MySQL репликация была построена на InnoDB redo log file (чего нет). С CPU этот вариант, кстати, тоже будет связан. Потому что transaction log нужно будет как-то передать по сети, записать на диск (CPU!) и в каком-то порядке apply (ага, в ту же многопоточность и возможность её реализации упираемся).
    
    В MySQL же отдельный независимый binary log. Я затрудняюсь сказать почему именно так в своё время реализовали. С моей точки зрения это, скорее, плюс, потому что делает репликацию независимой от реализации формата хранения данных. Можно, наверное, и на многодвижковость всё списать (которые тогда были ещй не-pluggable). Или на то, что много лет назад, когда встроенная репликация в MySQL была, а для PostgreSQL существовали только third-party решения, основным движком был MyISAM, у которого нет transaction log вообще.
    
    Но вообще смотрите dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_parallel_type Лучше в 5.7, в 5.6 поддерживается только DATABASE.
    
    mtyurin
    14.10.2015 12:40
    #8613105
    Светлана, в pg бинарный проигрыватель wal логов на реплике, по факту, не дороже бинарного писателя на мастере, так как и то и то делает суть одно и то же но в разных порядках:
    
    мастер: пишет страницы в куче во много потоков, НО при этом со всякими задержками на локах и логике (я про локи и логику на уровне движка таблиц/индексов и уровня sql); потом в один поток (особенности синхронизации, см WALInsertLock) в журнал
    слейв: пишет в журнал, потом в кучу — всё в один поток, НО! уже без уровня таблиц/индексов/sql
    
    svetasmirnova
    14.10.2015 21:14
    #8613939
    > слейв: пишет в журнал, потом в кучу — всё в один поток, НО! уже без уровня таблиц/индексов/sql
    
    А что происходит, если какой-то параллельный процесс на слейве будет читать или писать из/в тех же таблиц?
    
    mtyurin
    15.10.2015 17:04
    #8615065
    слейв всегда ридонли. могут быть ожидания со стороны проигрывателя wal логов, он может ждать читающий трафик, но «размер» допустимой «задержи» жестко задается в конфиге, приоритет отдается проигрывателю, читающий запрос получит исключение о прерывании (сбросе).
    
    при это есть также возможность внутри репликационного протокола мастеру получать фидбек от слейвов о читающих на слевах запросах. в таком случае мастер не будет вакуумить то, что еще может быть потенциально интересно на слейвах. включение фидбека и задержки в этом случае также конфигурируются.
    
    svetasmirnova
    15.10.2015 20:23
    #8615331
    слейв всегда ридонли
    
    Ну вот и ответ. В MySQL слейв всегда read-write. Даже когда включена опция read-only суперпользователю разрешается писать.
    
    В принципе можно написать feature request на bugs.mysql.com, чтобы добавили подобную оптимизацию для real read-only слейвов. (Типа как read-only транзакции сделали в 5.6) Проблема только в том, что большинство пользователей MySQL пишут на слейв =(
    
    А сейчас я ниже писала как повысить производительность единственного thread-а. Дополнительно можно понизить transaction isolation level и принудительно включить read only транзакции на слейве для всего, а не только для single-statement select (что по-любому read only в 5.6).
    
    zabivator
    14.10.2015 13:01
    #8613143
    Света, самое печальное — я до сих пор не видел ни одного исследования slave_parallel_type=LOGICAL_CLOCK, где было бы существенное улучшение производительности, и где были бы приведены числа и настройки.
    
    Т.е. девушка есть, но мы вам её не покажем.
    
    kaamos
    14.10.2015 13:13
    #8613173
    Так может это потому, что MySQL 5.7 ещё как бы не вышел? GA версия появится скорее всего через пару недель на Oracle OpenWorld. Как обычно какого-то широкого использвания не стоит ждать в течение года. А вот по-настоящему серьёзных исследований нужно ждать, когда на 5.7 начнут переходить тяжеловесы типа Facebook/Twitter/LinkedIn/Dropbox.
    
    zabivator
    14.10.2015 13:19
    #8613191
    Ну, пусть так. Только что делать с CPU-bound репликацией на нагруженных проектах — вопросов остаётся открытым
    Да, я знаю workaround'ы — записать general query log, построить по каждому типу запроса сводную статистику average response time и total time, отранжировать и исправить/убрать запросы
    Понятно, что можно базу распилить.
    Но это выглядит немного эээ странно, — у мастера ещё большой запас по CPU и IO, а реплика уже сдохла.
    
    Не было бы этой проблемы — не было бы моего доклада. И судя по фидбеку после доклада — многие с этим наелись, и как решать — никто не понимает
    
    kaamos
    14.10.2015 13:42
    #8613251
    Я там ниже написал про read-free replication. Надо сказать, я удачно зашёл на Хабр. Интересно, есть вообще темы в MySQL, которые в этом посте про PostgreSQL ещё не обсудили? :)
    
    svetasmirnova
    14.10.2015 23:03
    #8614033
    Я тут немножко про другие воркэраунды написала: habrahabr.ru/post/268631/?reply_to=8613105#comment_8614031
    
    svetasmirnova
    14.10.2015 21:20
    #8613945
    В принципе можно будет потестировать. ~~Или попросить кого-нибудь потестировать.~~ Если не забуду: опубликую результаты.
    
    Bozaro
    14.10.2015 11:44
    #8612987
    +3
    Я попробую объяснить.
    
    В MySQL используется логическая репликация: изменения записываются в лог репликации (в случае statement — сами запросы, в случае row-based — изменения записи).
    Далее на стороне реплики нужно:
    
    для statement — выполнить этот запрос;
    
    для row-based — найти эту запись по ключу и поменять.
    
    Поиск по ключу и выполнение запроса — дорогое удовольствие — отсюда лишняя нагрузка на IO и CPU.
    
    Плюс ко всему прочему, нужно все транзакции выстраивать в очередь — отсюда однопоточность.
    
    В PostgreSQL используется физическая репликация: на реплику передаются изменения страниц из лога транзакций.
    
    При этом несколько транзакций могут схлопываться в одну при групповом коммите (https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-group_commit.html, wiki.postgresql.org/wiki/Group_commit).
    
    Далее на стороне реплики нужно просто записывать эти страницы. Реплика даже не знает, что за данные поменялись — для неё это просто набор байтов.
    
    Недавно в PostgreSQL так же появилась логическая репликация для реализации мастер-мастер реплики.
    
    Сделать же физическую репликацию в MySQL нельзя, так как на уровне движка лога транзакций не существует (он может есть в хранилище InnoDB и отсутствует в MySQL, но движок о нем не знает).
    
    mtyurin
    14.10.2015 11:52
    #8613013
    ну вот я и не понимаю, как серьезные ребята выдают поиск по b-tree за бинарную репликацию. и потом быстро переходят на сравнение флоатов (флоатов!) на равенство =
    
    samokhvalov
    13.10.2015 01:51
    #8610967
    +3
    Алексей, привет. Отвратительно здесь всё работает. Хотел поднять карму — но, видите ли, «нельзя голосовать за пользователей, у которых нет размещённых публикаций».
    
    galaxy
    13.10.2015 02:38
    #8610983
    ТМ веселые правила жизни выдумывает. Ему, видимо, в песочницу надо писать.
    
    kaamos
    13.10.2015 09:39
    #8611133
    +1
    Николай, говорят можно в песочнице создать пост. Спасибо, что пытались помочь! :)
    
    varanio
    13.10.2015 09:42
    #8611135
    я тоже пытался плюсануть карму и не смог. Пишите в песочницу, я вам плюсану после этого.
    
    vlivyur
    13.10.2015 10:36
    #8611197
    Recovery mode
    
    ksm
    14.10.2015 01:29
    #8612673
    +1
    Так-то RBR репликация в MySQL как раз «тру бинарная». Другой вопрос, что не всем она подходит и стейтмент репликация может эффективнне с точки зрения нагрузки на сеть, размеры бинлогов и т.д.
    
    mtyurin
    14.10.2015 11:23
    #8612941
    -1
    даже не вдаваясь в подробности (куда вы и не вдавались), а только по внешнему признаку упираемости в цпу абсолютно очевидно, что она «тру» не более, чем никак
    
    ksm
    14.10.2015 22:30
    #8614003
    Судить по cpu-bound о типе репликации это что-то новое. Ну да бог с ним, давайте вдадимся в подробности и Вы расскажите почему RBR не бинарная.
    
    Bozaro
    15.10.2015 10:26
    #8614443
    +1
    Бинарная — очень плохой термин. Он не говорит ни о чем.
    
    О типе репликации я писал выше: habrahabr.ru/post/268631/#comment_8612987
    
    ksm
    15.10.2015 12:15
    #8614555
    О чем и речь. Комментарий о типах репликации я видел, в целом согласен. Хотя скажем в моем (ангажированном, конечно) понимании RBR более гибка, чем допустим вариант в постгресе (хотя я уже не имел возможности его глубоко потестить ). В RBR я могу на слейве иметь другую структуру таблиц: меньше полей (но с сохранением порядка следования), другие типы данных (по совместимые опять же). Отвязанность от storage engine дает возомжность менять (относительно, конечно) формат хранения страниц в движке не ломая совместимость в режимах old master — new slave.
    Да еще никто не упомянул GTID, которые позволяют иметь фактически любую топологию, менять ее и гарантировать что а) данные на серверах будут up-to-date, б) никакой апдейт не применится более одного раза на сервере.
    
    mtyurin
    14.10.2015 11:59
    #8613033
    посмотрите выше по треду — за вас (Bozaro) вдались в подрбности
    
    kaamos
    14.10.2015 13:29
    #8613213
    Всем, кто обсуждает проблемы row-based репликации в MySQL (именно так её правильно называть, чтобы говорить на одном с PostgreSQL и избежать путаницы с терминами логическая/бинарная/физическая репликация). Основная проблема, как я вижу, это чтение при репликации (так правильнее называть проблему с CPU-bound, как её здесь часто называют). Решение есть, например в TokuDB, называется read-free replication. Читать можно начинать отсюда: https://github.com/percona/tokudb-engine/wiki/Read-Free-Replication-with-TokuDB
    
    ksm
    14.10.2015 22:33
    #8614007
    Да я то как раз в курсе.

gatoazul
12.10.2015 13:07
#8609727
+1
Поиск по IP в mysql совсем несложно сделать, если хранить адрес длинным числом, а не строкой. Легко привешиваются и индексы.
1. vitalif
  12.10.2015 13:24
  #8609779
  +5
  ну просто по сути более правильное название этого преимущества — поддержка большого числа кастомных типов данных =)

Sway
12.10.2015 13:07
#8609729
+1
С полнотекстовым поиском далеко не всё в порядке. Кроме того, что его настраивать довольно муторно, особенно для кириллицы, у него так же много косяков с самим поиском когда он не находит то, что обычным лайком находится на ура. Часто это связано с именами/названиями. Приходится комбинировать его с like или ~ чтобы поиск был более-менее адекватным. + спец-словари для русского вообще сейчас невозможно найти (все ссылки древние и битые). Функционал сам по себе достаточный, но работает недостаточно хорошо чтобы использовать для языков отличных от английского.
1. mtyurin
  12.10.2015 13:17
  #8609755
  +1
  поиск по регулярке — это не поиск по морфологии, как бы вы всё перепутали. к полнотекстовому поиску есть вопросы по ранжированию, но ТОЧНО это не про ваши недорегулярки на лайках. а для поиска по регуляркам, например, есть САША КОРОТКОВ и его оптимизация регулярок на n-граммах и соответствующих индексах.
  1. Sway
    12.10.2015 13:23
    #8609773
    +1
    Я про проблемы с морфологией и пишу. Для русского языка всё не очень хорошо в случае поиска по словам типа «Хабрахабр». Далеко не факт что он найдет этот вариант при поиске «Хаб» или «Хабр» (не проверял конкретно это название, но все фейлы поиска были для подобных названий). Я напоролся на кучу проблем с такими названиями и в итоге пришлось страховаться регулярками т.к. попытки найти решение используя FTS провалились полностью.
    
    BlessMaster
    12.10.2015 16:11
    #8610049
    +1
    Ну так это и разные слова, если уже на то пошло (и русский язык в этом не виноват). Точно так же регулярки бессильны сопоставить «человек» и «люди», что элементарно для FTS с полноценной морфологией. Вхождение же «хабр» в «хабрахабр» — лишь частный случай, далеко не универсальный.
    Для разных же слов существуют тезаурусы, LSI, WordNet и прочие высшие материи.
    Чистый же FTS — не про это, хотя для некоторых задач достаточно даже простого стеммера вместо словарей — дёшево и сердито.
    Но в любом случае FTS — это не серебряная пуля, и нужно понимать, какая цель и какой инструмент лучше подходит.
    
    Sway
    12.10.2015 16:53
    #8610157
    Для разных же слов существуют тезаурусы, LSI, WordNet и прочие высшие материи.
    
    Вот тут-то и начинаются проблемы — я искал словари для русского. Либо ссылки мертвые, либо объем маловат.
    Вот и получается так, что постгресовский FTS годен разве что для поиска по тексту в худ. литературе, где нет терминов и т.п.
    С задачей типа «поиск по названиям книг и авторов» он не справляется =(
    На сколько помню древний sphinx с аналогичной задачей справлялся без проблем (последний раз использовал его 4 года назад, сейчас он еще лучше будет). Получается что эффективнее как раз внешнее решение. И настраивать его не так муторно.
    
    BlessMaster
    12.10.2015 17:38
    #8610255
    +2
    Когда мне нужен был FTS для предыдущего проекта, я использовал словарь ispell, установленный в системе, вручную сконвертировав его в utf-8. Сейчас, да, ispell объявлен устаревшим и давно уже отсутствует в моей системе (если нужно — могу поделиться).
    В этом году я вернулся к этой теме, но обстоятельства уже немного поменялись: python + pymorphy2 (со словарями opencorpora.ru) + jsonb — это небо и земля, по сравнению с тем, что было, когда FTS появился в Postgres. (Кстати, jsonb, как и hstore ранее, и индексы GiST и GIN, применяемые для всего этого, создавались одними и теми же людьми).
    FTS — это вообще очень непростое и капризное блюдо, которое нужно готовить как рыбу Фугу и качественные словари — основа всего. К сожалению действительно качественных и при этом бесплатных в свободном доступе для русского языка — нет. И вряд ли в ближайшее время появится. Словари от opencorpora в сочетании с pymorphy, прямо скажем, на голову лучше всего остального, с чем я сталкивался, но… совершенству предела нет, а русский язык — не самый простой, плюс ещё много факторов, добавляющих к ложке дёгтя.
    То есть, подытоживая, postgres — это не более чем коробка с инструментами. Инструменты качественные, с большими возможностями, но сами по себе они «ничто». FTS в постгрес — это фреймворк для создания собственных решений (который можно использовать как целиком, так и по частям), плюс пара утилит, чтобы набросать что-то на скорую руку и продемонстрировать концепцию (то же самое можно сказать про SQL в принципе — инструмент обработки данных не может заменить сами данные).
    Sphinx — это уже готовый специализированный продукт, имеющий какое-то завершённое и оттестированное решение. Наверняка «изкоробки» он будет работать лучше, чем фреймворк, в котором ещё нужно разобраться. Но я не уверен, что он будет работать лучше чем Postgres при прочих равных (использование открытых свободно-распространяемых словарей). Я успешно искал по сводной базе объявлений, думаю, книги — не более сложная задача. Но, если нужно «работает уже вчера» и всё устраивает, то может действительно стоит предпочесть готовое решение.
    
    Sway
    12.10.2015 18:12
    #8610341
    Так оно и есть. Из коробки только английский работал хорошо и без осечек.
    Внешнее решение наверняка будет уступать разве что по скорости, зато его намного проще и быстрее поднять. Вот если бы sphinx еще поддерживал нотификации postgresql (NOTIFY) — было бы вообще прекрасно =)
    
    BlessMaster
    15.10.2015 20:41
    #8615345
    Внешнее решение хорошо ровным счётом до того момента, пока оно без особых затрат на синхронизацию (к сожалению, я не в курсе, как сфинкс стыкуется с базой) полностью вписывается в решаемую задачу. Как только нужно больше — возвращаемся к тому, с чего начали, но при этом объём задачи уже сильно вырос, а опыт её решения — не накоплен, и что-то даже пошло в бизнес-модели и ломать/перестраивать становится тяжело и дорого. Хуже того, когда это тиражируемая система — приходится поддерживать сразу два варианта, поскольку не во всех экземплярах замена возможна. В общем, как всегда — в каждом случае своя цена, компромиссы и перспективы, которые нужно стратегически спланировать :-)
1. vitalif
  12.10.2015 13:26
  #8609783
  там ещё прикол в том что обязательно tsvector надо в таблице хранить, иначе очень медленно ранжирование работает.
  был доклад «полнотекстовый поиск в postgresql за миллисекунды», где они грозились допилить его, чтобы он сам информацию из tsvector'а хранил, и тогда его скорость реально приближалась к Sphinx'у. но по-видимому пока что не сделали…
  1. Timosha
    12.10.2015 17:17
    #8610219
    +1
    tsvector можно хранить в функциональном индексе
    
    vitalif
    12.10.2015 22:17
    #8610727
    +1
    вот именно тогда ранжирование и начинает тормозить, т.к. если для поиска нужен только индекс, для ранжирования нужен ещё и сам tsvector

sebres
12.10.2015 13:17
#8609759
+7
4. В Mysql можно прямо в запросе оперировать переменными…
В посгресе такого нет, по крайней мере я не нашел (напишите, если ошибся)

В PostgreSQL есть такое понятие как анонимная функция или анонимный код-блок — используйте ключевое слово DO
```
do $$
  declare x int;
  begin
    select col1 into x from table1;
  end;
$$;
```
Не все всегда гладко с execution plan (по крайней мере было пару лет назад в смысле повторного оптимального использования такого кода с параметрами снаружи, хотя возможно уже поправили).
1. mtyurin
  12.10.2015 13:18
  #8609763
  -2
  есть еще set [local] myvar = 'myval'
  1. sebres
    12.10.2015 13:22
    #8609769
    +5
    Нее… set только для установки локальных или сессионных конфиг-параметров. Это не переменная.
    
    mtyurin
    12.10.2015 16:44
    #8610127
    -1
    нееее… это именно ПЕРЕМЕННАЯ, причем может быть именно только в скопе транзакции
    
    sebres
    12.10.2015 18:29
    #8610387
    +1
    Если вы про этот set, то это как-раз то что я имел ввиду выше как параметр… (безотносительно области действия, что транзакция, что сессия).
    Если вы все же настаиваете, то приведите хотя бы пример использования этого как переменной (надеюсь вы понимаете что такое переменная в SQL).
    Если вы все же про set из psql (\set), то это вообще из другой оперы (только psql, никакого отношения к переменным SQL не имеющая в принципе).
1. Tonkonozhenko
  12.10.2015 15:21
  #8609957
  Но, к сожалению, вернуть что-то из этого блока нельзя.
  1. sebres
    12.10.2015 15:34
    #8609991
    +2
    Ну почему же, если используемое API поддерживает cursor-ы, вы можете вернуть ответ определив это как refcursor или напрямую, навскидку:
    
    do $$ declare x int; begin select col1 into x from table1; select x as result_x; end; $$;
    
    Ну или используя raise, по моему как-то так (нет postgres под рукой):
    
    do $$ declare x int; begin select col1 into x from table1; raise notice '%', x; end; $$;
    
    Tonkonozhenko
    12.10.2015 15:41
    #8609999
    Оба варианта у меня не сработали. Можете скинуть пример как это сделать через курсоры?
    И в доках вот что нашел:
    
    The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.
    
    sebres
    12.10.2015 16:04
    #8610039
    Не сработали — это из области «У меня компьютер сломался»… Можно подробнее (например в личку)?
    Под рукой к сожалению нет ничего, да и postgresql я юзаю в основном через свой биндинг c/c++ или в tcl (Pgtcl или tclodbc если под виндой) но однозначно помню, что возвращал так значения (и курсором, и out-bind-параметрами, и через raise):
    
    код-блок точно работает как prepared statement, т.е. можно привязать параметры, и насколько помню даже output parameter;
    
    для курсора используется то же api, что и для multi cursor statements;
    
    чтобы прочитать значение, возвращаемое через raise нужно читать message(s) а не result(s)
    
    Если вспомню что-то из «общепринятого» api типа пыха или питона или доберусь до железа с ним, солью сюда пример…
    
    sebres
    12.10.2015 16:10
    #8610047
    Кстати если у кого api совсем уж ущербное (т.е. ну совсем никак не читается), то вот пример через temporary table.
    Конечно не совсем комильфо, но на безрыбье как говорится…
1. varanio
  14.10.2015 09:32
  #8612799
  Это не совсем то.
  В приведенном мной примере «SELECT @x:= @x+1 FROM table» в результирующе выдаче в каждой новой строке будет новое число. В посгресе прямо в запросе (т.е. без циклов) этого вроде бы не сделать.
  1. galaxy
    14.10.2015 14:53
    #8613439
    Можно, хотя не через переменные и не очень очевидным образом:
    
    # with recursive counter as ( select 1 as var union all select var + 1 as var from counter where var < 10 ) select var from counter; var ----- 1 2 3 4 5 6 7 8 9 10 (10 rows)
    
    sebres
    14.10.2015 18:45
    #8613823
    Месье знает толк…
    Организовать рекурсию, чтоб вернуть counter, это просто что-то — чем row_number() то не устраивает?
    
    galaxy
    14.10.2015 18:48
    #8613827
    Человек хотел counter. Если бы нужны были числа Фибоначии — без проблем, я продемонстрировал принцип.
    
    sebres
    14.10.2015 19:50
    #8613881
    ну так-то ясно, но… человек хотел переменные в statement, у вас же оно — виртуальный столбец виртуальной таблицы, да еще и рекурсия сверху погоняет…
    
    varanio
    14.10.2015 20:00
    #8613895
    Я всё это умею, и оконные функции, и рекурсивные CTE, и циклы, и курсоры. Но в мускуле все-таки это немного проще и читабельней. Конечно, субъективно это. Так что именно такой же функциональности в пг все-таки, считаю, нет.
    
    BlessMaster
    15.10.2015 21:28
    #8615385
    Наивный вопрос: зачем на самом деле нужны переменные? Насколько хорошо запросы с переменными поддаются оптимизации? Документация сразу честно предупреждает, что далеко не всё с переменными просто и интуитивно, есть ряд ограничений.
    Я, конечно, не совсем в теме, но краем уха слышал о проблемах с использованием переменных при репликации, возможно это неактуальная информация (или звон совсем не там был, надеюсь кто-то просветит?)
    Такие вот вопросы.
    
    В общем, если посмотреть на проблему немного шире, Postgres силён своей расширяемостью — есть вот такие «переменные» http://www.garret.ru/imcs/user_guide.html :-)
    Наверно можно что-то попроще и полаконичней нарисовать, если есть такая необходимость.
    
    VolCh
    16.10.2015 06:16
    #8615705
    Основное назначение, вроде как, совпадает с назначением оконных функций типа lag, first_value и т. п., а так же агрегирующих с OVER — результаты текущей строки типа накопительных итогов или порядковых номеров (по сути тоже накопительный итог) зависят от других строк. В случае MySQL — только предшествующих, что частично обходится сортировкой. Но геммороя с ними куда больше.
  1. sebres
    14.10.2015 18:43
    #8613813
    Да можно же… FOR… LOOP, курсоры и т.д.
    
    В вашем же конкетном случае я и переменные использовать не буду:
    
    SELECT row_number() over () as x, * FROM table
    
    BlessMaster
    15.10.2015 21:38
    #8615393
    Наверно стоит добавить, что в особо запущенных случаях таки можно глянуть и в сторону хранимок на Python, Tcl, R, где можно не просто с переменными развернуться во всю ширь, но и со сложными алгоритмами и динамическими запросами. Это, конечно, не совсем то же самое, что выполнение произвольного запроса с переменными вот прямо из консоли, но, кажется, случаи, где это действительно нужно, беспощадно стремятся к нулю, а в реальном приложении — масса вариантов на выбор.

vitalif
12.10.2015 13:22
#8609771
-2
Мне лично в pg ещё не нравится отсутствие удобных команд типа SHOW TABLES, SHOW DATABASES и т.п.
Очень уж геморно запоминать вместо них \l \t \dl и что там ещё при работе в консоли (а я в ней обычно и работаю).
Ещё не нравится отсутствие возможности добавить колонку в заданное место (ALTER TABLE ADD x AFTER y).
1. BlessMaster
  12.10.2015 16:43
  #8610125
  +1
  В консоли постгрес работает автодополнение, которое может показать все базы, таблицы, поля и т.д. прямо в процессе написания запроса (или если включён bashcomp, то и при запуске psql — подсказывает имеющиеся локально базы).
  
  Более того, в postgres в каждой базе есть две стандартных схемы: «pg_catalog» и «information_schema», из которых можно извлечь абсолютно всю информацию о базе. Набросайте себе несколько хранимок и пользуйтесь ими как расширением. В интернете много готовых рецептов на разные случаи жизни и возможности выходят далеко за рамки SHOW TABLES, DESCRIBE DATABASE.
  
  С порядком столбцов, к сожалению, решения вроде как нет. Но по большей части он и не имеет смысла обычно, так что, вряд ли это реализуют в обозримой перспективе. Внутренний перфекционист, конечно, возмущён, но приходится смириться. В крайнем случае функция или вьюха решают проблему, если нужно много работать с таблицами из консоли.
  1. vitalif
    12.10.2015 22:22
    #8610731
    Это всё ясно и pg_catalog безусловно лучше чем то что есть в мыскле, но всё равно в консоли show tables удобнее)
    
    varanio
    12.10.2015 22:27
    #8610737
    Дык писать дольше, чем \dt. Или просто проще запомнить?
    
    vitalif
    12.10.2015 22:49
    #8610759
    Ага… И ещё SHOW CREATE TABLE удобная тема. Причём в mysql это же всё не команды консольного клиента, а просто SQL запросы, то есть их и из скриптов можно дёргать — тоже удобно. Минус правда в том, что зачастую в mysql это единственный реальный способ вытащить какую-то информацию о таблице, т.е. приходится парсить show create table вместо того, чтобы просто посмотреть в нужное место каталога…
    
    varanio
    12.10.2015 22:53
    #8610771
    мне попадалась где-то на stackoverflow хранимка, которая выдает аналог show create table. Костыльно конечно, чо уж там.
    Если мне нужна схема таблицы я обычно делаю pg_dump… --table mytable --schema-only
    
    svetasmirnova
    13.10.2015 21:16
    #8612433
    > Минус правда в том, что зачастую в mysql это единственный реальный способ вытащить какую-то информацию о таблице, т.е. приходится парсить show create table вместо того, чтобы просто посмотреть в нужное место каталога…
    
    Эээ, а что есть в SHOW CREATE TABLE и нет в Information Schema?
    
    BlessMaster
    15.10.2015 21:51
    #8615415
    Так а чем не подходит нажать Tab вместо того, чтобы печатать SHOW TABLES? Даже не знаю, куда уже проще запомнить :-)
  1. Borz
    13.10.2015 08:44
    #8611067
    +1
    В консоли постгрес работает автодополнение, которое может показать все базы, таблицы, поля и т.д. прямо в процессе написания запроса
    
    в MySQL тоже работает автодополнение
    
    postgres в каждой базе есть две стандартных схемы
    
    и в MySQL есть… три стандартные схемы: mysql, information_schema, performance_schema, правда они глобальные и требуется делать WHERE с именем нужной схемы.
    например консольная команда «show tables from mysql;» трансформируется в такой запрос: «SELECT table_name FROM tables WHERE table_schema='mysql';»
    
    galaxy
    13.10.2015 23:12
    #8612567
    +1
    в MySQL тоже работает автодополнение
    
    Есть, только работает через задницу, как и многое в mysql:
    
    mysql> select * from use use user.Create_view_priv user.Insert_priv user.Show_db_priv user.max_questions user user.Delete_priv user.Lock_tables_priv user.Show_view_priv user.max_updates user.Alter_priv user.Drop_priv user.Password user.Shutdown_priv user.max_user_connections user.Alter_routine_priv user.Event_priv user.Process_priv user.Super_priv user.plugin user.Create_priv user.Execute_priv user.References_priv user.Trigger_priv user.ssl_cipher user.Create_routine_priv user.File_priv user.Reload_priv user.Update_priv user.ssl_type user.Create_tablespace_priv user.Grant_priv user.Repl_client_priv user.User user.x509_issuer user.Create_tmp_table_priv user.Host user.Repl_slave_priv user.authentication_string user.x509_subject user.Create_user_priv user.Index_priv user.Select_priv user.max_connections user_host
    
    Сравните с контекстным дополнением в psql:
    
    postgres=# S SAVEPOINT SECURITY LABEL SELECT SET SHOW START postgres=# SE SECURITY LABEL SELECT SET postgres=# SELECT * from pg_t pg_tables pg_temp_1. pg_timezone_names pg_toast_temp_1. pg_ts_config pg_ts_dict pg_ts_template pg_tablespace pg_timezone_abbrevs pg_toast. pg_trigger pg_ts_config_map pg_ts_parser pg_type postgres=# SELECT * from pg_tables where table tablename tableowner tablespace
    
    Borz
    13.10.2015 23:28
    #8612585
    +2
    вы чувствуете, как вы подменили критерий «нет в MySQL» на «есть, но работает не так, как мне хочется»?
    
    galaxy
    14.10.2015 01:51
    #8612687
    +1
    Я не говорил «этого нет в MySQL». И работает оно не «не так, как мне хочется», а хреново. Объективно.
    
    BlessMaster
    15.10.2015 22:28
    #8615469
    Тезис был про невосполнимость утраты SHOW TABLES при переходе с MySQL.
    Как видим, общего, на самом деле, больше, чем различий и не так всё страшно.
    
    Если же обсуждать «в MySQL тоже есть», то по поводу автодополнения выше уже прошлись и похоже ситуация за те 7 лет, что я не пользовался им, мало изменилась, что на самом деле факт неприятный, но делает наличие SHOW TABLES и прочих подобных команд востребованным. Вместе с тем это воспитывает соответствующий стиль работы, ломать который тяжело и в итоге поднимается вопрос, «в %СУБД не хватает SHOW TABLES».
    
    Теперь же пару слов про схему.
    В документации висит такой комментарий и никто не спешит опровергать, хотя прошло уже шесть лет.
    
    Hans-Henrik St?rfeldt on February 20 2009
    
    The implementation of INFORMATION_SCHEMA can have serious impact on performance of the server. If you have many tables, and query into INFORMATION_SCHEMA without limitations on the schema and if possible the table itself, performance is severely impacted while the query runs.
    
    Здесь на хабре кажется тоже статья пробегала на эту тему. Речь, если не изменяет память шла про версию 5.5 и альфу 5.6, в которой не предвиделось положительных сдвигов. В документации к 5.7 этот вопрос частично затрагивается, упоминается просадка производительности при запросе информации по нескольким базам и рекомендуется такие запросы тюнить.
    
    Наверно это не критичная особенность, утверждать «всё плохо» не буду, хотя ряд ограничений на использование information_schema накладывает.

vitalif
12.10.2015 13:28
#8609787
-2
А, и это, есть же phpPgAdmin, почему его никто не вспомнил?
Неказист конечно немного, но юзабелен в целом.

youROCK
12.10.2015 13:45
#8609811
+14
Поставил плюс, потому что подобных статей на хабре люди пытались писать много, но они всегда получались однобокими — либо человек хорошо знал MySQL и плохо — Postgre, либо наоборот. В этой статье, как мне кажется, человеку более-менее удалось соблюсти баланс, хотя некоторые утверждения всё равно весьма спорны, особенно про производительность :). Но всё равно хорошо, статья вполне четко отвечает на вопрос в заголовке и описывает всё, что нужно.

kaamos
12.10.2015 13:50
#8609817
+8
Я понимаю, что лекция для ~~колхозников~~ пользователей PostgreSQL, но вот комментарий из противоположного лагеря. Полный ответ заслуживает отдельного поста, но навскидку:
- Аналогов Galera для PostgreSQL нет и видимо нескоро появятся;
- утверждения про «встроенность» транзакций в PostgreSQL весьма поверхностны. В MySQL они «встроены» ничуть не хуже. Да, есть нетранзакционные движки, но в 2015 про них можно говорить только в поддержке совместимости со старыми приложениями, т.е. нет никакого смысла их использовать в новых приложениях.
- да, в MySQL 5.7 строгий режим по умолчанию. Да и для предыдущих версий включить его не составляло проблем, что люди и делают при любом хоть сколько-нибуть серьёзном использовании MySQL.
- Утверждение «В mysql есть только AUTO_INCREMENT на поле таблицы, который тикает по одному» неверно, см. auto_increment_increment.
- но абсолютно согласен, sequence куда более гибкий механизм, чем AUTO_INCREMENT. Кстати, в MariaDB есть sequence storage engine, который частично устраняет этот пробел в функционале.
- полноценная поддержка JSON будет (есть) в MySQL 5.7.
  функциональные индексы реализованы в 5.7 через virtual columns, по которым можно строить индексы.
  понятно, что утверждения про лучшую производительность PostgreSQL и «меньше багов» субъективны по определению (у всех разные нагрузки и сценарии использования), но хорошо бы подтверждать конкретикой. Я слышал прямо противоположные утверждения от пользователей, которые долго работали как с MySQL, так и с PostgreSQL.
1. varanio
  12.10.2015 14:01
  #8609843
  +1
  Спасибо!
  А не могли бы вы написать подробный пост на эту тему? Очень интересно на самом деле.
  1. kaamos
    12.10.2015 14:06
    #8609857
    Да, конечно. In my copious free time… :)
1. Bozaro
  12.10.2015 16:43
  #8610121
  +4
  Работал с обоими СУБД. Лично мигрировал с MySQL на PostgreSQL.
  
  Транзакции в MySQL транзакции действительно прикручены сбоку. Это имеет кучу сайд-эффектов: несколько механизмов блокировок, несколько логов транзакций, хрупкая репликация, нетранзакционный DDL, репликация порождает распределенную транзакцию внутри БД (между логом репликации и логом движка) и т.п.
  
  У нас была забавная беда с AUTO_INCREMENT-ом: в случае использования InnoDB возможно повторное использование идентификаторов, т.к. при старте он начинается с максимального имеющегося в таблице значения. Это очень сильно доставляло при отдельном хранении исторических данных.
  
  В PostgreSQL много лучше оптимизатор запросов. В MySQL он, ИМХО, никогда не будет работать хорошо: MySQL-хранилища идут как плагины. Из-за этого получается, что выделение API хранилища требует, чтобы движок о них знал как можно меньше, а для реализации оптимизатора — как можно больше. Т.е. либо несколько хранилищ, либо качественный оптимизатор.
  
  Еще хотел бы отметить:
  
  В MySQL очень дорогой DDL: ALTER TABLE очень любит пересоздавать таблицы даже при удалении CONSTRAINT-ов.
  
  За утилиту mysqldump авторам надо оторвать руки — по умолчанию она делает не консистентный слепок данных.
  
  Из киллер-фичей MySQL от которой было очень боязно отказываться (она появилась только в PostgreSQL 9.3): читаемые bin-логи, позволяющие восстановить историю изменений в БД.
  1. youROCK
    12.10.2015 18:53
    #8610439
    Кстати фича «engine condition pushdown» в MySQL (https://dev.mysql.com/doc/refman/5.6/en/condition-pushdown-optimization.html) и её аналоги — это как раз способ все же сделать так, чтобы оптимизатор мог работать отдельно от общего движка. То есть, утверждение о том, что оптимизатор в MySQL сделать лучше нельзя, тоже не совсем верно. Другое дело, что в PostgreSQL намного проще сделать хороший оптимизатор, потому что там всего-лишь один движок.
    
    Bozaro
    12.10.2015 19:14
    #8610471
    dev.mysql.com/doc/refman/5.6/en/condition-pushdown-optimization.html
    
    Эта фича по-сути является костылём: расширили API хранилища, чтобы хранилище возвращало меньше бесполезных данных, которые будут откинуты движком. И, что важно, она должна быть в том или ином виде поддержана во всех движках.
    
    То есть, хотите более быстрое выполнение запросов:
    
    Расширьте API для хранилища;
    
    Научите оптимизатор этим API пользоваться (включая ситуации, когда в хранилище это API реализовано не до конца и когда идет запрос между хранилищами);
    
    Реализуйте это API во всех хранилищах.
    
    При этом многие хотелки будут разбиваться о то, что:
    
    они полезны только для одного хранилища;
    
    на разных хранилищах оптимизации могут давать противоположный результат.
    
    Я уверен, что до отказа от концепции подключаемых хранилищ, в MySQL не будет ни нормального оптимизатора, ни транзакционного DDL, ни стабильной репликации. Пойдут ли разработчики на этот шаг — большой вопрос.
    
    maep
    13.10.2015 09:52
    #8611145
    Транзакционный DDL!? Он даже в Oracle не транзакционный, кажись…
    
    zzashpaupat
    13.10.2015 13:09
    #8611481
    +2
    Вот тут говорят, что transactional DDL есть в PostgreSQL, MS SQL Server, DB2, Firebird
  1. svetasmirnova
    13.10.2015 21:20
    #8612441
    > В MySQL очень дорогой DDL: ALTER TABLE очень любит пересоздавать таблицы даже при удалении CONSTRAINT-ов.
    
    Он с каждой версией становится всё дешевле и дешевле: dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
    
    Bozaro
    13.10.2015 22:21
    #8612499
    О чем собственно и речь: InnoDB все больше и больше срастается с движком MySQL.
    
    mtyurin
    14.10.2015 11:30
    #8612953
    ждем
1. JSmitty
  12.10.2015 22:28
  #8610739
  +1
  Довольно поверхностно знаю MySQL, но с чем пришлось столкнуться за 4 месяца его использования:
  
  транзакции в MySQL ущербные по дефолту — откатить изменения в схеме нельзя, по умолчанию транзакция сама не отваливается при ошибках
  
  автоинкремент на две колонки не повесить, для установки полей сортировки требуется велосипед
  
  большие сомнения про JSON, т.к. минимум MariaDB и MySQL предполагают разный и (в обоих случаях) очень скромный синтаксис
  
  документация — на взгляд новичка в MySQL просто чудовищная документация, постоянно приходится гуглить, по самой документации найти ответы на конкретные вопросы очень сложно (например, как запустить fts). Список, что поддерживается той или иной версией MySQL, и какие новинки в поддержке SQL например в 5.6 против 5.5 — до сих пор не нашел.
  
  минимум в 5.5 mysql не умеет нормальный fts с изменениями слов
  
  нет индексов для like оператора
  
  убивают несовместимые поля для внешних ключей, когда в одной таблице PK — int(10), да еще и unsigned, в другой FK — int(11), то ключ не повесить, не поменяв схему одной из таблиц
  
  триггеры не стартуют каскадно (т.е. каскадный update/delete не запустит на сторонних таблицах триггеры)
  
  LOAD DATA INFILE спокойно может дропнуть входные данные и даже не сообщит об ошибке — например по конфликту PK
  
  чудовищная консоль, после удобной psql как ушат ледяной воды
  
  обилие форков создает ощущение, что проект заброшен
  1. ksm
    14.10.2015 01:49
    #8612681
    По последнему пункту — Оракл расширил команду MySQL, но приоритет отдается стабильности, поэтому форки имеют возможность дать больше фич раньше оригинального продукта.
1. maximw
  12.10.2015 22:33
  #8610747
  -1
  Не слишком хорошо знаю про sequence в PostgreSQL. Но в МуSQL sequence вполне реализуется, о чем написано в конце описания функции LAST_INSERT_ID()
  1. Bozaro
    13.10.2015 11:12
    #8611253
    Есть два важных момента:
    
    sequence дает идентификатор до вставки записи, что бывает иногда полезно;
    
    sequence не сбрасывается при рестарте базы, а auto_increment в InnoDB сбрасывается (https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html)
    
    maximw
    13.10.2015 14:45
    #8611691
    1) Ну да, sequence дает идентификатор до вставки записи. Тут как бы тоже можно в любой момент (даже без вставки какой-либо записи) получить число, которое гарантированно не будет выдано никому другому и использовать его как id или еще как-то.
    Инициализация:
    
    CREATE TABLE sequence (id INT NOT NULL); INSERT INTO sequence VALUES (0);
    
    Использование:
    
    UPDATE sequence SET id=LAST_INSERT_ID(id+1); SELECT LAST_INSERT_ID();
    
    2) Интересный нюанс, спасибо, почитаю.
    
    Bozaro
    13.10.2015 15:23
    #8611769
    +2
    Эмулировать SEQUENCE при помощи UPDATE можно, но для этого нужно выполнять UPDATE в отдельной транзакции. При чем коммит этой транзакции должен быть совершен до использования этого значения.
    
    До миграции на PostgreSQL я вынуждено эмулировал SEQUENCE в MySQL через отдельную таблицу. Вспоминаю об этом с содроганием.
    
    varanio
    14.10.2015 09:26
    #8612795
    +1
    Да, с помощью костылей и синей изоленты можно сделать эмуляцию чего угодно. Вопрос только зачем? Если бы в mysql были какие-то по-настоящему уникальные полезные фичи, ради которых имело бы смысл так стараться, я еще мог бы понять.
    
    Bozaro
    14.10.2015 10:06
    #8612827
    +1
    Ответ на вопрос зачем простой — legacy.
    
    Мигрировать живой проект на другую СУБД не так-то просто: у нас процесс миграции на PostgreSQL занял более года.

rakot
12.10.2015 15:09
#8609929
+7
Не сочтите за рекламу, но открыл для себя www.adminer.org вполне функционально, по крайней мере точно быстрее чем через консоль.
1. sudoroot
  14.10.2015 08:17
  #8612755
  Большое спасибо вам за сий ресурс!

Envek
12.10.2015 15:41
#8610001
+5
Оконные функции — сказка. Сколько раз они выручали, позволяя делать всякие не очень тривиальные штуки в миграциях одним запросом. Дюже рекомендую к изучению.

Cord
12.10.2015 15:51
#8610017
А есть ли полноценная многоверсионность в MySQL?
Когда много пользователей читает/пишет в Мускуле, локи на уровне строки/таблицы раньше были задницей. То есть Мускул был __непригоден__ для нормальной нагрузке.

А Постгрес вполне себе летал, и уже 8-9ка вполне себе сильно ближе по возможностям и уровню к Ораклу, а не игрушечному-плюшевому MySQL.
1. Bozaro
  12.10.2015 16:51
  #8610145
  Как минимум, в MySQL 5.6 о многоверсионности можно только мечтать :(
1. youROCK
  12.10.2015 18:54
  #8610443
  Не могли бы вы пожалуйста рассказать подробнее, что не так с MVCC в InnoDB?
  1. vitalif
    12.10.2015 23:07
    #8610785
    +3
    подозреваю, что это о временах myisam :-)
  1. Bozaro
    13.10.2015 11:43
    #8611315
    InnoDB вешает блокировку при попытке изменять читаемые кем-либо записи. В PostgreSQL этой проблемы нет.
    
    youROCK
    13.10.2015 12:44
    #8611441
    Приведите пример, пожалуйста
    
    Bozaro
    13.10.2015 13:19
    #8611505
    Я страдал от этой проблемы (https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html):
    
    To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
    
    When InnoDB scans an index, it can also lock the gap after the last record in the index. Just that happens in the preceding example: To prevent any insert into the table where id would be bigger than 100, the locks set by InnoDB include a lock on the gap following id value 102.
    
    youROCK
    13.10.2015 16:21
    #8611913
    Можете привести рабочий пример?
    Я утверждаю, что обычные SELECT'ы (т.е. без LOCK IN SHARE MODE и FOR UPDATE) в дефолтном уровне изоляции (то есть, REPEATABLE READ, а не SERIALIZABLE!) никогда не блокируют запись и наоборот.
    То есть, вы можете иметь сколько угодно параллельных селектов из таблицы, и это никак не будет мешать выполнению UPDATE или INSERT в эту же таблицу. Аналогично, выполняющийся UPDATE или INSERT в таблицу не будут блокировать чтение из этой же таблицы, даже тех же строк.
    Что может происходить – это что лок на запись будет браться больший, чем нужно (т.е. вряд ли у вас пройдут параллельные UPDATE следующего вида: UPDATE… WHERE id % 2 = 0 и UPDATE… WHERE id % 2 = 1)
    
    Bozaro
    13.10.2015 16:43
    #8611963
    Я утверждаю, что обычные SELECT'ы (т.е. без LOCK IN SHARE MODE и FOR UPDATE) в дефолтном уровне изоляции (то есть, REPEATABLE READ, а не SERIALIZABLE!) никогда не блокируют запись и наоборот.
    
    Я только что привел ссылку на документацию, в которой утверждается обратное.
    
    galaxy
    13.10.2015 18:21
    #8612195
    +2
    Не читайте до обеда документации mysql. Там даже пример с SELECT FOR UPDATE. И блокирует только в этом случае.
1. MacIn
  12.10.2015 20:22
  #8610603
  Что такое «нормальная нагрузка»? Нормально локи работают, еще от уровня изоляции зависит.

Anarchist
12.10.2015 17:08
#8610191
+3
> Mysql все знают, postgresql никто не знает.
«никто не знает» — слишком категоричная оценка. Уверяю Вас, на крупных проектах о существовании Pg осведомлены и частенько даже используют оный.

> Есть целый класс php-программистов, для которых слово “база данных” и mysql — это одно и то же, слова-синонимы.
Такие «программисты» нам не нужны. :)

> Особо бесит то, что для highload-проектов практически всегда нужен еще и pgbouncer (это сторонняя разработка), который копит коннекты в очереди и следит, чтобы к самому посгресу было ограниченное число коннектов.
Если в языке реализованы пулы соединений, зачем нужен pgbouncer? От него одна головная боль, особенно, в том, что касается подготовленных запросов.
1. varanio
  12.10.2015 19:46
  #8610521
  +2
  А если у вас много серверов с приложениями, которые коннектятся к одной базе?
  Ну или когда юзаешь какое-нибудь php-fpm, то без pgbouncer не обойтись.
  1. stalkerg
    14.10.2015 16:54
    #8613625
    Я это уже много где писал:
    проблема с пулерами только у PHP.
    Почему? Есть статья на хабре про это. :)

NeX
12.10.2015 18:59
#8610449
Еще к плюсу MySQL можно отнести
1) очень быстрые воборки по primary key (из постгреса не получается выжать быстрее, чем 2-3 мс на запрос 1 записи по pk)
2) Если надо еще быстрее — www.percona.com/doc/percona-server/5.5/performance/handlersocket.html

К постгресу:
1) Рекурсивные запросы (исопльзуйте аккуратно) habrahabr.ru/post/73700
1. mtyurin
  12.10.2015 19:49
  #8610529
  > из постгреса не получается выжать быстрее, чем 2-3 мс на запрос 1 записи по pk
  
  покажите explain (analyze, buffers) select * вашпример (добейтесь стабильного отклика и приведите его)
  1. varanio
    12.10.2015 20:17
    #8610589
    в innodb для pk используется кластеризованный индекс, я не очень разбираюсь, но там данные типа лежат упорядоченно так, чтобы именно по pk их быстрее было доставать. Вполне возможно, что мускуль здесь делает посгрес, хотя если данные уже в кеше, то должно быть примерно также всё
    
    varanio
    12.10.2015 20:28
    #8610615
    Хотя вот нашел и в посгресе такое же
    www.postgresql.org/docs/9.4/static/sql-cluster.html
    т.е. таблицу можно кластеризовать под конкретный индекс
  1. NeX
    13.10.2015 17:53
    #8612143
    Был неправ, когда все данные в кеше все хорошо
    
    mtyurin
    14.10.2015 12:45
    #8613115
    когда все данные в кеше и там и там. а когда данные не в кеше — я бы еще сильно посмотрел, где как. но это уже в лоб еще сложнее сравнивать
    
    BlessMaster
    16.10.2015 00:02
    #8615579
    Ну вот этот самый HandlerSocket [http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html] наглядно продемонстрировал, что абстракция, повешенная над движком InnoDB, замедляет доступ по ключу на порядок и всё упирается в процессор сильно раньше. То есть, InnoDB конечно быстрый движок, но не его скорость является бутылочным горлышком. В то же время Postgres имеет меньше лишних абстракций. Поэтому теоретические построения «кто кого» пусты и холиварны. А вот практические результаты всегда интересно знать с привязкой к условиям эксперимента.

Moskus
12.10.2015 19:55
#8610541
+1
postgis.net
Для MySQL такого нет, с географическими типами у него тоже все фигово.

milabs
12.10.2015 23:45
#8610841
В PostgreSQL есть табличный INHERITANCE, в MySQL его нет.

Stan_1
12.10.2015 23:58
#8610855
+1
В достоинства PostgreSQL еще можно добавить поддержку scheme. Крайне удобная штука для структурирования проекта и разбивки таблиц на группы.
1. Envek
  13.10.2015 02:01
  #8610973
  В этом году на PgDay был восхитительный доклад, как с помощью небольшой магии со схемами, триггерами и вьюхами сделать полноценную «машину времени» для базы данных, внося минимальные изменения в приложение: www.hagander.net/talks/tardis_orm.pdf
  
  Я полюбил схемы после этого доклада.
  
  И, кстати да, почему ещё никто не сказал про классные range-типы?
  1. samokhvalov
    13.10.2015 02:11
    #8610977
    Да много про чего тут не сказали, чего уж там ;)
1. svetasmirnova
  13.10.2015 21:25
  #8612445
  +1
  В MySQL schema — это синоним database. Можно сказать, что в MySQL всего одна база, если сравнивать с PostgreSQL или Oracle. А того, что там схем нет — нельзя.

samokhvalov
13.10.2015 02:09
#8610975
+1
Для тех, кто любит сравнивать, есть отличная старая и постоянно развиваемая статья в Википедии: en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
1. zzashpaupat
  13.10.2015 13:10
  #8611483
  +1
  Еще есть вот тут неплохое сравнение: www.sql-workbench.net/dbms_comparison.html

horlon
13.10.2015 09:07
#8611091
-10
Мой выбор однозначно MySQL… Функцый, которые там есть мне с головой достаточно. Всегда удивлялся непонятным доводомам крутоты postgresql… Там есть то, там есть се… Почему-то никогда не было потребности в том функцыонале. База, прежде всего — это просто база, а не истрибитель чтобы картошку возить…
1. Envek
  13.10.2015 09:36
  #8611129
  +5
  Это всего лишь значит, что у вас ещё не было сложного проекта. Я до недавнего времени мог обходиться вообще SQLite'ом, но теперь мы в проекте используем jsonb, range-типы, массивы, full-text search, рекурсивные CTE, оконные функции и ещё кучу фич и в хвост и в гриву и очень довольны. Постгрес офигенен, слазить с него не собираемся.
  1. horlon
    13.10.2015 13:45
    #8611561
    -3
    Сложных проэктов было много. Постоянная переписка проэктов c FireBird и Postgresql на MySQL показала, что никаких проблем в отсутствии функционала MySQL нет (вопреки уверенным заявлениям предыдущих разработчиков). Никогда не использую внутренний функционал баз СУБД (в основном только INSERT, UPDATE, DELETE, SELECT). Просто пишу классы на PHP.
    SQLite недотягивает до моих потребностей совсем немного — базы слижком большие и по размеру и по количеству записей да и самих баз много и медленный он для таких масштабов, а самое главное, что я привык работать с MySQL и написал под него массу своих утилит. Не раз хотел пересесть (когда только начал работать с базами) на postgre или Firebird, но получив большой опыт в работе с базами и в самом программировании (до такого уровня, что могу сам написать СУБД) — смысла в этом так и не вижу. SQLite меня привлекает более полной лицензионной свободой (помню времена были, когда postgre и Firebird тоже были под BSD лицензиями), но не дотягивает он до идеала… Несмотря на то, что он менее громоздкий, нежели другие СУБД, но и в нем есть куча ненужного мне функцыонала, а нужные качества отсутствуют. В данный момент переписываю очередную систему идентификации с FireBird на MySQL, полет нормальный…
1. VolCh
  13.10.2015 10:58
  #8611225
  +2
  Одни только частичные и функциональные индексы достаточный повод, чтобы перейти на Postgre, как только вам нужно делать сложные выборки на миллионах записей.

chabapok
13.10.2015 13:36
#8611535
Правда ли, что постгресу нужен строго родной коннектор — c тем же номером билда, что и сам постгрес?
Есть история от человека, работавшего с постгресом — понадобилось обслуживать систему, которой несколько лет, а коннектор оказался утерян. Новые коннекторы все ругаются, мол, конфликт версий, и не хотят коннектиться к старому постгресу. Старых версий уже просто так не найти. В результате, даже базу не экспортировать, и вообще ничего не сделать. Искали, просили по форумам, на поиски ушел где-то месяц. Повезло, нашелся человек, который им помог. Было это лет 7-8 назад.
1. galaxy
  13.10.2015 16:26
  #8611921
  Нет. У них протокол менялся в районе 8-й что ли версии, так что все, что умеет новый протокол, будет работать и с новейшими версиями базы.
  (Если под коннектором вы имеете в виду клиентскую библиотеку типа libpq, на уровне фреймворков и active record все, конечно, по-своему)

phoenixweiss
13.10.2015 14:49
#8611703
Если мы работаем с определенным уровнем абстракции, например используя ActiveRecord, для большинства «обычных» проектов нет разнице, pg там или не pg, за взаимодействие в любом случае отвечают коннекторы. Но понимание технологичских отличий в структуре БД в любом случае нужно.
Спасибо за статью, нескольких моментов не знал. На слоне у нас всего пара проектов за все время была, где без него никак, в подавляющем большинстве других спокойно использовали и используем мускуль.

jonywtf
13.10.2015 15:07
#8611747
Не так давно открыл для себя pgmodeler. На мой взгляд это ближайший кроссплатформенный аналог MySQL workbench для PostgreSQL.
Исходники открыты, но за бинарную сборку просят денег) Кому не лень можно собрать из исходников.
Всем доволен, но хотелось бы создавать отдельные EER диаграммы. Сейчас все таблицы в одной куче…
1. romy4
  13.10.2015 17:48
  #8612127
  Navicat умеет в EER диаграммы

viat0r
13.10.2015 15:27
#8611781
+1
Документацию забыли. Как по мне, то документация Postgres не только на голову выше, чем в MySQL — она одна из лучших среди всего opensource. Мне думается, что по ней можно изучать не только СУБД, но и SQL.

maxidler
13.10.2015 19:44
#8612351
+1
Хорошая статья, но все очень субъективно, когда касается скорости работы и т.д. Цифры где?
Автор говорит, что postgres не работает из коробки если как следует не настроить. Кстатим MySQL тоже работает из коробки, но как-то очень плохо, если его не настроить. А еще в обоих случаях нужно обязательно тюнить операционную систему.
Не очень понятно — тюнил ли автор MySQL и ОС, когда пытался использовать MySQL?

Функционалом Postgres очень богат — ФАКТ!!!
Но вот всем ли нужен этот функционал???

В целом получилась неплохая реклама для Postgres, а я ждал больше объективностей, из которых новичок мог бы следать вывод типа: Ого! В моем проекте нужно просто правильно потюнить MySQL. ИЛИ Упс! Да в моем проекте без Postgres никак!
1. varanio
  13.10.2015 21:41
  #8612469
  +1
  > Цифры где?
  Ну просто сферические тесты в вакууме приводить не хотел. Поэтому честно написал про субъективность. Хотя может в следующей статье… Кстати, какие бы тесты вы хотели бы видеть, чтобы они были наиболее объективны?
  
  > тюнил ли автор MySQL и ОС
  yes. Мы нанимали DBA для тюнинга и выжимали всё что могли. Пришлось перейти на postgres в основном из-за того, что репликация на наших объемах тупо не успевала. И баги были довольно странные (опять же, на малых объемах их может быть и не было бы). После перехода на постгрес с репликацией не было проблем вообще, ну может какие-то несущественные мелочи.
  
  > а я ждал больше объективностей
  Собственно, я честно пытался придумать, чем mysql может быть круче, и ожидал, что в коментах накидают много всего дополнительно. Но накидали как-то вяло, если честно.

FSA
13.10.2015 21:25
#8612443
Ну вот. Статья снова привела в уныние что изначально в своём небольшом проекте использовал MySQL, а не PostgreSQL. Столько всего вкусного что упростило бы жизнь.
1. varanio
  13.10.2015 21:42
  #8612471
  Если проект небольшой, то сможете перевести его на pg за день или два. Если будут вопросы — обращайтесь в личку, помогу чем смогу
  1. FSA
    13.10.2015 21:46
    #8612473
    Я давно задумывался о переводе. Но, к сожалению, быдлокод мешает. Сейчас полным ходом перевожу PHP код на использование PDO. Когда всё доделаю, можно уже и переходе задуматься. Один раз пробовал переделать, но столкнулся с проблемой прямого преобразования из-за изобилия всяких «0000-00-00 00:00:00» и прочих перлов. Тоже приходится по ходу изменения кода править базу.
  1. FSA
    14.10.2015 00:15
    #8612633
    Есть необходимость переноса нескольких десятков таблиц из MySQL в PostgreSQL. В своё время не нашёл как автоматизировать процесс. Какие есть варианты? Желательно чтобы это можно было сделать под Linux.
    
    zzashpaupat
    14.10.2015 00:21
    #8612639
    +1
    Сам недавно занимался подобным делом. Для начала посмотрите тут:
    wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL
    
    Лично мне больше всего приглянулся pgloader, им и воспользовался.
    
    varanio
    14.10.2015 07:05
    #8612739
    Спасибо. Судя по описанию, pgloader охренеть какая штука, на будущее запишу себе на всякий случай.
    Кстати, перед миграцией, я бы для начала поставил strict mode в mysql и погонял код на предмет ошибок

lybin
16.10.2015 21:01
#8616599
Чтобы работать в продакшене с посгресом, его нужно хорошенько профессионально настроить. Если вы не выставите правильно shared_buffers, настройки автовакуумов и т.д., то на серьёзных нагрузках всё будет медленно работать.

Аналогично и с mysql, на серьезных проектах требуется тюнинг иначе медленно работает(в сети множество статей и куча параметров). Отсюда становится под вопросом пункт про производительность.