23 posts tagged with "Postgres insights"

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:

• the official documentation is a must-read, as usual: "13.3. Explicit Locking" (do not be confused by the title – this article discusses not only explicit locks that we can add using queries like LOCK TABLE ... or SELECT ... FOR UPDATE or functions like pg_advisory_lock(..) but also the locks introduced by regular SQL commands such as ALTER or UPDATE)
• "PostgreSQL rocks, except when it blocks: Understanding locks" (2018) by Marco Slot – a great extension to the docs, explaining some aspects and conveniently "translating" the table provided in the docs from "lock language" to "SQL command language"
• As usual, a thorough and deep explanation of how PostgreSQL works by Egor Rogov:
  • "Locks in PostgreSQL: 1. Relation-level locks" (2020)
  • "Locks in PostgreSQL: 2. Row-level locks" (2020)

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?

An opinionated overview of four cases against using PostgreSQL subtransactions​

💬 Hacker News discussion​

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

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.