If you've ever wondered - What is a tuple? What is a Postgres page? What's the difference between a record and a row? What is a relation? What's an array? What is TOAST? This post is for you! These are terms you hear all the time in the Postgres world. This…

Operations in online databases are always a tend to be a little tricky to carry out. Renaming a table safely in Postgres isn’t too hard, but there is some technique to it.

Enhance your PostgreSQL database performance with EDB's expert tuning tips. Optimize queries, autovacuum, and more for a faster, more efficient database.

As PostgreSQL table sizes grow into millions of rows, more customized settings are required to ensure optimal performance. Read more about Lob's approach.

A series of articles on how to solve common JSON problems with PostgreSQL®

I’m excited to announce that the translation of the “ PostgreSQL 14 Internals ” book is finally complete thanks to the amazing work of Liudmila Mantrova.
The final part of the book considers each of the index types in great detail. It explains and demonstrates…

Indexes in PostgreSQL come with a cost in terms of performance and resource consumption. Here are 10 different ways PostgreSQL indexes can hurt performance.

PostgreSQL monitoring with Prometheus is an easy thing to do thanks to the PostgreSQL...

Non-trivial tips for database development

Here’s a list of reasons why SELECT * is bad for SQL performance, assuming that your application doesn’t actually need all the columns. When I write production code, I explicitly specify the columns of interest in the select-list (projection), not only for…

I want to work on optimizing all my queries all day long because it will definitely be worth the time and effort. That's a statement that has hopefully never been said. So when it comes to query optimizing, how should you pick your battles?

The last two posts introduced TOASTing strategies and how TOAST tables look like. Have a look at those posts if you are not yet familiar with TOAST in PostgreSQL. In this post we’ll look a bit under the hood to understand how TOASTing really works. We’ll…

As much as Andy loves PostgreSQL, there is one part that is terrible and causes many headaches for people. Learn what it is and why it sucks.

1000 Fetch errors a second, 500 sessions waiting on LockManager, 150,000 locksIn above graphs, our production system hit the wall by a blind siding pile up on LockManager waits causing the application to start hitting 1000 errors a second. I managed to mitigate…

How outliers can trick the optimizer into the wrong plan

Chapter 66. Transaction Processing Table of Contents 66.1. Transactions and Identifiers 66.2. Transactions and Locking 66.3. Subtransactions 66.4. Two-Phase Transactions This chapter …

The BUFFERS option to Postgres' EXPLAIN command can help you work out where queries are using lots of IO. learn how to use this to optimize your SQL.