How the PostgreSQL query optimizer works
Get insight into how the PostgreSQL optimizer works and how it speeds up queries. Find out about anti-join & from_collapse_limit.
Setting up SSL authentication for PostgreSQL
How to set up SSL authentication for PostgreSQL. Handle secure client-server connections in the best possible way.
Traveling Salesman problem with PostGIS and pgRouting
This article explains the optimization problem known as the Traveling Salesman Problem which is about the optimal order of stops on a trip.
Running Postgres in Docker - why and how?
This article gives an introduction from Postgres to Docker and whether you should run production Postgres workloads in a docker container.
PostgreSQL GitHub Actions - Continuous Integration
GitHub Actions are a piece of excellent machinery for PostgreSQL related tasks on your repo with support for Windows, Ubuntu and macOS environments.
Estimating connection pool size with PostgreSQL database statistics
This article shows how you can you use PostgreSQL database statistics to get an upper limit for the correct size for a connection pool.
PostgreSQL: How to write a trigger
UPDATED 2023: A trigger is a way to automatically respond to events. This is a tutorial on how to manage and program triggers in PostgreSQL.
PostgreSQL: CREATE STATISTICS - advanced query optimization
The PostgreSQL query planner doesn't know which data usually correlates. How to use CREATE STATISTICS to find correlations.
PostgreSQL: ANALYZE and optimizer statistics
To find the best execution plan PostgreSQL is relying on statistics to give the optimizer an indication of what to expect.
"Catchment areas" with PostgreSQL and PostGIS
This article explains how to partition an area of interest spatially: Approximation of customer potential using PostGIS. Catchment areas.
PostgreSQL: What is a checkpoint?
How to use checkpoints and checkpoint tuning, and how PostgreSQL writes data, database internals explained.
Postgres scaling advice
This article covers scaling advices for PostgreSQL and explains why it may be a good idea to keep it simple on the database side.
Golden Proportions in PostgreSQL
Learn how PostgreSQL can help with basic calculations regarding golden proportions. Do mathematics in your database development work.
Tips and tricks to kick-start the Postgres year 2021
Start learning some new Postgres stuff again in 2021 and find some tips: Partial indexing, estimating average row size and more.
The shibboleth of PostgreSQL
A shibboleth is a phase whose pronounciation determines if you belong or not. This article tells you how not to pronounce "PostgreSQL".
Upgrading and updating PostgreSQL
Update/ upgrade: what's the difference? This blog post covers the best ways of upgrading or updating PostgreSQL to the latest release.
Dealing with streaming replication conflicts in PostgreSQL
Replication conflicts can cause problems with streaming replication. This article tells you what they are and how to deal with them.
PostgreSQL clustering: vip-manager
vip-manager is a simple to use tool to handle service IPs in a PostgreSQL Patroni cluster | PostgreSQL High Availability Cluster
PostgreSQL: Foreign keys and insertion order in SQL
Foreign keys will introduce some issues which have to be considered when writing applications. If a foreign key is in place the inseration order starts to matter.
HOT updates in PostgreSQL for better performance
HOT updates (Heap Only Tuple) boost PostgreSQL DML performance. This article explains the concept, shows examples and gives tuning advice.
Generating a normal distribution in SQL
SQL and PostgreSQL can be used to create sample data which possesses certain statistical properties like a normal distribution.
PostgreSQL vs PostgreSQL vs Postgres vs Oracle vs all the rest
How can you find data even if there are typos? In PostgreSQL there are various solutions to the problem of a search with odd spelling.
PostgreSQL: GROUP BY expression
GROUP BY is an integral part of SQL and PostgreSQL. You might not know that the GROUP BY expression can handle more than just simple fields.
Understanding recursive queries in PostgreSQL
This article gives a simple introduction with examples to recursive queries in PostgreSQL. It also includes a comparison to Oracle recursive queries.
What is fillfactor and how does it affect PostgreSQL performance?
In this blog posting you find out: What is fillfactor and how does it affect PostgreSQL performance? | Effects & Testing
pgwatch2 v1.8.0 released
The most significant changes in pgwatch2 v1.8. PGWatch2 is a PostgreSQL monitoring tool - useful dashboard
PostgreSQL: Useful new data types - CREATE DOMAIN
UPDATED 08.05.2023 How to create useful new data types with CREATE DOMAIN in PostgreSQL | Advanced server side check constraints
Calculating differences between rows in SQL
Calculating differences between rows | How can one calculate the difference between the current and the previous row? | timeseries data
SQL trickery: Hypothetical aggregates
“If we had this data what would it mean?” - read more about this PostgreSQL technique, what it is good for & how it works.
Wrapping DB2 with PostgreSQL
Connection of DB2 with PostgreSQL done with the extension db2_fwd | Migration of db2's data to PostgreSQL | Read data from DB2
Composite type performance issues in PostgreSQL
Table functions & performance: Solve performance issues related to stored procedures and or a composite data type in PostgreSQL
PostgreSQL: Speeding up recursive queries and hierarchical data
How to use a hierarchical query, which is an SQL query that handles model data such as the structure of organizations - find out more
Partition management - do you really need a tool for that?
How to use partition management to speed up queries in PostgreSQL and make tables more manageable as data amounts grow.
How to count hits on a website - concurrent counting in PostgreSQL
How to grant users access to a certain piece of data a limited number of times. What happens if two users access it concurrently?
Binary data performance in PostgreSQL
This article discusses the question if binary data should be stored inside PostgreSQL or not. The performance of the various options is benchmarked.
PostgreSQL: Network latency does make a BIG difference
Find out about how network latency is essential to PostgreSQL database performance. Measure the performance impact caused by latency.
Series Forecasting with Recurrent Neural Networks (LSTM)
How to build your first Recurrent Neural Network (RNN) for series forecasting | Usage of Long Short Term Memory (LSTM)
Intersecting Tracks of individuals – MobilityDB
How to use MobilityDB for contact tracking. Extension built on top of PostGIS, specializing on processing and analysing spatio-temporal data
7 things that could be improved in PostgreSQL
UPDATE July 2023: What could be better in PostgreSQL? Find out about its weak points, and see what has improved since 2020.
PostgreSQL v13 new feature: tuning autovacuum on insert-only tables
PostgreSQL v13 introduces autovacuum for insert-only tables. This article tells you why and how to make use of this new feature.
Intersecting GPS-Tracks to identify infected individuals
PostGIS' functions can be used to trace down infected people and their contact with healthy individuals | GPS-Tracks | spatial data
Subtransactions and performance in PostgreSQL
The overuse of subtransactions in PostgreSQL can harm performance - find out how to diagnose if you are suffering from it.
Reduce WAL by increasing checkpoint distance
Adjusting checkpoints is useful when tuning a server and improves the database performance. Furthermore, it helps to reduce WAL.
Visualizing OSM data in QGIS
Visualizing OSM datasets with QGIS. Generation of some nice maps on the client | Map Styling in QGIS | Adding Spatial data
Tips and tricks to kick-start Postgres year 2020
Postgres Tips and Tricks: Benefit from these little Time-Saving commands, SQL snippets or PostgreSQL features for PostgreSQL users.