Skip to main content

26 posts tagged with "Postgres insights"

View All Tags

EXPLAIN (ANALYZE) needs BUFFERS to improve the Postgres query optimization process

· 15 min read
Nikolay Samokhvalov


Jupiter's moon IO. Credit: ALMA (ESO/NAOJ/NRAO), I. de Pater et al.; NRAO/AUI NSF, S. Dagnello; NASA/JPL/Space Science Institute

SQL query optimization is challenging for those who have just started working with PostgreSQL. There are many objective reasons for this, such as:

  • the difficulty of the field of system performance in general,
  • lack of good "playground" environments where people can experience how databases work at a larger scale,
  • lack of certain capabilities in Postgres observability tools that are still developing (though, at a good pace),
  • insufficiency of good educational materials.

All these barriers are reasonable. They limit the number of engineers possessing well-developed Postgres query optimization skills. However, there is a specific artificial barrier that is rather influential and which is relatively easy to eliminate.

Here it is: the EXPLAIN command has the BUFFERS option disabled by default. I am sure it has to be enabled and used by everyone who needs to do some SQL optimization work.

Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL

· 6 min read
Nikolay Samokhvalov

What is this about?​

Many DDL statements in PostgreSQL support modifiers IF EXISTS / IF NOT EXISTS. For example:

test=# create table if not exists mytable();
CREATE TABLE
test=# drop table if exists mytable;
DROP TABLE

I recommend using IF EXISTS / IF NOT EXISTS in DDL only when necessary. Here are three examples that demonstrate how the overuse of these words may lead to negative consequences.

How partial, covering, and multicolumn indexes may slow down UPDATEs in PostgreSQL

· 15 min read
Nikolay Samokhvalov

Based on a true story.

This article was originally published in 2018[1]. This is a reviewed and extended version of it. The discussed findings can be applied to any actual major version of PostgreSQL.

Primum non nocere​

"Primum non nocere" – this is a fundamental principle that is well-known to anyone working in healthcare: "first, do no harm". It is a reminder: when considering any action that is supposed to improve something, we always need to look at the global picture to see if there might be something else that be damaged by the same action.

This is a great principle and it is used not only in healthcare, of course. I strongly believe that it has to be used in database optimization too, and we need better tools to make it happen.

Links

  1. [1] in 2018 https://medium.com/@samokhvalov/how-partial-indexes-affect-update-performance-in-postgres-d05e0052abc

Useful queries to analyze PostgreSQL lock trees (a.k.a. lock queues)

· 4 min read
Nikolay Samokhvalov

For OLTP workloads (such as web and mobile applications), it is important to understand object-level and row-level locks in PostgreSQL. There are several good materials that I can recommend reading:

Links

  1. [1] "13.3. Explicit Locking" https://www.postgresql.org/docs/current/explicit-locking.html
  2. [2] LOCK TABLE ... https://www.postgresql.org/docs/current/sql-lock.html
  3. [3] SELECT ... FOR UPDATE https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
  4. [4] pg_advisory_lock(..) https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
  5. [5] "PostgreSQL rocks, except when it blocks: Understanding locks" https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/
  6. [6] Marco Slot https://twitter.com/marcoslot
  7. [7] Egor Rogov https://twitter.com/e_g_Orius
  8. [8] "Locks in PostgreSQL: 1. Relation-level locks" https://postgrespro.com/blog/pgsql/5967999
  9. [9] "Locks in PostgreSQL: 2. Row-level locks" https://postgrespro.com/blog/pgsql/5968005

What is a slow SQL query?

· 6 min read
Nikolay Samokhvalov

User interface performance​

For web and mobile applications (here, let's talk only about them, not touching analytical workloads, background processing, and so on), engineers usually aim to have as few SQL queries per each HTTP(S) request as possible, and keep the execution time of those queries very low.

How much low?

PostgreSQL subtransactions considered harmful

· 21 min read
Nikolay Samokhvalov

An opinionated overview of four cases against using PostgreSQL subtransactions​

💬 Hacker News discussion[1]​

💬 "Why we spent the last month eliminating PostgreSQL subtransactions" (GitLab)[2]​

This article discusses what subtransactions are, how to create them, and how widely they are used nowadays. Next, we try to understand why they have a terrible reputation among PostgreSQL experts who operate heavily loaded systems. We overview four problems that may arise in a Postgres database in which subtransactions are used, and load grows. In the end, we discuss the short-term and long-term options for PostgreSQL users willing to solve the ongoing or prevent future problems related to subtransactions.

Links

  1. [1] Hacker News discussion https://news.ycombinator.com/item?id=28374333
  2. [2] "Why we spent the last month eliminating PostgreSQL subtransactions" (GitLab) https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/