Solving "select count without where clause" performance issues in PostgreSQL databases

"select count without where clause" is a performance headache in most of the database engines, in this post we´ll talk about PostgreSQL engine.

In PostgreSQL we don´t have the "count" of a table anywhere, so, every time you need to "select count(1) from table", PostgreSQL will perform a sequential scan of the table, that will require large number of IO to be performed if it´s a big table. We´ll provide some solutions to this problem, please don´t miss the last one …

Solution 1

Increase server capacity and provide much more IO capacity, partition the table and use parallelism, so, these type of queries will perform much better but you will spend lots of resources (partitioning the tables, increasing the IO capacity of the server). This will not need any change from your application side and the information should be accurate.

Solution 2

Remove the "select count", yes, sometimes your application really don´t need to do a "select count" without where part. So, review with your development team the need to do so. Sometimes it´s necessary to do "select count" for paging in the frontend, to calculate the number of pages and put something like "1 of 2500", is this really necessary?, why frontend just put something like "next"?

Solution 3

You can rely on statistical information to get an approximation of the number of rows, but, that´s not accurate and it´ll depend on how often you take statistics of a single table. You have the pg_stat_all_tables/pg_stat_user_tables with column "n_live_tup" with the estimated number of live rows. This will require changes from your application side to change the "select count(1) from public.tests" to "select n_live_tup from pg_stat_user_tables where schemaname='public' and relname='tests'", and the information should not be accurate.

Solution 4

Use some analytical repository with calculated metrics like "number of rows in table" so, instead using "select count(1) from public.tests" you can use: "select numRows from aggtable where schemaname='public' and tablename='tests'" and have some ETL process or materialized view to update this information frequently, maybe each 10min, so, you´ll only have 1 process each 10min doing a "select count(1) from public.tests" to update the information in "aggtable". In this case, you need to change the application query like explained. And the information should not be totally accurate.

Solution 5

Use a staging table with the current calculated number of rows of all the requested tables and have a rewrite mechanism in the middle of the application and the database to "transform" automatically those "select count(1) from public.tests" into "SELECT totalrows FROM aggtableinfo where tableschema='public' and tablename='tests'". So, in this case, the information will be accurate and it´ll not require an application change.

We´ll implement the last solution by using two components

1. Staging table "aggtableinfo" and triggers in our PostgreSQL database to update that staging table whenever there is an insert or delete on the tables we want to improve the "select count"

2. Open source project "pgbouncer-rr", please visit the project here: GitHub - awslabs/pgbouncer-rr-patch: Adds query routing and rewriting extensions to pgbouncer, that will allow us to rewrite queries on fly without modifying the application side.

Staging table and triggers

• Create the staging table:

create table aggtableinfo (tableschema varchar(100),tablename varchar(100),totalrows bigint);

create index aggtableinfo_01_i on aggtableinfo(tableschema,tablename);

• Create the trigger function:

CREATE OR REPLACE FUNCTION public.aggtableinfo_func()
  RETURNS trigger AS
$BODY$
DECLARE
vcount integer;
vinc integer;
BEGIN
IF (TG_OP = 'DELETE') THEN
vinc=-1;
ELSIF (TG_OP = 'INSERT') THEN
vinc=1;
END IF;
 
UPDATE aggtableinfo set totalrows=(case when totalrows+vinc <0 then 0 else totalrows+vinc end) where tableschema=TG_TABLE_SCHEMA::text and tablename=TG_TABLE_NAME::text;
IF NOT FOUND THEN
EXECUTE 'SELECT COUNT(1) FROM '|| TG_TABLE_SCHEMA::text||'.'||TG_TABLE_NAME::text||'' INTO vcount;
INSERT INTO aggtableinfo (tableschema,tablename,totalrows) values(TG_TABLE_SCHEMA::text, TG_TABLE_NAME::text, vcount);
END IF;
    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

The trigger function will update staging table "aggtableinfo" whenever there is an insert or delete on the requested table. If function fails to update that staging table, it´ll execute a complete "select count" on the requested table.

• Create the trigger on the requested table:

CREATE TRIGGER aggtableinfo_trigger
  AFTER INSERT OR DELETE
  ON public.tests
  FOR EACH ROW
  EXECUTE PROCEDURE public.aggtableinfo_func();

Here we´re creating the trigger on table "tests" with clause "FOR EACH ROW", so, every single row affected by a delete or insert statement will fire the execution of the trigger. You need to consider this, as this could cause performance issues if you insert/delete multiple rows by a single sentence.

Pgbouncer-rr 

• Install pgbouncer-rr

Please follow up the instructions in pgbouncer-rr project: GitHub - awslabs/pgbouncer-rr-patch: Adds query routing and rewriting extensions to pgbouncer

