Avoiding disk spills due to PostgreSQL's logical replication

Logical replication is a versatile feature offered in PostgreSQL. I have discussed the the theoretical background of this feature in detail in my POSETTE talk. At the end of the talk, I emphasize the need for monitoring logical replication setup. If you are using logical replication and have setup monitoring you will be familiar with pg_stat_replication_slots. In some cases this view shows high amount of spill_txns, spill_count and spill_bytes, which indicates that the WAL sender corresponding to that replication slot is using high amount of disk space. This increases load on the IO subsystem affecting the performance. It also means that there is less disk available for user data and regular transactions to operate. This is an indication that logical_decoding_work_mem has been configured too low. That's the subject of this blog: how to decide the right configuration value for logical_decoding_work_mem. Let's first discuss the purpose of this GUC. Blog might serve as a good background before reading further.

Reorder buffer and logical_decoding_work_mem

When decoding WAL, a logical WAL sender accumulates the transaction in an in-memory data structure called reorder buffer. For every transaction that WAL sender encounters, it maintains a queue of changes in that transaction. As it reads each WAL records, it finds the transaction ID which it belongs to and adds it to the corresponding queue of changes. As soon as it sees a COMMIT record of a transaction, it decodes all the changes in the corresponding queue and sends downstream. If the reorder buffer fills up by transactions whose COMMIT record is yet to be seen, it spills the queue to the disk. We see such disk spills accounted in spill_txns, spill_count and spill_bytes. The amount of memory allocated to reorder buffer is decided by logical_decoding_work_mem GUC. If GUC value is lower, it will cause high disk spills and if the value is higher it will waste memory. Every WAL sender in the server will allocate logical_decoding_work_mem amount of memory, thus the total memory consumed for maintaining reorder buffer is {number of WAL senders} * logical_decoding_work_mem which can go upto max_wal_senders * logical_decoding_work_mem.

Setting logical_decoding_work_mem optimally

It's clear that reorder buffer should be able to hold WAL records of all the concurrent transactions to avoid disk spills. How many concurrent transactions there can be? Every backend in PostgreSQL, client as well as worker can potentially start a transaction and there can be only one transaction active at a given time in a given backend. Thus the higher bound on the number of concurrent transactions in a server is decided by max_connections which decided the maximum number of client backends in the server, max_prepared_transactions which decides the number of prepared transaction in addition to the transactions in client backends, max_worker_processes and autovacuum_max_workers which together decide the backends other than the client backends which may execute transactions. The sum of all these GUCs gives the higher bound on the number of concurrent transactions that can be running at a time in a server. Assuming that average amount of WAL produced by each transaction is known, the total amount WAL that may get added to reorder buffers is {maximum number of transactions in the system} * {average amount of WAL produced by each transaction}. The question is how to find the average?

Transactions by different applications and transactions by worker processes all may have different characteristics and thus produce different amounts of WAL. But they all compete for space in reorder buffer and they all are part of a single WAL stream, which can be examined by pg_waldump. There are a few ways, we can utilize this utility to estimate logical_decoding_work_mem.

1. Count the number of commits or aborts in a given set of WAL segments and divide the total size of WAL segments by that count. The total size of WAL segments will be {number of WAL segments] * {size of each WAL segment}. If you are seeing transactions being spilled to disk, the total amount of WAL generated by concurrent transactions is higher than logical_decoding_work_mem which by default is 64MB which is equivalent to 4 WAL segments of, default size, 16MB each. So you will need to analyze several WAL segments not just a few.
2. pg_waldump reports WAL records by transaction. It can be used for a better estimate by sampling typical transactions from pg_waldump and estimating sizes of each such typical transactions and their counts.
3. Modify pg_waldump to keep a running count of amount of WAL accumulated in reorg buffer. The algorithm would look like below
1. T = 0
2. Read a WAL record. If the record belongs to transaction x, Cx = Cx + size of WAL record, where Cx maintains the total size of WAL records of transaction x so far. If x is a new transaction, Cx = size of WAL record
3. T = T + Cx where T is the total size of WAL records accumulated in reorder buffer when that record was read.
4. When a COMMIT or ABORT WAL record of transaction x is read, T = T - Cx.
   
   This way T tracks the size of WAL records accumulated in the reorder buffer at a given point in time. Maximum over T can be used to estimate logical_decoding_work_mem.
