Introduction

PostgreSQL is “The World’s Most Advanced Open Source Database,” and I believe it. In my 10+ years of working with it, it’s been solid, serving up data to SaaS loads of over 1000 queries per second, rarely going down, surviving all manner of accusations of corruption (which turned out to be software engineering error) and performance degradation (which turned out to be user error). It’s got so many features and extensions that suit every need, maybe 50-60% of which most users don’t use on a regular basis, or haven’t even heard about. Unfortunately, in my recent experiences in technical support, I’ve learned that Postgres is generally very trusting, but that’s the case with many pieces of technology; it makes no judgments of how you should tune the parameters in postgresql.conf, just like the gas pedal on your sports car doesn’t make any judgments about how fast you should be driving. Just because you can put the pedal to the metal doesn’t mean you should do that in rush hour traffic, and just because you can set a parameter very high doesn’t necessarily mean that you should set it that high to serve a high-traffic OLTP application.

One of the parameters that gets the most misunderstanding is max_connections. It’s understandable that on modern systems with lots of CPUs and lots of RAM, serving modern SaaS loads to a global user base, one can see tens of thousands of user sessions at a time, each trying to query the database to update a user’s status, upload a selfie, or whatever else users might do. Naturally, a DBA would want to set max_connections in postgresql.conf to a value that would match the traffic pattern the application would send to the database, but that comes at a cost. One example of such a cost would be connection/disconnection latency; for every connection that is created, the OS needs to allocate memory to the process that is opening the network socket, and PostgreSQL needs to do its own under-the-hood computations to establish that connection. Scale that up to thousands of user sessions, and a lot of time can be wasted just getting the database ready for the user to use. Other costs involved in setting max_connections high include disk contention, OS scheduling, and even CPU-level cache-line contention.

So what should I set my max_connections to?

There’s not a lot of scientific data out there to help DBAs set max_connections to its proper value. Corollary to that, most users find PostgreSQL’s default of max_connections = 100 to be too low. I’ve seen people set it upwards of 4k, 12k, and even 30k (and these people all experienced some major resource contention issues). Talk to any PostgreSQL expert out there, and they’ll give you a range, “around 300-500,” or some will flat-out say, “not more than 700,” and “definitely no more than 1000.” But where do these numbers come from? How do they know that, and how do we calculate that? Ask these questions, and you’ll only find yourself more frustrated, because there isn’t a formulaic way to determine that number. The difficulty in setting this value lies in the application that the database needs to serve; some applications send a barrage of queries and the close the session, while other applications might send queries in spurts, with lots of idle time in between. Additionally, some queries might take up a lot of CPU time to perform joins and sorts, while others will spend a lot of time sequentially scanning the disk. The most rational answer that I have seen is to count the number of CPUs, account for % utilization (based on some benchmarking one would need to do) (slides), and multiply it by a scale factor. But even that involves some “hand-waving.”

Testing the tribal knowledge

Without a very clean way to calculate max_connections, I decided at least to test the validity of the tribal knowledge out there. Is it really the case that it should be “around 300-500,” “no more than 700,” and “definitely no more than 1000?” For that, I set up an AWS g3.8xlarge EC2 instance (32 CPU, 244GB RAM, 1TB of 3K IOPS SSD) to generously imitate some DB servers I’ve seen out there, and initialized a pgbench instance with --scale=1000. I also set up 10 smaller EC2 instances, to act as application servers, and on each of these, I ran a pgbench test for one hour, incrementing --client=NUM by one each hour (so they would aggregately create 100,200,300 … 5000 connections for each hour’s test). autovacuum was turned off to prevent any unnecesary interference and skew of the results (though I vacuumed between each test), and the postgresql.conf was otherwise tuned to some generally-accepted values. I set max_connections to 12k, figuring that my tests would use no more than the 5000 it would ask for in the final test. I walked away while the tests ran, and the results came back looking like this:

Below is a more zoomed-in view of the above graph:

So for this server that I’ve set up to be similar to some enterprise-grade machines, the optimal performance was when there were 300-500 concurrent connections. After 700, performance dropped precipitously (both in terms of transactions-per-second and latency). Anything above 1000 connections performed poorly, along with an ever-increasing latency. Towards the end, the latency starts to be non-linear – this was probably because I didn’t configure the EC2 instance to allow for more than the default ~25M open filehandles, as I saw several could not fork new process for connection: Resource temporarily unavailable messages after 3700 concurrent connections.

This interestingly matched all three adages – “around 300-500,” “no more than 700”, and “definitely no more than 1000.” It seemed too good to be true, so I ran the tests again, only going up to 1800. The results:

So it seems that for this server, the sweet spot was really somewhere between 300-400 connections, and max_connections should not be set much higher than that, lest we risk forfeiting performance.

But what if I need more connections?

Clearly, having max_connections = 400 is not going to allow a high-traffic application to handle all the work that the users give to it. Somehow, the database needs to be scaled up to meet these requirements, but doing so would seem to require some magic. One option is to set up a replication system so that reads are distributed across several servers, but if write traffic ever exceeds 400 concurrent sessions (which is very likely), other options need to be considered. A connection pooler would fit this need by allowing several client sessions share a pool of database connections and perform read-write transactions as needed, handing over the reins to other sessions when idle. Within the PostgreSQL community, the main players for pooling applications are pgbouncer and pgpool – both have been well-tested to enable DBAs to scale their PostgreSQL databases to tens of thousands of concurrent user connections.

To demonstrate the improved scalability when employing a connection pooler, I set up an m4.large EC2 instance similar to Alvaro Hernandez’s concurrent-connection test because 1) I wanted to use a benchmark that wasn’t just my own numbers, and 2) I wanted to save some money. I was able to get a similar graph as his:

However, this graph was created without the -C/--connect flag (establish new connection for each transaction) in pgbench, likely because Alvaro wasn’t trying to illustrate the advantages of using a connection pooler. Therefore, I re-ran the same test, but with -C this time:

As we can see, because each transaction had to connect and disconnect, throughput decreased, illustrating the cost of establishing connections. I then configured pgbouncer with max_client_conn = 10000, max_db_connections = 300, pool_mode = transaction, and ran the same pgbench tests again, using the pgbouncer port instead (-h <hostname> -p6432 -U postgres --client=<num_clients> --progress=30 --time=3600 --jobs=2 -C bouncer):

