Speed up PostgreSQL® pgvector queries with indexes
Learn the theory and the details of how to speed up PostgreSQL® pgvector queries using indexes IVFFlat, HNSW and traditional indexes
Tag: postgresql
SQL Optimization: a comprehensive developer’s guide
A SQL optimization guide for developers. With best practices, warnings, pro tips to speed up your SQL optimization.
How to speed up COUNT(DISTINCT)
While counting is common in every application, sometimes it is not enough: when we need to display the amount of unique users generating traffic in our website or provide the number of disparate items included in an order we need to apply a distinct count. Compared to a classical count, the distinct one is usually… Continue reading How to speed up COUNT(DISTINCT)
5 Ways to implement NOT EXISTS in PostgreSQL
When building an application, usually we query our database to retrieve the things we already know: what orders a client made or, more generally, what steps in the app flow have already been taken. However, to correctly address the user journey, it is often more important to query the database and retrieve data that informs… Continue reading 5 Ways to implement NOT EXISTS in PostgreSQL
Postgresql SQL optimization [2023 edition]
How I fixed my Slow PostgreSQL and made it faster: In this post I will share our experience in making PostgreSQL faster and will provide actionable tips you can use to make your PostgreSQL much faster, while reducing cost.We will focus on PostgreSQL 15 but the article is good for earlier versions of PG. PostgreSQL… Continue reading Postgresql SQL optimization [2023 edition]
My Oracle to PostgreSQL Migration: The 7 Tools That Made It Possible
I have a confession to make: I'm a huge fan of Oracle. I absolutely adore the company and its technology. Admittedly, I'm not fond of their pricing practices, but overall, they offer excellent products. As an Oracle DBA for a major energy infrastructure company in California, I've spent years mastering Oracle databases. However, a recent… Continue reading My Oracle to PostgreSQL Migration: The 7 Tools That Made It Possible
Choosing the best indexes for PostgreSQL query optimization
PostgreSQL is extremely popular these days, and we decided to share a few tips on choosing the best indexes for your PostgreSQL implementation. In this article we will explain how to do it manually, but if you prefer to find missing indexes, automatically you can use tools like EverSQL. let's start. How to find existing… Continue reading Choosing the best indexes for PostgreSQL query optimization
The complete PostgreSQL Index Advisor guide [Online]
What is an Index? A database index is a data structure that speeds up data retrieval operations from a table while requiring additional writes and storage space to maintain. Using an index, you can quickly find data in a database table without having to search every row every time. Indexes can be built from one… Continue reading The complete PostgreSQL Index Advisor guide [Online]
How to Fix PostgreSQL High CPU Usage?
We all love PostgreSQL, but sometimes, usually around 3 AM, it can reach a 100% CPU usage, which can be annoying for us, and a disaster for our customers. So how can you find the root cause and fix it? 1. Locate the root cause There might be many reasons for the 100% CPU Usage.… Continue reading How to Fix PostgreSQL High CPU Usage?
PostgreSQL – pg_stat_statements, explained
pg_stat_statements is a external library and a name of a table that collects tons of information about your database's executed queries. Like many database performance components, it has pros and cons. here are some frequently asked questions: What is pg_stat_statements ? pg_stat_statements is a preload shared library that you can use in order to load… Continue reading PostgreSQL – pg_stat_statements, explained