Базовые различия при работе с базами данными MySQL и PostgreSQL Дилетантский обзор

    Продолжая свое знакомство с БД PostgreSQL, с уже имеющимися навыками работы в БД MySQL, обнаружил ряд интересных полезных особенностей которые на практике часто не хватало в MySQL. Цель этого обзора не в создании бесконечного спора, что лучше, а дать легкое сравнение, которое обычно обсуждается программистами на обеденном перерыве в ближайшей кафешке. В сравнении обретаются новые знания и опыт, поэтому оно того стоит.

    1. Вакансиях многих компаний довольно часто пишут требуемые знания через косую черту MySQL/PostgreSQL

    На мой взгляд это совершенно разные базы данных и поэтому просто ставя между ними косую черту учитывая лишь во внимание написание схожих SQL запросов не совсем правильно. Все таки пару месяцев нужно для PostgreSQL, чтобы начать себя уверенно чувствовать в клиенте psql после MySQL.

    Что я бы выделил на этапе компиляции из исходников этих двух БД,

    1.1 PostgreSQL не имеет типы движков (MySQL — innodb, mysql, archive и т.д), но имеет кучу расширений, подобно PHP, которые можно дополнительно ставить, расширяя возможности. Создается впечатление, что PostgreSQL это своего рода каркас, который набиваешь функциональностью.
    1.2 Разворачивание сервера MySQL сводиться лишь по сути к запуску сервера (systemctl start mysql.conf, service mysql start), тогда как в PostgreSQL нужно завести отдельного пользователя (в операционной системе) для запуска сервера, развернуть отдельно кластер (крутое слово, но по сути тоже самое, что и в MySQL — несколько баз данных на одном сервере)
    1.3 Физическое указание расположения новых таблиц на диске (табличное пространство) на уровне SQL для PostgreSQL.

    и т.д.

    2. Различия в клиентах при запросах к БД — psql и mysql.

    2.1 Что явно не хватает в MySQL, так это подобие команды \watch в psql, которая позволяет, указав секунды, повторять выполнение SQL запроса (аналог утилиты watch -n). Это обычно удобно для того, чтобы отслеживать как идет миграция (наполнение данных в таблицах)

    select NOW() \watch 1;
    


    2.2 В PostgreSQL по умолчанию все выполняемые запросы не отображают время исполнения, в отличие от MySQL, нужно дополнительно указывать команду \timing. Повторное выполнение команды отключает опцию. Такой прием часто встречается во многих настройках PostgreSQL, в отличие от MySQL, где это нужно писать более длинее. При этом, в PostgreSQL, когда смотришь справочную информацию, то рядом в круглых скобочках отображается текущее значение просматриваемой настройки. Очень удобно. Плохо только, что одним шрифтом теста идет, не сразу зрительно быстро воспринимается.

    2.3 В PostgreSQL можно быстро просмотреть историю запросов из psql командой \s, тогда как в клиенте MySQL нужно использовать клавиши вверх/вниз задействую функционал readline библиотеки (либо смотреть историю команд отдельно от клиента mysql). Это часто нужно, когда тестируешь повторно запрос на использование индексов. Было бы удобней в PostgreSQL после набора \s вместо копирования запроса, набирать номер запроса, как это делается при profile в MySQL.

    3. Есть много общих моментов, которые, к примеру в MySQL более удобны, а в PostgreSQL более сложны.

    К примеру, вывод результата select, который не помещается по горизонтали. В обоих клиентах баз данных есть вертикальный вывод. Для MySQL достаточно добавить в конец запроса \G, тогда как в PostgreSQL в начале нужно выполнить \pset expanded. Когда нужно по быстрому просмотреть, вариант PostgreSQL на мой взгляд это не совсем удобно.

    4. Особо интересный момент в PostgreSQL, в отличие от MySQL, это более тесная интеграция с bash оболочкой.

    Можно из psql выполнять shell команды (наподобие как в vim редакторе :! pwd), сохранять в переменные результат и затем использовать в генерации SQL запросов. В MySQL это все можно тоже сделать, но более длинными и не всегда удобными путями.

    5. PostgreSQL выделяется особой любовью к использованию переменных окружения (export) в отличие от MySQL.

    Ты это чувствуешь сразу после того, как скомпилировал исходники и начинаешь “разворачивать” сервер, указывая путь к директории базы данных (-D или PGDATA).

    На этом думаю, закончить свой беглый дилетантский обзор. Как я уже писал, целью является не позиционирование той или иной БД, а получение дополнительного опыта через сравнение. Для себя конкретно изучение PostgreSQL является дополнительным конкурентным техническим преимуществом.
    • +10
    • 13k
    • 8
    Поделиться публикацией
    Похожие публикации
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 8
    • +5
      Насчёт пункта 3 есть \x auto — когда помещается будет колоночный вывод, в противном случае построчный. Вообще проще всего написать для себя файлик .psqlrc. У меня он примерно такого содержимого:
      \set COMP_KEYWORD_CASE upper
      \x auto
      \pset null ¤
      

      Psql по сравнению со всеми остальными подобными консольными утилитами просто небо и земля. Вот хорошая статья с примерами использования.
      • +5
        Основное бесиво, это реакция mysql консоли на Ctrl+C, когда хочется начать использовать это :)

        psqlrc забыл:
        \set QUIET 1
        -- formatting
        \x auto
        
        \set VERBOSITY verbose
        \set ON_ERROR_ROLLBACK interactive
        -- show execution times
        \timing
        -- limit paging
        \pset pager off
        -- replace nulls
        \pset null ¤
        \pset linestyle unicode
        \pset border 2
        
        -- colorize
        --\set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# '
        \set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
        --harolds
        --\set PROMPT1 '%[%033[1m%]%M/%/%R%[%033[0m%]%# '
        \set PROMPT2 '[more] %R > '
        
        
        
        --logging
        -- Use a separate history file per-database.
        \set HISTFILE ~/.psql_history- :DBNAME
        -- If a command is run more than once in a row, only store it once in the
        -- history.
        \set HISTCONTROL ignoredups
        
        -- Autocomplete keywords (like SELECT) in upper-case, even if you started
        -- typing them in lower case.
        \set COMP_KEYWORD_CASE upper
        
        -- greeting needs to know where we are
        -- \echo '\nWelcome, my magistrate\n'
        
        \set clear '\\! clear;'
        
        --helpful queries
        \set uptime 'select now() - backend_start as uptime from pg_stat_activity where pid = pg_backend_pid();'
        \set show_slow_queries 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;'
        \set settings 'select name, setting,unit,context from pg_settings;'
        \set conninfo 'select usename, count(*) from pg_stat_activity group by usename;'
        \set activity 'select datname, pid, usename, application_name,client_addr, client_hostname, client_port, query, state from pg_stat_activity;'
        \set waits 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;'
        \set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'
        \set tablesize 'SELECT nspname || \'.\' || relname AS \"relation\", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY pg_relation_size(C.oid) DESC LIMIT 40;'
        \set uselesscol 'SELECT nspname, relname, attname, typname, (stanullfrac*100)::int AS null_percent, case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end AS \"distinct\", case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end AS \"values\" FROM pg_class c JOIN pg_namespace ns ON (ns.oid=relnamespace) JOIN pg_attribute ON (c.oid=attrelid) JOIN pg_type t ON (t.oid=atttypid) JOIN pg_statistic ON (c.oid=starelid AND staattnum=attnum) WHERE nspname NOT LIKE E\'pg\\\\_%\' AND nspname != \'information_schema\' AND relkind=\'r\' AND NOT attisdropped AND attstattarget != 0 AND reltuples >= 100 AND stadistinct BETWEEN 0 AND 1 ORDER BY nspname, relname, attname;'
        
        
        -- 4 helpful queries from radek http://radek.cc/2009/08/15/psqlrc-tricks-table-sizes/
        \set trashindexes '( select s.schemaname as sch, s.relname as rel, s.indexrelname as idx, s.idx_scan as scans, pg_size_pretty(pg_relation_size(s.relid)) as ts, pg_size_pretty(pg_relation_size(s.indexrelid)) as "is" from pg_stat_user_indexes s join pg_index i on i.indexrelid=s.indexrelid left join pg_constraint c on i.indrelid=c.conrelid and array_to_string(i.indkey, '' '') = array_to_string(c.conkey, '' '') where i.indisunique is false and pg_relation_size(s.relid) > 1000000 and s.idx_scan < 100000 and c.confrelid is null order by s.idx_scan asc, pg_relation_size(s.relid) desc );'
        \set missingindexes '( select src_table, dst_table, fk_name, pg_size_pretty(s_size) as s_size, pg_size_pretty(d_size) as d_size, d from ( select distinct on (1,2,3,4,5) textin(regclassout(c.conrelid)) as src_table, textin(regclassout(c.confrelid)) as dst_table, c.conname as fk_name, pg_relation_size(c.conrelid) as s_size, pg_relation_size(c.confrelid) as d_size, array_upper(di.indkey::int[], 1) + 1 - array_upper(c.conkey::int[], 1) as d from pg_constraint c left join pg_index di on di.indrelid = c.conrelid and array_to_string(di.indkey, '' '') ~ (''^'' || array_to_string(c.conkey, '' '') || ''( |$)'') join pg_stat_user_tables st on st.relid = c.conrelid where c.contype = ''f'' order by 1,2,3,4,5,6 asc) mfk where mfk.d is distinct from 0 and mfk.s_size > 1000000 order by mfk.s_size desc, mfk.d desc );'
        \set rtsize '(select table_schema, table_name, pg_relation_size( quote_ident( table_schema ) || \'.\' || quote_ident( table_name ) ) as size, pg_total_relation_size( quote_ident( table_schema ) || \'.\' || quote_ident( table_name ) ) as total_size  from information_schema.tables where table_type = \'BASE TABLE\' and table_schema not in (\'information_schema\', \'pg_catalog\') order by pg_relation_size( quote_ident( table_schema ) || \'.\' || quote_ident( table_name ) ) desc, table_schema, table_name);'
        \set tsize '(select table_schema, table_name, pg_size_pretty(size) as size, pg_size_pretty(total_size) as total_size from (:rtsize) x order by x.size desc, x.total_size desc, table_schema, table_name);'
        
        
        -- Taken from https://github.com/heroku/heroku-pg-extras
        -- via https://github.com/dlamotte/dotfiles/blob/master/psqlrc
        \set bloat 'SELECT tablename as table_name, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat, CASE WHEN relpages < otta THEN ''0'' ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint) END AS table_waste, iname as index_name, ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat, CASE WHEN ipages < iotta THEN ''0'' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS index_waste FROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta, COALESCE(c2.relname,''?'') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename) AS nullhdr FROM pg_stats s, ( SELECT (SELECT current_setting(''block_size'')::numeric) AS bs, CASE WHEN substring(v,12,3) IN (''8.0'',''8.1'',''8.2'') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ ''mingw32'' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo) AS constants GROUP BY 1,2,3,4,5) AS foo) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> ''information_schema'' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml ORDER BY CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END DESC;'
        \set blocking 'select bl.pid as blocked_pid, ka.query as blocking_statement, now() - ka.query_start as blocking_duration, kl.pid as blocking_pid, a.query as blocked_statement, now() - a.query_start as blocked_duration from pg_catalog.pg_locks bl join pg_catalog.pg_stat_activity a on bl.pid = a.pid join pg_catalog.pg_locks kl join pg_catalog.pg_stat_activity ka on kl.pid = ka.pid on bl.transactionid = kl.transactionid and bl.pid != kl.pid where not bl.granted;'
        \set cache_hit 'SELECT ''index hit rate'' as name, (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) as ratio FROM pg_statio_user_indexes union all SELECT ''cache hit rate'' as name, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;'
        \set index_size 'SELECT relname AS name, pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE reltype=0 GROUP BY relname ORDER BY sum(relpages) DESC;'
        \set index_usage 'SELECT relname, CASE idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC;'
        \set index_usage_adv 'SELECT * FROM (SELECT stat.relname AS table, stai.indexrelname AS index, CASE stai.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stai.idx_scan / (stat.seq_scan + stai.idx_scan))::text || ''%'' END hit_rate, CASE stat.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stat.idx_scan / (stat.seq_scan + stat.idx_scan))::text || ''%'' END all_index_hit_rate, ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM generate_subscripts(idx.indkey, 1) AS k ORDER BY k) AS cols, stat.n_live_tup rows_in_table FROM pg_stat_user_indexes AS stai JOIN pg_stat_user_tables AS stat ON stai.relid = stat.relid JOIN pg_index AS idx ON (idx.indexrelid = stai.indexrelid)) AS sub_inner ORDER BY rows_in_table DESC, hit_rate ASC;'
        \set locks 'select pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, substr(pg_stat_activity.query,1,30) as query_snippet, age(now(),pg_stat_activity.query_start) as "age" from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_stat_activity.query <> ''<insufficient privilege>'' and pg_locks.pid=pg_stat_activity.pid and pg_locks.mode = ''ExclusiveLock'' order by query_start;'
        \set long_running_queries 'SELECT pid, now() - pg_stat_activity.query_start AS duration, query AS query FROM pg_stat_activity WHERE pg_stat_activity.query <> ''''::text AND now() - pg_stat_activity.query_start > interval ''5 minutes'' ORDER BY now() - pg_stat_activity.query_start DESC;'
        \set ps 'select pid, application_name as source, age(now(),query_start) as running_for, waiting, query as query from pg_stat_activity where query <> ''<insufficient privilege>'' AND state <> ''idle'' and pid <> pg_backend_pid() order by 3 desc;'
        \set seq_scans 'SELECT relname AS name, seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'
        \set total_index_size 'SELECT pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE reltype=0;'
        \set unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
        \set missing_indexes 'SELECT relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan > 0 THEN ''Missing Index?'' ELSE ''OK'' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname=''public'' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC;'
        
        -- Development queries
        
        \set sp 'SHOW search_path;'
        \set clear '\\! clear;'
        \set ll '\\! ls -lrt;'
        
        \unset QUIET
        
        
        • 0
          Ох ты ж блин, огромное тебе спасибо!
      • +1
        Также для Postgresql есть отличная утилита с автодополнением pgcli, а для Mysql — mycli
        • НЛО прилетело и опубликовало эту надпись здесь
          • 0
            Согласен, но не совсем. Я рассматриваю сравнение с точки зрения программиста. Для прикладного уровня проекта в принципе да, не имеет значение какая база данных. Под прикладным я понимаю, взять простым запросом небольшое количество данных и нарисовать в табличной форме. Другой вопрос, когда такая таблица из разряда middle/big table, то тут уже нужно понимать как строится запрос на уровне базы данных, как хранятся данные.
            • 0
              С точки зрения программиста различий в посте не указано.
              • НЛО прилетело и опубликовало эту надпись здесь

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