© Laurenz Albe 2020 Streaming replication in PostgreSQL is a well-established master-slave replication technique. It is simple to set up, stable and performs well. So many people are surprised when they learn about replication conflicts — after all, the standby server is read-only. This article describes replication conflicts and tells you how to deal with […]

Partitioning data is a well known way to optimize big tables into smaller ones using a given attribute. And while creating a partitioned table structure itself is not complicated, the migration of a table on live system can be tricky. Our solution offers a simple way to solve this problem.   The usual way can be summarized […]

Temporary tables have been around forever and are widely used by application developers. However, there is more to temporary tables than meets the eye. PostgreSQL allows you to configure the lifespan of a temporary table in a nice way and helps to avoid some common pitfalls. CREATE TEMPORARY TABLE … By default, a temporary table […]

Is your database growing at a rapid rate? Does your database system slow down all the time? And maybe you have trouble understanding why this happens? Maybe it is time to take a look at pg_squeeze and fix your database once and for all. pg_squeeze has been designed to shrink your database tables without downtime. […]

PL/pgSQL is the preferred way to write stored procedures in PostgreSQL. Of course there are more languages to write code available but most people still use PL/pgSQL to get the job done. However, debugging PL/pgSQL code can be a bit tricky. Tools are around but it is still not a fun experience. One thing to […]

© Laurenz Albe 2020 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 as it should be: Hence […]

SQL and PostgreSQL are perfect tools to analyze data. However, they can also be used to create sample data which has to possess certain statistical properties. One thing many people need quite often is a normal distribution. The main question therefore is: How can one create this kind of sample data? Tablefunc: Creating normal distributions […]

Is it Postgre, PostGreSQL, Postgres or PostgreSQL? We have all seen a couple of wrong ways to spell “PostgreSQL”. The question therefore is: How can one find data even if there are typos? In PostgreSQL there are various solutions to the problem. Depending on what kind of search you need you can choose between various […]

© Laurenz Albe 2020 In many PostgreSQL databases, you never have to think or worry about tuning autovacuum. It runs automatically in the background and cleans up without getting in your way. But sometimes the default configuration is not good enough, and you have to tune autovacuum to make it work properly. This article presents […]

GROUP BY is nothing new and is available in any relational database I am aware of. It is an integral part of SQL and PostgreSQL but what many people might not know is the fact that GROUP BY can do more than just group by simple fields. You can use expressions to group in an […]

© Laurenz Albe 2020 Many people consider recursive queries a difficult topic. Still, they enable you to do things that would otherwise be impossible in SQL. This articles gives a simple introduction with examples and shows the differences to Oracle’s implementation of recursive queries. Common table expressions (WITH clauses) A common table expression (CTE) can […]

An introductory story (with some slight “drama” added for fun) from those good old days of on-site consulting 🙂 So…I’m at a client where the database is not behaving nicely among other things…what a crappy DB product indeed I hear, it gets cranky every morning although there should be a constant workload and is saturating […]