4. If you are not comfortable with C or pg_waldump, above option can be implemented by parsing output of pg_waldump using higher level languages like python.

Once you have estimated the maximum amount of WAL that may get accumulated in the reorder buffer, add about 5% overhead of other data structures with reorder buffer and you have your first estimate of logical_decoding_work_mem. It can be refined further by setting the GUC and monitoring pg_stat_replication_slots.

However, remember that each WAL sender will consume logical_decoding_work_mem amount of memory which may affect the total memory available for the regular server operation. You may find an optimal value which leaves enough memory for regular server operation while reducing the disk spills. Option 3 and 4 would help you with that. If you plot the curve of T against time, you will find memory consumed by the WAL senders in the steady state, eliminating any picks or troughs in memory usage by logical decoding. logical_decoding_work_mem should be configured keeping this steady state consumption in mind.

Even after doing all this, the disk spill is high or there's too much memory consumed by WAL senders, you best bet is to use streaming in-progress transactions by specifying streaming parameter to logical replication protocol. Find more about that in this blog.

If you know other ways to estimate logical decoding work memory or avoiding disk spill, please comment on this blog.

The PostgreSQL operator labyrinth

While working on SQL/PGQ patch I wanted to find an equality operator for given left and right argument types to construct a condition to match an edge with its adjacent vertexes. It would look as simple as calling C function oper() with operator as "=" and required left and right data types. But soon it turned out to be a walk in PostgreSQL's operator labyrinth, which held my equality operator at the center instead of Minotaur.

First and foremost in PostgreSQL '=' does not necessarily mean and equality operator. It's simply a name of an operator used for comparing operands for equality. One could get swallowed by Sphinx for that. So oper() is useless. Equality operators are instead identified by strategies EqualStrategyNumbers like HTEqualStrategyNumber, BTEqualStrategyNumber, RTEqualStrategyNumber and so on. But there's no C function which would provide you an equality operator given the strategy number and data types of left and right operands. Suddenly I found myself trapped in the index labyrinth since BT, HT and RT are related to hash, b-tree and R-tree indexes. Now, all I was doing was begging to get out of the labyrinth rather than finding answer to my seemingly simple question. But this Wit-Sharpening Potion helped me to find my path out of the labyrinth and also answered my question.

The path is surprising simple Index -> Operator Class -> Operator Family -> Operator. Like Daedalus's labyrinth, it's unicursal but has a four course design instead of seven course. Like the An index needs operators to compare values of a column or an indexed expression. All values being indexes are of the same datatype. An operator class holds all the required comparison operators for that datatype. However, a a value being searched or compared to in that index may not necessarily have the same datatype. For example an index may be on an column of type int4 but it could still be used to search a value of type int2. PostgreSQL requires different operators for different pairs of operand data types as the semantics to compare values from same datatype may be different from those from different data types. That's where an operator family comes into picture. It holds operator classes, one for each data type in the "family" of data types e.g. integers. Each operator class would still contains operators comparing values of the same datatype. "Loose" operators in an operator family are used to compare values from different datatypes.

If you know an operator family, equality strategy and the data types of left and right operands, you can find the operator using get_opfamily_member(). But there's no ready function to get operator family given the data types of operands. Instead you have to taken a convoluted route, otherwise I would't call that simple path a labyrinth. From the two datatypes we choose one, usually the datatype of the values in a set being searched. Like datatype of primary key, which holds the set of values in which we search for a foreign key value. Find the comparison operators for that datatype using get_sort_group_operators(). Using sorting operator returned by that function, search for the operator family using get_ordering_op_properties(). Pass that operator family (and strategy) to get_opfamily_member() along with the datatypes of operands to reach the operator you want. Interestingly, get_sort_group_operators() calls lookup_type_cache() which saves the preferred operator family tree in type cache. But it's not exposed outside.

Hope this blog serves as a Cretan coin depicting PostgreSQL operator labyrinth.

