© 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© 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 moreMoving from Oracle to PostgreSQL has become a popular sport, widely adopted by many who want to free themselves from license costs, hefty support costs and also technical limitations on the Oracle side. The same is true for people moving from MS SQL and other commercial platforms to PostgreSQL. However, my impression is that moving […]
Read moreCYBERTEC Migrator is fully Kubernetes- and OpenShift- ready. We rely on modern container technology, which is perfect for quick deployment. Conveniently scale your deployments and ensure round-the-clock reliability of your databases. CYBERTEC’s Kubernetes- and OpenShift- ready migration to PostgreSQL means your enterprise is ahead of the game before others have even lined up at the […]
Read moreMigrate from Oracle with ease CYBERTEC Migrator is a feature-complete migration tool which allows you to facilitate Oracle migrations. As an enterprise-ready product, it focuses explicitly on providing the richest feature set possible. With years of successful migrations behind us, CYBERTEC has developed the ultimate tool for getting the job done right. Minimum downtime, maximum […]
Read moreOne of the biggest obstacles faced by people who want to migrate quickly is performance. But your performance doesn’t have to suffer. CYBERTEC Migrator is likely the fastest solution out there to reliably ship data from Oracle to PostgreSQL. We use state-of-the-art techniques such as parallelization and change data capture to ensure we stay at […]
Read moreOver 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© 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 is not done yet! PostgreSQL v14 will bring “bottom-up” index entry deletion, which is targeted at reducing unnecessary page splits, index bloat and fragmentation of heavily updated indexes. […]
Read moreIndex missing? When an index is missing, good performance won’t be kissing a PostgreSQL user looking for efficiency but instead feels like a legacy. To satisfy a DBA’s desire and thirst, let us load some data first. pgbench is the tool of the day but the next listing will explain that anyway: Loading millions of […]
Read moreCreating auto increment columns in PostgreSQL is easy. Simply use two pseudo data types serial and serial8, respectively, then PostgreSQL will automatically take care of your auto increment columns. However, once in a while problems can still occur. Let us take a look and see. Sequences: Avoid manual values To understand the underlying problem, one […]
Read morePostgreSQL is one of the best OLTP databases (OLTP = online transaction processing) in the world. However, it can do more than just OLTP. PostgreSQL offers many additional features relevant to a more OLAP-style workload. One of those features is called “GROUPING SETS”. Before we dive into the details, I’ve compiled some sample data which […]
Read moreHardening PostgreSQL has become ever more important. Security is king these days and people want to know how to make PostgreSQL safe. Some of us might still remember what happened to MongoDB in recent years and we certainly want to avoid similar security problems in the PostgreSQL world. What happened to MongoDB is actually stunning: […]
Read more