performance

September 13, 2019
in Postgres, performance
8 min read

Data Loading Performance of Postgres and TimescaleDB

Postgres is the leading feature-full independent open-source relational database, steadily increasing its popularity over the past 5 years. TimescaleDB is a clever extension to Postgres which implements time-series related features, including under the hood automatic partioning, and more.

Because he knows how I like investigate Postgres (among other things) performance, Simon Riggs (2ndQuadrant) prompted me to look at the performance of loading a lot of data into Postgres and TimescaleDB, so as to understand somehow the degraded performance reported in their TimescaleDB vs Postgres comparison. Simon provided support, including provisioning 2 AWS VMs for a few days each.

December 11, 2016
in Postgres, performance
10 min read

Choosing Postgres Bloom Index Parameters

Postgres Bloom is an extension contributed by Teodor Sigaev, Alexander Korotkov and Oleg Bartunov which provides a new index type for integer and text columns. There is some coverage on how to use it and how it works, which is good because documentation is scarse.

July 28, 2016
in Game, performance
9 min read

1010! Analysis

1010! is a simple puzzle game by Gram Games, originating from Turkey in August 2014. It feels like Tetris but without the time constraint and with more choices. It is available on Android and iOS.

Advices are available online on how to get a high score. I’m more interested in devising a decision strategy, automate it efficiently and optimize it through simulations… which also results in advices on how to get a high score, but more substanciated.

Teaser: below, learn how to get a score over 1 million every 8 games…

August 23, 2014
in Postgres, performance
4 min read

Postgres `FILLFACTOR` for `UPDATE`

This post discusses the performance impact of Postgres FILLFACTOR table storage parameter on an UPDATE OLTP load. Note that this FILLFACTOR is indeed the table storage parameter, although there is also an eponymous parameter for indexes.

August 17, 2014
in Postgres, performance
5 min read

Postgres page size for SSD (2)

I have recently posted performance figures with varying page size using pgbench on SSD, which show a +10% improvement with smaller 4 kB page size over the default 8 kB page size.

Josh Berkus pointed out that pgbench test uses rather small 100-bytes rows, and that changing the tuple size might induce a different conclusion. To assess this point, I ran some tests with different row sizes: 1 kB, 3 kB, 7 kB and 15 kB.

August 8, 2014
in Postgres, performance
5 min read

Postgres page size for SSD

In a previous post, I have outlined the time required by a Postgres database to warm-up from a HDD on a read-only load for a database that fits in memory.

In this post, I want to look at write performance on SSD, focusing on the impact of Postgres page size (blocksize), and on checking whether the current 8 kB default is relevant.

August 14, 2013
in Postgres, performance
7 min read

Postgres Warm-up

A database can take some time to reach good performances, the time necessary to OS and database caches to load the necessary data from the hard disk drive. Here is an example with Postgres, which outlines the long delay incurred (18 minutes), presents a simple model to explain this behavior, and shows how to reduce it significantly (to a few minutes).