Category: Hans-Juergen Schoenig
shared_buffers: Looking into the PostgreSQL I/O cache
The PostgreSQL caching system has always been a bit of a miracle to many people and many have asked me during consulting or training sessions: How can I figure out what the PostgreSQL I/O cache really contains? What is in shared buffers and how can one figure out? This post will answer this kind of […]
Setting PostgreSQL configuration parameters
A lot has been written about configuring postgresql.conf, postgresql.auto.conf and so on. However, sometimes it requires to take a second look in order to understand, how PostgreSQL really handles configuration parameters. You will notice that PostgreSQL configuration offers more than meets the eye at first glance. So let us dive into PostgreSQL GUCs and configuration […]
A beginner’s guide to PostgreSQL’s UPDATE and autovacuum
Looking at the type of PostgreSQL support requests, we have received recently, it is striking to see, how many of them are basically related to autovacuum and UPDATE in particular. Compared to other databases such as Oracle, PostgreSQL’s way of handling UPDATE and storage in general is quite different. Therefore people moving from Oracle to […]
idle_in_transaction_session_timeout: Terminating idle transactions in PostgreSQL
When running PostgreSQL on a production system, it might happen that you are facing table bloat. As you might know PostgreSQL has to copy a row on UPDATE to ensure that concurrent transactions can still see the data. At some point VACUUM can clean out dead rows but if transactions are too long, this cleanup […]
Advanced SQL: Why first_value and last_value are no bugs
SQL is a must, if you want to be a Data Analyst or a Data Scientist. However, every once in a while people are wondering, why a result is the way it is. While on the road in Berlin (Germany) the other day, I have found a fairly interesting scenario, which is pretty counterintuitive to […]
PostgreSQL: Speeding up analytics and windowing functions
As a professional PostgreSQL support company we see a lot of SQL performance stuff, which is often worth sharing. One of those noteworthy things, which might be interesting to you, the reader, happened this week when I was out on a business trip to Berlin, Germany. This (excellent) customer was making extensive use of windowing […]
What PostgreSQL Full-Text-Search has to do with VACUUM
What does PostgreSQL Full-Text-Search have to do with VACUUM? Many readers might actually be surprised that there might be a relevant connection worth talking about at all. However, those two topics are more closely related than people might actually think. The reason is buried deep inside the code and many people might not be aware […]
COPY in PostgreSQL: Moving data between servers
The COPY command in PostgreSQL is a simple way to copy data between a file and a table. COPY can either copy the content of a table to or from a table. Traditionally data was copied between PostgreSQL and a file. However, recently a pretty cool feature was added to PostgreSQL: It is now possible […]
PostgreSQL performance: How the optimizer handles views
“How does the PostgreSQL optimizer handle views?” or “Are views good or bad?” I assume that every database consultant and every SQL performance expert has heard this kind of question already in the past. Most likely this does not only hold true for PostgreSQL experts but is also true for Oracle, DB2 and MS SQL […]
Detecting performance problems easily in PostgreSQL
Is there a single significantly large and important database in the world, which does not suffer from performance problems once in a while? I bet that there are not too many. Therefore, every DBA (database administrator) in charge of PostgreSQL should know, how to track down potential performance problems to figure out, what is really […]
PostgreSQL indexing: Index scan vs. Bitmap scan vs. Sequential scan (basics)
Many people keep asking about index scans in PostgreSQL. This blog is meant to be a basic introduction to the topic because many people do not seem to be aware of what the optimizer does, when a single query is processed. I decided to give a brief introduction showing, how a table can be accessed […]
Avoiding unnecessary stored procedure calls in PostgreSQL
It is possible to write stored procedures in PostgreSQL in almost any widespread language such as Perl, Python or C. In general this offers a great deal of flexibility and acceptable performance. However, in some cases customers might say: “We have the feeling that procedures are slow”. The thing is: PostgreSQL might not be to […]