Tue 25 Apr 2023

Nine ways to shoot yourself in the foot with PostgreSQL

The common thread linking most of these gotchas is scalability. They're things that won't affect you while your database is small. But if one day you want your database not to be small, it pays to think about them in advance. Otherwise they'll came back and bite you later, potentially when it's least convenient. Plus in many cases it's less work to do the right thing from the start, than it is to change a working system to do the right thing later on.

Source: Nine ways to shoot yourself in the foot with PostgreSQL, an article by Phil Booth.

PostgreSQL Indexes Can Hurt You

The summary in simple words: Indexes are not cheap. There is a cost, and the cost can be manifold. Indexes are not always good, and sequential scans are not always bad, either. My humble advice is to avoid looking for improving individual queries as the first step because it is a slippery slope. A top-down approach to tuning the system yields better results starting from tuning the Host machine, Operating System, PostgreSQL parameter, Schema, etc. An objective “cost-benefit analysis” is important before creating an index.

Source: PostgreSQL Indexes Can Hurt You: Negative Effects and the Costs Involved, an article by Jobin Augustine.