Indexes are, by far, the most feature related to performance provided by every general purpose relational database. Without indexes, there are no such things as efficient search operations, no unique constraints, and no primary keys. Achieving good performance on reasonable amounts of data without indexes is therefore totally impossible. Generating sample data The main question now is: what happens during index creation, and how can we speed up the process? Creating indexes on billions of rows is quite costly because data has to be sorted and turned into an efficient search structure. To demon ..read more

© Markus Winand 2014 Keyset pagination is the most performant way to retrieve a large result set page by page. However, the neat trick with composite type comparison doesn't always work. This article explains why and how you can work around that shortcoming. An example table for paginated queries We create a table with a million rows: CREATE TABLE sortme ( id bigint PRIMARY KEY, val1 integer NOT NULL, val2 timestamp with time zone NOT NULL, val3 text NOT NULL ); -- deterministic, but sort of random values INSERT INTO sortme SELECT i, abs(hashint8(i)) % 200 + 1, TIM ..read more

Artificial Intelligence is the motto of the day. Everybody is talking about it, everybody seems to know what it means and the insane number of buzzwords floating around on the internet is just mind-boggling. It seems that in some areas of the industry, the number of slides and marketing videos exceeds the number of real-world use cases and sometimes even the amount of code that actually exists. However, as I tend to prefer the real PostgreSQL world over the fake marketing side of things, I decided to show some of the things  we recently experimented with to give you  some insights in ..read more

I enjoy writing about the technical aspects of PostgreSQL, but occasionally it is good to write about other aspects of PostgreSQL. Since I just returned from the Swiss PGDay 2024, I'll sing the praise of PostgreSQL conferences. The PostgreSQL community PostgreSQL is much more than the best database in the world: it is a great community of people. Jan Karremans once said that the software is a side effect of the community. While I would not take it that far, it is certainly true that every free open source project can only prosper as long as there are people who dedicate love and effort to it ..read more

© Laurenz Albe 2024 Enough has been written about the connection between VACUUM, the visibility map and index-only scans in PostgreSQL. But recently I demonstrated index-only scans to a class I was teaching and made some observations that surprised me at first. I could eventually explain what I saw, but I may have given the class more to chew on than I intended. I myself found the experience enlightening, and if you would like some insight into the implementation details and performance features of PostgreSQL, welcome to the show. The visibility map Every PostgreSQL table has a ..read more

PostGIS is getting more and more popular and for good reasons. It benefits from a great community and offers a huge set of cool and stable features. Nowadays, a lot of customers want to migrate their spatial datasets from various data sources to PostGIS, where they can easily work with them. Today I want to take a closer look at how this migration could look like for those transitioning from Microsoft SQL server to PostGIS.   My article is structured as follows: Requirements Datasets Migration Results Final thoughts Requirements To replay this demonstration easily, let’s look at my ..read more

Last week, I had the privilege to accompany my colleagues, Jan Karremans, Pavlo Golub and Antonin Houska to the 16th Prague Developer Conference. It was my first time at P2D2 which made me indeed curious.  Location and Hotel My last visit in Prague was in December to attend pgconf.eu. Back then, there was no need to leave the building as everything was organized there: from parking to all the meals, the event itself, and our accommodation were all located under the same roof. This time, however, everything was different. We stayed at the Hotel Silenzio situated on a small hill, with small ..read more

Relational databases provide one very essential functionality which is key to integrity, data quality and consistency: foreign keys. If you want to build a professional application that relies on correct data, there is basically no way around the concept of referential integrity. The same is, of course, true in PostgreSQL. Foreign keys and circular dependencies However, there is a corner case many people are not aware of: circular dependencies. Now, how can that ever happen? Consider the following example which has been tested in PostgreSQL: CREATE TABLE department ( id bigint ..read more

© Laurenz Albe 2024 Sometimes one of our customers looks at the most time consuming statements in a database (either with pg_stat_statements or with pgBadger) and finds COMMIT in the high ranks. Normally, COMMIT is a very fast statement in PostgreSQL, so that is worth investigating. In this article, I will explore the possible reasons for a slow COMMIT and discuss what you can do about it. The basic COMMIT activity in PostgreSQL A slow COMMIT is a surprising observation, because committing a transaction is a very simple activity in PostgreSQL. In most cases, all a COMMIT has to do is set the ..read more

It was in the afternoon of Tuesday that my trip commenced from Amsterdam Airport to Franjo Tuđman International Airport in Zagreb. I was contemplating how long it had been since I was in Croatia in general and Zagreb specifically. One thing was clear, it was going to be an evening with a good dinner and some good wine, together with my friend Marin Bjeliš from Nimium. After having tried to join the DORS/CLUC for the last couple of years, I am super excited that it finally happened in collaboration with Nimium and CYBERTEC! On May 15, the event officially started with "Open4Business". Tradition ..read more