Postgres.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Транзакции, ACID, MVCC, WAL."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "https://edu.postgrespro.ru/dba1/dba1_05_arch_mvcc.pdf"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Перечислить требования ACID к транзакционной системе;"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![acid](attest_image/trans.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Каким средством реализовано выполнение этих требований в PostgreSQL?"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### MVCC"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![mvcc](attest_image/mvcc.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![snap](attest_image/snap.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Многоверсионность (multiversion concurrency control) – один из возможных способов организации доступа к данным. Из четырех классических требований ACID к управлению транзакциями этот механизм имеет непосредственное отношение к атомарности (транзакция либо выполняется полностью, либо полностью отменяется), согласованности (транзакция сохраняет целостность данных) и изоляции(одновременно выполняющиеся транзакции не должны влиять друг на друга).\n",
    "\n",
    "Механизм состоит в поддержке на низком уровне одновременно нескольких версий данных. Транзакции не видят этого; они работают соснимком, который из многих версий составляет согласованную на определенный момент времени картину данных. В зависимости от уровня изоляции, снимок может определяться в момент начала транзакции (уровени repeatable read, serializable) или отдельно для каждой операции (уровень read committed).\n",
    "\n",
    "Таким образом, транзакции смотрят на данные через призму снимков и могут видеть разную (но согласованную) информацию. Разумеется, снимок не является полной физической копией всех данных: это только логическое представление, и его можно организовать по-разному. Простой способ состоит в полном ограничении одновременного доступа: и изменений, и чтений. Но при эффективной реализации – как в Постгресе и Оракле – читающая транзакция никогда не будет заблокирована другими транзакциями, читающими или изменяющими те же данные – каждая из них будет независимо работать со своей версией. Блокироваться будут только попытки изменить данные, которые уже изменены другой транзакцией, но еще не зафиксированы."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### WAL"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "https://edu.postgrespro.ru/dba1/dba1_06_arch_wal.pdf"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![wal](attest_image/wal.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "что когда при сбое пропадает содержимое оперативной памяти, для восстановления необходим журнал предзаписи. Размер необходимых файлов журнала и время восстановления ограничены благодаря периодически выполняемой контрольной точке.\n",
    "\n",
    "В предыдущих статьях мы уже посмотрели на довольно большое число важных настроек, так или иначе относящихся к журналу. В этой статье (последней в этом цикле) мы рассмотрим те вопросы настройки, которые еще не обсуждались: уровни журнала и их назначение, а также надежность и производительность журналирования."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Уровни журнала:  \n",
    "    - minimal\n",
    "    - replica\n",
    "    - logical"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Перечислить уровни изоляции транзакций. Отличие Read Committed от Repeatable Read. В каких случаях необходимо использовать Repeatable Read?"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/iso.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "**repeatable read** -  Этот уровень удобен,\n",
    "например, для отчетов, состоящих из нескольких запросов."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Сборка мусора "
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/vacum.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Индексы"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Для чего нужны индексы"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Ускоряют доступ к данным"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Будет ли прочитана вся таблица в случае запроса?"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "SELECT * FROM table_name ORDER BY name LIMIT 1\n",
    "# Зависит от индекса, если есть индекс то индекс уже отсортирован, и не будет вычитываться вся таблица"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Виды индексов"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "PostgreSQL поддерживает несколько типов индексов: \n",
    "\n",
    "- B-дерево, \n",
    "- хеш, \n",
    "- GiST, \n",
    "- SP-GiST, \n",
    "- GIN и \n",
    "- BRIN. \n",
    "\n",
    "Для разных типов индексов применяются разные алгоритмы, ориентированные на определённые типы запросов. По умолчанию команда CREATE INDEX создаёт индексы типа B-дерево, эффективные в большинстве случаев.\n",
    "\n",
    "B-деревья могут работать в условиях на равенство и в проверках диапазонов с данными, которые можно отсортировать в некотором порядке. Точнее, планировщик запросов PostgreSQL может задействовать индекс-B-дерево, когда индексируемый столбец участвует в сравнении с одним из следующих операторов:\n",
    "\n",
    "- \"<\"\n",
    "- \"<=\"\n",
    "- \"=\"\n",
    "- \">=\"\n",
    "- \">\"  \n",
    "\n",
    "При обработке конструкций, представимых как сочетание этих операторов, например BETWEEN и IN, так же может выполняться поиск по индексу-B-дереву. Кроме того, такие индексы могут использоваться и в условиях IS NULL и IS NOT NULL по индексированным столбцам."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Почему мы не используем индекс HASH"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Хеш-индексы работают только с простыми условиями равенства. Планировщик запросов может применить хеш-индекс, только если индексируемый столбец участвует в сравнении с оператором =."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Операции с хеш-индексами в настоящее время не проходят через WAL, так что после аварийной остановки базы данных может потребоваться перестроить хеш-индексы командой REINDEX. Кроме того, изменения в хеш-индексах после начальной копии не переносятся при потоковой или файловой репликации, так что в последующих запросах они будут давать неправильные ответы. По этим причинам настоятельно рекомендуется не использовать их."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Индекс B-tree"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/btree.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/btree2.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/btree3.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/btree4.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/btree5.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/btree6.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/btree7.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Что делает операция VACUUM?"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/vacum2.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "![iso](attest_image/vacum3.png)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# План запроса"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "https://habr.com/ru/post/203386/"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "EXPLAIN SELECT * FROM foo;"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "QUERY PLAN\n",
    "— Seq Scan on foo (cost=0.00..18334.00 rows=1000000 width=37)\n",
    "(1 row)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Чтение данных из таблицы может выполняться несколькими способами. В нашем случае EXPLAIN сообщает, что используется \n",
    "- Seq Scan — последовательное, блок за блоком, чтение данных таблицы foo.\n",
    "- cost Это не время, а некое сферическое в вакууме понятие, призванное оценить затратность операции. Первое значение 0.00 — затраты на получение первой строки. Второе — 18334.00 — затраты на получение всех строк.\n",
    "- rows — приблизительное количество возвращаемых строк при выполнении операции Seq Scan. Это значение возвращает планировщик. В моём случае оно совпадает с реальным количеством строк в таблице.\n",
    "- width — средний размер одной строки в байтах."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "INSERT INTO foo\n",
    "  SELECT i, md5(random()::text)\n",
    "  FROM generate_series(1, 10) AS i;\n",
    "EXPLAIN SELECT * FROM foo;"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "QUERY PLAN\n",
    "— Seq Scan on foo (cost=0.00..18334.00 rows=1000000 width=37)\n",
    "(1 row)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Значение rows не изменилось. Статистика по таблице старая. Для обновления статистики вызываем команду ANALYZE."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Что происходит при выполнении ANALYZE?\n",
    "\n",
    "- Считывается определённое количество строк таблицы, выбранных случайным образом\n",
    "- Собирается статистика значений по каждой из колонок таблицы:\n",
    "\n",
    "Сколько строк будет считывать ANALYZE — зависит от параметра default_statistics_target."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "EXPLAIN — только ожидания планировщика. Попробуем сверить их с результатами на реальных данных. Используем EXPLAIN (ANALYZE) ."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "EXPLAIN (ANALYZE) SELECT * FROM foo;"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "QUERY PLAN\n",
    "— Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37) (actual time=0.012..61.524 rows=1000010 loops=1)\n",
    "Total runtime: 90.944 ms\n",
    "(2 rows)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "В выводе команды информации добавилось.\n",
    "- actual time — реальное время в миллисекундах, затраченное для получения первой строки и всех строк соответственно.\n",
    "- rows — реальное количество строк, полученных при Seq Scan.\n",
    "- loops — сколько раз пришлось выполнить операцию Seq Scan.\n",
    "- Total runtime — общее время выполнения запроса."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Пора создать индексы."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "CREATE INDEX ON foo(c1);\n",
    "EXPLAIN SELECT * FROM foo WHERE c1 > 500;"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Но ничего не поменялось!!!"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "QUERY PLAN\n",
    "— Seq Scan on foo (cost=0.00..20834.12 rows=999519 width=37)\n",
    "Filter: (c1 > 500)\n",
    "(2 rows)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Принудительно заставим использовать индекс, запретив Seq Scan:"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "SET enable_seqscan TO off;\n",
    "EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500;"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "QUERY PLAN\n",
    "— Index Scan using foo_c1_idx on foo (cost=0.42..34623.01 rows=999519 width=37) (actual time=0.178..1018.045 rows=999500 loops=1)\n",
    "Index Cond: (c1 > 500)\n",
    "Total runtime: 1434.429 ms\n",
    "(3 rows)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Index Scan, Index Cond вместо Filter — используется индекс foo_c1_idx.\n",
    "При выборке практически всей таблицы использование индекса только увеличивает cost и время выполнения запроса. Планировщик не глуп!"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Усложним условие. Используем текстовое поле."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "EXPLAIN SELECT * FROM foo\n",
    "        WHERE c1 < 500 AND c2 LIKE 'abcd%';"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "QUERY PLAN\n",
    "— Index Scan using foo_c1_idx on foo (cost=0.42..27.00 rows=1 width=37)\n",
    "Index Cond: (c1 < 500)\n",
    "Filter: (c2 ~~ 'abcd%'::text)\n",
    "(3 rows)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Как видим, используется индекс foo_c1_idx для условия c1 < 500. Для c2 ~~ 'abcd%'::text используется фильтр."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "source": [],
    "metadata": {
     "collapsed": false
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}