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



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

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

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

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

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

    В посгресе такого нет, по крайней мере я не нашел (напишите, если ошибся). Ну, то есть, можно конечно сделать хранимку, где можно делать вообще всё что угодно, но вот чтобы прямо так в запросе — вроде как нет.
  5. Нет нормального аналога phpmyadmin. По сути, большинство известных мне крутых посгресистов работают с SQL в командной строке, к чему сложно привыкнуть поначалу. Нет, есть всякие pgMyAdmin и т.д., но каждый из них имеет какую-то ущербность. Возможно платные есть хорошие, я не проверял.
  6. Mysql все знают, postgresql никто не знает. Поэтому новые проекты часто боятся начинать на postgresql, потому что надо будет поддерживать, да и вообще боязнь неизвестного. Есть целый класс php-программистов, для которых слово “база данных” и mysql — это одно и то же, слова-синонимы. Т.е. им как-то сложно вылезти из скорлупы, что ли.
  7. Говорят, дешевые хостинги не очень любят postgresql, потому что его сложнее администрировать. Например, чтобы создать пользователя, который может логиниться к postgres, надо делать это в двух местах: выполнить sql-запрос и прописать в pg_hba.conf

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

  1. 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”.
  2. Работа с 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 не представляю, может есть какой-то способ?
  3. Разнообразные 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 могут быть любые.
  4. Киллер-фича последних версий посгреса — тип jsonb, позволяющий очень быстро искать по джейсонам. Не буду подробно останавливаться, потому что в каждой второй статье про это все уши прожужжали.
  5. Так называемые “оконные функции”. Например, надо выдать для каждого сотрудника его зарплату, и среднюю зарплату по отделу в той же строке, без использования подзапросов и 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)
    
    

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

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

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

