Transactions in PostgreSQL: READ COMMITTED vs. REPEATABLE READ
The ability to run transactions is the core of every modern relational database system. The idea behind a transaction is to allow users to control the way data is written to PostgreSQL. However, a transaction is not only about writing – it is also important to understand the implications on reading data for whatever purpose […]
PostgresSQL: Implicit vs. explicit joins
If you happen to be an SQL developer, you will know that joins are really at the core of the language. Joins come in various flavors: Inner joins, left joins, full joins, natural joins, self joins, semi-joins, lateral joins, and so on. However, one of the most important distinctions is the difference between implicit and […]
PostgreSQL: Parallel CREATE INDEX for better performance
PostgreSQL will shortly be released and it is therefore time to take a look at one of the most important new features provided by PostgreSQL 11: The ability to create indexes in parallel. For many years various commercial database vendors have already offered this feature and we are glad that PostgreSQL has become part of […]
What hot_standby_feedback in PostgreSQL really does
Many of you out there using PostgreSQL streaming replication might wonder what this hot_standby_feedback parameter in postgresql.conf really does. Support customers keep asking this question so it might be useful to share this knowledge with a broader audience of PostgreSQL users out there. What VACUUM does in PostgreSQL VACUUM is an essential command in PostgreSQL […]
PostgreSQL: Improving sort performance
Sorting is a very important aspect of PostgreSQL performance tuning. However, tuning sorts is often misunderstood or simply overlooked by many people. So I decided to come up with a PostgreSQL blog showing, how sorts can be tuned in PostgreSQL. Creating sample data To show how sorting works, I created a couple of million rows […]
3 ways to detect slow queries in PostgreSQL
When digging into PostgreSQL performance it is always good to know, which option one has to spot performance problems and to figure out, what is really going on on a server. Finding slow queries and performance weakspots is therefore exactly what this post is all about. There are many ways to approach performance problems. However, […]
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 […]
PostgreSQL clusters: Monitoring performance
When people are talking about database performance monitoring they usually think of inspecting one PostgreSQL database server at a time. While this is certainly useful it can also be quite beneficial to inspect the status of an entire database cluster or to inspect a set of servers working together at once. Fortunately there are easy […]
PostgreSQL: Detecting periods of activity in a timeseries
I have already written about timeseries and PostgreSQL in the past. However, recently I stumbled across an interesting problem, which caught my attention: Sometimes you might want to find “periods” of activity in a timseries. For example: When was a user active? Or when did we receive data? This blog post tries to give you […]
CREATE VIEW vs. ALTER TABLE in PostgreSQL
In PostgreSQL, a view is a virtual table based on an SQL statement. It is an abstraction layer, which allows to access the result of a more complex SQL fast an easily. The fields in a view are fields from one or more real tables in the database. The question many people now ask if: […]
Mapping Oracle datatypes to PostgreSQL
For those of you out there working with PostgreSQL in a professional way, migrating from Oracle to PostgreSQL might be one of the most beloved tasks available. One of the first things most people will notice, however, is that those data types available in Oracle might not be quite the same in PostgreSQL. This blog […]
PostgreSQL: Sharing data across function calls
Recently I did some PostgreSQL consulting in the Berlin area (Germany) when I stumbled over an interesting request: How can data be shared across function calls in PostgreSQL? I recalled some one of the other features of PostgreSQL (15+ years old or so) to solve the issue. Here is how it works. Stored procedures in […]