It becomes apparent that while pgbouncer maintains open connections to the database and shares them with the incoming clients, the connection overhead is offset, thereby increasing the throughput. Note that we’ll never achieve Alvaro’s graph, even with a pooler, because there will always be some overhead in establishing the connection (i.e., the client needs to tell the OS to allocate some space and open up a socket to actually connect to pgbouncer).

Conclusion

As we can see, max_connections should be determined with some on-site benchmark testing, with some custom scripts (note that all these tests used the built-in pgbench transaction that consists of 3 SELECTs, 1 UPDATE, and 1 INSERT – a closer-to-reality test can be created by providing a custom .sql file and using the -f/--file flag). Basically, do your homework – benchmark and find out the maximum concurrency that still gives good performance, round up to the nearest hundred (to give you some headroom), and set max_connections accordingly. Once set, any remaining requirements for concurrency ought to be met with any combination of replication or a connection pooler. A connection pooler is a vital part of any high-throughput database system, as it elimiates connection overhead and reserves larger portions of memory and CPU time to a smaller set of database connection, preventing unwanted resource contention and performace degradation.

At recent conferences, we have received a curious question from users who have used Oracle in the past and are now using PostgreSQL: “Do we have hypothetical indexes in PostgreSQL ?“. The answer to that question is YES. The true meaning of hypothetical is imaginary but not real. We can try creating indexes that are imaginary for the PostgreSQL optimizer which don’t exist in reality. Let’s look at some detailed examples.

How are hypothetical indexes helpful?

The execution time of an SQL in most of the relational databases depends on the cost of the execution. An optimizer chooses the plan that has the lowest cost, and thus considers further phases such as execute and fetch using that plan. One of the easiest ways of optimizing a well-written SQL is through appropriate indexes that are suitable for that query. An index may reduce the number of pages fetched from disk and may live in the cache due to its size (<<< table size). So, indexing is always a low-hanging fruit for admins and developers wishing to tune an SQL.

But often developers wish to see if an index can be really helpful. The only option available is to create the index and check to see if the queries are able to use it with a reduced cost. Creating the index may be fine if it is in a test environment, or a table that is not huge in size, but if for some reason you wish to see if an index can be helpful for an SQL without actually creating it, then you may test and try this extension to create hypothetical indexes.

PostgreSQL Extension for hypothetical indexes

In order to achieve the functionality of creating imaginary indexes in PostgreSQL, we need to use an extension named hypopg. This extension is made available in the PGDG repository from PostgreSQL 9.2 and works until the latest active release PostgreSQL 11.3. In order to create this extension, you may just use yum or apt depending on your linux distro or compile it from source.

Installing hypopg

In RedHat/CentOS

# yum install hypopg

For Debian/Ubuntu, you must make sure you have the development package named: postgresql-server-dev-X where X is the major version.

# apt install postgresql-server-dev-11 (Example for PostgreSQL 11)
# apt install postgresql-11-hypopg

Creating the Extension

# su - postgres -c "psql -d percona -c 'CREATE EXTENSION hypopg WITH SCHEMA myextensions'"
CREATE EXTENSION

In order to show you the list of functions created by hypopg, I have created this extension in a different schema. Here is the list:

percona=# select proname from pg_proc where pronamespace IN
(select oid from pg_namespace where nspname = 'myextensions');
proname
----------------------
hypopg
hypopg_create_index
hypopg_drop_index
hypopg_get_indexdef
hypopg_list_indexes
hypopg_relation_size
hypopg_reset
(7 rows)

hypopg Functions

hypopg_create_index: This function is used to create a hypothetical index. We do not have to specify the name of the index because it is ignored by this function anyway.

percona=# SELECT * FROM myextensions.hypopg_create_index('CREATE INDEX btree_hypo_idx on foo.products USING BTREE(quantity)');
indexrelid  | indexname
------------+------------------------------------
16665       | <16665>btree_foo_products_quantity
(1 row)

hypopg_drop_index: This function is used to drop a hypothetical index upon testing. As soon as you disconnect your session, the index automatically gets dropped as it is only created in the private memory of that session.

percona=# select * from myextensions.hypopg_drop_index(16687);
 hypopg_drop_index
-------------------
 t
(1 row)

hypopg_get_indexdef: The definition of the hypothetical index we have created using this function.

percona=# select * from hypopg_get_indexdef(16713);
                 hypopg_get_indexdef
-----------------------------------------------------
 CREATE INDEX ON foo.products USING btree (quantity)
(1 row)

hypopg_list_indexes: This function helps us list all the hypothetical indexes we have created in our session.

percona=# select * from hypopg_list_indexes();
 indexrelid |             indexname              | nspname | relname  | amname
------------+------------------------------------+---------+----------+--------
      16713 | <16713>btree_foo_products_quantity | foo     | products | btree
(1 row)

hypopg_relation_size: This function helps us estimate the index size, so we know the approximate amount of space this index could occupy.

percona=# CREATE INDEX idx1 ON foo.products (product_sku);
CREATE INDEX
percona=# \di+ idx1
                         List of relations
 Schema | Name | Type  |  Owner   |  Table   |  Size  | Description
--------+------+-------+----------+----------+--------+-------------
 foo    | idx1 | index | postgres | products | 631 MB |
(1 row)
percona=# SELECT * FROM myextensions.hypopg_create_index('CREATE INDEX on foo.products USING BTREE(product_sku)');
 indexrelid |               indexname
------------+---------------------------------------
      16718 | <16718>btree_foo_products_product_sku
(1 row)
percona=# select * from pg_size_pretty(hypopg_relation_size(16718));
 pg_size_pretty
----------------
 653 MB
(1 row)

hypopg_reset: This function drops all the hypothetical indexes created by us in a single command.

percona=# select * from hypopg_list_indexes();
 indexrelid |               indexname               | nspname | relname  | amname
------------+---------------------------------------+---------+----------+--------
      16715 | <16715>btree_foo_products_quantity    | foo     | products | btree
      16716 | <16716>btree_foo_products_product_sku | foo     | products | btree
(2 rows)
percona=# select * from hypopg_reset();
 hypopg_reset
--------------
(1 row)
percona=# select * from hypopg_list_indexes();
 indexrelid | indexname | nspname | relname | amname
------------+-----------+---------+---------+--------
(0 rows)

How does a hypothetical index work in reality?

When you use the

hypopg_create_index

EXPLAIN ANALYZE

