Posts by: tomas.vondra

On the impact of full-page writes

While tweaking postgresql.conf, you might have noticed there’s an option called full_page_writes. The comment next to it says something about partial page writes, and people generally leave it set to on – which is a good thing, as I’ll explain later in this post. It’s however useful to understand what full page writes do, because…

Basics of Tuning Checkpoints

On systems doing non-trivial number of writes, tuning checkpoints is crucial for getting good performance. Yet checkpoints are one of the areas where we often identify confusion and configuration issues, both on the community mailing lists and during performance tuning reviews for our customers. (The other one being autovacuum, discussed a few days ago by…

PostgreSQL vs. Linux kernel versions

I’ve published multiple benchmarks comparing different PostgreSQL versions, as for example the performance archaeology talk (evaluating PostgreSQL 7.4 up to 9.4), and all those benchmark assumed fixed environment (hardware, kernel, …). Which is fine in many cases (e.g. when evaluating performance impact of a patch), but on production those things do change over time –…

On the benefits of sorted paths

I had the pleasure to attend PGDay UK last week – a very nice event, hopefully I’ll have the chance to come back next year. There was plenty of interesting talks, but the one that caught my attention in particular was Performace for queries with grouping by Alexey Bashtanov. I have given a fair number…

Application users vs. Row Level Security

A few days ago I’ve blogged about the common issues with roles and privileges we discover during security reviews. Of course, PostgreSQL offers many advanced security-related features, one of them being Row Level Security (RLS), available since PostgreSQL 9.5. As 9.5 was released in January 2016 (so just a few months ago), RLS is fairly…

Auditing Users and Roles in PostgreSQL

One of the services we offer are security reviews (or audits, if you want), covering a range of areas related to security. It may be a bit surprising, but a topic that often yields the most serious issues is roles and privileges. Perhaps the reason why roles and privileges are a frequent source of issues…

On the usefulness of expression indexes

When teaching PostgreSQL trainings, both on basics and advanced topics, I often find out the attendees have very little idea how powerful the expression indexes may be (if they are aware of them at all). So let me give you a brief overview. So, let’s say we have a table, with a range of timestamps…

Tables and indexes vs. HDD and SSD

Although in the future most database servers (particularly those handling OLTP-like workloads) will use a flash-based storage, we’re not there yet – flash storage is still considerably more expensive than traditional hard drives, and so many systems use a mix of SSD and HDD drives. That however means we need to decide how to split…

Performance limits of logical replication solutions

In the previous blog post, I briefly explained how we got the performance numbers published in the pglogical announcement. In this blog post I’d like to discuss the performance limits of logical replication solutions in general, and also how they apply to pglogical. physical replication Firstly, let’s see how physical replication (built into PostgreSQL since…

On pglogical performance

A few days ago we released pglogical, a fully open-source logical replication solution for PostgreSQL, that’ll hopefully get included into the PostgreSQL tree in a not-too-distant future. I’m not going to discuss about all the things enabled by logical replication – the pglogical release announcement presents a quite good overview, and Simon also briefly explained…