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.

Interesting work on big data streaming & dynamic batching algorithms

While I was working on a solution for large scale Apache Kafka logging, I got interested in processing performance considerations.

Then I found an interesting work of Yuan Zhong on optimal buffer size and dynamic batching.


Maybe this is too adanced to get applied in my daily work, but I like the thing that engineers and doctors took this problem seriously and employed math to get optimal solutions.

The works like this get applied in your routers, switches, CPUs and software.


http://www.columbia.edu/~yz2561/dynamic_batching.pdf

https://www2.eecs.berkeley.edu/Pubs/TechRpts/2014/EECS-2014-133.html

pgbench -S on 64 core server - can you beat the Russians? :-)

Impressive :-)

http://obartunov.livejournal.com/181981.html

http://www.postgrespro.ru/blog/pgsql/2015/08/30/p8scaling

Word length histogram & frequency

In natural language analysis you sometimes need to know distribution of word length in given text.

In other words, a histogram of word length in given text.

You can use Perl as below. I used text corpus from the fortunes package.

Bin size is an estimated number of words in 100 - element random sample.

cat /usr/share/games/fortunes/*.u8 | \
perl -nle 'for $word (/(\w+)/g) { $wordcount++; $ls{length($word)}++; } 
  END{
    print join("\t", qw(length count frequency binsize));
    for $length (sort {$a<=>$b} grep {$_<=20} keys %ls) {

      $freq = $ls{$length} / $wordcount;

      print join("\t", $length, $ls{$length}, $freq, int(100*$freq));
    }
  }'

length count frequency binsize
1 32334 0.0723502995016883 7
2 75544 0.169036649519253 16
3 90270 0.201987429208183 20
4 80746 0.180676603066844 18
5 50783 0.11363163418056 11
6 36790 0.0823210094224999 8
7 30286 0.0677677111000226 6
8 20079 0.0449286096274633 4
9 13215 0.0295697781875057 2
10 8457 0.0189233154848079 1
11 4376 0.00979170256137155 0
12 2243 0.00501891884030082 0
13 1075 0.00240541139247587 0
14 406 0.00090846234915833 0
15 160 0.00035801471888013 0
16 55 0.000123067559615045 0
17 16 3.5801471888013e-05 0
18 17 3.80390638810138e-05 0
19 5 1.11879599650041e-05 0
20 3 6.71277597900244e-06 0