Testing hypothetical indexes

While testing hypothetical indexes, I want to check if there are any cases where it may not work. Because our main reason for using this extension is to test whether the optimizer could identify the index we wish to create, I have thought of testing almost all types of the most-used indexes in PostgreSQL such as BTREE, HASH, GIN, GiST, BRIN, and BLOOM indexes. Only BTREE indexes are currently mentioned as applicable for hypothetical indexes.

For this reason, I have created the following table which may not make much sense for a real-time use case but helps us test all the types of indexes we could create.

percona=# CREATE TABLE foo.products (product_sku character(8), quantity int, location point, comments text,
comments_tsv tsvector, assembled_days integer[], manufactured_date timestamptz);
CREATE TABLE
percona=# INSERT INTO foo.products VALUES ('a1b2c3d4','2',point '(1,1)','This is my first message',NULL,ARRAY[1,3,5], now() - interval '11 days');
.......................................................................................................................................
.......................................................................................................................................
percona=# INSERT INTO foo.products VALUES ('j1l2m3n4','1000000',point '(5,2)','This is my millionth message',NULL,ARRAY[2,5,7], now() - interval '10 days');
percona=# update foo.products set comments_tsv = to_tsvector(comments);
UPDATE 1000000
percona=# select * from foo.products LIMIT 1;
 product_sku | quantity | location |          comments          |      comments_tsv      | assembled_days |       manufactured_date
-------------+----------+----------+----------------------------+------------------------+----------------+-------------------------------
 a1b2c3d4    |        2 | (1,1)    | This is my first message   | 'first':4 'messag':5   | {1,3,5}        | 2019-06-01 17:31:35.632891-04
(1 row)

Btree Indexes

We may try creating a BTREE index on the quantity column to speed up the performance of queries that use that column as a predicate. As you can see in the following log, I have created a

hypothetical btree index

WITHOUT Index
--------------
percona=# EXPLAIN select * from foo.products where quantity IN (4,5,6);
                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on products  (cost=0.00..716349.40 rows=6246717 width=128)
   Filter: (quantity = ANY ('{4,5,6}'::integer[]))
(2 rows)
Create Hypothetical Index
--------------------------
percona=# SELECT * FROM
myextensions.hypopg_create_index('CREATE INDEX btree_hypo_idx on foo.products USING BTREE(quantity)');
 indexrelid |             indexname
------------+------------------------------------
      16665 | <16665>btree_foo_products_quantity
(1 row)
WITH Hypothetical Index
------------------------
percona=# EXPLAIN select * from foo.products where quantity IN (4,5,6);
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Index Scan using <16665>btree_foo_products_quantity on products  (cost=0.06..546930.72 rows=6246729 width=128)
   Index Cond: (quantity = ANY ('{4,5,6}'::integer[]))
(2 rows)

BLOOM Indexes

The documentation does not mention that hypothetical indexes currently work for BLOOM indexes. However, I have tested to see if an EXPLAIN could show a plan that uses the hypothetical bloom index, and it did indeed work.

percona=# CREATE EXTENSION bloom ;
CREATE EXTENSION
percona=# SELECT * FROM
myextensions.hypopg_create_index('create index on foo.products using bloom(quantity);');
 indexrelid |             indexname
------------+------------------------------------
      16703 | <16703>bloom_foo_products_quantity
(1 row)
percona=# SET enable_seqscan TO OFF;
SET
percona=# EXPLAIN select * from foo.products WHERE quantity = 4;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on products  (cost=199449.64..873500.25 rows=4164944 width=128)
   Recheck Cond: (quantity = 4)
   ->  Bitmap Index Scan on <16703>bloom_foo_products_quantity  (cost=0.00..198408.40 rows=4164944 width=0)
         Index Cond: (quantity = 4)
(4 rows)

BRIN Indexes

I have then tried to create a hypothetical BRIN index to see if I get any errors because only btree hypothetical indexes are currently supported. To my surprise, I didn’t see any errors. But then I do see a strange error when trying to do an EXPLAIN on the SQL, or even when I try to run a SELECT on that table, as you see in the following log. So, just because it allows you to create the brin hypothetical index doesn’t mean it will work.

percona=# SELECT * FROM
myextensions.hypopg_create_index('create index on foo.products using brin(manufactured_date) with (pages_per_range=4);');
 indexrelid |                 indexname
------------+--------------------------------------------
      16669 | <16669>brin_foo_products_manufactured_date
(1 row)
percona=# select * from hypopg_list_indexes();
 indexrelid |                 indexname                  | nspname | relname  | amname
------------+--------------------------------------------+---------+----------+--------
      16669 | <16669>brin_foo_products_manufactured_date | foo     | products | brin
(1 row)
percona=# EXPLAIN select * from foo.products WHERE manufactured_date < '2019-06-03 17:31:35';
ERROR:  could not open relation with OID 16669
percona=# select count(*) from foo.products WHERE manufactured_date < '2019-06-03 17:31:35';
ERROR:  could not open relation with OID 16669

Hash, GIN, GiST or other Indexes

Unlike BRIN indexes, when we try to create any other type of hypothetical index, it throws an exact error message that states that the index type we have specified is not supported.

Hash Index
-----------
percona=# SELECT * FROM
myextensions.hypopg_create_index('CREATE INDEX hash_hypo_idx on foo.products USING HASH(product_sku)');
ERROR: hypopg: access method "hash" is not supported
GiST Index
-----------
percona=# SELECT * FROM
myextensions.hypopg_create_index('create index on foo.products using gist(location);');
ERROR:  hypopg: access method "gist" is not supported
percona=#
GIN Index
----------
percona=# SELECT * FROM
myextensions.hypopg_create_index('create index on foo.products using gin(comments_tsv);');
ERROR:  hypopg: access method "gin" is not supported
percona=#

Conclusion

It is great to see some effort towards helping developers and admins with an extension which helps them know if an index can be used without actually having to create it. This is something we could use to automate index recommendations with a combination of another extension called: pg_qualstats, which we shall discuss in my next blog post. Thanks to all the contributors who have helped us achieve this functionality in PostgreSQL.

Postgres ships in-core data types for JSON with specific functions and operators (json since 9.2, and jsonb which is a binary representation since 9.4). The upcoming Postgres 12 is becoming more complaint with the SQL specifications by introducing SQL/JSON path language, introduced mainly by the following commit:

