Improving Performance of PostgreSQL queries

I recently wanted to implement some raw SQL queries and work on the efficiency of them so that returning data from database will be faster. After reading few articles and implementing some of the changes myself I decided to summarize them here so that you could use it in your projects :)

Now, let's talk about PostgreSQL. It's a super cool and flexible open-source relational database management system. While PostgreSQL is already pretty awesome at what it does, there's always room to make those queries zip along even faster! Want to know how? We've got some nifty strategies to supercharge your PostgreSQL queries.

Enjoy!

Limit the number of rows returned by a query

In case of small tables, we may not need to take care about the number of returned rows (while I highy suggest to limit them anyway!) in case of bigger tables, this can be extremely useful for improving performance of queries.

We can achieve that in SQL by using the keyword LIMIT with following query:

SELECT * FROM products LIMIT 10

Use JOIN instead of subqueries

If there are many records in the table, running a query with subqueries inside may not be performant - instead we could use the JOIN keyword which can be significantly faster.

Instead of using subqueries like following:

SELECT * FROM orders WHERE product_id IN (SELECT id FROM products WHERE name = 'Phone');

We could use JOIN keyword like this:

SELECT orders.* FROM orders JOIN products ON orders.product_id = products.id WHERE products.name = 'Phone';

This can be much faster if your table has big amount of rows.

Use efficient data types for columns

For rows where there are frequent calculations like average it can be beneficial to check what is the datatype for the column. For example, it will be more efficient to use smallint rather than integer. You can change it like following:

ALTER TABLE products ALTER COLUMN price TYPE smallint;

Use PREPARE for frequently executed queries

If you know that some queries will be executed frequently, for example a Fetch_Popular_Products_Query, you can use a keyword PREPARE like following:

PREPARE get_products_by_name (text) AS SELECT * FROM products WHERE name = $1;
EXECUTE get_products_by_name('Phone');

By using this approach, the query will be prepared once and the execution will be reused to improve performance.

Using INDEX for frequent queries

If you frequently search for products by their name, you can create an index of the name column like following:

CREATE INDEX products_name_idx ON products (name);

It will be especially effective if you would use multiple WHERE or JOIN keywords in your regular query.

Not use wildcard characters

Let's say that we have a query that will search for all products to find those whose name ends with phone:

SELECT * FROM products WHERE name LIKE '%phone';

This query will perform a sequential scan of the entire products table, which can be slow if the table is large. To optimize this query, you can rewrite it like this:

SELECT * FROM products WHERE name LIKE 'phone%';

This query will use the index created in previous point and perform a much faster index scan.

Debugging queries with EXPLAIN keyword

The EXPLAIN command will output the execution plan for the query, which can help you identify any performance issues and you can use it like following:

EXPLAIN SELECT COUNT(*) FROM products JOIN orders ON products.id = orders.products_id;

Use full text search service for faster searching by query/ID

Searching through milions of records to find a specific record by a name, or more difficult case - part of name, can be really slow. To solve this problem, you could implement a full test search service like ElasticSearch that can be much faster.

How this would work is that you will store key/value pairs inside your ElasticSearch database in a form of POSTGRESQL_ID/RECORD_NAME, then you will search through the values to find a full or part of the name and assigned PostgreSQL ID, and finally you will perform a PostgreSQL query WHERE id = 'POSTGRESQL_ID' instead of WHERE name LIKE 'RECORD_NAME' which can be significantly more performant as you will be querying for specific record rather than records that match certain search criteria.

This approach looks amazing for performance and optimizing queries, but you have to take into consideration that it will add additional layer of complexity and another service to your infrastructure that you would need to maintain.

📖 Learn more

If you would like to learn more about Vue, Nuxt, JavaScript or other useful technologies, checkout VueSchool by clicking this link or by clicking the image below:

It covers most important concepts while building modern Vue or Nuxt applications that can help you in your daily work or side projects 😉

✅ Summary

Well done! You have just learned how to check if a slot is empty in Vue.

Take care and see you next time!

And happy coding as always 🖥️