Lessons about PostgreSQL

The links, tools and tips our engineers and designers share with each other, one “today I learned” at a time.

lessons shared: 83
people sharing: 13
database started in: 2017

postgresql.org
PostgreSQL has a built-in case insensitive text type
F.9. citext — a case-insensitive character string type # F.9.1. Rationale F.9.2. How to Use It F.9.3. String Comparison Behavior F.9.4. …
Vitor RangelJune 22, 2023
postgresql.org
PostgreSQL has a built-in module for removing accents from text
F.48. unaccent — a text search dictionary which removes diacritics # F.48.1. Configuration F.48.2. Usage F.48.3. Functions unaccent is a text …
Vitor RangelJune 22, 2023
pganalyze.com
PostgreSQL Partitioning in Django:
We walk through the different types of partitions available in Postgres and how to use the django-postgres-extra package to make partitioning happen in Django.
Rebeca SaraiMay 12, 2023
flexport.engineering
Index Selectivity: A Key to High Performance SQL
Flavio JuvenalMay 8, 2023
crunchydata.com
Your Guide to Connection Management in Postgres
Connection pooling and management is one of those things most people ignore far too long when it comes to their database. As you grow into the hundreds, better connection management is a quick and easy win. Let's dig into the three variations of connection pooling and how to identify if you can benefit from a connection pooler and where.
Filipe XimenesJanuary 10, 2023
dba.stackexchange.com
In Postgres, "empty updates" have the same cost per row as any other update. Even if the values won't change after the UPDATE, the operation will still have the same performance:
Flavio JuvenalDecember 5, 2022
stackoverflow.com
Django needs a GinIndex with OpClass + Upper to support fast queries with the icontains operator:
Flavio JuvenalOctober 28, 2022
cybertec-postgresql.com
In Postgres, COUNT(*) vs. COUNT(id) is a non-issue:
Flavio JuvenalAugust 24, 2022
stackoverflow.com
How to estimate the size of one column in a Postgres table?
Flavio JuvenalJune 23, 2022
postgres.ai
Common DB schema change mistakes:
Common DB schema change mistakes and how to avoid them in PostgreSQL development.
Rebeca SaraiJune 7, 2022
github.com
Thin clones of PostgreSQL to build powerful development, test, and QA envs. Cloning a 1 TiB PostgreSQL database takes ~10 seconds:
DBLab enables 🖖 database branching and ⚡️ thin cloning for any Postgres database and empowers DB testing in CI/CD. This optimizes database-related costs while improving time-to-market and software ...
Rebeca SaraiJune 7, 2022
timescale.com
How percentile approximation works (and why it's more useful than averages)
What percentile approximations are, how percentiles work in PostgreSQL, and calculating them efficiently on time-series data.
Flavio JuvenalMay 25, 2022
beekeeperstudio.io
Beekeeper Studio - Open Source SQL Editor and Database Manager
Modern, fast, and easy to use SQL GUI. Loads of supported databases & full feature parity across all OSes.
Fellipe SoutoApril 5, 2022
supabase.com
Postgres Auditing in 150 lines of SQL:
PostgreSQL has a robust set of features which we can leverage to create a generic auditing solution in 150 lines of SQL.
Rebeca SaraiMarch 14, 2022
andrewbrookins.com
Using a Replica Router in Django to read your own writes - Scaling Django with Postgres Read Replicas:
Replication is a feature of PostgreSQL that you typically use to achieve high availability by running copies of a database that are ready to take over if you...
Flavio JuvenalMarch 11, 2022

More lessons

Book a call

Found it useful?
Let’s put it to work.

Tell us about your product and the problem in front of you. We’ll bring what we’ve learned on real projects.

Tell us about your product, and we’ll bring what we’ve learned.

Still prefer the good old email? Contact us

Book a call

Lessons about PostgreSQL

PostgreSQL has a built-in case insensitive text type

PostgreSQL has a built-in case insensitive text type

PostgreSQL has a built-in module for removing accents from text

PostgreSQL has a built-in module for removing accents from text

PostgreSQL Partitioning in Django:

PostgreSQL Partitioning in Django:

Index Selectivity: A Key to High Performance SQL

Index Selectivity: A Key to High Performance SQL

Your Guide to Connection Management in Postgres

Your Guide to Connection Management in Postgres

In Postgres, "empty updates" have the same cost per row as any other update. Even if the values won't change after the `UPDATE`, the operation will still have the same performance:

In Postgres, "empty updates" have the same cost per row as any other update. Even if the values won't change after the `UPDATE`, the operation will still have the same performance:

Django needs a `GinIndex` with `OpClass` + `Upper` to support fast queries with the `icontains` operator:

Django needs a `GinIndex` with `OpClass` + `Upper` to support fast queries with the `icontains` operator:

In Postgres, `COUNT(*)` vs. `COUNT(id)` is a non-issue:

In Postgres, `COUNT(*)` vs. `COUNT(id)` is a non-issue:

How to estimate the size of one column in a Postgres table?

How to estimate the size of one column in a Postgres table?

Common DB schema change mistakes:

Common DB schema change mistakes:

Thin clones of PostgreSQL to build powerful development, test, and QA envs. Cloning a 1 TiB PostgreSQL database takes ~10 seconds:

Thin clones of PostgreSQL to build powerful development, test, and QA envs. Cloning a 1 TiB PostgreSQL database takes ~10 seconds:

How percentile approximation works (and why it's more useful than averages)

How percentile approximation works (and why it's more useful than averages)

Beekeeper Studio - Open Source SQL Editor and Database Manager

Beekeeper Studio - Open Source SQL Editor and Database Manager

Postgres Auditing in 150 lines of SQL:

Postgres Auditing in 150 lines of SQL:

Using a Replica Router in Django to read your own writes - Scaling Django with Postgres Read Replicas:

Using a Replica Router in Django to read your own writes - Scaling Django with Postgres Read Replicas:

Found it useful?
Let’s put it to work.

Lessons about PostgreSQL

PostgreSQL has a built-in case insensitive text type

PostgreSQL has a built-in module for removing accents from text

PostgreSQL Partitioning in Django:

Index Selectivity: A Key to High Performance SQL

Your Guide to Connection Management in Postgres

In Postgres, "empty updates" have the same cost per row as any other update. Even if the values won't change after the UPDATE, the operation will still have the same performance:

Django needs a GinIndex with OpClass + Upper to support fast queries with the icontains operator:

In Postgres, COUNT(*) vs. COUNT(id) is a non-issue:

How to estimate the size of one column in a Postgres table?

Common DB schema change mistakes:

Thin clones of PostgreSQL to build powerful development, test, and QA envs. Cloning a 1 TiB PostgreSQL database takes ~10 seconds:

How percentile approximation works (and why it's more useful than averages)

Beekeeper Studio - Open Source SQL Editor and Database Manager

Postgres Auditing in 150 lines of SQL:

Using a Replica Router in Django to read your own writes - Scaling Django with Postgres Read Replicas:

Found it useful? Let’s put it to work.

In Postgres, "empty updates" have the same cost per row as any other update. Even if the values won't change after the `UPDATE`, the operation will still have the same performance:

Django needs a `GinIndex` with `OpClass` + `Upper` to support fast queries with the `icontains` operator:

In Postgres, `COUNT(*)` vs. `COUNT(id)` is a non-issue:

Found it useful?
Let’s put it to work.