commit: 72b6460336e86ad5cafd3426af6013c7d8457367
author: Alexander Korotkov <akorotkov@postgresql.org>
date: Sat, 16 Mar 2019 12:15:37 +0300

Partial implementation of SQL/JSON path language

SQL 2016 standards among other things contains set of SQL/JSON features for
JSON processing inside of relational database.  The core of SQL/JSON is JSON
path language, allowing access parts of JSON documents and make computations
over them.  This commit implements partial support JSON path language as
separate datatype called "jsonpath".  The implementation is partial because
it's lacking datetime support and suppression of numeric errors.  Missing
features will be added later by separate commits.

Support of SQL/JSON features requires implementation of separate nodes, and it
will be considered in subsequent patches.  This commit includes following
set of plain functions, allowing to execute jsonpath over jsonb values:

* jsonb_path_exists(jsonb, jsonpath[, jsonb, bool]),
* jsonb_path_match(jsonb, jsonpath[, jsonb, bool]),
* jsonb_path_query(jsonb, jsonpath[, jsonb, bool]),
* jsonb_path_query_array(jsonb, jsonpath[, jsonb, bool]).
* jsonb_path_query_first(jsonb, jsonpath[, jsonb, bool]).

This commit also implements "jsonb @? jsonpath" and "jsonb @@ jsonpath", which
are wrappers over jsonpath_exists(jsonb, jsonpath) and jsonpath_predicate(jsonb,
jsonpath) correspondingly.  These operators will have an index support
(implemented in subsequent patches).

Catversion bumped, to add new functions and operators.

Code was written by Nikita Glukhov and Teodor Sigaev, revised by me.
Documentation was written by Oleg Bartunov and Liudmila Mantrova.  The work
was inspired by Oleg Bartunov.

Discussion: https://postgr.es/m/fcc6fc6a-b497-f39a-923d-aa34d0c588e8%402ndQuadrant.com
Author: Nikita Glukhov, Teodor Sigaev, Alexander Korotkov, Oleg Bartunov, Liudmila Mantrova
Reviewed-by: Tomas Vondra, Andrew Dunstan, Pavel Stehule, Alexander Korotkov

The documentation can be looked at in details for all the additions, but here is a short description of each concept introduced. Note that there are many operators and features part of what has been committed, so only a very small part is presented here.

First, one needs to know about some expressions, which are similar to XPath for XML data to do lookups and searches into different parts of a JSON object. Let’s take a sample of data, so here is a JSON blob representing a character in an RPG game (this should be normalized, but who cares here):

=# CREATE TABLE characters (data jsonb);
CREATE TABLE
=# INSERT INTO characters VALUES ('
{ "name" : "Yksdargortso",
  "id" : 1,
  "sex" : "male",
  "hp" : 300,
  "level" : 10,
  "class" : "warrior",
  "equipment" :
   {
     "rings" : [
       { "name" : "ring of despair",
         "weight" : 0.1
       },
       {"name" : "ring of strength",
        "weight" : 2.4
       }
     ],
     "arm_right" : "Sword of flame",
     "arm_left" : "Shield of faith"
   }
}');

The basic grammar of those expressions is to use the keys part of the JSON objects combined with some elements:

• Dots to move into a tree
• Brackets for access to a given array member coupled with a position.
• Variables, with ‘$’ representing a JSON text and ‘@’ for result path evaluations.
• Context variables, which are basically references with ‘$’ and a variable name, with values that can be passed down to dedicated functions.

So for example, when applied to the previous JSON data sample we can reach the following parts of the tree with these expressions:

• $.level refers to 10.
• $.equipment.arm_left refers to “Shield of faith”.
• $.equipment.rings refers to the full array of rings.
• $.equipment.rings[0] refers to the first ring listed in the previous array (contrary to arrays members are zero-based).

Then comes the second part. These expressions are implemented using a new datatype called jsonpath, which is a binary representation of the parsed SQL/JSON path. This data type has its own parsing rules defined as of src/backend/utils/adt/jsonpath_gram.y parsing the data into a tree of several JsonPathParseItem items. After knowing about that comes the actual fun. Because, combining a jsonpath, a jsonb blob and the new set of functions implemented, it is possible to do some actual lookups in the JSON blob. jsonb_path_query() is likely the most interesting one, as it allows to directly query a portion of the JSON blob:

=# SELECT jsonb_path_query(a, '$.name') FROM characters;
     name
---------------
 "Iksdargotso"
(1 row)
=#  SELECT jsonb_path_query(data, '$.equipment.rings[0].name')
      AS ring_name
    FROM characters;
     ring_name
-------------------
 "ring of despair"
(1 row)

Note as well that there is some wildcard support, for example with an asterisk which returns all the elements of a set:

=#  SELECT jsonb_path_query(data, '$.equipment.rings[0].*') AS data
    FROM characters;
      name
-------------------
 "ring of despair"
 0.1
(2 rows)

New operators are also available and these allow for much more complex operations. One possibility is that it is possible to apply some functions within a result set as part of the expression. Here is for example how to apply floor() for a integer conversion for the weight of all the rings:

=# SELECT jsonb_path_query(data, '$.equipment.rings[*].weight.floor()')
     AS weight
   FROM characters;
 weight
--------
 0
 2
(2 rows)

This is actually only the top of cake, because one can do much more advanced context-related lookups for a JSON blob. For example you can apply a filter on top of it and fetch only a portion of them. Here is for example a way to get the names of all rings for a character which are heavier than 1kg (I am afraid that the unit is true as this applies to a ring of strength after all):

=# SELECT jsonb_path_query(data, '$.equipment.rings[*] ? (@.weight > 1)')->'name'
     AS name
   FROM characters;
       name
--------------------
 "ring of strength"
(1 row)

Note that all the most basic comparison operators are implemented and listed in the documentation, so there is a lot of fun ahead. Due to time constraints, not all the features listed in the specification have been implemented as datetime is for example lacking, still this is a nice first cut.

Note: there is a kind of mathematical easter egg in this post. Can you find it?

Cluster encryption can be used if the DBA can not or does not rely on the file system in terms of confidentiality. If this feature is enabled, PostgreSQL encrypts data  (both relations and write-ahead log) when writing to disk, and decrypts it when reading. The encryption is transparent, so the applications see no difference between the encrypted and unencrypted clusters.

PostgreSQL 9.6 with TDE on Ubuntu

