Skip to content

Postgres

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.

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.

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.

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.

DataFiller 2.0.0 is out!

DataFiller processes a Postgres database schema file augmented with directives in comments, and generates pseudo-random data matching this schema, taking into account constraints such as types, but also primary key, unique, foreign keys, not null…

Version 2.0.0 introduces the following new features:

DataFiller 1.1.3 is out!

I have just released version 1.1.3 of DataFiller.

The Python script processes a Postgres database schema file augmented with directives in comments, and generates random data matching this schema, taking into account constraints such as types, but also primary key, unique, foreign keys, not null…

Postgres Archeology

In a previous post I have ported a Turing Machine (TM) in SQL down to Postgres 7.3.

I report here my fruitless attempts at running previous versions of Postgres on a Debian or Ubuntu Linux. They would not configure and/or compile, at least with the minimal effort I was ready to undergo: writing portable-to-the-future system-dependent C code does not work. Basically, the code from 15 years ago is lost unless you run it on a system from 15 years ago, which may require a hardware from 15 years ago as well, or maybe a VM.

Turing Machine in SQL (5/5)

This is the last of five posts on this subject.

In a previous post I have shown how to build a Turing Machine (TM) in SQL with Postgres using a recursive SQL function. I claimed that this would work with versions of Postgres older than 8.4. In this post, I actually investigate this claim, porting the script down to version 7.3 on a Debian Linux. Testing the relatively simple SQL script with older versions reminds us of all the goodies that have been added over the years.