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 we would check out 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 […]
7 things that could be improved in PostgreSQL
By Kaarel Moppel – UPDATED by Laurenz Albe 06.07.2023 – See what progress has been made What are PostgreSQL’s weaknesses? How can PostgreSQL be improved? Usually, in this blog I write about various fun topics around PostgreSQL – like perhaps new cool features, some tricky configuration parameters, performance of particular features or on some “life […]
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 (Updated 2023-02-22) 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 […]
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 at the new PostgreSQL troubleshooting extension – pg_show_plans
By Kaarel Moppel 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 […]
Introduction to Time Series Forecasting
By Kevin Speyer – Time Series Forecast – This post shows you 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 […]
Performance differences between normal and generic audit triggers
What are the 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 does it look from the performance side? To quickly recap the previous blog post: the most common approaches for […]