In this blog, we go through the basic steps used to install PostgreSQL 9.6 with Transparent Data Encryption (TDE) on Ubuntu. You can download the patch for this here.

Create data directory for PostgreSQL:

Just for example, I am creating it at the default location:

sudo mkdir -p /usr/local/pgsql/data
sudo chown postgres:postgres /usr/local/pgsql/data

Install the libraries for readline, bison, flex, openssl, zlib and crypto:

sudo apt-get install libreadline8 libreadline-dev zlibc zlib1g-dev bison flex libssl-dev openssl

Run configure from the source code directory:

You can choose where all files will be installed by passing --prefix. Default is /usr/local/pgsql which I am using here for example. Make sure you enable openssl by passing --with-openssl:

sudo ./configure --prefix=/usr/local/pgsql --with-openssl
sudo make

The above two commands should run without errors. Now we are ready to install:

sudo make install

We can now proceed to initialize the cluster. For that, let’s switch to the postgres user:

sudo su - postgres

As a good practice, lets add the PostgreSQL binaries to PATH:

export PATH=$PATH:/usr/local/pgsql/bin

To create encrypted cluster, use the -K option to pass the initdb utility. For example:

initdb -D /usr/local/pgsql/data -K/usr/local/pgsql/keypass

Here /usr/local/pgsql/keypass is an executable file that returns either encryption key or encryption password with the appropriate prefix. In this case, we are passing the encryption_password in 8-16 characters in a simple executable file which outputs: 

encryption_password=<8-16_passphrase>

$ chmod 755 /usr/local/pgsql/keypass
$ cat /usr/local/pgsql/keypass
echo encryption_password=UrOs2k11CHiGo

Internally, PostgreSQL always uses the encryption key. If the encryption key command returns a password then a key will be generated from the password using a built-in key derivation function. Optionally, you can pass encryption_key as a hex encoded 256 bit key from any key store. 

$cat /usr/local/pgsql/keypass
echo encryption_key=<`sh /location/return-key.sh`>

On completion, initdb stores the encryption key command to postgresql.conf. Thus, the user can control the cluster using pg_ctl without passing the encryption command  again and again.

If encryption is enabled, full_page_writes must not be turned off, otherwise the server refuses to start. This is because the encryption introduces differences between data within a page, and therefore a server crash during a disk write can result in more serious damage of the page than it would do without encryption. The whole page needs to be retrieved from WAL in such a case to ensure reliable recovery.

vi postgresql.conf

full_page_writes = on

Once the PostgreSQL server is running, client applications should recognize no difference from an unencrypted cluster, except that data_encryption configuration variable is  set.

Unlike pg_ctl, some of the server applications (for  example pg_waldump) do not need the -K because  they are not able to process the postgresql.conf file. 

Since WAL is encrypted, any replication solution based on log shipping assumes that all standby servers are encrypted using the same key as their standby server. On the other hand,  logical-replication allows replication between encrypted  and unencrypted clusters, or between clusters encrypted with different keys.

To read more about the ongoing discussion on adding TDE in core PostgreSQL, please check here.

The post Install PostgreSQL 9.6 with Transparent Data Encryption appeared first on Cybertec.

There is a wide variety of indexes available in PostgreSQL. While most are common in almost all databases, there are some types of indexes that are more specific to PostgreSQL. For example, GIN indexes are helpful to speed up the search for element values within documents. GIN and GiST indexes could both be used for making full-text searches faster, whereas BRIN indexes are more useful when dealing with large tables, as it only stores the summary information of a page. We will look at these indexes in more detail in future blog posts. For now, I would like to talk about another of the special indexes that can speed up searches on a table with a huge number of columns and which is massive in size. And that is called a bloom index.

In order to understand the bloom index better, let’s first understand the bloom filter data structure. I will try to keep the description as short as I can so that we can discuss more about how to create this index and when will it be useful.

Most readers will know that an array in computer sciences is a data structure that consists of a collection of values and variables. Whereas a bit or a binary digit is the smallest unit of data represented with either 0 or 1. A bloom filter is also a bit array of m bits that are all initially set to 0.

A bit array is an array that could store a certain number of bits (0 and 1). It is one of the most space-efficient data structures to test whether an element is in a set or not.

Why use bloom filters?

Let’s consider some alternates such as list data structure and hash tables. In the case of a list data structure, it needs to iterate through each element in the list to search for a specific element. We can also try to maintain a hash table where each element in the list is hashed, and we then see if the hash of the element we are searching for matches a hash in the list. But checking through all the hashes may be a higher order of magnitude than expected. If there is a hash collision, then it does a linear probing which may be time-consuming. When we add hash tables to disk, it requires some additional IO and storage. For an efficient solution, we can look into bloom filters which are similar to hash tables.

Type I and Type II errors

While using bloom filters, we may see a result that falls into a

type I error

type II error

false positive

false negative

probabilistic data structure

The bloom filter algorithm

Let’s now understand the algorithm behind bloom filters better. As discussed earlier, it is a bit array of m bits, where m is a certain number. And we need a k number of hash functions. In order to tell whether an element exists and to give away the item pointer of the element, the element (data in columns) will be passed to the hash functions. Let’s say that there are only two hash functions to store the presence of the first element “avi” in the bit array. When the word “avi” is passed to the first hash function, it may generate the output as 4 and the second may give the output as 5. So now the bit array could look like the following:

All the bits are initially set to 0. Once we store the existence of the element “avi” in the bloom filter, it sets the 4th and 5th bits to 1. Let’s now store the existence of the word “percona”. This word is again passed to both the hash functions and assumes that the first hash function generates the value as 5 and the second hash function generated the value as 6. So, the bit array now looks like the following – since the 5th bit was already set to 1 earlier, it doesn’t make any modifications there:

Now, consider that our query is searching for a predicate with the name as “avi”. The input: “avi” will now be passed to the hash functions. The first hash function returns the value as 4 and the second returns the value as 5, as these are the same hash functions that were used earlier. Now when we look in position 4 and 5 of the bloom filter (bit array), we can see that the values are set to 1. This means that the element is present.

Collision with bloom filters

Consider a query that is fetching the records of a table with the name: “don”. When this word “don” is passed to both the hash functions, the first hash function returns the value as 6 (let’s say) and the second hash function returns the value as 4. As the bits at positions 6 and 4 are set to 1, the membership is confirmed and we see from the result that a record with the name: “don” is present. In reality, it is not. This is one of the chances of collisions. However, this is not a serious problem.

