Category: Hans-Juergen Schoenig
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, one 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 most […]
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 means 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
In PostgreSQL it is possible to write stored procedures 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 […]
Cybertec pgconfigurator: Configuring PostgreSQL visually
We are proud to announce our latest tool released to the PostgreSQL community. We know that many people want a visual tool to configure their PostgreSQL database servers. Therefore we proudly want to announce Cybertec pgconfigurator, which has been designed to give users a tool, to quickly determine the most important config parameters to be […]
Preparing data for machine learning
You have probably noticed that everyone is talking about Artificial Intelligence and Machine Learning these days. Quite rightly because it is a very important topic, which is going to shape our future for sure. However, when looking at most of the code related to machine learning available on the net, it strikes me, how much […]