# install required packages - see https://github.com/pgbouncer/pgbouncer#building
sudo yum install libevent-devel openssl-devel python-devel libtool git patch make -y

# download the latest tested pgbouncer distribution - 1.12
git clone https://github.com/pgbouncer/pgbouncer.git --branch "pgbouncer_1_12_0"

# download pgbouncer-rr extensions
git clone https://github.com/awslabs/pgbouncer-rr-patch.git

# merge pgbouncer-rr extensions into pgbouncer code
cd pgbouncer-rr-patch
./install-pgbouncer-rr-patch.sh ../pgbouncer

# build and install
cd ../pgbouncer
git submodule init
git submodule update
./autogen.sh
./configure ...
make
sudo make install

After that, you´ll need to start pgbouncer as a service, I used these configuration files to make it work with my Azure Database for PostgreSQL server:

• Pgbouncer.ini

[pgbouncer]
listen_port = 5439
listen_addr = *
auth_type = trust
auth_file = users.txt
logfile = /tmp/pgbouncer.log
pidfile = /tmp/pgbouncer.pid
admin_users = master
ignore_startup_parameters = extra_float_digits,TimeZone
pkt_buf = 32768
pool_mode = statement
rewrite_query_py_module_file = ./rewrite_query.py
rewrite_query_disconnect_on_failure = true
[databases]
testsdb = host=<servername>.postgres.database.azure.com port=5432 dbname=testsdb

• rewrite_query.py

import re
def rewrite_query(username, query):
    q1="SELECT count\(1\) FROM public.tests"
    if re.match(q1, query):
        new_query = "SELECT totalrows FROM aggtableinfo where tableschema='public' and tablename='tests'"
    else:
        new_query = query
    return new_query

• users.txt

"username" "password"

Important file here is "rewrite_query.py" where you can see the rewrite sentence, when if found the query "select count(1) FROM public.tests" pgbouncer-rr will replace it by "SELECT totalrows FROM aggtableinfo where tableschema='public' and tablename='tests'"

After that, you only need to point your application to pgbouncer endpoint (in this case, using port 5439) and your applications will have much better performance when issuing the "select count(1) FROM tests", because that query will be replaced by other query with much more performance as no need to sequentially scan all blocks of "tests" table.

With this approach you´re using also the benefits of pgbouncer, an open source connection pooler for PostgreSQL database that will help you to reduce the overall resource consumption, as it´ll reuse the connections.

As a summary, with pgbouncer-rr, we have a powerful connection pooler for PostgreSQL, but also a routing and rewrite engine to extend its capabilities, that creates another abstraction layer that allow us to improve our application performance even without touching a line of code. 

Solving locking problems with monolithically increase identifiers

We usually observe locking problems in applications that use monolithically increase identifiers for their application components, where multiple sessions are trying to obtain the next identifier, in all this situations it´s highly recommendable to use "sequences" from the database side, that will allow less locking problems within the databases:

 

 

With locking problems:

 

update serials set lastvalue=lastvalue+1 where type='invoice' returning lastvalue into vlastval;

Insert into invoices (id, …) values (vlastval, …);

 

 

For a single session, the "update serials" command will create an exclusive row lock in the row with "type='invoice'". When you have multiple sessions trying to obtain the next identifier, each session that obtains the lock will block all other sessions trying to obtain the same lock, so a locking contention appears at the database server level. Your database server "precious" time is wasting only waiting for locks to be released …, just not doing anything good for the business.

 

Without locking problems:

 

Insert into invoices (id, …) values (nextval('seq_invoice_serial'), …);

 

 

Here we obtain the next identifier by using the sequence "seq_invoice_serial", that will not generate the locks/blocks observed before.

 

The problem arrives when application logic is not using a standard number for those identifiers, so "sequences" approach seams to not work properly, by example, imagine an application component (billing) that create their billing identifiers by this formula:

 

"AYYYYMMS9999999", where:

• A=Fixed character
• YYYY=Year
• MM=Month
• S=Fixed character
• 9999999=Sequential number reset to 1 on each new Month

 

So, we could have this identifiers:

A202010S0001234, A202010S0001235, A202011S0000001, A202011S0000001, …

 

This special identifiers doesn´t match exactly with the database "sequences" approach. With sequences we can control the increment (increment by 1,2…), the minvalue, the maxvalue, if cycling values, but we can´t have a sequential string or reset based in a timeframe, here you have the sequence create command:

 

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name [ AS { SMALLINT | INT | BIGINT } ] [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ]

 

In the following example we will illustrate and measure how could we remove locking problems in this scenario by still using the "sequence" approach.

 

Original billing sequential numbering

Here we´ll show a generic billing sequential number process:

 

Test datamodel

 

