/PostgreSQL

Postgres Full Text Search Is Awesome!

- Montana Low tl;dr: "With PostgresML, you can now skip straight to full on machine learning when you have the related data. You can load your feature store into the same database as your search corpus. Each data source can live in its own independent table, with its own update cadence, rather than having to reindex and denormalize entire documents back to ElasticSearch, or worse, large portions of the entire corpus, when a single thing changes."

featured in #349

My Notes On GitLab Postgres Schema Design

- Shekhar Gulati tl;dr: "My motivation to understand the schema of a big project like Gitlab was to compare it against schemas I am designing and learn some best practices from their schema definition. I can surely say I learnt a lot."

featured in #338

Speeding Up Sort Performance In Postgres 15

- David Rowley tl;dr: David explores each of the 4 improvements in PostgreSQL 15 that make sort performance go faster: (1) Improvements sorting a single column. (2) Reduce memory consumption by using generation memory context. (3) Add specialized sort routines for common datatypes. (4) Replace polyphase merge algorithm with k-way merge.

featured in #321

Postgres Auditing In 150 lines Of SQL

- Oliver Rice tl;dr: "Data auditing is a system that tracks changes to tables' contents over time. PostgreSQL has a robust set of features which we can leverage to create a generic auditing solution in 150 lines of SQL."

featured in #303

How We Optimized PostgreSQL Queries 100x

- Vadim Markovtsev tl;dr: Some of the tricks we used to speed up SELECT-s in PostgreSQL: LEFT JOIN with redundant conditions, VALUES, extended statistics, primary key type conversion, CLUSTER, pg\_hint\_plan + bonus.

featured in #298

A Hairy PostgreSQL Incident

- Jeremy Schneider tl;dr: "The tech team that I met uses PostgreSQL as a data store in one part of their application architecture. They have many deployments of this application around the world. This morning, in one geography, they performed a major version upgrade of a PostgreSQL database to version 11. After the upgrade, one part of the application started experiencing problems."

featured in #292

How Postgres Stores Rows

- Ketan Singh tl;dr: "I was trying to understand how PostgreSQL stores the data onto the disk and there are a few interesting things that I have noticed that might be useful for application developers. In this post, I will try to go into the implementation level details and map out how PostgreSQL row storage really works."

featured in #290

UK COVID-19 Dashboard Built Using Postgres And Citus For Millions Of Users

- Claire Giordano tl;dr: "In this post you’ll learn about the database challenges the team faced as the dashboard needed to scale—with an eye toward how the UKHSA team uses Azure, the Azure Database for PostgreSQL managed service, and the Citus extension which transforms Postgres into a distributed database."

featured in #287

Tricking PostgreSQL Into Using An Insane – But 200x Faster – Query Plan

- Jacob Martin tl;dr: "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 way faster, without adding any additional indices." Jacob shows us that query plans aren’t that hard to interpret with the right tools and how to rewrite queries to "achieve substantial performance boosts without unnecessary indices or denormalizing the data model."

featured in #284

Lesser Known PostgreSQL Features

- Haki Benita tl;dr: 20 features including: (1) Get the number of updated and inserted rows in an Upsert. (2) Grant permissions on specific columns. (3) Match against multiple patterns, and more.

featured in #268