A point to remember is – “The fewer the hash functions, the more the chances of collisions. And the more the hash functions, lesser the chances of collision. But if we have k hash functions, the time it takes for validating membership is in the order of k“.

Bloom Indexes in PostgreSQL

As you’ll now have understood bloom filters, you’ll know a bloom index uses bloom filters. When you have a table with too many columns, and there are queries using too many combinations of columns  – as predicates – on such tables, you could need many indexes. Maintaining so many indexes is not only costly for the database but is also a performance killer when dealing with larger data sets.

So, if you create a bloom index on all these columns, a hash is calculated for each of the columns and merged into a single index entry of the specified length for each row/record. When you specify a list of columns on which you need a bloom filter, you could also choose how many bits need to be set per each column. The following is an example syntax with the length of each index entry and the number of bits per a specific column.

CREATE INDEX bloom_idx_bar ON foo.bar USING bloom (id,dept_id,zipcode)
WITH (length=80, col1=4, col2=2, col3=4);

length

number of bits

Bits per each column

Here is what it means in theory when we have specified length = 80 and col1=2, col2=2, col3=4. A bit array of length 80 bits is created per row or a record. Data inside col1 (column1) is passed to two hash functions because col1 was set to 2 bits. Let’s say these two hash functions generate the values as 20 and 40. The bits at the 20th and 40th positions are set to 1 within the 80 bits (m) since the length is specified as 80 bits. Data in col3 is now passed to four hash functions and let’s say the values generated are 2, 4, 9, 10. So four bits – 2, 4, 9, 10 –are set to 1 within the 80 bits.

There may be many empty bits, but it allows for more randomness across the bit arrays of each of the individual rows. Using a signature function, a signature is stored in the index data page for each record along with the row pointer that points to the actual row in the table. Now, when a query uses an equality operator on the column that has been indexed using bloom, a number of hash functions, as already set for that column, are used to generate the appropriate number of hash values. Let’s say four for col3 – so 2, 4, 9, 10. The index data is extracted row-by-row and searched if the rows have those bits (bit positions generated by hash functions) set to 1.

And finally, it says a certain number of rows have got all of these bits set to 1. The greater the length and the bits per column, the more the randomness and the fewer the false positives. But the greater the length, the greater the size of the index.

Bloom Extension

Bloom index is shipped through the contrib module as an extension, so you must create the bloom extension in order to take advantage of this index using the following command:

CREATE EXTENSION bloom;

Example

Let’s start with an example. I am going to create a table with multiple columns and insert 100 million records.

percona=# CREATE TABLE foo.bar (id int, dept int, id2 int, id3 int, id4 int, id5 int,id6 int,id7 int,details text, zipcode int);
CREATE TABLE
percona=# INSERT INTO foo.bar SELECT (random() * 1000000)::int, (random() * 1000000)::int,
(random() * 1000000)::int,(random() * 1000000)::int,(random() * 1000000)::int,(random() * 1000000)::int,
(random() * 1000000)::int,(random() * 1000000)::int,md5(g::text), floor(random()* (20000-9999 + 1) + 9999)
from generate_series(1,100*1e6) g;
INSERT 0 100000000

The size of the table is now 9647 MB as you can see below.

percona=# \dt+ foo.bar
List of relations
Schema | Name | Type  | Owner    | Size    | Description
-------+------+-------+----------+---------+-------------
foo    | bar  | table | postgres | 9647 MB | (1 row)

Let’s say that all the columns: id, dept, id2, id3, id4, id5, id6 and zip code of table: foo.bar are used in several queries in random combinations according to different reporting purposes. If we create individual indexes on each column, it is going to take almost 2 GB disk space for each index.

Testing with btree indexes

We’ll try creating a single btree index on all the columns that are most used by the queries hitting this table. As you can see in the following log, it took 91115.397 ms to create this index and the size of the index is 4743 MB.

postgres=# CREATE INDEX idx_btree_bar ON foo.bar (id, dept, id2,id3,id4,id5,id6,zipcode);
CREATE INDEX
Time: 91115.397 ms (01:31.115)
postgres=# \di+ foo.idx_btree_bar
                             List of relations
 Schema |     Name      | Type  |  Owner   | Table |  Size   | Description
--------+---------------+-------+----------+-------+---------+-------------
 foo    | idx_btree_bar | index | postgres | bar   | 4743 MB |
(1 row)

Now, let’s try some of the queries with a random selection of columns. You can see that the execution plans of these queries are 2440.374 ms and 2406.498 ms for query 1 and query 2 respectively. To avoid issues with the disk IO, I made sure that the execution plan was captured when the index was cached to memory.

Query 1
-------
postgres=# EXPLAIN ANALYZE select * from foo.bar where id4 = 295294 and zipcode = 13266;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Index Scan using idx_btree_bar on bar  (cost=0.57..1607120.58 rows=1 width=69) (actual time=1832.389..2440.334 rows=1 loops=1)
   Index Cond: ((id4 = 295294) AND (zipcode = 13266))
 Planning Time: 0.079 ms
 Execution Time: 2440.374 ms
(4 rows)
Query 2
-------
postgres=# EXPLAIN ANALYZE select * from foo.bar where id5 = 281326 and id6 = 894198;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_btree_bar on bar  (cost=0.57..1607120.58 rows=1 width=69) (actual time=1806.237..2406.475 rows=1 loops=1)
   Index Cond: ((id5 = 281326) AND (id6 = 894198))
 Planning Time: 0.096 ms
 Execution Time: 2406.498 ms
(4 rows)

Testing with Bloom Indexes

Let’s now create a bloom index on the same columns. As you can see from the following log, there is a huge size difference between the bloom (1342 MB) and the btree index (4743 MB). This is the first win. It took almost the same time to create the btree and the bloom index.

postgres=# CREATE INDEX idx_bloom_bar ON foo.bar USING bloom(id, dept, id2, id3, id4, id5, id6, zipcode)
WITH (length=64, col1=4, col2=4, col3=4, col4=4, col5=4, col6=4, col7=4, col8=4);
CREATE INDEX
Time: 94833.801 ms (01:34.834)
postgres=# \di+ foo.idx_bloom_bar
                             List of relations
 Schema |     Name      | Type  |  Owner   | Table |  Size   | Description
