Authentication monitoring in PostgreSQL

How about a situation when you want to log all the login attempts made to your PostgreSQL server. Yes, one way would be to read your log files and find out all the information related to login attempts and then transfer to them some other file, etc. to further use this information. Now, this is one way of doing it but it might pose some challenges like time and memory consumption in reading from those long files, additionally this is going to incur a lot of I/O which might be a serious issue when you are on cloud. During my time working for Zalando, we came across this problem and decided to write a Postgres extension to handle this efficiently.

And that's the story motivation behind the development of this extension - pg_auth_mon. In order to use this, include it in shared_preload_libraries, and then run CREATE EXTENSION pg_auth_mon on your server. Once this extension is in place, all the log in attempts to your server will be saved in a view called pg_auth_mon in the following way.

Now, a caveat to remember here is that all the information in this view would be lost at the server restart.

If you are interested in understanding what is happening inside the extension, here is a brief description on it. Basically, at the time of login  prev_client_auth_hook redirects the control to this extension. And it is here in function auth_monitor, all the details from the connection like, authentication method, user name, etc. which are available in Port data structure are read and saved in the required view. It also records the the time of the last login attempt. So, it might help in understanding if there's some  security attack attempted or a genuine mistake by the username. 

The underlying data structure used here is hash_table, which makes it easy to search for the usernames if they have been already logged in before. Additionally, it also logs the attempts made using incorrect usernames i.e. usernames that do not exist in the database. Now, in order to be mindful of the space it takes in case of invalid username, all such attempts are counted in a single row and the username column is left blank for this. So, one drawback in this attempt is to not able to know what all incorrect usernames were tried by an attacker (if any) in such cases.

In latest version, the code also takes care of the case when a user is renamed since the last login attempt. It has an additional field login_name_at_last_attempt to provide the last username used.

Using parallel sequential scan in PostgreSQL

Parallel sequential scan is the first parallel access method in PostgreSQL and is introduced in version 9.6.  The committer of this feature and my colleague at EnterpriseDB Robert Haas wrote an awesome blog on it, there is another great blog by another PostgreSQL committer and my colleague Amit Kapila. Both of these blogs explain this access method, its design, usage, and related parameters. 

Still, I could not help but notice that there are curiosities around the usage of this access method. Every now and then I could see a complaint saying parallel sequential scan is not getting selected or it is degrading the performance of a query.  So, I decided to write this blog to cater more practical scenarios and specifically focus on its less talked about aspect  -- where parallel sequential scan would (should) not improve the performance.

Before diving into the details of parallel SeqScan, let's first understand the basic infrastructure and terminology related to it in PostgreSQL. The processes that run in parallel and scan the tuples of a relation are called parallel workers or workers in short. There is one special worker namely leader which co-ordinates and collects the output of the scan from each  of the worker. This worker may or may not participate in scanning the relation depending on it's load in dividing and combining processes. End users can also control the involvement of leader in relation scan by GUC parameter parallel_leader_participation, it is a boolean parameter. 

Now, let's understand the concept of parallel scan in PostgreSQL by a simple example.

• Let there be a table T (a int, b int) containing 100 tuples
• Let's say we have two workers and one leader,
• Cost of scanning one tuple is 10
• Cost of communicating a tuple from worker to leader is 20
• Cost of dividing the tuples among workers is 30
• For simplicity, let's assume that leader gives 50 tuples to each of the worker

Now, let's analyse if parallel scan will be faster than non parallel scan,

Cost of SeqScan = 10*100 = 1000
Cost of Parallel SeqScan = 30 + (50 * 10)  + (50 * 20) * 2 = 2530

Here, we can see that though the cost of scanning the tuples is halved yet the cost of combining the total result is enough to make the overall cost of parallel SeqScan higher than non parallel SeqScan.

Now, let's say we want to list only the tuples which have a > 80, and there are only 20 (say) such tuples, then cost of SeqScan will remain same, but cost of parallel SeqScan can be given as,

Cost of Parallel SeqScan = 30 + (50 * 10) + (10 * 20) * 2 =  730

Hence, parallel SeqScan is likely to improve the performance of queries that require scanning a large amount of data but only few of them satisfy the selection criteria. To generalise this,

Cost of SeqScan = Cost of scanning one tuple * number of tuples

Cost of parallel SeqScan = Cost of dividing the work among workers + cost of combining the work
                                            from workers + cost of work done by a worker * number of workers

Let's dive into it a bit more,

Cost of dividing the work among workers is fairly constant depending on the relation size

Cost of combining the the work from workers = cost of communicating the selected tuples from each
                                                                              worker to the leader

Cost of work done by a worker = cost of scanning a tuple * number of tuples the respective worker
                                                      has received

Now, we can see that the cost of combining the work is dependent on the number of tuples received by each worker. Now, for the queries where all or almost all of the tuples are in the final result, we will paying more cost than its non parallel flavour, first in scanning the tuple and second in combining it to the final result.

In PostgreSQL, the cost determining the cost of dividing the work among workers is given as parallel_setup_cost, the cost of communicating the tuple from worker to leader is given by parallel_tuple_cost, and the number of workers is upper bounded by the GUC max_parallel_workers_per_gather.

