Conditional foreign keys and polymorphism in SQL: 4 Methods
by
Laurenz Albe |
08.2023 This article is about the notoriously difficult problem of “conditional foreign keys”. In object-oriented programming languages, polymorphism is a […]
Indexing "LIKE" in PostgreSQL and Oracle
by
Laurenz Albe |
08.2023© Laurenz Albe 2023 Unless you use the binary collation, creating a b-tree index to support a LIKE condition in […]
"Why does my pg_wal keep growing?" That's a question I keep hearing again and again. It is an urgent […]
Reproducible builds: a PostgreSQL query optimization example
This post shows how to optimize a slow query that came out of the Reproducible Builds project. The Reproducible Builds […]
PostGIS, ArcGIS Enterprise and the Tour de France route
How to analyze a stretch of the Tour de France in a hybrid environment, ArcGIS Enterprise & PostGIS - we’ll utilize PostGIS native geometry types instead of ESRI geometry types to analyze this challenging stage both in ArcGIS Pro and PostGIS
Subqueries and performance in PostgreSQL
by
Laurenz Albe |
07.2023 SQL allows you to use subqueries almost anywhere where you could have a table or column name. All you […]
View Access Logging - Fix it in PostgreSQL
By Kirk Wolak Continuous Improvement is an important part of reducing technical debt. Over 30 years our active database has […]
Memory context: private memory management in PostgreSQL
by
Laurenz Albe |
06.2023 (updated 2025-01-27) PostgreSQL uses shared memory for data shared between processes. With the exception of the dynamic shared memory […]
Unexpected downsides of UUID keys in PostgreSQL
There are various compelling reasons to use universally unique identifiers (UUID) as primary keys. Two examples are: To be able […]
Use HOT, so CLUSTER won't rot in PostgreSQL
by
Laurenz Albe |
06.2023CLUSTER is sometimes the last resort to squeeze performance out of an index scan. Normally, you have to repeat CLUSTER […]
