PostgreSQL - Trigger-based Auditing

Sometimes we need an auditing solution for some tables in our databases and face some of the following requirements:

• Auditing from application side is not an option for us as we are using a third-party application, so, we don´t have direct access to modify the application code and that third party application doesn´t provide those auditing capabilities.
• We need to access the auditing records from inside the server, by using SQL commands.
• We have huge transactional activity in some tables that we don´t need to audit, we just need to audit some subset of tables.
• We need a standard solution to work between different cloud providers or on-prem without configuration changes.

Proposal

In this scenario, we should consider trigger-based auditing with specific permission policy.

We can create triggers in the tables we want to audit, so we can collect the auditing data properly in separate table/tables with following benefits:

• Trigger-based auditing don´t need any application side change to be applied.
• Trigger-based auditing can be enabled for selected tables, no need to enable auditing for whole server/tables.
• Trigger-based auditing is a transparent solution that should work across different cloud providers or on-prem configurations (as far as hstore extension is available, see "Preparation" section).
• Trigger-based auditing will allow us to access the audited data using standard SQL commands.

Implementation

We´ll use Audit trigger 91plus , git code can be found here: audit-trigger 

Preparation

Hstore extension

This solution requires hstore extension, so, in our Azure Database for PostgreSQL Flexible server we should allow that extension to be used: PostgreSQL extensions in Azure Database for PostgreSQL - Flexible Server . Apart from that, the provided script will create a schema called audit with some functions, table, view that will help in the triggers creation and to maintain the auditing records.

Creating schema, table, view and auditing functions

We need to execute the contents of audit-trigger  in the database we want to enable our trigger-based auditing solution.

Usage

Here we have some usage examples: Audit trigger 91plus , pasting here some of them:

- Auditing table "target_table_name":

SELECT audit.audit_table('target_table_name');

"The table will now have audit events recorded at a row level for every insert/update/delete, and at a statement level for truncate. Query text will always be logged."

- Canceling auditing on table "target_table_name":

DROP TRIGGER audit_trigger_row on target_table_name;
DROP TRIGGER audit_trigger_stm on target_table_name;

- Auditing table "target_table_name" , without query text, without some columns (version_col, changed_by, changed_timestamp):

"If you want finer control use audit.audit_table(target_table regclass, audit_rows boolean, audit_query_text boolean, excluded_cols text[]) or CREATE TRIGGER the audit trigger yourself. For example:

SELECT audit.audit_table('target_table_name', 'true', 'false', '{version_col, changed_by, changed_timestamp}'::text[]);

... would create audit triggers on target_table_name that record each row change but omit the query text (the 'false' argument) and omit the version_col, changed_by and changed_timestamp columns from logged rows. An UPDATE that only changes ignored columns won't result in an audit record being created at all."

- Check tables with auditing enabled:

select * from audit.tableslist;

schema | auditedtable
--------+--------------
 public | test

Here we are auditing table test in public schema.

- Geeting the audited data:

select * from audit.logged_actions;

event_id | schema_name | table_name | relid | session_user_name |       action_tstamp_tx        |       action_tstamp_stm       |       action_tstamp_clk       | transaction_id | application_name | client_addr  | client_port |        client_query        | action |   row_data   | changed_fields | statement_only
----------+-------------+------------+-------+-------------------+-------------------------------+-------------------------------+-------------------------------+----------------+------------------+--------------+-------------+----------------------------+--------+--------------+----------------+----------------
        1 | public      | test       | 25422 | frpardil          | 2023-07-31 07:57:42.266933+00 | 2023-07-31 07:57:42.266933+00 | 2023-07-31 07:57:42.269395+00 |        3070917 | psql             | 47.61.102.31 |       53673 | update test set col='321'; | U      | "col"=>"123" | "col"=>"321"   | f

Here we can observe the query used: update test set col='321', previous value on that column: "col"=>"123" and change applied: "col"->"321".

Limitations

Here we´ll show some limitations or concerns to consider before using this triggers-based auditing:

• Performance: triggers will create an overhead for each DML activity executed on the audited tables.
• Locking: Trigger-based auditing uses a single table to save all auditing records, this could have some locking issues for high auditing activity.
• Testing, we should test trigger-based auditing before applying it to any production environment to measure the performance impact.
• Only DML activity, the trigger-based auditing is limited to DML activity (insert, update, delete), we can´t audit SELECT statements using this method.