Update on 3rd December 2024 - There's another passage from datatypes to equality operator. Use GetDefaultOpclass() to get the default operator class for the chosen datatype. From there get the operator family of that operator class using get_opclass_family(). Use get_opfamily_member() to get the desired operator. With this method, you can try both hash and btree methods since an equality operator is available in both the methods. In the earlier method you could get the operator family only if there existed an ordering which is not available in hash method. It doesn't look unicursal anymore and thus not a labyrinth but a maze!

SQL/PGQ and graph theory

The story goes almost two decades back. I was studying computer engineering in College of Engineering Pune. Prof Vinayak Joshi had recently joined COEP and had research interest in discrete mathematic, especially Lattices. He needed some software for exploring some patterns in graphs. Matlab, though almost three decades old by then, was new to us and quite expensive. He approached my scholar friend Amit Bose and requested him to write a program for exploring the desired graph patterns. If memory serves, Amit wrote the program in C and quite possibly used TurboC as an IDE. That was a remarkable feat given that graphs could consume a huge memory, C is a very basic language and TurboC was clumsy and clunky back then. May be Amit knew how to use gcc and linux which allowed huge memory models. (I am sure today's software engineers haven't heard about memory models.) In the due course we studied discrete mathematics, graph theory and also lattices.

Few years later I joined post-graduate program in Computer Science and Engineering at IIT Bombay. The institute employed eminent faculty in the area of theoretical computer science, including graph theory. I worked as a teaching assistant to Prof. Ajit Diwan and Prof. Abhiram Ranade. I also took a course run by Prof. Sundar Vishwanathan. That developed my interest in the graph theory. But I felt any work in graph theory required a lot of patience (more details to follow) and it was too difficult and intangible. Like many others in my class, I dared not take a research project in the subject.

Graph theory still fascinates me. Graphs are still my first go-to-tool to model and solve any problem in profession or life. If graph theory fails I use other tools. Fast forward to the present, when I saw opportunity to work with graphs in RDBMS, I immediately grabbed it. Follow pgsql-hackers thread for more about that work. I don't know whether Prof. Ajit had graph databases in his mind when he said to our class, "Do you think that I can not offer projects in databases? I can offer a lot of them". But that's quite true. In the age of AI and analytics, graph databases are once again a hot topic.

In Prof. Sundar prescribed "Introduction to graph theory (second edition)" by Douglas West for his course. the exercises in that book were like puzzles for me. I liked to work those out myself. Most of the times I didn't. One such problem was related to "king". If memory serves me, it was 1.4.38 on page 66 of that book, which I still have with me. I spent hours trying to prove the theorem but did not succeed. I went to Prof. Sundar for help. He patiently listened to all the things I had tried to solve that problem. He said I was very close to the solution and any hint from him would be as good as the solution itself. He suggested that I sit in his room and try again. After an hour of struggle, I left his room without any success. The problem still haunts me.

I don't know whether Matlab is still expensive and whether Prof. Joshi is still using programs to explore his graphs. But if he is, SQL/PGQ might come handy. Having it in PostgreSQL means they can use it for free. All the database capabilities allow them to store and retrieve the graphs they have tried in their research. Let's take a simple example, of a king.

In a digraph, a king is a vertex from which every vertex is reachable by a path of length at most 2. In other words, if a vertex v is a king, it is connected to every other vertex by a path of length at most two. Let's see how to do that with SQL/PGQ. Assume a table "vertexes" which contains all the vertexes of the graph and a table "edges" which contains all the edges in the graph connecting those vertices.

create table vertexes (id int primary key,

                        name varchar(10));

create table edges (id int primary key,

                    src int references vertexes(id),

                    dest int references vertexes(id),

                    name varchar(10));

create property graph tournament

    vertex tables (vertexes default label)

    edge tables (edges source key (src) references vertexes(id)

                        destination key(dest) references vertexes(id)

                        default label)

Let's build the query to find a king step by step. First step would be to find all the nodes reachable from a given node by a path of length at most 2.

select src_name, dest_name 

    from graph_table (tournament 

                        match (src is vertexes)->{1,2}(dest is vertexes)

                        where (src.id <> dest.id)

                        columns (src.name as src_name, dest.name as dest_name))

    order by src_name;;

I have discussed most of the constructs in my previous posts on DBaaG and its components. {1, 2} is a new construct being used here which indicates that the path between src and dest is of length maximum 2. Thus it lists all src nodes and respective dest nodes which are connected to their respective src node by a path of length at most 2. Also notice that we are eliminating the src node being reported as dest node to simplify the next step in the query.

