Be prepared for prepared transactions
Prepared transactions are disabled in PostgreSQL by default, since the parameter max_prepared_transactions has the default value 0. You don’t need prepared transactions in most cases. However, they can cause nasty problems, so I think that everybody who runs a PostgreSQL database should understand them. To illustrate these problems, I’ll show you how to use prepared […]
PostgreSQL affiliate projects for horizontal multi-terabyte scaling
Some weeks ago I wrote about some common concepts / performance hacks, how one can (relatively) easily scale to a terabyte cluster or more. And based on my experience visiting customers from various industries, 80% of them are not even reaching that threshold…but just to be clear I wanted to write another post showing that […]
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 […]
Machine Learning in PostgreSQL Part 1: Kmeans clustering
Machine Learning in 10 Lines Every person that reads newspapers, magazines or any other media of general interest has at least a basic idea of what Machine Learning is. And this is not only a fashion, Machine Learning is already part of our everyday life and will be much more in the future: from personalized […]
Killed index tuples
Since I only recently learned about the concept of “killed index tuples”, I thought there might be some others who are not yet familiar with this interesting PostgreSQL concept. This may give you an explanation the next time you encounter wildly varying execution times for the same execution plan of the same PostgreSQL query. […]
Peeking at query performance of the upcoming version 11 of PostgreSQL
Last weeks the first release candidate of the upcoming Postgres version 11 was released, stating that everything is going more or less as planned – great news! The 11 release is promising a lot of very cool stuff as usual, including – better partitioning, more parallization and JIT capabilities (which are not activated by default […]
Ideas for scaling PostgreSQL to multi-terabyte and beyond
After a recent chat with a DBA, making his first steps towards migrating a big database away from Oracle to Postgres, I thought there must be just too few articles on PostgreSQL features and “hacks”, aiding in achieving scalability to squeeze the last out of the hardware to safely accomodate some decent multi-terabyte size databases. […]
Are your foreign keys indexed?
Foreign key constraints are an important tool to keep your database consistent while also documenting relationships between tables. A fact that is often ignored is that foreign keys need proper indexing to perform well. This article will explain that and show you how to search for missing indexes. Index at the target of a […]
Correlation of PostgreSQL columns explained
After you ANALYZE a PostgreSQL table to collect value distribution statistics, you will find the gathered statistics for each column in the pg_stats system view. This article will explain the meaning of the correlation column and its impact on index scans. Physical vs. logical ordering Most common PostgreSQL data types have an ordering: they support […]
Next feature release for the pgwatch2 monitoring tool
With summer fading away it’s time to get busy again – last couple of weeks I’ve taken time to work on our Open Source PostgreSQL monitoring tool pgwatch2 and partly on request from a couple of bigger organizations using the tool on a larger scale, added another batch of useful management / scaling features 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 […]