--------+---------------+-------+----------+-------+---------+-------------
 foo    | idx_bloom_bar | index | postgres | bar   | 1342 MB |
(1 row)

Let’s run the same queries, check the execution time, and observe the difference.

Query 1
-------
postgres=# EXPLAIN ANALYZE select * from foo.bar where id5 = 326756 and id6 = 597560;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on bar  (cost=1171823.08..1171824.10 rows=1 width=69) (actual time=1265.269..1265.550 rows=1 loops=1)
   Recheck Cond: ((id4 = 295294) AND (zipcode = 13266))
   Rows Removed by Index Recheck: 2984788
   Heap Blocks: exact=59099 lossy=36090
   ->  Bitmap Index Scan on idx_bloom_bar  (cost=0.00..1171823.08 rows=1 width=0) (actual time=653.865..653.865 rows=99046 loops=1)
         Index Cond: ((id4 = 295294) AND (zipcode = 13266))
 Planning Time: 0.073 ms
 Execution Time: 1265.576 ms
(8 rows)
Query 2
-------
postgres=# EXPLAIN ANALYZE select * from foo.bar where id5 = 281326 and id6 = 894198;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on bar  (cost=1171823.08..1171824.10 rows=1 width=69) (actual time=950.561..950.799 rows=1 loops=1)
   Recheck Cond: ((id5 = 281326) AND (id6 = 894198))
   Rows Removed by Index Recheck: 2983893
   Heap Blocks: exact=58739 lossy=36084
   ->  Bitmap Index Scan on idx_bloom_bar  (cost=0.00..1171823.08 rows=1 width=0) (actual time=401.588..401.588 rows=98631 loops=1)
         Index Cond: ((id5 = 281326) AND (id6 = 894198))
 Planning Time: 0.072 ms
 Execution Time: 950.827 ms
(8 rows)

From the above tests, it is evident that the bloom indexes performed better. Query 1 took 1265.576 ms with a bloom index and 2440.374 ms with a btree index. And query 2 took 950.827 ms with bloom and 2406.498 ms with btree. However, the same test will show a better result for a btree index, if you would have created a btree index on those 2 columns only (instead of many columns).

Reducing False Positives

If you look at the execution plans generated after creating the bloom indexes (consider Query 2),  98631 rows are considered to be matching rows. However, the output says only one row. So, the rest of the rows – all 98630 – are false positives. The btree index would not return any false positives.

In order to reduce the false positives, you may have to increase the signature length and also the bits per column through some of the formulas mentioned in this interesting blog post through experimentation and testing. As you increase the signature length and bits, you might see the bloom index growing in size. Nevertheless, this may reduce false positives. If the time spent is greater due to the number of false positives returned by the bloom index, you could increase the length. If increasing the length does not make much difference to the performance, then you can leave the length as it is.

Points to be carefully noted

1. In the above tests, we have seen how a bloom index has performed better than a btree index. But, in reality, if we had created a btree index just on top of the two columns being used as predicates, the query would have performed much faster with a btree index than with a bloom index. This index does not replace a btree index unless we wish to replace a chunk of the indexes with a single bloom index.
2. Just like hash indexes, a bloom index is applicable for equality operators only.
3. Some formulas on how to calculate the appropriate length of a bloom filter and the bits per column can be read on Wikipedia or in this blog post.

Conclusion

Bloom indexes are very helpful when we have a table that stores huge amounts of data and a lot of columns, where we find it difficult to create a large number of indexes, especially in OLAP environments where data is loaded from several sources and maintained for reporting. You could consider testing a single bloom index to see if you can avoid implementing a huge number of individual or composite indexes that could take additional disk space without much performance gain.

PostgreSQL 12 is just around the corner and therefore we already want to present some of the new features we like. One important new feature gives users and devops the chance to control the behavior of the PostgreSQL optimizer. Prepared plans are always a major concern (especially people moving from Oracle seem to be most concerned) and therefore it makes sense to discuss the way plans are handled in PostgreSQL 12.

Firing up a PostgreSQL test database

To start I will create a simple table consisting of just two fields:

db12=# CREATE TABLE t_sample (id serial, name text);
CREATE TABLE

Then some data is loaded:

db12=# INSERT INTO t_sample (name)
       SELECT 'hans' FROM generate_series(1, 1000000);
INSERT 0 1000000

db12=# INSERT INTO t_sample (name)
       SELECT 'paul' FROM generate_series(1, 2);
INSERT 0 2

Note that 1 million names are identical (“hans”) and just two people are called “paul”. The distribution of data is therefore quite special, which has a major impact as you will see later in this post.

To show how plans can change depending on the setting, an index on “name” is defined as shown in the next listing:

db12=# CREATE INDEX idx_name ON t_sample (name);
CREATE INDEX

The PostgreSQL query optimizer at work

Let us run a simple query and see what happens:

db12=# explain SELECT count(*) FROM t_sample WHERE name = 'hans';
                        QUERY PLAN
------------------------------------------------------------------
Finalize Aggregate (cost=12656.23..12656.24 rows=1 width=8)
  -> Gather (cost=12656.01..12656.22 rows=2 width=8)
     Workers Planned: 2
     -> Partial Aggregate (cost=11656.01..11656.02 rows=1 width=8)  
     -> Parallel Seq Scan on t_sample 
          (cost=0.00..10614.34 rows=416668 width=0)
        Filter: (name = 'hans'::text)
(6 rows)

In this case PostgreSQL decided to ignore the index and go for a sequential scan. It has even seen that the table is already quite large and opted for a parallel query. Still, what we see is a sequential scan. All data in the table has to be processed. Why is that? Remember: Most people in the table have the same name. It is faster to read the entire table and kick out those other ones instead of having to read almost the entire index. The planner figures (correctly) that running a sequential scan will be faster.

What you can take away from this example is that an index is not used because it exists – PostgreSQL uses indexes when they happen to make sense. If we search for a less frequent value, PostgreSQL will decide on using the index and offer us the optimal plan shown in the next listing:

db12=# explain SELECT count(*) FROM t_sample WHERE name = 'paul';
                 QUERY PLAN
------------------------------------------------
Aggregate (cost=4.45..4.46 rows=1 width=8)
  -> Index Only Scan using idx_name on t_sample 
       (cost=0.42..4.44 rows=1 width=0)
     Index Cond: (name = 'paul'::text)
(3 rows)

Optimizer statistics: Fueling good performance

