PostgreSQL

PostgreSQL is the RDMS (database system) that we use at Prospect.io.

Basic information

Like any SQL database engine, PostgresSQL is a software that allows executing SQL commands.

To install it, just get the package using brew:

$ brew install postgres

Then, we can start executing SQL commands with the psql command-line interface, or, by connecting it to our favorite backend system.

Usage

In order to use well the database, please follow the rules below:

Contact it as fewer as possible

Try to group your queries.

# Instead of doing:
SELECT * FROM table WHERE id = 1
SELECT * FROM table WHERE id = 1
# Do
SELECT * FROM table WHERE id IN (1, 2)

Make it return only what it needs

Unnecessary data can cause the database to transmit a lot of data over the network and so, answer slowly. So, prefer doing: SELECT id FROM ... , than SELECT * FROM ...

Monitor it frequently Like any complex engine, it can sometimes be slow or work a lot in unexpected situations. So, that's important to check periodically that the DB is going well using the monitoring tips given below. Check that all tables have a reasonable size, that indexes are used, that queries are fast, ...

Monitoring

Overall diagnosis on Heroku

If you are using Postgres on Heroku, you can just run the command below to have some basic insights:

$ heroku pg:diagnose -a app-name

To clean the data cache, just run:

$ heroku pg:stats_reset -a app-name

Table & index sizes

To get the size of all tables

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 25;

To get the size of all indexes:

SELECT s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
s.idx_scan as usage
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE 0 <>ALL (i.indkey) -- no index column is an expression
AND NOT i.indisunique -- is not a UNIQUE index
AND NOT EXISTS -- does not enforce a constraint
(SELECT 1 FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
AND s.relname = 'table_name'
ORDER BY pg_relation_size(s.indexrelid) DESC;

Index usage

-- Index hit rate
WITH idx_hit_rate as (
SELECT
relname as table_name,
n_live_tup,
round(100.0 * idx_scan / (seq_scan + idx_scan),2) as idx_hit_rate
FROM pg_stat_user_tables WHERE (seq_scan + idx_scan) > 0
ORDER BY n_live_tup DESC
),
-- Cache hit rate
cache_hit_rate as (
SELECT
relname as table_name,
heap_blks_read + heap_blks_hit as reads,
round(100.0 * sum (heap_blks_read + heap_blks_hit) over (ORDER BY heap_blks_read + heap_blks_hit DESC) / sum(heap_blks_read + heap_blks_hit) over (),4) as cumulative_pct_reads,
round(100.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read),2) as cache_hit_rate
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0
ORDER BY 2 DESC
)
SELECT
idx_hit_rate.table_name,
idx_hit_rate.n_live_tup as size,
cache_hit_rate.reads,
cache_hit_rate.cumulative_pct_reads,
idx_hit_rate.idx_hit_rate,
cache_hit_rate.cache_hit_rate
FROM idx_hit_rate, cache_hit_rate
WHERE idx_hit_rate.table_name = cache_hit_rate.table_name
AND cumulative_pct_reads < 100.0
ORDER BY reads DESC;

Slow queries

Run the snippet below to find all slow queries:

select query,calls,total_time,min_time,max_time,mean_time,stddev_time,rows
from pg_stat_statements order by mean_time desc;

The data used to find them can be reset using:

SELECT pg_stat_statements_reset()

Table usage

To find the usage of a single table (not studying a particular slow query), run:

SELECT * FROM pg_stat_user_tables WHERE relname = 'table_name' ORDER By relname;

Optimize a single query

When you have found a slow query, run the EXPLAIN command (see doc) to find the origin of the problem.

EXPLAIN query # Explain the query plan of the query
EXPLAIN ANALYZE query # Explain the query plan and run the query
EXPLAIN (analyze,buffers,costs off) query # More detailled explain call

You'll obtain a detailed query plan ( explanation of what does the DB do to run a particular query). For more information about how to read it, check this article.

Common issue: missing index

Very often, problems of performance in the database can be explained by the lack of an index. In that case, the query plan will look like:

QUERY PLAN
Limit (cost=0.09..240.66 rows=2 width=501)
-> Index Scan Backward using table_pkey on table (cost=0.09..209539.43 rows=1742 width=501)
Filter: (foreign_key_id = 2)

It states that the DB is doing:

  1. An index scan on the primary key of the table

  2. Then, a filtering on the values returned by the index

This kind of plan is very slow, because it has to browse a whole index to find a particular record.

Adding an index will change it to:

Limit (cost=0.09..4.09 rows=1 width=501)
-> Index Scan using table_pkey on table (cost=0.09..4.09 rows=1 width=501)
Index Cond: (id = 2)

Here, it's much faster because instead of filtering the index, it's putting a condition (i.e. using the index), thus, increasing the DB performance.

Troubleshooting performance

Sometimes, you can see that some requests may experience timeouts, or slowness. This can be discovered by seeing logs that indicate "ERROR: canceling statement due to statement timeout", or, because some features appear to be slower than expected.

There can be many causes for that. But, generally, you can use the procedure below to find what is going wrong.

Step 1: Find the slow query

First step of the investigation is to find the query that is running slow.

To do that, search in the logs for the following statement:ERROR: canceling statement due to statement timeout.

Around that, you will find the exact query causing the problem.

It can be something like:

SELECT COUNT(*) FROM mytable WHERE columnname = ?

Step 2: Reproduce

Once you've got the problematic query, try reproducing the issue, replacing the placeholder values with values that are issuing the same slow query.

You should do that, if possible, on the same environment on which was running the original query, since the amount of data which caused the problem can be part of the answer.

Note that for some queries, running it once can make subsequent calls faster, thanks to the cache. So you must find the right set of parameters to reproduce the problem.

Step 3: Analyze

Then, you need to analyze the query.

Generally, you can solve the problem using the section Optimize a single query in the above Monitoring section, using a simple ANALYZE command. If the problem was due to a missing index, you can finish here.

Step 4: Analyze temporal correlations

If the previous step did not work, you'll have to look after the root cause elsewhere.

Try making a time correlation analysis, by having a look at the general state of the database, other bugs, etc… to see if the problem is not due to the timeframe of the request.

An example can be that the DB is particularly overloaded at a particular moment in time.

This can give us logs like the following:

Illustration of a temporal issue

Here, if we have timeouts between 00:00 and 01:00 AM, it can be because the database was generally overloaded.

Step 5: Fix overall performance degradation

If the root cause is due to a temporary database overload, try the following if this applies:

  • Look for other slow queries, which can impact the overall performance of the DB and fix them (don't focus on the initial issue)

  • Look at all other monitoring tools, such as overall cache/index hit rate, and try to improve it if it was too low, by either : fixing other issues, looking for poor index, table usage for other queries, running vacuum or rebuilding indexes, and, as a last resort, tune Postgres settings (like cache buffers), or upgrade the DB.