I Really Wish PostgreSQL Had Index Hints.
I’ve been working with Microsoft SQL Server since the late 1990s, and about a decade ago, I started working with Postgres. I just recently started teaching Postgres index tuning, and…
How to Name Your Indexes in Postgres
When you create indexes on a table, here are a few simple guidelines to make your life easier down the road. 1. Start the index name with the table name,…
How to Use check_indexes to Learn About Your Postgres Database’s Indexes
When I’m reviewing a PostgreSQL’s database structure, I often wanna look at one or more tables to understand their current index designs. The check_indexes function in our open source SmartPostgres…
How to Use drop_indexes To, Uh, Drop Your Indexes
The open source drop_indexes function in our open source Postgres Bag of Tricks (Github) lets you pass in a schema and table, like this: PgSQL select * from drop_indexes('public', 'users');…
Announcing Fundamentals of Python: The Course for Data Professionals
You work with databases every day. SQL is second nature to you—you can write queries, build indexes, and optimize performance. But the data world is changing, and you’re starting to…
Smart Postgres News and Techniques 2025 Vol 5
If you need to speed up text searches, and you’re willing to make database schema changes and query changes, then Alex Klibisz explains how to use trigrams. If you’re considering…
Smart Postgres News and Techniques 2025 Vol 4
If you use Roman numerals like MMXXV, you’ll be thrilled to know that Postgres 18 is adding more support for it as Hubert Lubaczewski explains. Also, I have questions about…
Smart Postgres News and Techniques 2025 Vol 3
If you want faster reports, Karen Jex gives a good high-level overview of Postgres settings to review. This isn’t about giving you exact setting values, but more about a list…
Smart Postgres News and Techniques 2025 Vol 2
If you want advice on common performance scale issues, Shayon Mukherjee’s list of problems & solutions is beautifully concise. Seriously, it’s good and straight to the point. If you need…
How to Read the Buffers Numbers in Explain Plans
You’re tuning a Postgres query and you’ve been told to run EXPLAIN with the BUFFERS option, like this: PgSQL EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) 1 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)…