© Laurenz Albe 2023 This is a short war story from a customer problem. It serves as a warning that there are special considerations when running software in a Docker container. The problem description The customer is running PostgreSQL in Docker containers. They are not using the “official” image, but their own. Sometimes, under conditions […]
© Laurenz Albe 2023 A while ago, I wrote about how difficult it is to get an execution plan for a parameterized query. The method suggested in that article works, but is still somewhat complicated. So I wrote a patch to support an EXPLAIN option GENERIC_PLAN, which provides native support for that. My patch got […]
© Laurenz Albe 2023 The PostgreSQL documentation has some information about row locks. But typically, you cannot see them in pg_locks, and not everybody knows how they work and how to track and debug row locks. This article intends to give some insight into how PostgreSQL row locks work “under the hood”. Why are there […]
© Laurenz Albe 2023 I am on a spree! After writing about breaking your database and transaction ID wraparound, here I am writing about damage caused by CHECK constraints! Trust me, I don’t want to destroy PostgreSQL databases. It’s just that this Twitter message was brought to my attention and triggered memories of other reports […]
Stored procedures are a core concept which can be found in most relational database systems. They have proven to be useful in many areas and have been widely adopted by developers and DBA’s alike. Stored procedures basics In PostgreSQL stored procedures have been around for a number of years. The syntax of this important command […]
PostgreSQL provides all kinds of time-related functions. But time is not as easy as it seems. One question many people keep asking: How can I find the current timestamp? What time is it? While this seems like an easy question the answer isn’t as clear-cut as you might expect. Basically, there are three functions we […]
© Laurenz Albe 2022 Most people are aware of transaction ID wraparound. The concept has been well explained in Hans’ article, so I won’t repeat all that here. But for most people it is an abstract concept, a bogeyman lurking in the dark around the corner. Many people know horror stories about anti-wraparound autovacuum tanking […]
© Laurenz Albe 2022 I have been faced with a request for an unusual unique constraint that puzzled me for a while. Since the solution I came up with is a nice show-case for range data types, I’ll share it with you. Also, it allows me to rant some about NULL, which is a temptation […]
© Laurenz Albe 2022 When processing a big result set in an interactive application, you want to paginate the result set, that is, show it page by page. Everybody is familiar with that from the first web search on. You also get a button to scroll to the next page, and you get a total […]
Analyzing time series often comes with challenges. This is especially true if you want to do a bit more than just run simple counts on values in a column. More often than not you’ll want to create a month’s report, a weekly overview and so on. This requires you to group on expressions rather than […]
© Laurenz Albe 2022 A while ago, I wrote about the performance impact of query parameter data types. Recently I encountered a similar problem with UNION ALL that baffled me for a while, so I’d like to share it with you. UNION ALL to implement polymorphism Different entities can have something in common. For example, […]
© Laurenz Albe 2022 For detailed query performance analysis, you need EXPLAIN (ANALYZE, BUFFERS) output for an SQL statement. With a parameterized statement, it can be difficult to construct a run-able statement for EXPLAIN (ANALYZE). Sometimes, you don’t even know the parameter values. I’ll show you how you can get at least plain EXPLAIN output […]
