---
title: 'Несколько советов по Postgres для начинающих: Часть 1 | Zomro'
description: 'PostgreSQL предлагает множество инструментов для повышения производительности и управления данными.'
image: 'https://dx86q6oq7ry0e.cloudfront.net/uploads/blog/a48%40server-panel.net/2023/12/21/postgress_q50.jpg'
---

![opened image]()

  * [Zomro](https://zomro.com/rus/)
  * [FAQ](https://zomro.com/rus/blog/faq/)
  * Несколько советов по Postgres для начинающих: Часть 1 



# Несколько советов по Postgres для начинающих: Часть 1

21-12-2023

![Несколько советов по Postgres для начинающих: Часть 1 - 1](https://dx86q6oq7ry0e.cloudfront.net/uploads/blog/a48%40server-panel.net/2023/12/21/postgress_q50.jpg)

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


## **1\. Понимание основ: кортежи как физические строки данных в PostgreSQL**

Тем, кто плохо знаком с **PostgreSQL** , следует усвоить одну ключевую концепцию — роль кортежей. По сути, кортеж в **PostgreSQL** действует как физическая форма строки данных. Это означает, что любое изменение данных строки приводит к созданию новой версии этой строки, называемой кортежем. PostgreSQL использует систему, известную как **Multiversion Concurrency Control (MVCC)** , для управления этими версиями. Понимание **MVCC** имеет решающее значение для проектирования эффективных систем.

### **Во время различных операций по манипулированию данными происходит следующее:**

При выполнении операции **DELETE** дисковое пространство освобождается не сразу. Старый кортеж помечается как устаревший, но остается на месте до тех пор, пока процесс **VACUUM** не удалит его. Накопление и последующее удаление этих устаревших кортежей в больших количествах может привести к раздуванию как таблиц, так и индексов.

  
Когда вы **UPDATE** строку, PostgreSQL не изменяет существующий кортеж. Вместо этого он генерирует новую версию строки (новый кортеж) и помечает предыдущую как устаревшую.

  
Интересно, что даже отмененный **INSERT** приводит к устаревшему кортежу. Это означает, что если операция вставки инициируется, а затем откатывается, вставляемый кортеж помечается как устаревший.

  
Чтобы лучше понять эту механику, каждая таблица в PostgreSQL включает скрытые столбцы, доступные для выбора: **ctid** , **xmin** и **xmax**. **ctid** указывает местоположение кортежа (номер страницы и смещение внутри него), тогда как **xmin** и xmax служат « _**датой создания**_ » и «** _датой истечения срока действия_** » кортежа.

Познакомившись с этими аспектами заранее, вы сможете более эффективно решать проблемы, связанные с управлением дисковым пространством, раздуванием и процессом автоочистки, предназначенным для очистки этих устаревших кортежей.
    
    
    alex=# create table example_table as select 1 as id;
    SELECT 1
    
    alex=# select ctid, xmin, xmax, * from example_table;
     ctid  | xmin  | xmax | id
    -------+-------+------+----
     (0,1) | 10000 |    0 |  1
    (1 row)
    
    alex=# update example_table set id = id where id = 1;
    UPDATE 1
    
    alex=# select ctid, xmin, xmax, * from example_table;
     ctid  | xmin  | xmax | id
    -------+-------+------+----
     (0,2) | 10001 |    0 |  1
    (1 row)
    

![Несколько советов по Postgres для начинающих: Часть 1 - 2](https://dx86q6oq7ry0e.cloudfront.net/uploads/blog/a48%40server-panel.net/2023/12/25/1_q50.jpg)

Этот пример показывает, как система работает с данными на уровне кортежей. Кортежи — это, по сути, версии строк в таблице. Каждый раз, когда происходит изменение данных в строке, создаётся новый кортеж. Это делается для поддержания стабильности и эффективности работы с данными, особенно при одновременном доступе к ним разными пользователями.

  * **Создание таблицы и выборка данных** : Сначала мы создаём таблицу **example_table** с одним столбцом **id** , который содержит значение **1**. Когда мы выбираем данные из этой таблицы, мы видим кортеж с **ctid (0,1), xmin 10000** и **xmax 0**. Здесь **ctid** указывает на местоположение кортежа в таблице, **xmin** – на транзакцию, которая создала этот кортеж, а **xmax** – на транзакцию, которая его удалит (в данный момент равен **0** , что означает, что кортеж ещё активен).




  * **Обновление данных:** После выполнения команды **UPDATE** , которая обновляет значение **id** , PostgreSQL не изменяет существующий кортеж. Вместо этого создаётся новый кортеж. Это видно по изменению **ctid** на **(0,2)** и **xmin** на **10001** , что указывает на новую транзакцию, создавшую обновлённый кортеж.




  * **MVCC и управление версиями данных** : Этот процесс демонстрирует, как в PostgreSQL используется система **MVCC**. Каждое обновление создаёт новую версию строки (новый кортеж), позволяя разным транзакциям работать с разными версиями данных одновременно. Это повышает эффективность и безопасность работы с базой данных.




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

## **2\. Использование EXPLAIN ANALYZE с BUFFERS для более глубокого анализа запросов**

  
Для оптимизации производительности запросов в PostgreSQL необходимо понимать, как они работают изнутри. Команда **EXPLAIN** является основным инструментом для этого. Однако, чтобы получить более детальное представление, рекомендуется использовать **EXPLAIN (ANALYZE, BUFFERS).**

### **Вот почему это важно:**

  * Базовая команда **EXPLAIN** показывает план выполнения запроса. Она демонстрирует, как PostgreSQL планирует доступ к данным или их изменение, включая последовательные сканирования, индексные сканирования, соединения и сортировки. Используйте эту команду, когда хотите просмотреть план без выполнения самого запроса.




  * Добавление **ANALYZE** к команде не только отображает запланированные шаги, но и выполняет запрос, собирая статистику выполнения в реальном времени. Это очень ценно для сравнения ожидаемых результатов (например, количества строк) с фактическими, выявляя потенциальные неточности в оценках PostgreSQL. Кроме того, она записывает время выполнения каждого этапа процесса.




  * Включение опции **BUFFERS** углубляет ваш анализ. Она предоставляет информацию об использовании буферов, конкретно о количестве блоков, которые были обработаны в пуле буферов или прочитаны из кэша или с диска. Это позволяет лучше понять, насколько интенсивно запрос взаимодействует с операциями ввода/вывода.




Используя **EXPLAIN (ANALYZE, BUFFERS),** вы получаете полное представление о том, как ваш запрос выполняется, начиная от планирования и заканчивая взаимодействием с системой буферов PostgreSQL.

Например рассмотрим запрос к базе:
    
    
    EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM my_table WHERE id = 10;

Получим такой вывод:
    
    
     QUERY PLAN
    -------------------------------------------
     Index Scan using my_table_pkey on my_table  (cost=0.29..8.30 rows=1 width=432) (actual time=0.023..0.024 rows=1 loops=1)
       Index Cond: (id = 10)
       Buffers: shared hit=4
     Planning Time: 0.050 ms
     Execution Time: 0.049 ms
    (5 rows)
    

![Несколько советов по Postgres для начинающих: Часть 1 - 3](https://dx86q6oq7ry0e.cloudfront.net/uploads/blog/a48%40server-panel.net/2023/12/25/2_q50.jpg)

  * **Plan Type** : Вывод показывает, что используется Index Scan на **my_table** с использованием ключа **my_table_pkey**. Это означает, что запрос использует индекс для поиска данных.




  * **Cost and Rows** : **cost=0.29..8.30 rows=1 width=432** \- это оценка стоимости выполнения запроса. Здесь**0.29** \- начальная стоимость, **8.30** \- общая стоимость, **rows=1** \- ожидаемое количество строк, а **width=432** \- средний размер строки в байтах.




  * **Actual Time and Rows** : **actual time=0.023..0.024 rows=1 loops=1** показывает фактическое время выполнения запроса, количество полученных строк и количество выполнений цикла (обычно **loops=1**).




  * **Index Condition** : **Index Cond** : (**id = 10**) указывает условие, используемое для индексного сканирования.




  * **Buffers** : **Buffers: shared hit=4** говорит о том, сколько раз данных было запрошено из общего буфера. В данном случае было**4** обращения к буферу, что указывает на эффективное использование кэшированных данных.




  * **Planning and Execution Time** : Время планирования (**Planning Time: 0.050 ms**) и время выполнения (**Execution Time: 0.049 ms**) показывают, сколько времени ушло на составление плана запроса и на его выполнение соответственно.




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

## **Заключение**

В заключение нашего обзора ключевых аспектов PostgreSQL, мы увидели, как мощные функции этой системы управления базами данных могут радикально изменить процесс обработки и анализа данных. От понимания кортежей и системы MVCC до подробного анализа запросов с использованием EXPLAIN (ANALYZE, BUFFERS), PostgreSQL предлагает множество инструментов для повышения производительности и управления данными.

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

Похожие статьи

[Доступ по FTP к папке сайта на сервере с панелью Hestia](https://zomro.com/rus/blog/faq/224-dostup-po-ftp-k-papke-sajta-na-servere-s-panelju-hestia) [Ошибка 406 Not Acceptable](https://zomro.com/rus/blog/faq/491-error-406-not-acceptable) [Fail2ban: автоматическая блокировка брутфорса SSH и веб-приложений](https://zomro.com/rus/blog/faq/728-fail2ban-automatically-block-ssh-and-web-application-brute-force-attacks) [Ошибка 504 524 Gateway Timeout (решено)](https://zomro.com/rus/blog/faq/226-oshibka-504-524-gateway-timeout) [HestiaCP: Как выдать SSL сертификат для домена, и установить Wordpress](https://zomro.com/rus/blog/faq/252-hestiacp-kak-vydat-ssl-sertifikat-dlja-domena-i-ustanovit-wordpress)

[ Сервер для Forex от €7.95/мес ](/rus/server-for-forex)