/PostgreSQL

Making A Postgres Query 1,000 Times Faster

- Alejandro García Montoro tl;dr: Alejandro describes optimizing a Postgres query that powers Elasticsearch indexing. The original query took over 24 seconds due to processing the entire posts table. By using row comparisons instead of separate conditions, the optimized query runs in 34ms - a 1000x speedup. However, the same optimization makes the MySQL version 50x slower.

featured in #516


The Notifier Pattern For Applications That Use Postgres

tl;dr: “Listen / notify in Postgres is an incredible feature that makes itself useful in all kinds of situations. I’ve been using it a long time, started taking it for granted long ago, and was somewhat shocked recently looking into MySQL and SQLite to learn that even in 2024, no equivalent exists.”

featured in #512


How Levels.fyi Built Scalable Search With PostgreSQL

- Tanishq Singh tl;dr: The post outlines how Levels.fyi built a scalable fuzzy search solution using PostgreSQL that handles over 10 million search queries per month with p99 query performance under 20ms, outlining the key steps. 

featured in #504


Postgres Is Eating The Database World

- Ruohang Feng tl;dr: “PostgreSQL isn’t just a simple relational database; it’s a data management framework with the potential to engulf the entire database realm. The trend of “Using Postgres for Everything” is no longer limited to a few elite teams but is becoming a mainstream best practice.”

featured in #498


Looking Back At Postgres

- Murat Demirbas tl;dr: The Postgres origin story: “Riding on the success of Ingres project at Berkeley, Stonebraker began working on database support for data types beyond the traditional rows and columns in the early 1980s. A motivating example was to provide database support for CAD tools for the microelectronics industry, including "new data types such as polygons, rectangles, text strings, etc...

featured in #483


How We Migrated Our PostgreSQL Database With 11 Seconds Downtime

- David McDonald tl;dr: “Our source database is about 400GB in size. It has about 1.3 billion rows, 85 tables, 185 indexes and 120 foreign keys. It is PostgreSQL version 11. On a usual weekday, we do somewhere in the region of 1,000 inserts or updates per second, plus a similar number of reads.” The DB is relied upon to send millions of important and timely notifications each day, from flood alerts to updating users about their passport applications . 

featured in #482


An Overview Of Distributed PostgreSQL Architectures

- Marco Slot tl;dr: “What many users notice within the first few minutes of using a distributed database is how unexpectedly slow they can be, because you quickly start hitting performance trade-offs. There are many types of distributed PostgreSQL architectures, and they each make a different set of trade-offs. Let’s go over some of these architectures."

featured in #479


The Surprising Impact Of Medium-Size Texts On PostgreSQL Performance

- Haki Benita tl;dr: Haki’s article delves into the intricacies of text field sizes and their impact on PostgreSQL query performance. He classifies text fields into "small", "medium", and "large", highlighting the unexpected performance implications of medium-sized texts. Through the lens of PostgreSQL's TOAST mechanism, which compresses and/or breaks up large field values, Haki demonstrates that medium texts can sometimes lead to slower queries than even larger texts. "The main problem with medium-size texts is that they make the rows very wide," affecting performance due to increased IO.

featured in #453


Grouping Digits In SQL

- Peter Eisentraut tl;dr: Peter discusses a new feature in PostgreSQL 16 that allows for the grouping of digits in numeric literals by separating them with underscores. This feature aims to improve readability and reduce the likelihood of errors when dealing with large numbers. The feature faced initial challenges due to PostgreSQL's existing parsing behavior, which allowed numeric constants and identifiers to be adjacent without separating whitespace.

featured in #451


Some Useful, Non-Obvious Postgres Patterns

- Phil Booth tl;dr: Several PostgreSQL patterns that might not be immediately obvious to many developers including: (1) Always define explicit ON DELETE semantics. (2) If in doubt, use ON DELETE SET NULL. (3) Mutually exclusive columns. (4) Prohibit hidden nulls in jsonb columns. (5) Declare your updated\_at columns NOT NULL to make sorting easier.

featured in #446