Leveraging PostgreSQL statistics for min() and max() approximation.

Sometimes you have a big table (terabytes or more) and you want to know what's the minimal or maximal, or average value of some column. This can happen when you prepare your training and testing datasets for your latest Machine Learning (a.k.a AI) project!

Let's assume we have this big table with following structure.

>>> \d my_table
                Table "public.my_table"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 userid      | integer |           |          |
 date        | bigint  |           |          |
 description | text    |           |          |
 body        | text    |           |          |
 id          | uuid    |           | not null |
Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id)

In my test environment, table size is 12 TB and has approximately 3 billions rows.

Method 1. Full table scan

Let's imagine you take the simplest approach:

>>> SELECT min(date) from my_table;
... sound of grass growing ...
...
      min
---------------
 1651924922126
(1 row)

Time: 878291.856 ms (14:38.292)

This sledgehammer method works but requires real patience if your data is really big. Such query can take minutes or hours to complete. Here it was below 15 minutes, as the dataset is only 12 TB.

Metod 2. Indexes

You can create indexes on all columns you need. This will speed up *some aggregates* including min() and max(). Here, example after creating an index on the date column:

>>> SELECT min(date) FROM my_table;
      min
---------------
 1651924922126
(1 row)

Time: 2.177 ms

That was fast! Unfortunately, there are many situations you can't create indexes or the approach is not practical (either you don't own the system, or there is too many columns to be indexed, or you don't have enough disk space for the indexes).

Method 3. Sampling

You can use TABLESAMPLE clause to select only a portion of the table, like 0.5% or 0.1% in following examples:

>>> SELECT min(date) AS samp_min_date FROM my_table tablesample system(0.05);
      min
---------------
 1651941070954
(1 row)

Time: 12137.988 ms (00:12.138)

>>> SELECT min(date) FROM crm.emails_sent tablesample system(0.1);
      min
---------------
 1651931602939
(1 row)

Time: 24582.320 ms (00:24.582)

That's faster than full table scan. Sampling with TABLESAMPLE is nice and good enough in many cases.

Method 4. Database statistics

If you know that RDBM systems keep statistics on attributes, you can use that to achieve even better precision without scanning through tons of data!

PostgreSQL keeps that too and you can access the data in in the pg_stats system view. Here we use pg_stats.histogram_bounds column which is of anyarray type hence it needs special casting to be useful.

For any numeric (integer, float etc) columns, this expression will yield approximated minimum value from the whole dataset: (histogram_bounds :: text :: numeric[])[1] from pg_stats where tablename='my_table' and attname='my_column'

Examples - same expression works for date and userid columns:

>>> select (histogram_bounds :: text :: numeric[])[1] as stat_min_date
  from pg_stats
 where tablename = 'my_table' and attname = 'date';
  
 stat_min_date
---------------
 1651932598261
(1 row)

Time: 1.297 ms

>>> select (histogram_bounds :: text :: numeric[])[1] as stat_min_userid
  from pg_stats
 where tablename = 'my_table' and attname = 'userid';
 
 stat_min_userid
-----------------
            2038
(1 row)
Time: 1.237 ms

That's really fast, the results are returned almost instantly even without index on respective columns!

But how accurate is it? We need to remember any statistically - sampled results are not super precise. In my table, the actual value of min(userid) is zero but here you see 2038.

But it's true to the order of magnitude. If you want more precise sampling, you can ask postgres to analyze your data with higher precision by raising the default_statistics_target sessions parameter, running ANALYZE and results should be closer to reality:

>>> set default_statistics_target to 1000;
SET
Time: 0.426 ms
     
>>> analyze verbose crm.emails_sent ( userid );
INFO:  analyzing "crm.emails_sent"
INFO:  "emails_sent": scanned 300000 of 53667393 pages, containing 16709008 live rows and 0 dead rows; 300000 rows in sample, 2989096330 estimated total rows
ANALYZE
Time: 128758.921 ms (02:08.759)

> select (histogram_bounds :: text :: numeric[])[1] as stat_min_userid from pg_stats where tablename = 'my_table' and attname = 'userid';
 stat_min_userid
-----------------
             262
(1 row)

Time: 1.904 ms

Thanks for reading!

Detect and remove duplicated indexes in PostgreSQL

In Postgres, you can create multiple indexes on same table and identical list of columns. While it is useful for maintenance (you can create new index and drop the old one, for example) it is NOT advisable in long term.

Here is a catalog query which will detect and print duplicate indexes:

WITH dup AS (
	SELECT x.indrelid, x.indkey, i.relam, md5(x.indpred)
	FROM pg_index x
	JOIN pg_class i ON i.oid = x.indexrelid
	GROUP BY 1,2,3,4
	HAVING count(*) > 1
)
SELECT
	n.nspname AS schemaname,
	c.relname AS tablename,
    i.relname AS indexname,
	/*am.amname, x.indnatts, x.indkey,
    ARRAY(
        SELECT pg_get_indexdef(x.indexrelid, k + 1, true)
        FROM generate_subscripts(x.indkey, 1) as k
        ORDER BY k
    ) AS indkeyname,
	x.indpred, */
    pg_get_indexdef(x.indexrelid) AS indexdef,
    pg_size_pretty(c.relpages::int8 * current_setting('block_size')::int) AS tablesize,
    pg_size_pretty(i.relpages::int8 * current_setting('block_size')::int) AS indexsize
FROM pg_index x 
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_am AS am ON i.relam = am.oid
JOIN dup ON (
	dup.indrelid = x.indrelid
	AND dup.indkey = x.indkey
	AND dup.relam = i.relam
	AND dup.md5 IS NOT DISTINCT FROM md5(x.indpred))
ORDER BY
	n.nspname, c.relname, dup.md5 nulls first, i.relname
;

pg_terminator: tiny utility for PostgreSQL DBAs

Hello everyone!

depesz just released a new useful tool, pg_terminator
 
I believe it will be useful in Pg DBA's toolbox.

It requires just Ruby, and ruby-pg gem.

You run it like this:

$ pg_terminator etc/pg_terminator/webdb.yaml

and the yaml config looks like this:

log: /tmp/pg_terminator.webdb.log
min_sleep: 1
database:
  dbname: webdb
  host: webdbsrv1
rules:
- name: long-webapp-txn
  action: transaction
  threshold: 180
  type: transaction
  condition: application_name ~ 'webapp' and state <> 'idle'


Here is an example of pg_terminator in action:
 2018-09-25 13:01:27 Sleeping for 20 seconds
2018-09-25 13:01:47 Sleeping for 20 seconds
2018-09-25 13:02:07 Sleeping for 15.0 seconds
2018-09-25 13:02:22 Terminated a query (pid: 2242) because of rule #1: long-txn
2018-09-25 13:02:22 Sleeping for 37.987898 seconds

PostgreSQL projection from INT4 x INT4 to INT8, and back again.

From time to time I need to cast (integer,integer) to bigint and bac k.

The rule is simple:

  0001000f
          0001000f
  ================
  0001000f0001000f

SQL functions:

CREATE OR REPLACE FUNCTION
intpair2bigint(integer, integer)
 RETURNS bigint
 IMMUTABLE STRICT
 LANGUAGE sql
AS '
  SELECT (

    $1::bit(32)::bit(64)

    | ($2::bit(32)::bit(64)>>32)

  )::int8

';

CREATE OR REPLACE FUNCTION
bigint2intpair(bigint)
 RETURNS TABLE(high integer, low integer)
 IMMUTABLE STRICT
 LANGUAGE sql
AS '
  SELECT
    $1::bit(64)::bit(32)::int4,
    $1::bit(32)::int4
';

Some tests:

SELECT intpair2bigint( 0, 0 );

0

SELECT intpair2bigint( 0, (2^31-1)::int4 );

2147483647

SELECT intpair2bigint( 0, (-2^31)::int4 );

-2147483648

SELECT intpair2bigint( (2^31-1)::int4, 0 );

140737488289792

SELECT intpair2bigint( (2^31-1)::int4, (2^31-1)::int4  );

140739635773439

SELECT intpair2bigint( (2^31-1)::int4, (-2^31)::int4 );

140735340806144

SELECT intpair2bigint( (-2^31)::int4, 0 );

-140737488355328

SELECT intpair2bigint( (-2^31)::int4, (2^31-1)::int4 );

-140735340871681

SELECT intpair2bigint( (-2^31)::int4, (-2^31)::int4 );

-140739635838976

Voila!

Simplistic PostgreSQL monitoring: pg_stat_database_hist

Good day!

In this series I will show you, how to create very simple but useful monitoring - using only PostgreSQL native tools, simple shell scripts and finally Grafana to add some visual sugar and dashboards.

There are dozens of ready-made monitoring tools, but they all need data source.  Here you are, going in "bare metal" mode.

Part 1 - Data store.

Run this once, in your postgres database:

CREATE TABLE pg_stat_database_hist (
  LIKE pg_stat_database,
  time timestamptz, 
  PRIMARY KEY (datname,time)
);
Do not worry about data size - it is tiny, compared to any kind of real life application data. Something like 3 MB per month.

Note: keeping this in same database simplifies a few things, but when your database server is out of order, you will not be able to read the stats.