Now we have to find node/s which is connected to all the nodes in such a way. To do that we simply count the distinct dest nodes an src node is reachable to. If this count is same as the number of vertexes in the graph but one, corresponding src node is the king.

select src_name, count(distinct dest_name) num_reachable_nodes 

    from graph_table (tournament

                match (src is vertexes)->{1,2}(dest is vertexes)

                where (src.id <> dest.id)

                columns (src.name as src_name, dest.name as dest_name))

    group by src_name

    having count(distinct dest_name) = (select count(*) - 1 from vertexes);

distinct in aggregate count makes sure to count each dest node only once. having clause filters every node which is not connected to all  the other nodes in the graph. If we populate vertex and edge tables as follows:

insert into vertexes values (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e');

insert into edges values (1, 1, 2, 'a-b'), (2, 2, 3, 'b-c'), (3, 1, 3, 'a-c'), (4, 3, 4, 'c-d'), (5, 4, 5, 'd-e'), (6, 2, 1, 'b-a');

This will create a graph as shown in the figure below. Notice the cycle a->b->a. Node e is reachable from node a by a path of length 3. All other nodes are reachable from node a by a path of length at most 2.

Above query does not return any rows since there is no king in this graph.

Let's remove outlier node e and the edge connecting nodes d and e.

delete from edges where name = 'd-e';

delete from vertexes where name = 'e';

With that the above query returns two king nodes a and b.

src_name   num_reachable_nodes

---------- -------------------

a   | 3

b   | 3

PostgreSQL is loved by developers. Hope introduction of SQL/PGQ makes it popular among graph theory researchers as well.

Property graphs: elements, labels and properties

A property graph consists of three types of "things" in it: elements, labels and properties. 

Elements are nodes or edges in the graphs. They form the basic structure of a graph. An edge connects two nodes. Two nodes may be connected by multiple edges corresponding to different relationships between them.

Labels classify the elements. An element may belong to multiple classes and thus have multiple labels.

Properties are key-value pairs providing more information about an element. All the elements with the same label expose same set of keys or properties. A property of a given element may be exposed through multiple labels associated with that element.

Let's use the diagram here to understand the concepts better. There are three elements: N1, N2, the vertexes and an edge connecting them, labels L1 to L4, properties P1 to P7. The arrows connecting a label to an property indicates that that label exposes that property. E.g. label L3 exposes properties P2, P3, P5. Property P1 is exposed by both L1 and L2. An arrow between an element and a label indicates that that label is associated with that element. N1 has labels L1 and L2 whereas the edge has just one label L4. The properties that are associated with (and are exposed by) an element are decided by the labels associated with it. E.g. the properties P1, P2 and P4, which are union of properties associated with labels L1 and L2, are exposed by element N1. P1 has the same value v1 irrespective of which label is considered for this association. E.g height of a person will not change whether that person is classified as a teacher, businessman or a plumber. Similarly notice that the edge exposes properties P6 and P7 since it is labelled as L4.

SQL/PGQ's path pattern specification language allows to specify paths in terms of labels ultimately exposing the properties of individual paths that obey that patterns. E.g. (a IS L1 | L2)-[]->(b IS L3) COLUMNS (a.P3) will returns values of property P2 of all the nodes with labels L1 or L2. If you notice that N1 and N2 are the elements associated with either L1 or L2 or both. But N1 does not expose property P3. Hence we might expect that the above query would return an error. But instead the standard specified that it should report NULL, quite inline with the spirit of SQL NULL which means unknown.

The way I see it, a property can not exist without at least one label exposing it. A label can not exist without being associated with at least an element. But once defined, they have quite an independent existence.

PostgreSQL's memory allocations

There's a thread on hackers about recovering memory consumed by paths. A reference count is maintained in each path. Once paths are created for all the upper level relations that a given relation participates in, any unused paths, for which reference count is 0, are freed. This adds extra code and CPU cycles to traverse the paths, maintain reference counts and free the paths. Yet, the patch did not show any performance degradation. I was curious to know why. I ran a small experiment.

Experiment

I wrote an extension palloc_test which adds two SQL-callable functions palloc_pfree() and mem_context_free() written in C. Function definitions can be found here. The first function palloc's some memory and then pfree's it immediately. Other function just palloc's but never pfrees, assuming that the memory will be freed when the per-tuple memory context is freed. Both functions take the number of iterations and size of memory allocated in each iteration respectively as inputs. These functions return amount of time taken to execute the loop allocating memory. It appears that the first function spends CPU cycles to free memory and the second one doesn't. So the first one should be slower than the second one.

Results

The table below shows the amount of time reported by the respective functions to execute the loop as many times as the value in the first column, each iteration allocating 100 bytes. The figure shows the same as a plot. The time taken to finish the loop increases linearly for both the function indicating that the palloc logic is O(n) in terms of number of allocations. But the lines cross each other around 300K allocations.

 

count	palloc_pfree	memory context reset
100	0.0029	0.007124
100100	2.5646	5.079862
200100	5.1682	10.375552
300100	7.6373	15.704286
400100	10.1827	19.038238
500100	12.7013	23.847599
600100	15.2838	28.708501
700100	17.8255	36.982928
800100	20.3718	41.863638
900100	23.0706	44.332727
1000100	51.3311	52.546201
2000100	56.7407	104.747792
3000100	76.3961	154.225157
4000100	102.3415	206.510045
5000100	126.1954	256.367685
6000100	155.8812	314.178951
7000100	179.9267	367.597501
8000100	206.2112	420.003351
9000100	234.7584	474.137076

Inference and conclusion

This agrees with the observations I posted on the thread. Instead of letting all the useless path to be freed when query finishes, freeing them periodically during planning is time efficient as well as memory efficient. It compensates for the extra CPU cycles spent to maintain reference counts, traverse and free paths.

The actual memory allocation and freeing pattern as implemented in that patch is different from that in the experiment, so it might be worth repeating those experiments by simulating similar pattern.

I used chunk size of 100 since I thought it's closer to the order of average path size. But it might be worth repeating the experiment with larger chunk sizes to generalize the result.

DBaaG with SQL/PGQ

For those who have studied ERD-lore, it's not new that a relational database is very much like a graph. But it has taken SQL, more than 30 years since it became a standard and almost half a century since its inception to incorporate construct that will allow a DataBase to be treated as a Graph, DBaaG. This is surprising given that SQL was developed as language for relational databases which are modeled using ER diagrams. Better late than never. SQL/PGQ has arrived as 16th part of SQL:2023.

Entity Relationship Diagram, ERD in short, is a tool to model and visualize a database as entity types (which classify the things of interest) and relationships that can exist between them. Entity types and the relationships both map to relations in a Relational DataBase Management System (RDBMS in short). The rows in the relations represent entities (instances of entity types) and relationship between entities respectively. Fig. 1 below shows an ERD for a hypothetical shop.

This diagram very much looks like a graph with entity types represented as nodes and relationships represented by edges. That's exactly what SQL/PGQ is about. It adds language constructs to SQL to present underlying database as a "Property Graph". For example, property graph definition corresponding to the above ERD would look like

CREATE PROPERTY GRAPH shop

VERTEX TABLES (

    CreditCard label Payment,

    BankAccount label Payment,

    Person label Customer,

    Company label Customer,

    Trust label Customer,

    Wishlist label ProdLink,

    Order label ProdLink,

    Product)

EDGE TABLES (

    CCOwns label Owns

    BAHolds lable Owns,

    CustOrders label CustLink,

    CustWishlist label CustLink,

    CompanyOrders label CustLink,

    CompanyWishlist label CustLink,

    TrustOrders label CustLink,

    TrustWishlist label CustLink,

    OrderCCPayment label OrderPayment,

    OrderBAPayment label OrderPayment,

    OrderItems label ItemLink,

    WishlistItems label ItemLink);

 

Clever readers may have noticed that some of the entity types have some commonality. CreditCard and BankAccount are both Payment methods. Person, Company and Trust all can be considered as "Customers" by the shop. In a graph entities with commonalities will be represented by visual annotations like colors in Fig. 2. SQL/PGQ chooses to represents them by labels. Columns of the underlying tables are exposed through properties of labels. Traditionally the labels may be implemented as table inheritance or through tables abstracting commonalities. But it may not be necessary anymore.

Augmented with query constructs in SQL/PGQ they make it easy to write queries, especially analytical. Imagine a query to find all the products paid via credit card. There will be tons of JOIN and UNIONs over those joins. That's almost like "implementing" a logic in SQL. You would ask which parts do I JOIN before UNION, and which parts do I UNION before JOIN and so on. That's against the "imperative" spirit of SQL which should allow you to tell "what" you want and leave "how" for the DBMS to figure out. With SQL/PGQ you tell the DBaaG which paths in the graph to traverse. How to traverse them is the system's responsibility. So the SQL/PGQ query looks like below. Much simpler than joins and unions. In fact, it allows me not to mention edge tables at all in the query.

SELECT distinct name FROM

    GRAPH_TABLE (shop

                MATCHES

                   (o IS Orders)->(py IS Payment WHERE py.type = 'CC')<-(c IS Customer)->(o IS Order)->(p is Product)

                COLUMNS (p.name));

 

I must note that the query looks more like a mathematical equation than SQL which till now followed natural language syntax. But well, there it is.

What those () mean? What about various literals in it? How to specify properties? I am sure I have roused more questions than those answered here. I plan to write more about it in future. This blog has gone longer than I initially intended it to be, but I hope it has aroused your interest in SQL/PGQ nonetheless.

Oh! but before I end, please note that we are working on implementing SQL/PGQ in PostgreSQL. If you are interested and want to contribute, please follow and respond on pgsql-hackers thread.

   

 

Partitioning as a query optimization strategy?

I had discussed about query optimization techniques applicable to queries involving partitioned tables in PGConf.India 2018 (Video recording, slides). (My previous blog discusses these techniques in detail.) The takeaway from that presentation was these query optimization techniques improved query performance if the tables were already partitioned. But partitioning wasn't good enough as a query optimization strategy by itself even when partitionwise join, partitionwise aggregate and parallel query were all used together on small data-sizes. Experiments then hinted that if the data was large enough partitioning would become a query optimization strategy. But we didn't know how large is large enough. Experiments to establish would require beefy machines with larger resources which were costly, took long time to procure or get access to. On top of them it took long time to setup and finish the runs. At one point we stopped experimenting. Fast forward to today and things have changed drastically, thanks to the cloud!

EDB's BigAnimal comes to help

EnterpriseDB offers PostgreSQL-as-a-service in the form of a DBAAS platform called BigAnimal. It allows its users to deploy and run PostgreSQL in cloud on hardware configuration of their choice. It also provides a starter free credit to try out this platform. I experimented with very large datasets by using BigAnimal. I ran the experiments on PostgreSQL 15 hosted on a m5.4xlarge instance (64 GB RAM, 16 vCPUs) with 1500 GB storage. All of this without wasting much time and also money; I destroyed the instance as soon as my experiments were over.

Experiment

I wanted to see the impact of only partitioning as a query optimization strategy. So instead of using whole TPCH setup, I crafted a micro-benchmark with two queries involving two tables li and ord modeled after lineitem and orders tables in TPCH benchmark. When partitioned each of these two tables have matching 1000 partitions each. The tables have following schema

$\d+ li
                                            Table "public.li"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 key1   | integer |           | not null |         | plain    |             |              |
 key2   | integer |           | not null |         | plain    |             |              |
 d      | date    |           |          |         | plain    |             |              |
 m      | money   |           |          |         | plain    |             |              |
 t1     | text    |           |          |         | extended |             |              |
 t2     | text    |           |          |         | extended |             |              |
Indexes:
    "li_pkey" PRIMARY KEY, btree (key1, key2)
Access method: heap

$\d+ ord
                                           Table "public.ord"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 key1   | integer |           | not null |         | plain    |             |              |
 d      | date    |           |          |         | plain    |             |              |
 m      | money   |           |          |         | plain    |             |              |
 t1     | text    |           |          |         | extended |             |              |
 t2     | text    |           |          |         | extended |             |              |
Indexes:
    "ord_pkey" PRIMARY KEY, btree (key1)
Access method: heap

When partitioned they are partitioned by range on key1. Each row in ord has 3 matching rows in li, roughly imitating the data-size ratio between corresponding tables in TPCH benchmark.

Query 1 which extracts relevant parts of TPCH Q3 or Q4 looks like

select count(*)
    from (select o.key1, sum(o.m) revenue, o.d
            from li l, ord o
            where l.key1 = o.key1 and
                o.d > current_date + 300 and
                l.d < current_date + 700
            group by o.key1, o.d
            order by revenue, o.d
    ) as t1

 Query 2 which is a pure join between li and ord looks like

select o.key1, l.key1, o.d
            from li l, ord o
            where l.key1 = o.key1 and
                o.d > current_date + 300 and
                l.d < current_date + 700

The time required to execute these two queries is measured using EXPLAIN ANALYZE. We varied the number of rows per partition as well as the number of partitions.

The execution times for queries are given in tables below.

Table 1: 10K rows per partition

	Average execution time Q1 (ms)			Average execution time Q2 (ms)
No. of partitions	unpartitioned table	partitioned table without PWJ	partitioned table with PWJ	unpartitioned table	partitioned table without PWJ	partitioned table with PWJ
5	83.05	93.29	53.68	48.83	60.55	50.85
10	195.87	221.33	90.24	104.40	129.06	105.20
50	1,183.25	1,487.00	432.07	584.31	723.90	527.97
100	2,360.19	3,001.81	888.46	1,342.69	1,595.53	1,053.91
500	11,968.68	15,220.69	4,350.62	6,903.91	8,082.09	5,381.46
1000	33,772.31	31,090.57	8,847.61	16,461.44	17,646.42	10,875.05

Table 2: 100K rows per partition

	Average execution time Q1 (ms)			Average execution time Q2 (ms)
No. of partitions	unpartitioned table	partitioned table without PWJ	partitioned table with PWJ	unpartitioned table	partitioned table without PWJ	partitioned table with PWJ
5	1,157.23	1,489.53	514.68	609.81	773.31	582.07
10	2,326.40	2,990.32	1,041.11	1,375.69	1,597.55	1,152.33
50	11,899.34	15,181.49	4,792.88	7,196.35	8,446.64	5,828.54
100	24,139.10	30,660.87	9,594.33	14,277.53	16,753.36	11,512.35
500	1,53,922.35	1,65,550.06	50,308.85	74,387.34	85,175.79	58,282.17
1000	3,13,534.59	3,38,718.63	1,31,482.31	2,03,569.14	1,32,688.60	1,23,643.18

Same numbers in the form of graphs are better to understand. Next we see graphs depicting the average execution time of each of these queries varying with the number of partitions. In each graph Y-axis shows the execution times in logarithmic scale, X-axis shows the number of partitions. Blue line shows the query execution times when tables are not partitioned. Red line shows query execution times when tables are partitioned but partitionwise join and aggregation are not used (turning both enable_partitionwise_join and enable_partitionwise_aggregate OFF). Yellow line shows query execution times when tables are partitioned and partitionwise join and partitionwise aggregate is used.

Note that the Y-axis denoting the execution time is drawn with logarithmic scale. Thus the linear difference on that axis shows improvement in integer multiples instead of fractions. For example, Q1's execution time improves almost by 4 times when tables are partitioned and partitionwise join and aggregate are enabled.

Graph 1

Graph 2

Graph 3

Graph 4

Key takeaways

The graphs above make it clear that when datasizes are very large partitioning can also be used as a query optimization technique along with its other advantages. I will share some key points here

1.  When the total data size reaches the house of millions, partitioning can be considered as a query optimization strategy. The exact number of partitions and average rows per partition do not make much difference. We see similar performance whether 5M rows are divided into 500 partitions or 50 partitions.
   
2. The exact thresholds depend upon properties of data and queries. E.g. size of each rows, columns used in query, operations performed by the query etc.
   
3. Since these optimization techniques are very much dependent upon the partition key, choosing the right partition key is very important.
4. When tables are partitioned, queries perform better when partitionwise operations are used irrespective of the datasize.
   

Each workload is different. Above charts provide some guidance. But experimenting with the size and number of partitions as well as the partition key is important to know whether partitioning will help you optimize queries in your application or not. Experimentation shouldn't be an issue anymore. EDB's BigAnimal platform allows its users to experiment quickly without requiring a large upfront investment.