Подробнее
Реклама
Комментарии 313
  • –3
    Я думаю, вместо CTE в Mysql можно использовать View. Не так гибко, конечно, но похоже по функционалу.,
    • +5
      Хочу посмотреть как вы через вьюхи сделаете рекурсию.
      • +1
        Ну рекурсию, да. Я же не сказал, что это полный функциональный аналог. Речь шла про удобство записи сложных запросов, и тут вьюхи могут помочь.
      • +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?
        • 0
          для запросов из нескольких баз вы можете использовать dblink
          www.postgresql.org/docs/9.3/static/contrib-dblink-function.html
          • 0
            Читал про dblink, но он не так очевиден для начинающего пользователя как в самом запросе указать просто базу и таблицу. Не сужусь сказать хорошо это или плохо, просто не привычно для mysql разработчика.
            • +2
              Как по мне не привычнее делать запросы с участием таблиц из разных баз данных.
              • +3
                В Postgres чаще используют схемы в рамках одной базы, и между ними запросы ходят отлично. А вот в MySQL отказались от схем, но зато сделали возможными запросы между базами.
                При этом в Postgres можно сделать CREATE FOREIGN TABLE и делать запросы к другому серверу как к своей таблице, с версии 9.5 будет возможность сделать IMPORT FOREIGN SCHEMA.
                Не знаю, есть ли такие возможности в MySQL, глубоко не копал.
                • 0
                  В MySQL CREATE DATABASE и CREATE SCHEMA полные синонимы.

                  Существует FEDERATED engine, позволяющая создавать в локальной базе таблицы, ссылающиеся на таблицы в других базах (инстансах, локальных или удаленных) MySQL. Обычно по умолчанию этот движок отключен, но вообще он есть. На версии 5.1 работал, скажем так, странно, а более новые не смотрел.
            • +1
              Как тут уже написал zzashpaupat, внешний источник данных можно явно объявить через CREATE FOREIGN TABLE и делать запросы к двум базам таким образом. Как приятный довесок другой базой могут быть не только базы Postgres, но, и тот же MySQL, Redis, Mongo, MemCache, простые файлы на диске — всё, на что будет написан соответствующий враппер. Гуглить FDW.

              Вот: http://pgxn.org/tag/fdw/, если покопаться, даже к твиттеру есть.

              Но вообще, запросы к двум базам — это достаточно экстремальное занятие класса «100 избранных способов прострелить себе ногу», особенно, если мы используем СУБД чуть больше, чем хранилище для статей в любимом бложике и мы рассчитываем на согласованное состояние данных под нагрузками и в распределённых системах, это хозяйство требует хорошо прокачанных скиллов уровнем не ниже совета джедаев :-) Не уверен, что «начинающему пользователю» это «ну очень надо».
              • 0
                Спасибо большое за ссылки. Крутой конечно механизм внешних источников.
                • 0
                  На одном из прошлых проектов, вытаскивали справочные из сторонней СУБД через JDBC и запихивали в свою БД. Теперь понимаю, что можно было бы обернуть это дело через FDW и было бы куда проще.
                  • 0
                    я в таких случаях обычно пишу запрос в исходной БД в результате которого будет SQL запрос на вставку в другую БД. как правило 90% проблем миграции это решает. как вариант в xml формат.
                  • 0
                    В мускуле схема с двумя базами одного инстанса позволяет держать согласованность и т. п. обычными средствами, просто где-то в REFERENCE указываем не table3.field4, а database2.table3.field4. По сути это просто нэймспэйс, позволяющий структурировать таблицы, вьюхи и т. п.
                    • 0
                      Ну тогда это аналог посгресового термина «схема»
                      • 0
                        CREATE DATABASE и CREATE SCHEMA в мускуле полные синонимы :)
                        • 0
                          Они-то синонимы, но создается при этом база, а не схема.
                          • 0
                            А что считать базой, а что схемой?
                • 0
                  В мускле вью очень плохи тем, что те вью, что могли бы использоваться в реальной жизни, всегда оказываются теми, которые материализовываются. А это огромные временные таблицы на каждый чих.

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

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

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

                  А вот с TRUNCATE-ом не так все хорошо, что немного неочевидно. Увы.
                  • +1
                    угу, есть еще c enum проблемы
                    • 0
                      Некоторые возможно уже решены сторонними разработками: dklab.ru/lib/dklab_postgresql_enum
                      • +7
                        Использовал его во времена mysql, на постгресе как-то обходился без него.
                        На самом деле, даже лучше без него — определяйте значение констант на уровне приложения. Иначе каждый раз при добавлении значения вам нужно будет alter базы. Зачем это в высоконагруженной системе?
                        • 0
                          Да, мы тоже стали без него обходиться. Иногда рядом создаем таблицу-справочник, чтобы в базе было понятно, что к чему.
                          • +1
                            Ну кстати в Percona (и наверное уже в MySQL) альтер таблицы с ENUM не пересобирает таблицу целиком, если вы только добавляете новое значение поля.
                          • 0
                            Я как раз первый раз собираюсь их использовать в проде. Можно ли подробности?
                            • 0
                              невозможно добавить еще одно значение в тип внутри транзакции.
                              test=> begin; alter type mood add value 'test';
                              BEGIN
                              ERROR:  ALTER TYPE ... ADD cannot run inside a transaction block
                              


                          • 0
                            У PgAdmin, есть один существенный недостаток — не отображает clob-ы :(.
                            • +3
                              Есть pgAdmin III. Он хорош.

                              Он конечно лучше, чем консоль, но в нем есть много бяк. В первую очередь синхронные гуи, которые виснут намертво при первом же потерянном пакете/таймауте.
                              • +2
                                Да, когда последний раз им пользовался через двойной ssh туннель, то всё постоянно падало при первом удобном случае )=
                                • 0
                                  А еще он через ssh-подключение бекап не может сделать.
                                  • 0
                                    Посмотрите у меня на гитхабе, там есть phpPgAdmin на стероидах — пропатченны так, что им удобнее стало пользоваться. Рекомендую в нем первым делом отключить в конфиге фреймовость — без фреймов в разы удобнее.
                                    • 0
                                      … я имею в виду — удобно им пользоваться для браузинга под данным в таблицах. Менять схему, конечно, как было неудобно, так и осталось — для этого лучше применять, например EMS (он работает и под wine, кстати).
                              • 0
                                Есть еще ru.wikipedia.org/wiki/PhpPgAdmin

                                Но вообще, привыкнув к командной строке, я так и не смог перейти на GUI — всегда недостаточно функционала.
                              • +9
                                Посоветуйте хорошую статью по настройке pgbouncer-а, автоваккуума и проч. пожалуйста!
                                • +2
                                  По поводу автоваккуума, посмотрите презентации Ильи Космодемьянского, он на последнем PgDay как раз про это рассказывал
                                  http://pgday.ru/ru/papers/31 Так же много интересного есть в видео записях того же летного PgDay 2015. p.s надеюсь организаторы мне не сломают руку за ссылку.
                                  • 0
                                    вообще, для серьёзного проекта надо нанять кого-то типа Ильи и его команды. Они посоветуют и по серверу, и по настройкам ОС, и по базе со всеми заморочками естественно, и скажут вам в невежливой форме, если вы пишете совсем неправильно код для работы с бд.
                                  • +2
                                    Вообще очень рекомендую вот эту замечательную книгу: http://postgresql.leopard.in.ua/
                                    Помогает понять и решить очень многое.
                                  • +5
                                    В 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
                                    
                                    • +11
                                      не сочтите за рекламу, но альтернатива pgMyAdmin для Postgres есть в продуктах jetBreans, панельку Database если настроить,
                                      в запросах работает и автодополнение функций, названий таблиц и полей, скорость написания запросов можно в разы повысить + сохраняется история и все под рукой.
                                      • +1
                                        Еще можно pgAdmin использовать.
                                        • +2
                                          + фича о которой почему-то не все знают: при просмотре таблицы по F4 можно перейти от строки на связанные с ней внешними ключами (в обе стороны) строки других таблиц.
                                          А по Ctrl+Q открывается попап со «сводкой» в виде этой строки и связанных с ней строк.
                                          • 0
                                            Кстати, вопрос по Database в PhpStorm. Как сделать, чтобы уже созданные функции в редакторе показывали полный набор возвращаемых полей (когда возвращаем таблицу) вместо «пустого» set of record? И еще в передаваемых в функцию массивах вместо _int8 чтоб стоял указанный bigint[] и т.п…
                                            • +2
                                              JetBrains начал выносить эту «панельку» в самостоятельный продукт, 0xDBE. Пока получается неплохо.
                                              • 0
                                                Больше всего меня порадовал инструмент сравнения схем двух баз данных. Удобно, например, по необходимости сравнивать тестовую/девелоперскую базу с продакшеном. В результате выдается список запросов для обновления схемы продакшена. Миграции для ленивых, для небольших проектов, где нет времени/средств заморачиваться со специализированными инструментами или писать свои. Не всегда, правда, все сходу срабатывает без ручной доводки (ну например, если есть поле NOT NULL без дефолтного значения), но все же мне очень помогает.
                                              • 0
                                                Про менеджеры добавлю.
                                                Лучший из бесплатных — PgAdmin, из платных — EMS SQL Manager for PosgreSQL.
                                                Это по моему опыту.
                                                • +1
                                                  EMS безумно глючный. По моему опыту. Но более функционального действительно ничего не попадалось.
                                                  • 0
                                                    Кроме долгой прогрузки метаданных, на вскидку других претензий к нему не припомню.
                                                    • +2
                                                      а Navicat? пользуюсь — нареканий нет. (уже ниже написали)
                                                    • 0
                                                      EMS просто никакой. А вот https://www.dbvis.com/ вполне рабочий. Да и сам PgAdminIII неплох.
                                                      • +1
                                                        По-моему, из бесплатных все же лучший HeidiSQL. А из платных брали лицензию Navicat Premium.
                                                        • 0
                                                          HeidiSQL что-то виснет у меня наглухо при попытке смотреть свойства мастер-таблицы. Подобного поведения не встречал в PgAdmin, ни в ValentinaStudio.
                                                          • 0
                                                            Попробовал HeidiSQL для Postgre… Постоянно падает. А новая версия так при этом ещё и теряет все свои конфиги (оО). Правда я под wine-ом запускаю. Попробовал pgAdmin… Не глючит, да. Но как им вообще пользоваться, пока не очень понимаю. Правда в postgreSQL я ещё совсем чайник.
                                                          • 0
                                                            Лучше Navicat Premium из платных я ничего не припомню. По крайней мере в нем есть всё, что нужно и оно вполне стабильно работает, если привыкнуть к некоторым глюкам. Пробовал многие другие — намного более глючные или нет какой-то информации/функционала для продвинутого использования.
                                                            Из бесплатных — PgAdmin. Но, на сколько помню, он не умеет соединяться через ssh
                                                            • 0
                                                              Умеет, конечно же.
                                                              • 0
                                                                Уже умеет =) Версия, которая шла с postgresql 9.2 не имела такого функционала (только что проверил, там по теме была только вкладка SSL). С того момента я его и не использовал.
                                                          • +2
                                                            А есть что почитать про автовакуумы? Мне в наследство достался zabbix на postgres, так autovacuum доставил мне много истинной боли…
                                                            • +8
                                                              сталкивался с такой особенностью.
                                                              в pg нельзя изменить кодировку базы. т.е. против mysql-ного
                                                              ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
                                                              

                                                              в Postgres нужно сделать бекап -> создать новую базу с нужной кодировкой -> залить дамп -> грохнуть старую базу.

                                                              зы, возможно отстал от жизни, и данная проблема уже решена.
                                                              • +6
                                                                Увы, не решена. Ставим себе в привычку указывать utf8 при создании новой базы.
                                                                • +8
                                                                  Такая ситуация вообще не должна происходить. Поэтому разработчики postgresql и не делают этот функционал. Он нужен в единичных случаях чтобы исправить косяк того, кто базу создавал. И это правильно. Накосячил — страдай. В следующий раз думать будешь перед тем как делать.
                                                                  В постгресе вообще всё максимально строго. И это правильно.
                                                                  • +1
                                                                    Кодировка ладно, а вот коллэйт может меняться во время жизни приложения по объективным (независящим от разработчиков) причинам, например с украинского на русский.
                                                                  • +1
                                                                    Ну хз, хз. Есть исторические базы в однобайтовой кодировке. К которой, например, прикрутили новую версию софта, теперь в юникоде. И чего теперь?
                                                                    • 0
                                                                      Ну типичный единичный случай. Dump-Drop-Create-Restore. Это ж не каждый день делать придется, а один раз за жизнь проекта.
                                                                    • +1
                                                                      чтобы исправить косяк того, кто базу создавал. И это правильно. Накосячил — страдай.

                                                                      Может и ALTER TABLE тоже убрать? Чтобы все всегда сразу создавали правильную структуру или страдали.
                                                                      • 0
                                                                        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 — очень редко используемый функционал, требующий довольно сложной реализации (если бы было все так просто — давно бы уже сделали, не из вредности же разработчики не делают его).
                                                                        Приговор: нет смысла тратить время на его реализацию.

                                                                        Так понятнее моя точка зрения?
                                                                        • 0
                                                                          Кстати, на эту тему — колонку из varchar в int в postgresql переделать нельзя

                                                                          Можно:
                                                                          alter TABLE table_name ALTER COLUMN column_name TYPE int USING column_name::int;
                                                                          

                                                                          Если, конечно, данные сконвертятся.
                                                                          • +1
                                                                            Хм… Похоже, мне пора освежить свои знания о postgresql…
                                                                  • +3
                                                                    Не написали про обязательное приведение типов при работе в постресе(если чар сравнивать с интом будет ошибка, нужно делать что-то типа char_value::int = int_value)

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

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

                                                                    p.s. Хранимки на python рулят :)
                                                                    • 0
                                                                      насчет group by, вроде как в mysql 5.7 сделали такое же поведение по умолчанию
                                                                    • +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 вообще не нужен.

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

                                                                        И не нужен, есть EMS SQL Manager.
                                                                        Веб-морда к БД в продакшене это что-то с чем-то.
                                                                        • +1
                                                                          Из платных клиентов еще NaviCat хорош.
                                                                          • 0
                                                                            Вот для себя так и решил. Для удобной работы с данными — Navicat, для администрирования, просмотра статистики и анализа планов запросов — pgAdmin
                                                                          • +1
                                                                            Кроме функциональных индексов хорошая фича — частичные индексы. А уж если часто нужно выполнять запросы типа… WHERE is_active = 1 AND func(field1, field2) = value то мускул отдыхает вообще. Или когда нужно ограничение на уникальность не глобально на всю таблицу, например, номер счёта должен быть уникальным только среди выставленных и не отмененных счетов, а в черновиках и отмененных может быть любым — на мускуле такую задачу решить можно только на уровне приложения, а база не поможет.
                                                                            • –9
                                                                              INSERT IGNORE и INSERT ON DUPLICATE KEY UPDATE

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

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

                                                                              pgadmin отвратетиелен, но чтобы подправить что-то или сделать проверочную выборку, вполне достаточно. Для полноценной работы есть пока что ничем не заменимый Navicat

                                                                              • +9
                                                                                А зачем вам неразрывность последовательности AUTOINCREMENT ID?
                                                                                • –7
                                                                                  Для предварительной генерации чего-нибудь, когда знаешь, что выбирая WHERE id > 50 LIMIT 50 у тебя точно будут id с 51 по 100.
                                                                                  • +3
                                                                                    Плохая это практика, завязываться на ID. Да и потом откатываем транзакцию и мы уже потеряли следующий ID.
                                                                                    • –7
                                                                                      На MyISAM нет транзакций.

                                                                                      Минусующие — идите в жопу. Не зная задачи — не понимаете, почему выбран метод из «плохой практики».
                                                                                      • 0
                                                                                        Транзакции могут быть на уровне приложения.
                                                                                        • 0
                                                                                          В контекте ACID, как минимум Durability требует поддержки со стороны СУБД.

                                                                                          В случае с MyISAM, на сколько я помню, никаких гарантий сохранности данных при внештатной остановке MySQL вообще нет.
                                                                                        • +3
                                                                                          До тех пор, пока вы не объясните свою задачу и не обоснуете выбор «плохой практики», вас так и будут минусовать. За необоснованный выбор «плохой практики».
                                                                                • +2
                                                                                  Чтобы работать в продакшене с посгресом, его нужно хорошенько профессионально настроить

                                                                                  Точно такая же ситуация и с mysql. Так что весьма странно записывать это в недостатки postgresql.
                                                                                  • +1
                                                                                    Ну не… MySQL из коробки заводится даже на самом ужасном железе. Если взять MariaDB, так и того лучше. Тюнить если и надо, то по минимуму.
                                                                                    • +2
                                                                                      postgres аналогично заводится. речь о том, чтобы выжать максимум производительности по сравнению с mysql.
                                                                                      • 0
                                                                                        У меня с дефолтами не влез по памяти на довольно ущербной железяке…
                                                                                        • +2
                                                                                          крутится несколько проектов на самом дешевом тарифе от digitalocean. Еще ущербнее железяка?
                                                                                    • +7
                                                                                      Ну так и postgresql заводится с настройками из коробки. На низких нагрузках любая база заводится из коробки. Но автор говорил про большие нагрузки:
                                                                                      Если вы не выставите правильно shared_buffers, настройки автовакуумов и т.д., то на серьёзных нагрузках всё будет медленно работать.

                                                                                      А для серьезных нагрузок на mysql нужно настраивать, как минимум, key_buffer (для myisam, по дефолту 8МБ), query_cache_size (кэш запросов запрещен по дефолту), innodb_buffer_pool_size (для innodb, по дефолту 8МБ), innodb_flush_log_at_trx_commit.
                                                                                  • 0
                                                                                    for update skip locked
                                                                                    • +2
                                                                                      postgresql — никому не принадлежит, и полный порядок с комунити, нет разброда с форками
                                                                                      • +6
                                                                                        Михаил, я этот FUD довольно часто слышу от представителей Postgres Community, но никогда не мог добиться ответа на два вопроса:

                                                                                        1. В чём собственно заключается «разброд»? Например, общее количество дистрибутивов Linux даже подсчёту не поддаётся, но никто не говорит, что это плохо (кроме адептов BSD, конечно ;). Говорят, что это хорошо, open source, экосистема, конкуренция и вот это всё.

                                                                                        2. Где собственно «форки» MySQL. Единственным настоящим форком является MariaDB. Всё остальное (собственно, WebscaleSQL и Percona Server) являются скорее набором расширений, т.к. 1) постоянно и регулярно объединяют upstream со своими патчами и 2) предлагают патчи для включения в upstream.
                                                                                        • –1
                                                                                          так так, вы линукс то с майсиквелем не ровняйте)

                                                                                          разброд:
                                                                                          1) отсутствие тру бинарной репликации из-за архитектуры «плагабл» движков
                                                                                          2) хватит первого

                                                                                          форки — да ну ладно, их точно более двух, плюс движки. ну и «постоянно и регулярно объединяют upstream» как-то не очень звучит
                                                                                          • +3
                                                                                            Извините, но это не ответ. Вы мне сейчас пересказываете доклад Олега Царёва, я бы рад поговорить про этот доклад, но это уход от темы.

                                                                                            Мы обсуждаем разброд форков. Значит «разброд» — это отсутствие бинарной репликации. Допустим, но я стесняюсь спросить, а причём тут форки-то? Есть форки, где эта проблема решена?

                                                                                            И собственно форки. Мы насчитали только MariaDB, но вообще их «точно больше двух»? Про регулярное обновление, как я могу вас убедить? Вот, например, Percona Server версии x.y.z выходит после релиза MySQL x.y.z не позднее, чем через месяц. И да, в нём содержится весь функционал MySQL x.y.z + все перконовские патчи. Это достаточно убедительно?
                                                                                            • –3
                                                                                              это ответ. в субд нет бинарной репликации.

                                                                                              далее, что мешает перконе/марии не коммитить в оракл, или ораклу не принять патч перконы/марии, или другие сочетания. вот это убедительно.
                                                                                              • +3
                                                                                                Мда. Я, признаться, был лучшего мнения. Бинарная репликация есть, конечно, но дело и не в этом. В чём «разброд форков» мы так и не выяснили. Ну и ладно.

                                                                                                PS. Кому-то не принять патч от кого-то конечно никто не мешает. Так же как Fedora не обязана быть стопроцентной копией Debian или ArchLinux. Однако почему это не проблема для Linux, но вселенская проблема для MySQL, я тоже так и не понял.
                                                                                                • –1
                                                                                                  так. форков точно не два. есть как минимум oracle / maria / percona — в подавляющем числе случаев это разные ветки. при этом это разные компании — они конкурируют, их цели вообще говоря могут пересекаться, а с учетом маневров оракла, это всё выглядит так себе. эти компании в том числе двигают разные движки (еще один привет бинарному логу).

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

                                                                                                  а бинарная репликация — просто яркая особенность, одна из.

                                                                                                  • +3
                                                                                                    Я уже объяснил, что MariaDB — это единственный «форк» MySQL, то есть проект, который развивается независимо. Ни Percona, ни WebscaleSQL форками не являются, они жёстко привязаны к Oracle MySQL (вы посмотрите что ли в мой профиль).

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

                                                                                                    Ничего не понял, но пожалуйста, примеры «форков» из прикладного ПО, которые почему-то никому не мешают: OpenOffice/LibreOffice, GNU Emacs/XEmacs/Aquamacs, Konqueror/WebKit/Chrome.

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

                                                                                                    Чувствую, мне пора писать первый пост на Хабр. «Как правильно критиковать MySQL». Его есть за что критиковать, но то, как вы это делаете — хочется обнять и плакать, честное слово. Нельзя критиковать, просто повторяя где-то услышанные мантры, надо ж разбираться в предмете.
                                                                                                    • –2
                                                                                                      теперь вы приводите примеры текстовых редакторов и броузеров, ок. ждем постов

                                                                                                      • +3
                                                                                                        Хорошо, у меня персонально для Вас, уважаемый Михаил, есть пуленепробиваемый пример форков. Вот, говорят, список бывших и ныне живущих «форков» и дистрибутивов PostgreSQL: wiki.postgresql.org/wiki/PostgreSQL_derived_databases

                                                                                                        В этом контексте хотелось понять: почему форки PostgreSQL — это «полный порядок с комунити, нет разброда с форками», а форки MySQL — это таки «разброд», ужас, нет бинарной репликации, патчи не примут, звучит как-то не очень, выглядит так себе, и т.д.?
                                                                                                        • –2
                                                                                                          тааак, отлично, вернулись к базам от текстовых редакторов.

                                                                                                          то, что вы привели — это «derived forks and rebranded distributions» это не альтернативные ветки postgres а (bsd лайк лицензия располагает). никто не купил postgres (нечего покупать) и не стал в защиту делать maria_pg.
                                                                                                          • +1
                                                                                                            Так вот, теперь, когда мы говорим на одном языке, MariaDB — это «derived fork». А WebscaleSQL и Percona Server — это «rebranded distributions». Что с ними не так и чем они отличаются от 43 СУБД из того списка?

                                                                                                            Да, PostgreSQL не принадлежит коммерческой организации. Но дальше-то что?
                                                                                                            • 0
                                                                                                              > MariaDB — это «derived fork»

                                                                                                              угу, вы это им расскажите, интересный будет разговор.

                                                                                                              mariadb.org/en/about
                                                                                                              «MariaDB An enhanced, drop-in replacement for MySQL»

                                                                                                              и отказ от innodb
                                                                                                              • +2
                                                                                                                Я не вижу противоречий между «derived fork», «enhanced drop-in replacement». Первый описывает происхождение проекта, второй — краткое описание самого проекта.

                                                                                                                Никакого отказа от InnoDB в MariaDB нет. Можно использовать XtraDB или InnoDB на выбор. Где вы все эти сплетни собираете?
                                                                                                                • 0
                                                                                                                  да какие сплетни? одно делается в замену другому: не как отнаследуюсь и что-то добавлю, а как замена.
                                                                                                                  • +4
                                                                                                                    Ох, XtraDB — это «derived fork» и «ennhanced drop-in replacement» InnoDB. Унаследованный, да. Это я вам как разработчик XtraDB говорю.

                                                                                                                    Кстати, у меня теперь отрицательная карма, какой-то поклонник PostgreSQL постарался. Я не знаю, как здесь всё работает, но по-моему посты с отрицательной кармой писать нельзя. Печаль…
                                                                                                                    • 0
                                                                                                                      разработчик XtraDB с минусовой кармой на хабре… ) хабр — это нечто, болотце порядочное)

                                                                                                                      ну ок. вы, так сказать, лицо заинтересованное, ну ладно, допустим. возможно у вас там так принято считать и «drop-in replacement» это как бы без конфликтов.

                                                                                                                      но! mariadb.com/kb/en/mariadb/using-innodb-instead-of-xtradb майсикуель прекрасен конечно и вот как это идет на пользу? мы не будем за стабильность, давай rps тут, а стабильность там, и не одно с другим, а одно за место другого?!
                                                                                                                      • 0
                                                                                                                        Я не очень понял вопрос. Вот есть InnoDB. И есть XtraDB = InnoDB + delta. Разработчики MariaDB предлагают выбор: по умолчанию XtraDB, но если пользователь хочет, он может перейти на ванильный InnoDB одной строчкой в конфиге.

                                                                                                                        Там не написано, что InnoDB — это только стабильность, а XtraDB — это только performance. Там перечислены теоретические причины, по которым этот выбор вообще может понадобиться.
                                                                                                                        • –2
                                                                                                                          теоретические, да. тем более интересно, если это касается top critical части субд, теоретические рассуждения на этот счет. может самолет и упадет, если быстро полетим, а может и нет, сложно сказать.
                                                                                                                          • +1
                                                                                                                            Ну так и самолёты падают. И в PostgreSQL баги бывают, да? Я вот попользвался PostgreSQL 5 минут и сразу нашёл баг. Правда я знал где искать ;)
                                                                                                                            • 0
                                                                                                                              > попользвался PostgreSQL 5 минут и сразу нашёл баг

                                                                                                                              «самолет упал» и вы не смогли восстановиться?
                                                                                                                              какой баг?
                                                                                                                              • +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 это давно проходили и исправили. Поэтому я знал, где копнуть :)
                                                                                                                                • 0
                                                                                                                                  > я записываю правильное 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
                                                                                                                                  • +4
                                                                                                                                    Да я понимаю, что «документировано». Но invalid data, как ни крути.

                                                                                                                                    И в MySQL этот test case работает без всякого подкручивания конфига А если в PostgreSQL я выставлю extra_float_digits, то получу правильные значения для одних чисел и «мусор» для других. И как быть, если чисел у меня много?

                                                                                                                                    И дело тут не в точности, а в том, что разработчики PostgreSQL не очень разобрались в предмете. И их ещё много чудных открытий ждёт.
                                                                                                                                    • +1
                                                                                                                                      invalid data — даже сессия не упала, не то что движок! и о потери базы целиком никто даже не говорит.

                                                                                                                                      > PostgreSQL не очень разобрались в предмете

                                                                                                                                      забавно) это мне говорит разработчик базы, которая с нулами не работает и делит на 0 по-тихому. и это только то, что на первом плане
                                                                                                                                      • 0
                                                                                                                                        а на счет флоатов — это как бы сразу скользкая тема, хранить их в базе в таком виде, да еще и на граничных величинах — весьма сомнительное занятие.
                                                                                                                                    • 0
                                                                                                                                      set extra_float_digits = 3 на сессию дампа и всё работает
                                                                                                                                      • +1
                                                                                                                                        Увы и ах, печатать больше чем DBL_DIG (15) цифр нельзя. Почитайте определение DBL_DIG. То есть, extra_float_digits — это костыль, чтобы исправить один случай и сломать другие.
                                                                                                                                        • 0
                                                                                                                                          что же там ломается?

                                                                                                                                          а дамп по дефолту в pg юзает доп цифры
                                                                                                                                          http://www.postgresql.org/message-id/flat/20090909185152.GA7893@kehcheng.Stanford.EDU#20090909185152.GA7893@kehcheng.Stanford.EDU

                                                                                                                                          вот пример комунити — написал, разобрали
                                                                                                                                          • 0
                                                                                                                                            Ну, то есть 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 нет списков рассылок, форумов и прочего, где пользователям отвечают на вопросы?
                                                                                                                                    • 0
                                                                                                                                      То есть, вставил число, сделал дамп базы, а дамп потом не импортируется. Круто? :)

                                                                                                                                      Тестировали или умозрительно злорадствуете? Все импортируется.
                                                                                                                                      А здесь тоже тупой постгрес виноват:
                                                                                                                                      postgres=# select 2.1::real - 2;
                                                                                                                                      
                                                                                                                                            ?column?
                                                                                                                                      --------------------
                                                                                                                                       0.0999999046325684
                                                                                                                                      

                                                                                                                                      ?
                                                                                                                                      Мы в MySQL это давно проходили и исправили

                                                                                                                                      Ну расскажите нам, как и что вы исправили
                                                                                                                                      • +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, процессора и флагов компиляции.

                                                                                                                                        Ну как-то так.
                                                                                                                                        • 0
                                                                                                                                          Этот мусор может иметь и более опасные последствия. Поскольку стандарт не гарантирует преобразование без потерь при печати чисел с числом десятичных цифр > DBL_DIG, данные могут искажаться при экспорте/импорте.
                                                                                                                                          Стандарт гарантирует минимум 3 лишних цифры, но рекомендует не иметь таких ограничений вообще.

                                                                                                                                          Мусор никому не мешает, до тех пор пока «Conversion to an external character sequence must be such that conversion back using round to even will recover the original number.»
                                                                                                                                          • +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 расчитывает при экпорте/импорте. И где-то как-то оно работает, пока не случится ой.
                                                                                                                                            • 0
                                                                                                                                              «До тех пор, пока» это часть стандарта, так что никакого undefined behavior нет.

                                                                                                                                              Разница в том что если мы хотим видеть только достоверные десятичные цифры, то выводить надо DBL_DIG=15 цифр. Но в таком случае одно десятичное представление может иметь несколько бинарных аналогов.

                                                                                                                                              Если мы хотим однозначное соответствие между бинарным и десятичным представлениями, то выводить надо DBL_DIG+2=17 цифр.

                                                                                                                                              Никакого undefined behavior, все по стандарту, просто значение по умолчанию реализует одно поведение, если надо другое ставим extra_float_digits=2 и все.
                                                                                                                                              • 0
                                                                                                                                                > Разница в том что если мы хотим видеть только достоверные десятичные цифры, то выводить надо 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 при экспорте. Тут ссылка уже была.
                                                                                                                                                • 0
                                                                                                                                                  Не существует никакого однозначного соответствия для 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 нет.
                                                                                                                            • 0
                                                                                                                              И отдельным комментом про подключаемые движки (раз пост мне не дают написать). Это безусловно усложняет архитектуру, но есть и плюсы. Разные движки заточены под разные цели, нельзя иметь один единственный «православный» движок, который оптимален на любых нагрузках.

                                                                                                                              Например, сильной стороной InnoDB как и всех движков на B-Tree (включая PostgreSQL) является primary key lookups на полностью закешированных данных. На таких нагрузках вряд ли PostgreSQL сможет соревноваться с InnoDB, хотя было бы интересно проверить.

                                                                                                                              Слабая сторона B-Tree движков — интенсивная запись в базу, особенно когда dataset не умещается в память. Особенно когда много индексов, или нужна компрессия. Для таких нагрузок какой-нибудь TokuDB покажет результаты, которые не снились ни InnoDB, ни PostgreSQL.

                                                                                                                              Ещё раз, тезисно: 1) нет идеального способа хранить данные; 2) MySQL предлагает варианты с помощью движков, PostgreSQL — нет. 3) да, возможность иметь подключаемые движки имеет свою цену. Архитектура усложняется, сервер не всегда может «срезать углы», как если бы он работал с одним движком. Да, приходится дублировать журналы. Нет, это не так плохо, как рассказывает Олег в презентации :)
                                                                                                                              • 0
                                                                                                                                я не очень понимаю, при чем тут движки. Postgres не предлагает варианты с помощью движков, но он предлагает кучу видов индексов, не только b-tree (еще Hash, GiST, SP-GiST, GIN)
                                                                                                                                К примеру, для задачи, где нужно много записи и индексы занимают много места — в 9.5 вводят индекс нового типа BRIN. Почти бесплатен при записи, но хуже на чтении.
                                                                                                                                Кроме того, насколько я понимаю, в посгресе можно кластеризовать таблицу под конкретный индекс. Это то, что в innodb сделано для primary key
                                                                                                                                • +1
                                                                                                                                  Ну разные типы индексов — это только часть решения. В MySQL/InnoDB тоже предлагает специализированные индексы, хоть и в меньшем количестве.

                                                                                                                                  Hash, GiST, GIN вам никак не помогут при интенсивной записи. Если BRIN помогает — хорошо, но вот TokuDB умеет ещё компрессию. Причём алгоритмы компрессии (surprise!) тоже pluggable. Хочется сильнее сжимать но медленние — есть zlib. А хочется слабее, но быстрее — есть quicklz. А можно вообще свой алгоритм добавить. Как-то так. Это я ещё не касаюсь сильно специализированных движков типа NDB.

                                                                                                                                  По поводу кластерных индексов — если я правильно читаю документацию, в PostgreSQL это «одноразовая» операция. Можно кластеризовать готовые данные, но новые данные будут храниться опять как попало. В InnoDB данные кластеризованы всегда. А в TokuDB вообще можно иметь несколько кластерных ключей одновременно. Опять же, такое никак не сделать добавлением нового типа индекса, да?
                                                                                                                                  • 0
                                                                                                                                    ну в целом понятно, удел движков — кеши, ок
                                                                                                                                • 0
                                                                                                                                  бррр… само по себе «подключаемое» это очень хорошо. ( постгрес весь максимально плагабл, но без ущерба (как минимум, без отсутствия контроля над) acid-у в узле.)

                                                                                                                                  но блин! если вы при этом получаете ограничение на утилизацию железа, тем что реплика не успевает проигрывать транзакциии! как же это если, это не ужасно?

                                                                                                                                  потом, давайте тут не будем сфеерическими тестами, я видел сотни тысяч rps на postgres и полную утилизацию железа. а майсиквел не может утилизировать мастер — реплика не поспевает. так какая мне разница какой бенч тогда на одном узле, надо бенчить скорость реплкики.
                                                                                                                                  • +6
                                                                                                                                    Давайте я уже перескажу доклад Олега, чтобы мы больше к нему не возвращались:

                                                                                                                                    — в базе данных живут гномики;
                                                                                                                                    — в огороде бузина, а в MySQL — binary log
                                                                                                                                    — у нас в проекте тормозила репликация
                                                                                                                                    — у нас в проекте репликация CPU-bound, а значит у всех должна быть такой
                                                                                                                                    — мы взяли альфа-версию MySQL 5.7, которая должны была решить наши проблемы
                                                                                                                                    — но она не решила
                                                                                                                                    — почему — мы так и не разобрались
                                                                                                                                    — вопросы?

                                                                                                                                    Нет, правда.
                                                                                                                                    • 0
                                                                                                                                      да, всё верно. печально. и то что мария вместо решения всего этого предлагает еще больше rps (хотя cpu уже и так нет) но при этом теоретически больше краша (а еще страшнее, что без возможности восстановления) я называю «разброд»
                                                                                                                                      • 0
                                                                                                                                        Я к тому, что из того доклада вообще ничего не понятно. Ни в чём конкретно была проблема, ни что было сделано для решения.
                                                                                                                                        • 0
                                                                                                                                          ничего не было сделано, где комунити? (разброд: это не мое, то не сё, а там оракл, поддержки нет, все хотят денег) у человека одна из крупнейших в европе систем была.
                                                                                                                                          • +1
                                                                                                                                            А я откуда знаю, где комунити? С крупнейшей-то в европе системой можно было и у Перконы поддержку купить. Но нет
                                                                                                                                            • 0
                                                                                                                                              вот именно, хрен кто что будет развивать. плати бабки — вот это и разброд, другой подход к снаряду, люди по другому видят развитие программного продукта и место коммунити в нём.

                                                                                                                                              а по факту ничего бы перкона бы не сделала, деньги бы взяли, да, и что-то бы даже переделали (tokudb там еще муть какую-нибудь, чтобы все концы спрятать ), но репликация бы так и не работала. потому, что всё, мы так зарабатываем.
                                                                                                                                              • 0
                                                                                                                                                Ну это уже какие-то фантазии пошли. «бы», да «кабы». Несерьёзный разговор.
                                                                                                                                                • 0
                                                                                                                                                  а какой может быть серьезный, если его и не может быть… так как полноценного открытого коммунити нет
                                                                                                                                                  • 0
                                                                                                                                                    Михаил, вы сначала вот сюда гляньте: http://db-engines.com/en/ranking

                                                                                                                                                    А потом возвращайтесь, поговорим про community.
                                                                                                                                                    • 0
                                                                                                                                                      а вы потом удивляетесь, почему у вас на хабре минус в рейтинге? там плюс, тут минус)