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!
effective_cache_size: What it means in PostgreSQL
Many PostgreSQL database users might have stumbled over the effective_cache_size parameter in postgresql.conf. But how can it be used to effectively tune the database and how can we speed up PostgreSQL using effective_cache_size? This blog will hopefully answer some of my readers’ questions and reveal the hidden power of this secretive setting. RAM, Linux, PostgreSQL […]
Open Street Map to PostGIS – The Basics
OSM to PostGIS – The Basics Ever wondered how to import OSM (OpenStreetMap) data into PostGIS [1] for the purpose of visualization and further analytics? Here are the basic steps to do so. There are a bunch of tools on the market— osm2pgsql; imposm; ogr2org; just to mention some of those. In this article I […]
Introduction and How-To: etcd clusters for Patroni
etcd is one of several solutions to a problem that is faced by many programs that run in a distributed fashion on a set of hosts, each of which may fail or need rebooting at any moment. One such program is Patroni; I’ve already written an introduction to it as well as a guide on how […]
PostgreSQL v12 initial query performance impressions
With the latest major version freshly released, it’s again time to gauge its performance. I’ve already been doing this for years, and I have my scripts ready, so it’s not too much work for me – but rather, for the machines. The v12 release, as always, adds quite a lot of nice stuff. Some […]
Upgrading Postgres major versions using Logical Replication
Some weeks ago, in the light of PostgreSQL v12 release, I wrote a general overview on various major version upgrade methods and benefits of upgrading in general – so if upgrading is a new thing for you I’d recommend to read that posting first. But this time I’m concentrating on the newest (available since v10) […]
Implementation of a Reinforcement Learning algorithm from scratch
After reading this post you will be able to write your first Reinforcement Learning program to solve a real life problem – and beat Google at it. Reinforcement Learning (RL) has gained a lot of attention due to its ability to surpass humans at numerous table games like chess, checkers and Go. If you are […]
Never lose a PostgreSQL transaction with pg_receivewal
© Laurenz Albe 2019 “Durability”, the D of ACID, demands that a committed database transaction remains committed, no matter what. For normal outages like a power failure, this is guaranteed by the transaction log (WAL). However, if we want to guarantee durability even in the face of more catastrophic outages that destroy the WAL, […]
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 […]