Part 2 - data collection

Run this INSERT in your postgres database every 10 minutes.

INSERT INTO pg_stat_database_hist 
  SELECT *, now() FROM pg_stat_database;

As you are probably running your postgres on Linux host, this can be done with a cron job. Put this into /etc/cron.d/pgmon (in a single line of text!)

*/10 * * * * postgres psql -Xqc 'INSERT INTO pg_stat_database_hist SELECT *, now() FROM pg_stat_database'

Part 3 - What is There.

I will not repeat the official documentation.


Part 4: Sample Queries

For "numbackends" which is a gauge - type metric, this is simple.
Number of client sessions, over time:

SELECT time, sum(numbackends) 
FROM pg_stat_database_hist
GROUP BY time ORDER BY time;

For the other metrics, they are counters (accumulated) so to extract deltas, you need a little of substraction & division here and there:
 

WITH level1 AS (
  SELECT time, sum(tup_inserted) AS v
  FROM pg_stat_database_hist GROUP BY 1)
SELECT time, (v - lag(v) OVER w1)
 / extract(epoch from time - lag(time) OVER w1) AS tup_inserted_per_second
FROM level1
WINDOW w1 AS (ORDER BY time);

Note: actually, you can delegate the substraction logic to Grafana but we will get to this later.

To Be Continued....

Which Flat-File Format is the Best for Your Data Processing in PostgreSQL (or anywhere)

Good day everyone.

If you are dealing with data, sooner or later you will deal with flat data files.

Now - there are some competing options for transfer format. Human world will prefer rich / hierarchical file formats, markup languages, Excel etc etc.

But the machines will happily consume raw arrays of bits.

Here I'm going to compare elementary processing performance for two most common text file formats used while pushing data in/out of PostgreSQL: the native COPY format and the CSV format.

The COPY format is more compact and machine-efficient, while the CSV is only a little more complex, but as we will see this makes a difference.

Let's go to the real use case.

USE CASE 

We have a table with 230 k different email templates. This content is similar characteristics as html. xml, or text document data, about 1000 characters wide plus some extra attributes.

I will measure input/output performance, starting with...

EXPORT TIMING

testdb=# copy mailtemplates to '/dev/null';
COPY 231674
Time: 4183.323 ms (00:04.183)
testdb=# copy mailtemplates to '/dev/null';
COPY 231674
Time: 3326.553 ms (00:03.327)

testdb=# copy mailtemplates to '/dev/null' with (format csv, header on);
COPY 231674
Time: 3714.943 ms (00:03.715)
testdb=# copy mailtemplates to '/dev/null' with (format csv, header on);
COPY 231674
Time: 3707.985 ms (00:03.708)



OK so we got some numbers, and quick calculation shows, that

CSV EXPORT IS 11% SLOWER:

testdb=# SELECT 3707.985 / 3326.553;
      ?column?     
--------------------
 1.1146628356740446
(1 row)

Now, let's test the...

IMPORT TIMING

Table was truncated before each test.

testdb=# create unlogged table public.temptable (like mailtemplates including constraints including indexes);
CREATE TABLE

testdb=# copy temptable from '/tmp/cm';
COPY 231674
Time: 16834.553 ms (00:16.835)
testdb=# copy temptable from '/tmp/cm';
COPY 231674
Time: 15490.150 ms (00:15.490)
testdb=# copy temptable from '/tmp/cm.csv' with (format csv, header on);
COPY 231674
Time: 19058.464 ms (00:19.058)
testdb=# copy temptable from '/tmp/cm.csv' with (format csv, header on);
COPY 231674
Time: 18825.862 ms (00:18.826)

So,

CSV IMPORT IS 21 % SLOWER

(because even without indexes, the parser has ~ 20% more work to do)

testdb=# SELECT 18825.862 / 15490.150;
        ?column?       
------------------------
 1.21534407349186418466
(1 row)

Also, COPY has following advantages:

LINE COUNT FOR PG FORMAT IS EXACTLY EQUAL TO ROW COUNT

-bash-4.2$ wc -l /tmp/cm*
    231674 /tmp/cm
   6973810 /tmp/cm.csv

and...

FILE SIZE: CSV FORMAT IS 3 % BIGGER

-bash-4.2$ ls -lh /tmp/cm*
-rw-r--r-- 1 postgres postgres 828M Dec 15 12:33 /tmp/cm
-rw-r--r-- 1 postgres postgres 850M Dec 15 12:33 /tmp/cm.csv



That's all for now :-).
I think this was kind of obvious but 11 or 20% is definitely a big cost increase, so let's be ecological and do not waste energy when not needed.