If you are looking for good performance, keeping an eye on optimizer statistics is definitely a good idea. The main question now is: Which data does the optimizer keep? pg_stats contains information about each column:

db12=# \x
Expanded display is on.
db12=# SELECT *
       FROM   pg_stats
       WHERE  tablename = 't_sample'
             AND attname = 'name';
-[ RECORD 1 ] 
--------------------------+------------
schemaname                | public
tablename                 | t_sample
attname                   | name
inherited                 | f
null_frac                 | 0
avg_width                 | 5
n_distinct                | 1
most_common_vals          | {hans}
most_common_freqs         | {1}
histogram_bounds          |
correlation               | 1
most_common_elems         |
most_common_elem_freqs    |
elem_count_histogram      |

PostgreSQL keeps track over the percentage of NULL entries in the table (null_frac). The average width of a column, the estimated number of distinct values (are all different, are all values the same). Then PostgreSQL keeps a list of the most frequent entries as well as their likelihood. The histogram_bounds column will contain the statistical distribution of data. In our example you will only find entries in this field if you are looking for the “id” column. There are only two names so keeping a histogram is basically pointless. The correlation column will tell us about the physical order of rows on disk. This field can be pretty important because it helps the optimizer to estimate the amount of I/O.

Preparing plans manually

If you send a query to PostgreSQL it is usually planned when the query is sent. However, if you explicitly want to prepare a query, you can make use of the PREPARE / EXECUTE commands. Here is how it works:

db12=# PREPARE myplan(text) AS
       SELECT  count(*)
       FROM    t_sample
       WHERE   name = $1;
PREPARE
db12=# EXECUTE myplan('paul');
count
-------
 2
(1 row)

As you can see the following query will give us an indexscan:

db12=# explain EXECUTE myplan('paul');
                         QUERY PLAN
-------------------------------------------------
Aggregate (cost=4.45..4.46 rows=1 width=8)
  -> Index Only Scan using idx_name on t_sample 
     (cost=0.42..4.44 rows=1 width=0)
     Index Cond: (name = 'paul'::text)
(3 rows)

If we fall back to the more common value we will again get a parallel sequential scan:

db12=# explain EXECUTE myplan('hans');
                              QUERY PLAN
-------------------------------------------------------------------
Finalize Aggregate (cost=12656.23..12656.24 rows=1 width=8)
  -> Gather (cost=12656.01..12656.22 rows=2 width=8)
     Workers Planned: 2
     -> Partial Aggregate (cost=11656.01..11656.02 rows=1 width=8)   
     -> Parallel Seq Scan on t_sample 
          (cost=0.00..10614.34 rows=416668 width=0)
        Filter: (name = 'hans'::text)
(6 rows)

Why is that the case? In PostgreSQL life is not so straight forward. Even if we prepare explicitly we will still get a “fresh plan” before a generic plan is created. What is a generic plan? A generic plan is made assuming some constant parameters. The idea is to keep the plan and execute it multiple times in the hope that overall performance goes up due to lower planning overhead. Up to PostgreSQL 11 this process has been a bit “obscure” to most people.

Here is how the “obscure” thing works in detail. There are two ways PostgreSQL can choose for executing a prepared statement:

• It could create a new plan for every execution that considers the current parameter value. That will lead to the best possible plan, but having to plan the query for every execution can remove most of the benefit for an OLTP application, which is to avoid having to plan the same statement over and over again.
•  It could create a “generic plan” that does not take the parameter values into account. That will avoid re-planning the statement every time, but it can lead to problems during execution if the best plan depends heavily on the parameter values.

By default, PostgreSQL chooses a “middle road”: it will generate a “custom plan”
during the first 5 executions of the prepared statement that takes the parameter values
into account. From the sixth execution on, it will check if the generic plan would
have performed as well (by comparing the estimated execution costs of the custom and
the generic plan). If it thinks that the generic plan would have done just as well,
the prepared statement will always use the generic plan from that point on.
PostgreSQL 12 introduces a new variable, which allows users to control the behavior more explicitly. Let us try the same thing again and enforce a generic plan:

db12=# SET plan_cache_mode = 'force_generic_plan';
SET
db12=# PREPARE newplan(text) AS
       SELECT count(*)
       FROM   t_sample
       WHERE  name = $1;
PREPARE
db12=# explain EXECUTE newplan('hans');
                       QUERY PLAN
-----------------------------------------------------------
Finalize Aggregate (cost=12656.23..12656.24 rows=1 width=8)
  -> Gather (cost=12656.01..12656.22 rows=2 width=8)
     Workers Planned: 2
     -> Partial Aggregate (cost=11656.01..11656.02 rows=1 width=8)
     -> Parallel Seq Scan on t_sample 
          (cost=0.00..10614.34 rows=416668 width=0)
        Filter: (name = $1)
(6 rows)

db12=# explain EXECUTE newplan('paul');
                         QUERY PLAN
-------------------------------------------------------------------
Finalize Aggregate (cost=12656.23..12656.24 rows=1 width=8)
  -> Gather (cost=12656.01..12656.22 rows=2 width=8)
     Workers Planned: 2
     -> Partial Aggregate (cost=11656.01..11656.02 rows=1 width=8)
     -> Parallel Seq Scan on t_sample 
          (cost=0.00..10614.34 rows=416668 width=0)
        Filter: (name = $1)
(6 rows)

What you see here is that the plan is constant and PostgreSQL does not attempt replanning. Planning time will be cut BUT it does not necessarily mean that you always win. You might save on some CPU cycles to optimize the query but this of course means that the plan you are using is not necessarily optimal for your parameters.

plan_cache_mode: Valid parameters

If you want to play around with plan_cache_mode you can try the following values:

db12=# SET plan_cache_mode = 'force_custom_plan';
SET

db12=# SET plan_cache_mode = 'force_generic_plan';
SET

db12=# SET plan_cache_mode = 'auto';
SET

“auto”, which is the default value, resembles the traditional behavior of letting
PostgreSQL choose whether to use a generic plan or not.
You might ask what good it could be to use a prepared statement with “force_custom_plan”.
The main reason is that using prepared statements is the best way to prevent SQL injection
attacks, so it may be worth using them even if you don’t save on planning time.
If you want to learn more about PostgreSQL 12, consider checking out our blog post about optimizer support functions.

The post Tech preview: How PostgreSQL 12 handles prepared plans appeared first on Cybertec.