Postgres scaling advice for 2021
So, you’re building the next unicorn startup and are thinking feverishly about a future-proof PostgreSQL architecture to house your bytes? My advice here, having seen dozens of hopelessly over-engineered / oversized solutions as a database consultant over the last 5 years, is short and blunt: Don’t overthink, and keep it simple on the database side! […]
Tips and tricks to kick-start the Postgres year 2021
I decided to start out this year by looking into my notes from last year and extracting from them a small set of Postgres tips for you. This might not be, strictly speaking, fresh news… but I guess the average RAM of tech workers is about 64KB or so nowadays, so some repetition might not […]
Insert-only data modelling to smooth peaks on slow disks
A few years ago, I wrote a short post on a similar topic; since then, I’ve often seen that the whole concept of suggesting to do more INSERT-s in critical parts of the code seems pretty strange to most customers. It’s even alien to those who are not new to databases in general. So I […]
Having a 2nd look at Postgres v13 improvements
Version 13 has been out there for over a month by now and the most important stuff has already been well digested… but luckily (or not) there’s so much non-major stuff in every release! This becomes quite evident when looking at the release notes with its 150 items of change or peeking at the great […]
A quick look at PostgreSQL 13 RC1 query performance
If you read this blog post the new PostgreSQL version will be probably already officially released to the public for wider usage…but seems some eager DBA already installed the last week’s Release Candidate 1 and took it for a spin 😉 The “spin” though takes 3 days to run for my scripts, so that’s the […]
A quick pg_stat_statements troubleshooting hack
An introductory story (with some slight “drama” added for fun) from those good old days of on-site consulting 🙂 So…I’m at a client where the database is not behaving nicely among other things…what a crappy DB product indeed I hear, it gets cranky every morning although there should be a constant workload and is saturating […]
What is fillfactor and how does it affect PostgreSQL performance?
Recently I was asked if there’s a rule of thumb / best practice for setting the fillfactor in Postgres – and the only answer I could give was to decrease it “a bit” if you’re doing lots and lots of updates on some table. Good advice? Well it could be better of course – this […]
pgwatch2 v1.8.0 released
After exactly half a year (seems to be a pattern already) since the last major release of pgwatch2 Open Source PostgreSQL monitoring tool, I’m glad to announce that another set of useful features and improvements have found their way into the pgwatch2 code repository! New version is incremented to v1.8.0 and content wise it’s mostly […]
Partition management – do you really need a tool for that?
The functionality of using table partitions to speed up queries and make tables more manageable as data amounts grow has been available in Postgres for a long time already, with nicer declarative support available from v10 – so in general it’s a known technique for developers. But what is not so uniformly clear is the […]
7 things that could be improved in PostgreSQL
Usually in this blog I’m writing about various fun topics around PostgreSQL – like perhaps new cool features, some tricky configuration parameters, performance of particular features or on some “life hacks” to ease the life of DBA-s or developers. This post will be quite different though – inspired by an article I stumbled upon recently, […]
Detailed look on the new PostgreSQL troubleshooting extension – pg_show_plans
2 weeks ago we announced a new, long awaited, Postgres extension to look into execution plans for “live” running queries: pg_show_plans. This was not possible before in PostgreSQL yet, so it’s really a very cool piece of functionality and we’d like to echo out the message again. So here in this blogpost we’ll reiterate some […]
Performance differences between normal and generic audit triggers
Recently I was talking in a more general way about some common auditing / change tracking approaches for PostgreSQL…but it also made me curious, how it roughly looks from the performance side? To quickly recap the previous blog post: the most common approaches for tracking important changes are mostly solved with writing some triggers. There […]