So, if you are using a system a high frequency multiple processor then lowering the parallel_setup_cost and parallel_tuple_cost will help in selection of parallel scans. If there are not many processes running in parallel, then increasing max_parallel_workers_per_gather can leverage more parallel processes to improve the query performance. Another point to note is that the number of workers is further capped by max_worker_processes.

Performance of parallel index scans in PostgreSQL

In this blog will continue the discussion of parallel query in PostgreSQL. In the previous blog of this series by my colleague we learned about parallel index scans, its design in PostgreSQL and the performance improvement achieved for a few queries on the industrial benchmark of TPC-H. Therein we analysed the performance improvement only for a small factor of 20 (database size was approximately 20GB). But the performance benefits realised by parallel operators aren’t that significant until we leverage them for higher scale factors. Hence, in this blog we will analyse the performance of parallel index scans on 300 scale factor.

For this experiment we used TPC-H inspired benchmark for PostgreSQL.  We used 300 scale factor, which gives 300+ GB of database, depending on the available indexes, etc. Additional indexes we created were on columns (l_shipmode, l_shipdate, o_orderdate, o_comment). We tuned following parameters,

• random_page_cost = 0.1
• seq_page_cost = 0.1
• effective_cache_size = 10GB
• shared_buffers = 10GB
• work_mem = 1GB

In the table below, we compared the performance of the queries using parallel-index scans on v10 to their performance in v9.6. Note that in v9.6 parallel seq scans and parallel nested loop joins were available. Additionally, we tabulated the total contribution of parallel index-scan for each of the queries. All the values of timing are in seconds.

TPC-H query	On v9.6	On v10	Contribution of PIS
6	883	155	137
12	740	717	122
14	422	111	53
15	2295	1138	157
17	17724	3376	16

From the above table, it is clear that the benefits of parallel index scans are significant on large amounts of data as well. To get a better understanding of how this scan method benefits the queries in question, we studied their query plans on both versions. On analysing those query plans we found two primary explanations for the benefits attained:

• Contribution of parallel index scan in total execution time of the query

If the most time-consuming operation in a query is index scan then parallelising it is likely to improve query performance significantly, e.g. Q6 and Q12. To be more precise have a look at the query plan of Q6,

Q6 Query plan on v9.6

———————————

Limit

 ->  Aggregate  

       ->  Bitmap Heap Scan on lineitem

            ->  Bitmap Index Scan on idx_lineitem_shipdate

Execution time: 882812.376 ms

Q6 Query plan on v10

———————————

Limit

->  Finalize Aggregate

            ->  Gather  

                 ->  Partial Aggregate

                    -> Parallel Index Scan using idx_lineitem_shipdate on lineitem                          

      Execution Time: 155619.579 ms

Here, scan is the only time consuming operator, hence, parallelising it improves the performance of the query. Similarly for Q12, the index scan is on lineitem which is the largest table of database. Once it is scanned in parallel, the join above it could also be done in parallel and hence the improvement in performance.

On the other hand, the benefits are not so pronounced when the plan is more complex and involves a number of operators with index scan as one of the less significant operator in terms of total execution time of the query.

• Operators above it in plan tree could also leverage parallelism

In some of the queries, it has been observed that even when the contribution of parallel index scan is not much in total execution time of the query, the benefits are significant, e.g. Q15 and Q17. In such cases the benefits are not coming from parallel index scans alone but from the fact that now more operators could be pushed to workers and hence extend parallelism upto higher levels. To explain this observation, query plan for Q15 is used:

Q15 Query plan on v9.6

—————————————

Limit

  InitPlan 1 (returns $0)

    …

  -> Merge Join  

       Merge Cond: (lineitem.l_suppkey = supplier.s_suppkey)

      -> GroupAggregate  

        Group Key: lineitem.l_suppkey

        -> Sort

            -> Bitmap Heap Scan on lineitem

                  -> Bitmap Index Scan on idx_l_shipdate

            -> Index Scan using supplier_pkey on supplier  

Execution time: 2297222.717 ms

Q15 Query plan on v10

—————————————

Limit

 InitPlan 1 (returns $0)

    …

  -> Nested Loop  

       ->  Finalize GroupAggregate

             Group Key: lineitem.l_suppkey

              ->  Gather Merge  

                   ->  Sort  

                       ->  Partial HashAggregate

                         Group Key: lineitem.l_suppkey

                            ->  Parallel Index Scan using idx_lineitem_shipdate on Lineitem

->  Index Scan using supplier_pkey on supplier

Execution Time: 1133723.985 ms

In this case, with the introduction of parallel index scan the plan structure changed altogether. With parallel index scan, the aggregates are pushed to workers and the join method is also changed, eventually improving query performance. This shows that it may not be necessary for parallel index scan to be the most time consuming operator for it to improve query performance, rather having it may help other operators to use parallelism which could not be done otherwise.

Overall, we saw there are significant benefits of parallel index scans on large volume of data as well. Thus, aiming towards a more OLAP compatible and scalable version of PostgreSQL. Stay tuned to know about more groundbreaking features in PostgreSQL.