PostgreSQL pg_dump filtering data by using Row Level Security (RLS)

pg_dump command doesn´t provide a native way to execute the dump based in a filter criteria. 

Sometimes we need such feature in order to dump only a part of a table without needing to create other artifacts to do so. We might also need this feature to have greater parallelism dump control within big tables.

For that reason in this post I´ll create an example by using RLS (Row Level Security) to allow partial dumps by using pg_dump tool.

Another way to obtain a filtered dump could be by using staging tables. You can create staging tables that contains just the data to be exported, in this case, you need to execute a previous step before executing pg_dump to create those tables based in some SQL clause.

By example, if I need to export just only "report_id" between 1 and 3 in table "report_logs", I could create a staging table "report_logs_stg1" like this: 

create table report_logs_stg1 as select * from report_logs where report_id between 1 and 3;

and execute the pg_dump just only for that table: 

pg_dump ... -t report_logs_stg1 ...

in the target server, after importing, I´ll need some additional step to merge the data from report_logs_stg1 into the actual table name:

insert into report_logs select * from report_logs_stg1;

This approach will need to write in source server and will require some free space to allocate the staging tables.

Our proposed solution for this should be using RLS, so you can define some policies that will apply to some users when accessing the table data by using SQL conditional expressions. These users will be used only by pg_dump processes, so RLS policies will only apply to pg_dump, it will not affect other applications working with the database.

Lets put all together in an example:

Situation

I have a database with some small tables and a big one "table1". This table has a column "report_id" with different values: 1,2,3,4 and other timestamp column "ts". Database is in use by other applications.

Request

I need to export the complete database by using parallelism, 4 parallelism degree, (both at table and data levels within table "table1") discarding old rows from "table1" ("ts" < 2years ago).

Solution

To achieve this, we can use parallelism at table level by using parameter "--jobs" in pg_dump command, with this option the pg_dump process will span multiple dump processes each of them will take care of some of the tables (but each table will be dumped by only one process).

To use parallelism at data level within table "table1" and to discard rows based in timestamp, we´ll use RLS to filter the data based in the username but letting other applications working as usual.

Data example, table1,...table16, table1 having "report_id" from 1 to 4 with mixed old and new rows based in column "ts":

CREATE TABLE TABLE1 (REPORT_ID BIGINT, TS TIMESTAMP(6));
INSERT INTO TABLE1 SELECT 1, NOW()-INTERVAL '10 YEARS' FROM GENERATE_SERIES(1,100);
INSERT INTO TABLE1 SELECT 2, NOW()-INTERVAL '10 YEARS' FROM GENERATE_SERIES(1,100);
INSERT INTO TABLE1 SELECT 3, NOW()-INTERVAL '10 YEARS' FROM GENERATE_SERIES(1,100);
INSERT INTO TABLE1 SELECT 4, NOW()-INTERVAL '10 YEARS' FROM GENERATE_SERIES(1,100);
INSERT INTO TABLE1 (REPORT_ID, TS) SELECT REPORT_ID, NOW() FROM TABLE1;

CREATE TABLE TABLE2 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE3 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE4 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE5 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE6 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE7 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE8 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE9 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE10 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE11 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE12 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE13 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE14 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE15 AS SELECT * FROM TABLE1 LIMIT 10;
CREATE TABLE TABLE16 AS SELECT * FROM TABLE1 LIMIT 10;

Users test1, .. test4 to use for pg_dump parallel processes:

CREATE ROLE TEST1 WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD '<password>';
CREATE ROLE TEST2 WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD '<password>';
CREATE ROLE TEST3 WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD '<password>';
CREATE ROLE TEST4 WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD '<password>';

GRANT CONNECT ON DATABASE DB TO TEST1;
GRANT CONNECT ON DATABASE DB TO TEST2;
GRANT CONNECT ON DATABASE DB TO TEST3;
GRANT CONNECT ON DATABASE DB TO TEST4;

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO TEST1;
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO TEST2;
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO TEST3;
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO TEST4;

Policies drop:

DROP POLICY DUMP_RLS_1 ON TABLE1;
DROP POLICY DUMP_RLS_2 ON TABLE1;
DROP POLICY DUMP_RLS_3 ON TABLE1;
DROP POLICY DUMP_RLS_4 ON TABLE1;

Policies creation:

CREATE POLICY DUMP_RLS_1 ON TABLE1 FOR ALL TO PUBLIC USING ((REPORT_ID =1 AND TS>NOW()-INTERVAL '2 YEARS' AND CURRENT_USER='TEST1') OR CURRENT_USER NOT IN ('TEST1','TEST2','TEST3','TEST4'));
CREATE POLICY DUMP_RLS_2 ON TABLE1 FOR ALL TO PUBLIC USING ((REPORT_ID =2 AND TS>NOW()-INTERVAL '2 YEARS' AND CURRENT_USER='TEST2') OR CURRENT_USER NOT IN ('TEST1','TEST2','TEST3','TEST4'));
CREATE POLICY DUMP_RLS_3 ON TABLE1 FOR ALL TO PUBLIC USING ((REPORT_ID =3 AND TS>NOW()-INTERVAL '2 YEARS' AND CURRENT_USER='TEST3') OR CURRENT_USER NOT IN ('TEST1','TEST2','TEST3','TEST4'));
CREATE POLICY DUMP_RLS_4 ON TABLE1 FOR ALL TO PUBLIC USING ((REPORT_ID =4 AND TS>NOW()-INTERVAL '2 YEARS' AND CURRENT_USER='TEST4') OR CURRENT_USER NOT IN ('TEST1','TEST2','TEST3','TEST4'));

Lets explain dump_rls_1 policy: this policy will force data to be retrieved to match following criteria: "report_id"=1 and "ts" > 2years ago and current_user=test1 (so, when connected with user=test1, the connection will only retrieve report_id=1 and ts>2years ago. Apart of that there is an "or CURRENT_USER NOT IN ('TEST1','TEST2','TEST3','TEST4')", so, if user currently connected is different from test1, ..test4, it could see all the data in the table without restriction.

So, lets summarize the criteria based on user connected:

test1 => will take report_id=1 and ts> 2years ago from table "table1"

test2 => no data from table "table1"

test3 => no data from table "table1"

test4 => no data from table "table1"

other user => all data from table "table1"

Enable row level security for table1 and force it to use even for owner user:

ALTER TABLE TABLE1 ENABLE ROW LEVEL SECURITY;

ALTER TABLE TABLE1 FORCE ROW LEVEL SECURITY;

Dump processes:

export PGPASSWD=<password>
pg_dump -h <servername> -U test1 -s -d db -f db.schema.dump

nohup pg_dump -h <servername> -U test1 -Fd -j 4 --enable-row-security -a -d db -f db.data1.dump &

nohup pg_dump -h <servername> -U test2 -t table1 --enable-row-security -a -d db -f db.data2.dump &
nohup pg_dump -h <servername> -U test3 -t table1 --enable-row-security -a -d db -f db.data3.dump &
nohup pg_dump -h <servername> -U test4 -t table1 --enable-row-security -a -d db -f db.data4.dump &

First pg_dump is a schema only export, to get all the tables creation and definition without data.

Second pg_dump is a directory format export with 4 parallel connections, it will take a complete database dump in a table parallelized mode by using user "test1", so, for big table "table1" will only take those rows specified by the policy criteria dump_rls_1.

Third and other pg_dump processes will only export "table1" by using users test2,..,test4, so each dump will take only the allowed rows from the policies definitions dump_rls_2, .., dump_rls_4.

As you see, last 4 pg_dump processes start with "nohup" and end by "&" this will launch all these processes in parallel. So we could span up to 7 parallel parallel dump processes (4 for the first pg_dump, and other 3 for the other pg_dump commands), as our database had only 1 big table, first pg_dump process will finish quickly and will keep only 1 dump connection, so the initial spike in number of dump processes will not be so long in time, after that, only 4 parallel processes will remain exporting rows from "table1".

Result

You can use the RLS approach to execute partial exports from big tables without needed to create staging tables in source server (by example, when your source server is out of space and you can´t create any other object), you can also use RLS approach to have parallel exports at data level not only at table level.

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.