• 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: PostgreSQL performance

    20.12.2022 | Laurenz Albe

    UNION ALL, data types and performance

    © Laurenz Albe 2022 A while ago, I wrote about the performance impact of query parameter data types. Recently I encountered a similar problem with UNION ALL that baffled me for a while, so I’d like to share it with you. UNION ALL to implement polymorphism Different entities can have something in common. For example, […]

    Read more
    06.12.2022 | Ants Aasma

    Faceting large result sets in PostgreSQL

    While the term faceting may sound foreign to you, you almost certainly have run into it in your adventures online. It is those helpful little boxes that turn up when browsing in web shops or searching in catalogs, telling how to further narrow down the search results, (for example, by color) and how many items […]

    Read more
    24.05.2022 | Laurenz Albe

    Help, I cannot cancel a 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
    19.04.2022 | Hans-Jürgen Schönig

    Find and fix a missing PostgreSQL Index

    Spot a missing PostgreSQL index Missing indexes are a key ingredient if you are looking for a perfect recipe to ruin performance in the most efficient way possible. 🙂  However, if you want to ensure that your database performs well and if you are generally not in favor of user complaints – better watch out […]

    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
    14.09.2021 | Hans-Jürgen Schönig

    PostgreSQL: Create indexes after bulk loading

    Over the years, many of our PostgreSQL clients have asked whether it makes sense to create indexes before – or after – importing data. Does it make sense to disable indexes when bulk loading data, or is it better to keep them enabled? This is an important question for people involved in data warehousing and […]

    Read more
    08.09.2021 | Laurenz Albe

    Index bloat reduced in PostgreSQL v14

    © Laurenz Albe 2021 PostgreSQL v12 brought more efficient storage for indexes, and v13 improved that even more by adding deduplication of index entries. But Peter Geoghegan wasn’t finished! PostgreSQL v14 added “bottom-up” index entry deletion, which targets reducing unnecessary page splits, index bloat and fragmentation of heavily updated indexes. Why do we get index […]

    Read more
    27.05.2021 | Laurenz Albe

    How to interpret PostgreSQL EXPLAIN ANALYZE output

    © Laurenz Albe 2021 EXPLAIN ANALYZE is the key to optimizing SQL statements in PostgreSQL. This article does not attempt to explain everything there is to it. Rather, I want to give you a brief introduction, explain what to look for and show you some helpful tools to visualize the output. How to call EXPLAIN […]

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

    PostgreSQL: Detecting slow queries quickly

    UPDATED March 2023: In this post, we’ll focus our attention on PostgreSQL performance and detecting slow queries. Performance tuning does not only mean adjusting postgresql.conf properly, or making sure that your kernel parameters are properly tuned. Performance tuning also implies that you need to first find performance bottlenecks, isolate slow queries and understand what the […]

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

    BLOB cleanup in PostgreSQL

    PostgreSQL offers a nice BLOB interface which is widely used. However, recently we came across problems faced by various customers, and it makes sense to reflect a bit and figure out how PostgreSQL handles BLOBs – and especially BLOB cleanup. Using the PostgreSQL BLOB interface In PostgreSQL, you can use various means to store binary […]

    Read more
    16.09.2020 | Laurenz Albe

    HOT updates in PostgreSQL for better performance

    © Laurenz Albe 2020 UPDATED blog article on 09.01.2023 HOT updates are not a new feature. They were introduced by commit 282d2a03dd in 2007 and first appeared in PostgreSQL 8.3. But since HOT is not covered by the PostgreSQL documentation (although there is a README.HOT in the source tree), it is not as widely known […]

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

    PostgreSQL: More performance for LIKE and ILIKE statements

    LIKE and ILIKE are two fundamental SQL features. People use those things all over the place in their application and therefore it makes sense to approach the topic from a performance point of view. What can PostgreSQL do to speed up those operations and what can be done in general to first understand the problem […]

    Read more

    Posts navigation

    Older 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