CLUSTER: IMPROVING POSTGRESQL PERFORMANCE

-

 When designing a database application, the layout of data in disk is often neglected. However, the way data is stored by PostgreSQL can have a major performance impact. Therefore it makes sense to take a look at what can be done to improve speed and throughput. In this post you will learn one of the most important tricks.

PostgreSQL: To sort or not to sort

To demonstrate the importance of the on-disk layout I have created a simple test set:

1
2
3
4
5
6
7
test=# CREATE TABLE t_test AS SELECT *
        FROM generate_series(1, 10000000);
SELECT 10000000
test=# CREATE TABLE t_random AS SELECT *
        FROM t_test
        ORDER BY random();
SELECT 10000000

Note that both data sets are absolutely identical. I have loaded 10 million rows into a simple table. However, in the first case data has been sorted, then inserted. generate_series returns data in ascending order and because the table is new data will be written to disk in that order.
In the second case I decided to shuffle the data before insertion. We are still talking about the same data set. However, it is not in the same order:

1
2
3
4
5
6
7
test=# \d+
                    List of relations
 Schema |   Name   | Type  | Owner |  Size  | Description
--------+----------+-------+-------+--------+-------------
 public | t_random | table | hs    | 346 MB |
 public | t_test   | table | hs    | 346 MB |
(2 rows)

In both cases the size on disk is the same. There are no changes in terms of space consumption which can be an important factor as well.

Creating an index in PostgreSQL

Let us create an index on both tables:

1
2
3
4
5
6
7
8
test=# \timing
Timing is on.
test=# CREATE INDEX idx_test ON t_test (generate_series);
CREATE INDEX
Time: 3699.416 ms (00:03.699)
test=# CREATE INDEX idx_random ON t_random (generate_series);
CREATE INDEX
Time: 5084.823 ms (00:05.085)

Even creating the index is already faster on sorted data for various reasons. However, creating initial indexes does not happen too often, so you should not worry too much.

In the next step we can already create optimizer statistics and make sure that all hint bits are set to ensure a fair performance comparison:

1
2
test=# VACUUM ANALYZE;
VACUUM

Reading blocks of database

Now that all the test data sets are in place we can run a simple test: Let us fetch 49000 rows from the sorted data set first:

1
2
3
4
5
6
7
8
9
10
11
12
test=# explain (analyze, buffers) SELECT *
    FROM    t_test
    WHERE   generate_series BETWEEN 1000 AND 50000;
                                                           QUERY PLAN                                                           
---------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_test on t_test  (cost=0.43..1362.62 rows=43909 width=4) (actual time=0.017..7.810 rows=49001 loops=1)
   Index Cond: ((generate_series >= 1000) AND (generate_series <= 50000))
   Heap Fetches: 0
   Buffers: shared hit=138
 Planning Time: 0.149 ms
 Execution Time: 11.785 ms
(6 rows)

Not bad. We need 11.785 milliseconds to read the data. What is most important to consider here is that the number of 8k blocks needed is 138, which is not much. “shared hit” means that all the data has come from memory.

Let me run the same test again:

1
2
3
4
5
6
7
8
9
10
11
12
test=# explain (analyze, buffers) SELECT *
    FROM    t_random
    WHERE   generate_series BETWEEN 1000 AND 50000;
                                                             QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_random on t_random  (cost=0.43..1665.17 rows=53637 width=4) (actual time=0.013..9.892 rows=49001 loops=1)
   Index Cond: ((generate_series >= 1000) AND (generate_series <= 50000))
   Heap Fetches: 0
   Buffers: shared hit=18799
 Planning Time: 0.102 ms
 Execution Time: 13.386 ms
(6 rows)

In this case the query took a bit longer: 13.4 ms. However, let us talk about the most important number here: The number of blocks needed to return this result. 18799 blocks. Wow. That is roughly 150 times more.

One could argue that the query is not really that much slower. This is true. However, in my example all data is coming from memory. Let us assume for a moment that data has to be read from disk because for some reason we get no cache hits. The situation would change dramatically. Let us assume that reading one block from disk takes 0.1 ms:

138 * 0.1 + 11.7 = 25.5 ms
vs.
18799 * 0.1 + 13.4 = 1893.3 ms

That is a major difference. This is why the number of blocks does make a difference – even if it might not appear to be the case at first glance. The lower your cache hit rates are, the bigger the problem will become.

There is one more aspect to consider in this example: Note that if you want to read a handful of rows only the on-disk layout does not make too much of a difference. However, if the subset of data contains thousands of rows, the way is ordered on disk does have an impact on performance.

CLUSTER: PostgreSQL comes to the rescue

The CLUSTER command has been introduced many years ago to address exactly the issues I have just outlined. It allows you to organize data according to an index. Here is the syntax:

1
2
3
4
5
6
test=# \h CLUSTER
Command:     CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER [VERBOSE] table_name [ USING index_name ]
CLUSTER [VERBOSE]

URL: https://www.postgresql.org/docs/12/sql-cluster.html

Utilizing the CLUSTER command is easy. The following code snipped will show how you can do that:

1
2
test=# CLUSTER t_random USING idx_random;
CLUSTER

To see what happens I have executed the same query as before again. However, there is something important to be seen:

1
2
3
4
5
6
7
8
9
10
11
12
test=# explain (analyze, buffers)
    SELECT *    FROM t_random
    WHERE   generate_series BETWEEN 1000 AND 50000;
                                                          QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_random  (cost=1142.21..48491.32 rows=53637 width=4) (actual time=3.328..9.564 rows=49001 loops=1)
   Recheck Cond: ((generate_series >= 1000) AND (generate_series <= 50000)) Heap Blocks: exact=218 Buffers: shared hit=2 read=353 ->  Bitmap Index Scan on idx_random  (cost=0.00..1128.80 rows=53637 width=0) (actual time=3.284..3.284 rows=49001 loops=1)
         Index Cond: ((generate_series >= 1000) AND (generate_series <= 50000))
         Buffers: shared hit=2 read=135
 Planning Time: 1.024 ms
 Execution Time: 13.077 ms
(9 rows)

PostgreSQL has changed the execution plan. This happens due to wrong statistics. Therefore it is important to run ANALYZE to make sure that the optimizer has up-to date information:

1
2
test=# ANALYZE;
ANALYZE

Once the new optimizer statistics is in place the execution plan will be as expected again:

1
2
3
4
5
6
7
8
9
10
11
12
test=# explain (analyze, buffers) SELECT *
    FROM    t_random
    WHERE   generate_series BETWEEN 1000 AND 50000;
                                                              QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_random on t_random  (cost=0.43..1807.12 rows=50884 width=4) (actual time=0.012..11.737 rows=49001 loops=1)
   Index Cond: ((generate_series >= 1000) AND (generate_series <= 50000))
   Heap Fetches: 49001
   Buffers: shared hit=355
 Planning Time: 0.220 ms
 Execution Time: 15.267 ms
(6 rows)

Maintaining order

If you have decided to cluster a table it does NOT mean that order on disk is maintained forever. If you run UPDATES etc. frequently the table might gradually loose order again. Therefore, CLUSTER is especially useful if your data is rather static. It can also make sense to order data as you import it to ensure physical order.


Comments

Post a Comment

Popular posts from this blog

PostgreSQL - Architecture

-
Image
When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The other processes that the postgres process starts are responsible for the ongoing work of the database.  PostgreSQL`s architecture is based on a client/server communication. In the server side there is a server process that manages the files, database operations, connection and so on.. On the client side there is a tool/program which can be a graphical tool (gui)  like pgAdmin or a command line tool(cli) like psql  that is repnsible for connection the database and allowing the user
Read more

PostgreSQL - Backup & Point In Time Recovery

-
Image
  In this post you will learn about how to restore a PostgreSQL database in Point in time. Open source databases are taking over other conventional Database softwares at very high speed. For me personally, I really like the way PostgreSQL is designed and it’s simple architecture. I have tried to write it down every possible steps which will help you to restore your Postgres database. Below Steps will be same, but few commands may vary with any other backup tool. PITR PostgreSQL Allow you to restore database to a specific moment in time PITR. It make use of live database files and WAL files. This method can only backup and restore the whole cluster, for individual database use pg_dump. It can be done when the DB is online. Step 1. If archive logs not enable then enable it, otherwise go to Step3. Create Directory for archive logs mkdir /postgres/postgres12.2/wal_archives Make changes in postgresql.conf Postgresql.conf is present under your data directory, in my case it is in /postgres/po
Read more

Installing pgbadger on Linux for PostgreSQL log analysis

-
As a DBA, we often encounter scenarios when you need to track PostgreSQL database performance by analyzing workloads and identify bottlenecks if any. There comes  pgbadger  – an open source log analyzer for PostgreSQL. It parse the PostgreSQL log files and gives you a nice overview of the PostgreSQL cluster including various query metrics like number of query executions, their types and duration, session details, Locks statistics, warnings, errors and other event patterns etc. All of these details will be available in a nice HTML format on your web browser. Installation: Prerequisite: The  pgbadger  is written in pure  Perl  and uses a JavaScript library ( flotr2 ) to draw graphs. Hence, you need to ensure that a modern Perl distribution is available in your system. Charts are rendered using a JavaScript library and Your web browser will do all the work. Nothing additional is required here. Use the below command to install  perl  if it is not already installed in your system. $ yum ins
Read more