Month: April 2020
Speeding up count(*): Why not use max(id) – min(id)
Our PostgreSQL blog about “Speeding up count(*)” was widely read and discussed by our followers on the internet. We also saw some people commenting on the post and suggesting using different means to speed up count(*). I want to specifically focus on one of those comments and to warn our readers. max(id) – min(id) will […]
Intersecting Tracks of individuals – MobilityDB
Last time I announced to checkout MobilityDB to improve our approach to extract overlapping passage times of healthy and infected individuals – here we go! MobilityDB itself is a PostgreSQL extension built on top of PostGIS, specializing on processing and analysing spatio-temporal data. To do so, the extension adds a bunch of types and functions […]
7 things that could be improved in PostgreSQL
Usually in this blog I’m writing about various fun topics around PostgreSQL – like perhaps new cool features, some tricky configuration parameters, performance of particular features or on some “life hacks” to ease the life of DBA-s or developers. This post will be quite different though – inspired by an article I stumbled upon recently, […]
PostgreSQL v13 new feature: tuning autovacuum on insert-only tables
© Laurenz Albe 2020 Most people know that autovacuum is necessary to get rid of dead tuples. These dead tuples are a side effect of PostgreSQL’s MVCC implementation. So many people will be confused when they read that from PostgreSQL v13 on, commit b07642dbc adds support for autovacuuming insert-only tables (also known as “append-only tables”). […]
Tuning max_connections in PostgreSQL
© Laurenz Albe 2020 In my daily work, I see many databases with a bad setting for max_connections. There is little advice out there for setting this parameter correctly, even though it is vital for the health of a database. So I decided to write up something. What is max_connections? According to the documentation, max_connections […]
Intersecting GPS-Tracks to identify infected individuals
In times of COVID-19, governments contemplate tough measures to identify and trace infected people. These measures include the utilization of mobile phone data to trace down infected individuals and subsequently contacts to curb the epidemic. This article shows how PostGIS’ functions can be used to identify “overlapping” sections of infected and healthy individuals by analysing tracks […]
Detailed look on the new PostgreSQL troubleshooting extension – pg_show_plans
2 weeks ago we announced a new, long awaited, Postgres extension to look into execution plans for “live” running queries: pg_show_plans. This was not possible before in PostgreSQL yet, so it’s really a very cool piece of functionality and we’d like to echo out the message again. So here in this blogpost we’ll reiterate some […]
Introduction to Time Series Forecasting
Time Series Forecast In this post we will show how to approach a time series problem, using machine learning techniques. Predicting the behavior of a variable over time is a common problem that one encounters in many industries, from prices of assets on the stock market to the amount of transactions per minute on a […]
Performance differences between normal and generic audit triggers
Recently I was talking in a more general way about some common auditing / change tracking approaches for PostgreSQL…but it also made me curious, how it roughly looks from the performance side? To quickly recap the previous blog post: the most common approaches for tracking important changes are mostly solved with writing some triggers. There […]