create table serials (type varchar(100), sequence bigint, yearmonth varchar(6), lastvalue varchar(20));

create index serials_01_I on serials(type);

insert into serials values ('invoice',0,'','');

create table invoices (code varchar(20), fx_datetime timestamp);

 

 

Function to get next identifier

 

create function get_next_invoice()
returns varchar as $$
declare
	vlastval varchar:='';
begin
update serials set
	sequence=(case when to_char(now(),'YYYYMM')=yearmonth then sequence+1 
							else 1
			 end),
	yearmonth=(case when to_char(now(),'YYYYMM')=yearmonth then yearmonth
							else to_char(now(),'YYYYMM')
			  end),
	lastvalue=(case when to_char(now(),'YYYYMM')=yearmonth then 'A'||to_char(now(),'YYYYMM')||LPAD((sequence+1)::varchar,7,'0') 
							else 'A'||to_char(now(),'YYYYMM')||'0000001'
			 end)
where type='invoice'			  
returning lastvalue into vlastval;
return vlastval;
end;
$$ language plpgsql;

 

 

Here we use the serials table to update always the same row (to guarantee uniqueness and atomically increase identifiers), checking if we are in a new month, we set the sequence to 1 and generating the lastvalue as a string as the application request "AYYYYMMS9999999"

Example function usage

 

insert into invoices select get_next_invoice(), current_timestamp;

 

Benchmark

To create a benchmark, we will create a file: "get_invoice.sql" with the only command:

insert into invoices select get_next_invoice(), current_timestamp;

 

