Performance bottlenecks at the database layer will hit at some point and there are quicker ways to analyze what is the bottleneck and how to make fixes.
Explain and Analyze
Postgres has great tooling to understand how slow a query runs. You can execute a simple SQL statement using EXPLAIN to ask the database why a particular query is taking so long. For example, in our
users model, we can make the following query in the SQL tab of the Data page of the console:
Postgres Explain command displays the execution plan generated for the supplied SQL statement.
EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS)SELECT *FROM usersWHERE age > 70;
and when you execute the above command, you should see the following (similar) output.
QUERY PLAN---------------------------------------------------------Seq Scan on users (cost=0.00..155.00 rows=10000 width=4)(1 row)
The official PostgreSQL documentation contains a guide to using EXPLAIN. For more details, this EXPLAIN glossary from pgMustard includes descriptions of each operation type and their fields.
Postgres indexes are a way of increasing performance on a column that is queried selectively and frequently. The concept is similar to the one of an index in a book. It helps access the data you’re looking for quicker, by maintaining additional metadata.
Let’s say the database receives a large number of requests for selecting users being queried by their age filter, for example:
SELECT * FROM users WHERE age > 70;
After running a
EXPLAIN on top of this, we should see a sequential scan.
CREATE INDEX users_age_index ON users (age);
The database can now use the index to look up the results more efficiently, allowing the performance of the queries to improve significantly.
- Build apps and APIs 10x faster
- Built-in authorization and caching
- 8x more performant than hand-rolled APIs