• EN
    Blog

    Cybertec Logo

    • Services
      • Requirement Analysis
      • PostgreSQL consulting
      • PostgreSQL migration
        • Migration from Oracle
        • Migrating from MySQL / MariaDB
        • CYBERTEC Migrator
      • CYPEX Online Academy
      • PostgreSQL Infrastructure
        • Setup & Installation
        • Kubernetes
        • Database architecture
      • PostgreSQL development
        • Database modeling
        • Functions & Features
      • Update & Upgrade
      • Optimization & Security
        • PostgreSQL Health Check
        • Performance Tuning
        • Enterprise Security
        • Security Audit
      • Troubleshooting & Data Recovery
      • PostgreSQL clustering and HA
        • Clustering and failover
        • High availability with Patroni
        • Synchronous and asynchronous replication
        • Scaling with PL/Proxy
      • Spatial Services
        • GIS Tooling
        • PostGIS OSM Downloader
      • CYBERTEC Partner Network
    • Support
    • Products
      • Our Products
        • CYPEX – Build Apps & Forms
        • CYBERTEC PostgreSQL Enterprise Edition
        • CYBERTEC Migrator
        • PostgreSQL Transparent Data Encryption
        • Scalefield – Private Cloud
        • Data Masking for PostgreSQL
        • PL/pgSQL_sec – Fully encrypted stored procedures
      • PostgreSQL Tools & Extensions
        • pg_timetable – Advanced Job Scheduling
        • pg_show_plans – Monitoring Execution Plans
        • pgwatch – PostgreSQL Monitoring Tool
        • pg_squeeze – Shrinks Tables
        • Walbouncer – Enterprise Grade Partial Replication
        • PGConfigurator – Visual PostgreSQL Configuration
        • Patroni Environment Setup
      • Assessment Packages
        • Data Science Assessment Package
        • Start-Up Assessment Package
        • Spatial Data Assessment Package
      • CYBERTEC Partner Network
    • Training
    • PostgreSQL
      • Advantages of PostgreSQL
      • PostgreSQL Books
      • Solutions – Who uses PostgreSQL
        • PostgreSQL for Startups
        • PostgreSQL for governments and public services
        • Longlife solutions
      • Business Cases
        • Fraud Detection
        • PostgreSQL for biotech and scientific applications
    • Data Science
      • Data Science Overview
      • Machine Learning
      • Big Data Analytics
    • Contact

    Posts

    Tag: lock

    04.04.2023 | Laurenz Albe

    Row locks in PostgreSQL

    © Laurenz Albe 2023 The PostgreSQL documentation has some information about row locks. But typically, you cannot see them in pg_locks, and not everybody knows how they work and how to track and debug row locks. This article intends to give some insight into how PostgreSQL row locks work “under the hood”. Why are there […]

    Read more
    30.08.2022 | Hans-Jürgen Schönig

    ALTER TABLE … ADD COLUMN … done right in PostgreSQL

    Running ALTER TABLE … ADD COLUMN can have side effects which have the potential to cause serious issues in production. Changing data structures is an important issue and often comes up, therefore it’s important to understand what is really going on. Let’s dive in and see how to run ALTER TABLE … ADD COLUMN in […]

    Read more
    23.06.2022 | Laurenz Albe

    Debugging deadlocks in PostgreSQL

    © Laurenz Albe 2022 (Updated 2023-04-07) Even if you understand what a deadlock is, debugging deadlocks can be tricky. This article shows some techniques on how to figure out the cause of a deadlock. A simple deadlock example Setting the stage We will test our techniques for debugging deadlocks with the following example: The deadlock […]

    Read more
    24.06.2021 | Hans-Jürgen Schönig

    Constraints over multiple rows in PostgreSQL

    Manage constraints over multiple rows: In PostgreSQL and many other relational databases, constraints are an integral part of the feature set. Many people are aware of primary keys, foreign keys, CHECK-constraints, and table constraints. However, from time to time, the situation is way more complicated. That’s when some more advanced techniques are needed to enforce […]

    Read more
    09.06.2021 | Laurenz Albe

    Disabling autocommit in PostgreSQL can damage your health

    © Laurenz Albe 2021 When analyzing customer’s problems, I have seen the mess you can get into if you disable autocommit in your interactive client, so I’d like to bring this topic to a wider audience. What is autocommit? In PostgreSQL, like in any other ACID-complicant database, each statement runs in a transaction: if the […]

    Read more
    27.04.2021 | Hans-Jürgen Schönig

    PostgreSQL: Understanding deadlocks

    Many might have seen PostgreSQL issue the following error message: “ERROR: deadlock detected”. But what does it really mean? How can we prevent a deadlock and how can we reproduce the problem? Let’s dive into PostgreSQL locking and understand what deadlock and deadlock_timeout really mean. How does a deadlock happen? Many people approach us because […]

    Read more
    07.03.2019 | Laurenz Albe

    “LOCK TABLE” can harm your database’s health

    © Laurenz Albe 2019   Many people know that explicit table locks with LOCK TABLE are bad style and usually a consequence of bad design. The main reason is that they hamper concurrency and hence performance. Through a recent support case I learned that there are even worse effects of explicit table locks. Table locks […]

    Read more
    11.12.2018 | Laurenz Albe

    Be prepared for prepared transactions

    Prepared transactions are disabled in PostgreSQL by default, since the parameter max_prepared_transactions has the default value 0. You don’t need prepared transactions in most cases. However, they can cause nasty problems, so I think that everybody who runs a PostgreSQL database should understand them. To illustrate these problems, I’ll show you how to use prepared […]

    Read more
    23.04.2018 | Laurenz Albe

    What’s in an xmax?

    xmax is a PostgreSQL system column that is used to implement Multiversion Concurrency Control (MVCC). The documentation is somewhat terse: The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting […]

    Read more
    21.07.2015 | Hans-Jürgen Schönig

    SKIP LOCKED: One of my favorite 9.5 features

    PostgreSQL 9.5 is just around the corner and many cool new features have been added to this wonderful release. One of the most exciting ones is definitely SKIP LOCKED. To make sure that concurrent operations don’t lead to race conditions, SELECT FOR UPDATE has been supported for many years now and it is essential to […]

    Read more
    04.10.2013 | Hans-Jürgen Schönig

    Reducing the impact of locking

    “Waiting is the slowest way to execute an operation” – this is how a professor at my university in Vienna usually described bad locking and low concurrency. He could not have been more right. The same applies to performing calculations: The fastest way to calculate something is to try to skip it entirely. In this […]

    Read more

    logo

    CYBERTEC PostgreSQL International GmbH
    Römerstraße 19
    2752 Wöllersdorf
    AUSTRIA

    +43 (0) 2622 93022-0
    office@cybertec.at
    twitter.com/PostgresSupport
    github.com/cybertec-postgresql

    Our Services

    • Administration
    • Replication
    • Consulting
    • Database Design
    • Support
    • Migration
    • Development

     

    SUPPORT CUSTOMERS
    Go to the support platform >>

    Newsletter

    Check out previous newsletters!

    Stay well informed about PostgreSQL by subscribing to our newsletter.

    © 2000–2023 CYBERTEC PostgreSQL International GmbH
    • IMPORTANT INFORMATION ABOUT COVID-19
    • Contact
    • Data protection policy
    • Imprint
    • Terms and Conditions