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
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
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
The Unexpected Find That Freed 20GB Of Unused Index Space
- Haki Benita tl;dr: Haki’s team managed to free up more than 70GB of database storage without dropping any indexes or deleting data. They initially used conventional techniques like rebuilding indexes and tables to clear up space. However, a surprising discovery allowed them to free an additional ~20GB. They realized that PostgreSQL indexes NULL values, which led them to create a partial index that excludes these NULL values, thereby significantly reducing the index size. The article also delves into the concept of "bloat" in PostgreSQL tables and indexes, offering solutions like using the REINDEX command and the pg\_repack extension to manage it. Haki suggests that partial indexes are particularly useful for fields with a high percentage of NULL values.featured in #443
Create An Advanced Search Engine With PostgreSQL
- Tudor Golubenco tl;dr: “The Postgres approach to full-text search offers building blocks that you can combine to create your own search engine. This is quite flexible but it also means it generally feels lower-level compared to search engines like Elasticsearch, Typesense, or Mellisearch.” The article covers: (1) The tsvector and tsquery data types. (2) The match operator @@ to check if a tsquery matches a tsvector. (3) Functions to rank each match (ts\_rank, ts\_rank\_cd). (4) The GIN index type, an inverted index to efficiently query tsvector.featured in #430