And will execute the pgbench command (https://www.postgresql.org/docs/10/pgbench.html:(

pgbench -h <servername>.postgres.database.azure.com -p 5432 -U frpardil@<servername> --protocol=prepared  -P 1 --time=20 --client=30 -r --jobs=6 test1 -f get_invoice.sql

• -r => to show per statement latencies
• -P 1 => to show metrics each 1 second
• --time=20 => 20 seconds of test
• --client=30 => 30 different sessions
• --jobs=6 => 6 jobs managing the client sessions

 

Test server is an Azure Database for PostgreSQL server, Tier: General Purpose, version:10, 4vCores, public IP access, (https://docs.microsoft.com/en-us/azure/postgresql/concepts-pricing-tiers)

 

pgbench -h <servername>.postgres.database.azure.com -p 5432 -U frpardil@<servername> --protocol=prepared  -P 1 --time=20 --client=30 -r --jobs=6 test1 -f get_invoice.sql
starting vacuum...ERROR:  relation "pgbench_branches" does not exist
(ignoring this error and continuing anyway)
ERROR:  relation "pgbench_tellers" does not exist
(ignoring this error and continuing anyway)
ERROR:  relation "pgbench_history" does not exist
(ignoring this error and continuing anyway)
end.
progress: 3.1 s, 29.8 tps, lat 75.596 ms stddev 44.555
progress: 4.0 s, 345.6 tps, lat 86.734 ms stddev 44.257
progress: 5.0 s, 284.0 tps, lat 106.031 ms stddev 74.786
progress: 6.0 s, 321.0 tps, lat 90.318 ms stddev 64.367
progress: 7.0 s, 265.0 tps, lat 117.689 ms stddev 137.011
progress: 8.0 s, 308.0 tps, lat 96.085 ms stddev 63.510
progress: 9.0 s, 247.0 tps, lat 117.740 ms stddev 130.024
progress: 10.0 s, 331.0 tps, lat 91.790 ms stddev 104.197
progress: 11.0 s, 276.0 tps, lat 108.908 ms stddev 121.539
progress: 12.0 s, 269.0 tps, lat 109.838 ms stddev 107.313
progress: 13.0 s, 303.0 tps, lat 99.651 ms stddev 84.757
progress: 14.0 s, 300.0 tps, lat 93.716 ms stddev 77.808
progress: 15.0 s, 295.0 tps, lat 106.247 ms stddev 136.072
progress: 16.0 s, 305.0 tps, lat 94.900 ms stddev 88.672
progress: 17.0 s, 272.0 tps, lat 108.254 ms stddev 103.558
progress: 18.0 s, 224.0 tps, lat 141.480 ms stddev 262.932
progress: 19.0 s, 293.0 tps, lat 106.081 ms stddev 131.669
progress: 20.0 s, 292.0 tps, lat 96.766 ms stddev 87.897
transaction type: get_invoice.sql
scaling factor: 1
query mode: prepared
number of clients: 30
number of threads: 6
duration: 20 s
number of transactions actually processed: 5032
latency average = 103.273 ms
latency stddev = 115.041 ms
tps = 245.795076 (including connections establishing)
tps = 252.400446 (excluding connections establishing)
statement latencies in milliseconds:
       103.440  insert into invoices select get_next_invoice(), current_timestamp;

 

Locks when pgbench is executing

(from https://wiki.postgresql.org/wiki/Lock_Monitoring)

 

  

 

We observe multiple blocked sessions during all the test execution.

 

In the test results, we see up to 252 Transactions Per Second (tps), a total of 5032 executions, so, in 20 seconds we correctly registered 5032 invoices.

Improved atomic billing sequential numbering

Now, we are going to generate a similar optimized function to improve these numbers and remove locks:

Create sequence

 

CREATE SEQUENCE IF NOT EXISTS seq_invoice_serial AS BIGINT INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 NO CYCLE OWNED BY NONE;

 

Function to get next identifier

 

create or replace function get_next_invoice_improved()
returns varchar as $$
declare
	vlastval varchar;
begin
update serials set	
	yearmonth=to_char(now(),'YYYYMM'),
	sequence=(select setval('seq_invoice_serial', 1)),
	lastvalue='A'||to_char(now(),'YYYYMM')||'0000001'
where type='invoice' and yearmonth<>to_char(now(),'YYYYMM')
returning lastvalue into vlastval;
if vlastval is null then
	select  'A'||to_char(now(),'YYYYMM')||LPAD((nextval('seq_invoice_serial'))::varchar,7,'0') into vlastval;
end if;
return vlastval;
end;
$$ language plpgsql;

 

 

Here we use the serials table to update the same row, only when we are in a different month that the already saved, so, only 1 single update will take place every month, in this update we also reset the sequence "seq_invoice_serial" to 1, so we start again for each changed month, but will use the normal sequence to generate the application required format string with a simple select over the sequence: select 'A'||to_char(now(),'YYYYMM')||LPAD((nextval('seq_invoice_serial'))::varchar,7,'0') into vlastval;

Benchmark

To create a benchmark, we will create a file: "get_invoice_improved.sql" with the only command:

insert into invoices select get_next_invoice_improved(), current_timestamp;

 

In the same server, we execute the same pgbench command using the new get_invoice_improved.sql file:

 

 

pgbench -h <servername>.postgres.database.azure.com -p 5432 -U frpardil@<servername> --protocol=prepared  -P 1 --time=20 --client=30 -r --jobs=6 test1 -f get_invoice_improved.sql
progress: 3.5 s, 39.9 tps, lat 63.407 ms stddev 42.206
progress: 4.0 s, 633.8 tps, lat 48.170 ms stddev 13.816
progress: 5.0 s, 589.9 tps, lat 50.579 ms stddev 12.461
progress: 6.0 s, 638.1 tps, lat 47.403 ms stddev 2.199
progress: 7.0 s, 649.0 tps, lat 46.602 ms stddev 1.840
progress: 8.0 s, 594.0 tps, lat 50.016 ms stddev 13.926
progress: 9.0 s, 617.0 tps, lat 48.574 ms stddev 4.460
progress: 10.0 s, 593.0 tps, lat 51.081 ms stddev 12.930
progress: 11.0 s, 635.0 tps, lat 46.966 ms stddev 1.955
progress: 12.0 s, 566.9 tps, lat 53.043 ms stddev 24.858
progress: 13.0 s, 574.1 tps, lat 52.268 ms stddev 16.469
progress: 14.0 s, 631.0 tps, lat 47.366 ms stddev 1.824
progress: 15.0 s, 549.0 tps, lat 54.685 ms stddev 29.363
progress: 16.0 s, 634.0 tps, lat 47.730 ms stddev 3.021
progress: 17.0 s, 619.0 tps, lat 48.068 ms stddev 2.890
progress: 18.0 s, 636.0 tps, lat 47.068 ms stddev 2.197
progress: 19.0 s, 596.0 tps, lat 50.293 ms stddev 12.967
progress: 20.0 s, 644.0 tps, lat 46.581 ms stddev 1.508
transaction type: get_invoice_improved.sql
scaling factor: 1
query mode: prepared
number of clients: 30
number of threads: 6
duration: 20 s
number of transactions actually processed: 10260
latency average = 49.356 ms
latency stddev = 13.588 ms
tps = 507.757804 (including connections establishing)
tps = 523.664725 (excluding connections establishing)
statement latencies in milliseconds:
        49.840  insert into invoices select get_next_invoice_improved(), current_timestamp;

 

Locks when pgbench is executing

 

 

We don´t observe any session being blocked during all the test execution.

 

In the test result, we see up to 523 Transactions Per Second (tps), a total of 10260 executions, so, in 20 seconds we correctly registered 10260 invoices, double number of invoices than old locking method.

 

So, even it seams a little complex, we can remove the locking problem when using user defined logic for monolithically increase identifiers by still using the sequential approach, with some code.