Paginating records across large datasets in a web application seems like an easy problem that can actually be pretty tough to scale. The two main pagination strategies are offset/limit and cursors.
We'll first take a look at the two methods and then a slight modification that can make offset/limit extremely performant.
Source: Efficient Pagination Using Deferred Joins, an article by Aaron Francis.
This article will show you one such optimization story, which is interesting because both the before and after query plans will look somewhat ridiculous (in terms of cost). Moreover, it shows how PostgreSQL row count estimation can go very wrong. In this case, domain knowledge will help us trick PostgreSQL into a different query plan which will be waaay faster, without adding any additional indices.
Source: Tricking Postgres into using an insane - but 200x faster - query plan, an article by Jacob Martin.
PostgreSQL query planner is full of surprises, so a common-sense approach to writing performant queries is sometimes misleading. In this blog post, I’ll describe examples of optimizing seemingly obvious queries with the help of EXPLAIN ANALYZE and Postgres metadata analysis.
Source: Five Easy to Miss PostgreSQL Query Performance Bottlenecks, an article by Paweł Urbanek.
When I started working with Git I had some experience with Concurrent Versions System (CVS) and Apache Subversion (SVN), so I tried to understand it in terms of those classic source code repository systems. That way of thinking only got me a limited understanding of Git’s capabilities. Since then I have grown to understand Git much better, so this article is a kind of “note to self” text to remind myself how Git works and explain it to those who are new to it. I assume you know your way around other more classical source code repositories like CVS or SVN.
Source: Learn the workings of Git, not just the commands, an article by Andre Fachat.