Kaarel Moppel: Insert-only data modeling with PostgreSQL?

Datetime:2016-08-23 02:17:26         Topic: PostgreSQL          Share        Original >>
Here to See The Original Article!!!

During recent years there has been quite a lot of fuzz about “insert-only” approaches and some database-like products (Datomic, Apache Samza) have emerged, being inspired by the idea of having an immutable datastore. In light of cheap storage and powerful hardware, I see the idea definitely having potential for certain use cases. So why not to apply the concept using a PostgreSQL database? No inherent limitations there. One should just find a suitable use case and design the data model accordingly.

My own interest though was more towards performance of the approach in comparison to the standard “update” way, as in theory according to my understanding “insert-only” approach should provide some performance benefits as Postgres implementation of UPDATE is actually a DELETE+INSERT together with the possible update of all index entries in the worst-case scenario.

Pros and Cons

When doing only INSERT’s instead I would reckon that altogether fewer IO operations would be needed, bloat would be minimized (meaning no AUTOVACUUM hiccups), mostly sequential writing would happen (index pages could still split), a lot less full page images need to be written into XLOG, plus on the logical level one would get the full history of changes making audit tables unnecessary.

There would also be of course downsides – more disk space and additional indexing would be needed, queries could easily get unwieldy for joins etc, limited foreign key usage, for really big amounts of data one could need custom batch jobs for cleaning up old versions of rows still at some point, to name a few considerations.

Finding a suitable use case

So after some thinking about a good but simple test case for my theoretical hunch about possible performance benefits I decided to model my “insert-only” schema to match the short version (using the –skip-some-updates flag) of the default “pgbench” TPC-B scenario. What the short version does, is that it leaves out balance updates on the smaller “pgbench_branches” and “pgbench_tellers” tables (where locking could occur, thus not maxing out IO), meaning that my customized “insert-only” script would become very simple, only doing inserts into the “pgbench_accounts”, leaving out also the insert on “pgbench_history” as we would already have the full history in the accounts table itself anyways.

Now to the course of testing itself. Files for converting the schema to insert-only mode and the custom “pgbench” test script can be found here.

# Initialize the normal “pgbench” schema with a small 1.3GB dataset (fits in RAM)
pgbench -i -s 100 &>/dev/null

# run the short version for 5min
pgbench -T 300 --skip-some-updates -c4 -j4 –protocol=prepared
starting vacuum...end.
transaction type: Update only pgbench_accounts
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 300 s
number of transactions actually processed: 1555859
latency average: 0.771 ms
tps = 5186.167970 (including connections establishing)
tps = 5186.221344 (excluding connections establishing)

# Initialize the read-only schema with a small 1.3GB dataset
pgbench -i -s 100 &>/dev/null

# convert the schema to insert-only mode
psql -f insert_only_data_model_changes.sql

# run the custome version for 5min
pgbench -s 100 -T 300 -c4 -j4  --protocol=prepared -f insert_only_tx_pgbench.sql
starting vacuum...end.
transaction type: Custom query
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 300 s
number of transactions actually processed: 2228239
latency average: 0.539 ms
tps = 7427.369377 (including connections establishing)
tps = 7427.546212 (excluding connections establishing)

Test results

After looking at the TPS numbers for both runs we see that the performance benefit expressed in TPS was +43% here! And behind the scenes I actually also measured the amount of WAL generated, the difference there was even bigger with +135%.

To sum it up – although a very simplistic example here – “insert-only” data modeling provided a nice TPS boost for our test scenario, while providing exactly the same (and even more, full history) information. So in short it could be a worthwhile investigation when one has to deal with sudden peaks for example, as it should give you more predictable IO when applied correctly. A nice exercise in thought in any case.

Kaarel Moppel

I’ve been interested with databases for the last 9 years, working last 5 years exclusively with PostgreSQL. And still I’m constantly surprised by it’s powerful set of features and the fast pace of development by the globally friendly community. On my spare time I enjoy playing soccer and travelling.


Put your ads here, just $200 per month.