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!
Prewarming PostgreSQL I/O caches
PostgreSQL uses shared_buffers to cache blocks in memory. The idea is to reduce disk I/O and to speed up the database in the most efficient way possible. During normal operations your database cache will be pretty useful and ensure good response times. However, what happens if your database instance is restarted – for whatever reason? […]
What is autovacuum doing to my temporary tables?
Did you know that your temporary tables are not cleaned up by autovacuum? If you did not, consider reading this blog post about PostgreSQL and autovacuum. If you did – well, you can still continue to read this article. Autovacuum cleans tables automatically Since the days of PostgreSQL 8.0, the database has provided this miraculous […]
How PostgreSQL estimates parallel queries
Parallel queries were introduced back in PostgreSQL 9.6, and the feature has been extended ever since. In PostgreSQL 11 and PostgreSQL 12, even more functionality has been added to the database engine. However, there remain some questions related to parallel queries which often pop up during training and which definitely deserve some clarification. Estimating the […]
Fixing track_activity_query_size in postgresql.conf
Many of you might have wondered why some system views and monitoring statistics in PostgreSQL can contain incomplete query strings. The answer is that in PostgreSQL, it’s a configuration parameter that determines when a query will be cut off: track_activity_query_size. This blog post explains what this parameter does and how it can be used to […]
Patroni : Setting up a highly available PostgreSQL Cluster
Patroni is a cluster manager used to customize and automate deployment and maintenance of PostgreSQL HA (High Availability) clusters. It uses distributed configuration stores like etcd, Consul, ZooKeeper or Kubernetes for maximum accessibility. In this tutorial, we will be using a single local etcd instance and two local Patroni instances on a single host instead […]
Using “Row Level Security” to make large companies more secure
Large companies and professional business have to make sure that data is kept secure. It is necessary to defend against internal, as well as external threats. PostgreSQL provides all the necessities a company needs to protect data and to ensure that people can only access what they are supposed to see. One way to protect […]
Today I learned
As you all may know, our main blog is a very appreciable source for various PostgreSQL-relevant guides and tips. Our PostgreSQL experts publish content about different topics – e.g. Patroni, advanced PostgreSQL features, configuration settings, security, etc. All of those posts have one thing in common: they are data- or database-centric. That’s why, that’s the […]
Manage Linux control groups in PostgreSQL with pg_cgroups
In another blog post, I described how Linux control groups work and how they are useful for PostgreSQL. Here I will present my little PostgreSQL plugin pg_cgroups which makes this simple to handle for the DBA. What is pg_cgroups good for? If you have a machine dedicated to a single PostgreSQL cluster, you normally […]
PostgreSQL High-Availability and Patroni – an Introduction.
Are you running PostgreSQL databases or plan to do so? Have you thought about disaster recovery? What happens if your database crashes or – more likely – the (virtual) server it runs on stops working? The best option is probably a copy of your database, that is able to take over operations, as soon as […]
Tracking view dependencies in PostgreSQL
We all know that in PostgreSQL we cannot drop an object if there are view dependencies on it: Some people like it because it keeps the database consistent; some people hate it because it makes schema modifications more difficult. But that’s the way it is. In this article I want to explore the mechanics […]
A Primer on PostgreSQL Upgrade Methods
Soon it’s that time of the year again – basically a 2nd Christmas for followers of the “blue elephant cult” if you will :). I’m, of course, referring to the upcoming release of the next PostgreSQL major version, v12. So I thought it’s about time to go over some basics on upgrading to newer major […]
Remote Backup and Restore with pgBackRest
In my previous post about pgBackRest, we saw how to install and setup pgBackRest and make a backup of a PostgreSQL database with it. It was a very basic single server setup, only intended to get the hang of the tool. Such setups are not used in a production environment, as it is not recommended […]