Too many tables are bad for you
This article shows that problems that too many tables in a single PostgreSQL database can cause and how to deal with these problems.
When is a function leakproof?
This article discusses row-level security, security barrier functions and leakproof functions, which are necessary to make them work.
My queries to monitor autovacuum
This article shows the queries that I use to monitor autovacuum and explains the background and the measures you can take to avoid problems.
Create your PostgreSQL clusters with the "builtin" C collation!
Read my plea for using the C collation when you create a PostgreSQL database cluster, ideally using the "builtin" collation provider.
Dealing with integer overflow in sequence-generated primary keys
Sequence-generated 4-byte integer primary keys can experience integer overflow. I'll show you how to monitor for the problem and fix it.
The bastard DBA from hell
Two little stories from the working life of the bastard DBA from hell, fighting user problems (but mostly users).
Benefits of a DESCending index
The DESC clause in CREATE INDEX is rarely needed. I'll show use cases for descending indexes, including storage efficiency and performance.
How to do UPDATE ... LIMIT in PostgreSQL
There is no UPDATE ... LIMIT in PostgreSQL. This article shows how to achieve the same result and how to avoid potential pitfalls.
How to handle "database is not accepting commands"
I'll explain how to handle the dreaded error message "database is not accepting commands" and alert you to the responses you should avoid.
The Fun of Open Source: Roman Numerals in PostgreSQL
This article talks about the new support for converting Roman numerals to numbers in PostgreSQL v18, as well as fun in general.
Type alignment and padding bytes: how to not waste space in PostgreSQL tables
Learn the concepts of data type alignment and padding bytes in PostgreSQL and how to define your table so that you don't waste storage space.
stats_fetch_consistency: caching PostgreSQL monitoring statistics?
This article describes the little known parameter stats_fetch_consistency and its potential applications for statistics collection.
Dealing with trigger recursion in PostgreSQL
This article describes the problem of endless trigger recursion in PostgreSQL and shows how to deal with it and get good performance.
What's so great about PostgreSQL v17?
PostgreSQL v17 has been released. If the list of new features does not inspire you, this article will try to change your mind.
What you should know about Linux memory overcommit in PostgreSQL
Understand why and how to disable memory overcommit to make PostgreSQL run reliably on Linux, including implications for containerization.
Keyset pagination with descending order
This article shows how to use keyset pagination if you want to sort some columns in ascending and others in descending order.
Swiss PGDay 2024 and PG conferences in general
Read about my impressions from the Swiss PGDay 2024 and learn why you should attend PostgreSQL conferences yourself.
Making the PostgreSQL visibility map visible
This article explains how to examine the PostgreSQL visibility map and showcases performance improvements added in recent releases.
Why do I have a slow COMMIT in PostgreSQL?
This article describes the possible reasons for a slow COMMIT in PostgreSQL and discusses what you can to against that.
Syslog logging with journald in PostgreSQL
This article explains how to configure syslog logging in PostgreSQL and shows how to view and manage the log with journald.
Broken foreign keys: how can that happen in PostgreSQL?
Do you have a broken foreign key? This article shows possible causes for broken foreign keys, so that you know what to avoid in the future.
What you should know about PostgreSQL minor upgrades
Read all there is to know about minor upgrade in PostgreSQL: how, why and when to install them, and what if your software vendor forbids it.
Conditional foreign keys and polymorphism in SQL: 4 Methods
How to model conditional foreign keys: 4 methods to transfer a reference to a polymorphic type to SQL. Explore how PostgreSQL features like JSON support and table inheritance can help.
Postgres v16: 14 Cool New Features
Find out what PostgreSQL v16 has to offer, what the top new features will be and why the new postgres is so cool and useful.
Indexing "LIKE" in PostgreSQL and Oracle
How to index LIKE conditions in PostgreSQL and Oracle. Answers the question: Why is LIKE indexing simpler in Oracle? Is it better?
Bulk load performance in PostgreSQL
Compare the performance of bulk load methods in PostgreSQL. Get recommendations for parameter settings to improve performance even more.
Subqueries and performance in PostgreSQL
This article discusses how subqueries perform in PostgreSQL and how to rewrite queries to improve their performance.
Use HOT, so CLUSTER won't rot in PostgreSQL
How to use HOT updates to maintain the row order established by CLUSTER to get efficient index scans in PostgreSQL.
ERROR: invalid byte sequence - Fix bad encoding in PostgreSQL
Fix ERROR: invalid byte sequence for encoding - Wrong encoding causes data corruption in PostgreSQL. How to fix bad encoding.
PostgreSQL ALTER DEFAULT PRIVILEGES - permissions explained
ALTER DEFAULT PRIVILEGES command for PostgreSQL permissions explained: learn how to use it correctly, know its pitfalls and use cases.
EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16
This article describes the EXPLAIN option GENERIC_PLAN introduced in PostgreSQL 16, including some examples. Learn about options syntax.
Row locks in PostgreSQL
Row locks in PostgreSQL - how do they work? How can I debug problems with them? The answers with examples are here.
Breaking your PostgreSQL database with bad CHECK constraints
This article shows two examples of CHECK constraints that cause problems and explains why PostgreSQL cannot be any more restrictive.
Transaction ID wraparound: a walk on the wild side
Have you ever seen data corruption from transaction ID wraparound? Avoid PostgreSQL performance problems - read here what causes it.
A unique constraint where NULL conflicts with everything
A cute solution for an unusual request for a unique constraint in PostgreSQL. This blog showcases range data types & exclusion constraints.
Pagination and the problem of the total result count
This article compares pagination methods for PostgreSQL and their performance. Also, avoid displaying the total result count!
UNION ALL, data types and performance
Find out the impact of PostgreSQL column data types on the performance of queries that involve a UNION ALL.
EXPLAIN that parameterized statement in PostgreSQL!
EXPLAIN (ANALYZE, BUFFERS) output is hard to get for a parameterized statement in PostgreSQL. With a few tricks we can get EXPLAIN output.
Rewrite OR to UNION in PostgreSQL queries
Improve queries in PostgreSQL - it is not always possible to rewrite OR to UNION in a query. Find out when it is safe to do manually.
Why are my PostgreSQL updates getting slower?
Updates getting slower is a frequent complaint of PostgreSQL users. What is behind it and how can I avoid slow updates?
Better SQL functions in PostgreSQL v14
New syntax for SQL functions was introduced in PostgreSQL v14, this article gives you vital tips for this important change & its advantages.
Transaction anomalies with SELECT FOR UPDATE
This article shows how surprising transaction anomalies can happen with SELECT FOR UPDATE and what you can to to avoid them.
Case-insensitive pattern matching in PostgreSQL
We discuss the options for case-insensitive comparison and pattern matching in PostgreSQL, comparing the performance of different approaches.
View permissions and row-level security in PostgreSQL
How view permissions are checked, and how that changes if you use the security_invoker view option from PostgreSQL v15. Use row-level security effectively.
How to cancel a hanging PostgreSQL query
This article describes how to cancel queries in PostgreSQL and shows a trick that you can use if canceling a query doesn't work.
Time zone management in PostgreSQL
Time zone management and timestamp data types in PostgreSQL - learn how to use 'timestamp with time zone' correctly and optimize performance.
How to DROP ROLE or DROP USER in PostgreSQL
Learn how to use DROP ROLE and DROP USER in PostgreSQL. This article explains how to remove users and gives troubleshooting tips.
Query parameter data types and performance
How do query parameter data types affect performance? Find out how to avoid bad performance by choosing the correct parameter types.
Pipeline mode for better PostgreSQL performance on slow networks
This article shows how pipeline mode, new with PostgreSQL v14, can improve query performance over slow network connections.
Entity-attribute-value (EAV) design in PostgreSQL - don't do it!
This article explains what the entity-attribute-value (EAV) model is and why you shouldn't use it in a relational database.
WITH HOLD cursors and transactions in PostgreSQL
This article describes how cursors and transactions interact, how WITH HOLD can overcome the limitations, plus some caveats and tricks.
Gaps in sequences in PostgreSQL
How you can end up with gaps in sequences & how they can even skip backwards. How to build a gapless sequence in PostgreSQL.
JSON in PostgreSQL: how to use it right
This article describes what can go wrong when using the much-loved JSON capabilities of PostgreSQL and gives guidelines how to do it right.
Index bloat reduced in PostgreSQL v14
This article describes how PostgreSQL v14 reduces B-tree index bloat with "bottom-up index tuple deletion". A test case shows the difference.
How to interpret PostgreSQL EXPLAIN ANALYZE output
UPDATED July 2023: How to use EXPLAIN ANALYZE in PostgreSQL, learn tools to visualize the output. See query execution time and row count.
When to use tablespaces in PostgreSQL
This article explains what PostgreSQL tablespaces are, when to use them and how they are different from Oracle tablespaces.
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.
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".
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.
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.