Get the newest tricks and useful tips about the world of PostgreSQL and Data Science! Our authors and technicians share their knowledge to keep you going with your data.
Searching for a quick tip? Check out our microblog, where we share our insights as they happen – short and simple!
PostgreSQL grouping sets: ROLLUP & CUBE
PostgreSQL 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 […]
PostgreSQL Security: Things to avoid in real life
Hardening 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: […]
PostgreSQL zheap: Current status
zheap has been designed as a new storage engine to handle UPDATE in PostgreSQL more efficiently. A lot has happened since my last report on this important topic, and I thought it would make sense to give readers a bit of a status update – to see how things are going, and what the current […]
PostgreSQL: LIMIT vs FETCH FIRST ROWS … WITH TIES
Most people in the SQL and in the PostgreSQL community have used the LIMIT clause provided by many database engines. However, what many do not know is that LIMIT / OFFSET are off standard and are thus not portable. The proper way to handle LIMIT is basically to use SELECT … FETCH FIRST ROWS. However, […]
Understanding LATERAL joins in PostgreSQL
LATERAL joins are one of the lesser-known features of PostgreSQL and other relational databases such as Oracle, DB2 and MS SQL. However, LATERAL joins are a really useful feature, and it makes sense to take a look at what you can accomplish with them. Inspecting FROM more closely Before we dive into LATERAL, it makes […]
Simulating temporal tables with dblink and replication delay
On some rare occasions, I’ve been asked if Postgres supports system-versioned temporal tables – in other words, time travel on table data for your queries. As quite often with Postgres, thanks to its extensibility, I can answer: well, officially, it does not…but, you can make it work, nevertheless 🙂 Why are people even asking such […]
PostGIS setup with Ubuntu 20.04.2
PostGIS setup with Ubuntu 20.04.2 In one of his last blogposts, our CEO Hans-Jürgen Schönig explained how to set up PostgreSQL on Ubuntu. He consciously avoided any discussion of how to deal with our beloved PostGIS extension. Let’s fix that up right now by following these steps to implement PostGIS setup: Status quo assessment Setup […]
PostgreSQL: 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, table constraints and so on. However, from time to time, the situation is way more complicated. That’s when some more advanced techniques are needed to enforce integrity in the […]
From MD5 to scram-sha-256 in PostgreSQL
© Laurenz Albe 2021 Since v10, PostgreSQL has provided support for scram-sha-256 for password hashing and authentication. This article describes how you can adapt your application safely. Why do we need scram-sha-256? PostgreSQL uses cryptographic hashing for two purposes: The actual database password is a hash of the clear text password entered by the user. […]
Creating and refreshing materialized views in PostgreSQL
A “materialized view” is a database object which stores the result of a precalculated database query and makes it easy to refresh this result as needed. Materialized views are an integral feature of pretty much all advanced database systems. Naturally, PostgreSQL also provides support for materialized views, and offers the end-user a powerful tool to […]
About cross join in PostgreSQL
© Laurenz Albe 2021 For many people, “cross join” is something to be afraid of. They remember the time when they forgot the join condition and the DBA was angry, because the query hogged the CPU and filled the disk. However, there are valid use cases for cross joins which I want to explore in […]
Data warehousing: Making use of synchronized seq scans
PostgreSQL contains some hidden gems which have been around for many years and help to silently speed up your queries. They optimize your SQL statements in a clever and totally transparent way. One of those hidden gems is the ability to synchronize sequential scans. Actually, this feature has been around for 15+ years, but has […]