• 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
      • Standard PostgreSQL Support
        • Product Support
        • 9/5 Basic Support
        • 24/7 Basic Support
        • 24/7 Enterprise Support
      • Advanced PostgreSQL Support
        • 9/5 Remote DBA
        • 24/7 Remote DBA
        • 9/5 Dedicated DBA
        • 24/7 Cloud-based Support
      • Support for Reseller
        • 3rd Level Support
        • CYBERTEC Partner Network
    • 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

    Category: Laurenz Albe

    Laurenz Albe is a senior consultant and support engineer at CYBERTEC. He has been working with and contributing to PostgreSQL since 2006, has written patches for core, and wrote oracle_fdw. He holds a Master’s degree in Mathematics from the University of Vienna and a Master’s in Computer Science from the Technical University of Vienna.
     

    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
    15.06.2022 | Laurenz Albe

    Case-insensitive pattern matching in PostgreSQL

      © Renée Albe 2022 Case-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server. There are several solutions to the problem, one of which is to use case-insensitive ICU collations. This works like a charm, except if you want to perform pattern matching. So let’s have a closer […]

    Read more
    24.05.2022 | Laurenz Albe

    How to cancel a hanging PostgreSQL query

    © Laurenz Albe 2022 Sometimes a PostgreSQL query takes forever. Usually, it is easy to interrupt (cancel) such a statement, but sometimes it can turn into a problem, and a query cannot be canceled. This article discusses what might be the cause. I’ll also show you a trick how to solve the problem (not for […]

    Read more
    10.05.2022 | Laurenz Albe

    Time zone management in PostgreSQL

    © Laurenz Albe 2022 Next to character encoding, time zones are among the least-loved topics in computing. In addition, PostgreSQL’s implementation of timestamp with time zone is somewhat surprising. So I thought it might be worth to write up an introduction to time zone management and recommendations for its practical use. Time zones and the […]

    Read more
    29.03.2022 | Laurenz Albe

    How to DROP ROLE or DROP USER in PostgreSQL

    © Laurenz Albe 2022 You might, at first glance, believe that DROP ROLE (or DROP USER, which is the same) is a simple matter. However, that’s not always the case. So I thought it might be a good idea to show you the problems involved and how to solve them. Why is there no DROP […]

    Read more
    22.03.2022 | Laurenz Albe

    Query parameter data types and performance

    © Laurenz Albe 2022 Recently, I could help solve a “mysterious” performance problem for a customer. The problem turned out to be a badly chosen query parameter type, which led to poor query performance. After working on this customer’s case, I realized that the meaning of the data type of a query parameter is not […]

    Read more
    15.03.2022 | Laurenz Albe

    Pipeline mode for better PostgreSQL performance on slow networks

    © Laurenz Albe 2022 It is known that high network latency is bad for database performance. PostgreSQL v14 has introduced “pipeline mode” for the libpq C API, which is particularly useful to get decent performance over high-latency network connections. If you are using a hosted database in “the cloud”, then this article might be interesting […]

    Read more
    01.02.2022 | Laurenz Albe

    Automatic partition creation in PostgreSQL

    © Laurenz Albe 2022 Table partitioning is one of the best-liked features out of the more recent PostgreSQL developments. However, there is no support for automatic partition creation yet. This article shows what you can do to remedy that. Use cases for automatic partition creation There are essentially two use cases: Create partitions triggered by […]

    Read more
    23.11.2021 | Laurenz Albe

    Entity-attribute-value (EAV) design in PostgreSQL – don’t do it!

    © Laurenz Albe 2021 Customers have often asked me what I think of “Entity-attribute-value” (EAV) design. So I thought it would be a good idea to lay down my opinion in writing. What is entity-attribute-value design? The idea is not to create a table for each entity in the application. Rather, you store each attribute […]

    Read more
    27.10.2021 | Laurenz Albe

    TCP keepalive for a better PostgreSQL experience

    © Laurenz Albe 2021 (Updated 2023-06-22) If you’ve heard about TCP keepalive but aren’t sure what that is, read on. If you’ve ever been surprised by error messages like: server closed the connection unexpectedly SSL SYSCALL error: EOF detected unexpected EOF on client connection could not receive data from client: Connection reset by peer then […]

    Read more
    20.10.2021 | Laurenz Albe

    WITH HOLD cursors and transactions in PostgreSQL

    © Laurenz Albe 2021 Both cursors and transactions are basic ingredients for developing a database application. This article describes how cursors and transactions interact and how WITH HOLD can work around their limitations. We will also see the dangers involved and how to properly use WITH HOLD cursors in a PL/pgSQL procedure. Cursors in PostgreSQL […]

    Read more
    30.09.2021 | Laurenz Albe

    Gaps in sequences in PostgreSQL

    © Laurenz Albe 2021 Most database tables have an artificial numeric primary key, and that number is usually generated automatically using a sequence. I wrote about auto-generated primary keys in some detail in a previous article. Occasionally, gaps in these primary key sequences can occur – which might come as a surprise to you. This […]

    Read more

    Posts navigation

    Older posts
    Newer posts

    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