#create named pipe
mkfifo ~/pipe

#use psql
[pavel@nemesis ~]$ psql
psql (13devel)
Type "help" for help.

postgres=# \o ~/pokus
postgres=# select * from obce limit 10;
postgres=# --redirect content to pipe
postgres=# \o ~/pipe
postgres=# select current_timestamp;
postgres=# -- repeat it every 1sec
postgres=# \watch 1

pspg -f ~/pipe

JOINs the hard way: comparing MongoDB and Postgres

Dr. Michael Stonebraker and I have just published a new blog post on our series comparing MongoDB and Postgres. This second post compares JOINing data in MongoDB and Postgres, and follows on our previous post, “Schema Later” Considered Harmful.

This new post analyzes how to structure data in both systems to model a classical employee-department data model. We analyze why the embedded model won’t work for the Mongo case, and why the reference model is brittle in this system. The post also includes a simple performance benchmark, comparing both systems. Following on our best practices, it is open, public and anyone can reproduce it.

Our performance results were not surprising. On a given user group, last year, an user claimed:

If 500ms for a 1000x500 rows lookup is normal, thats very bad news for MongoDB in our project

and one of MongoDB’s top engineers replied:

That’s because you are choosing to do a join.

Depending on your workload, the planning time can represent a significant part of the overal query procesing time. This is especially import in OLTP workload, but OLAP queries with numerous tables being joined and an aggressive configuration on the JOIN order search can also lead to hight planning time.

Planning counters in pg_stat_statements

Previously, pg_stat_statements was only keeping track of the execution part of a query processing: the number of execution, cumulated time, but also minimum, maximum, mean and also the standard deviation. With PostgreSQL 13, you’ll also have those metrics for the planification part!

    commit 17e03282241c6ac58a714eb0c3b6a8018cf6167a
    Author: Fujii Masao 
    Date:   Thu Apr 2 11:20:19 2020 +0900

        Allow pg_stat_statements to track planning statistics.

        This commit makes pg_stat_statements support new GUC
        pg_stat_statements.track_planning. If this option is enabled,
        pg_stat_statements tracks the planning statistics of the statements,
        e.g., the number of times the statement was planned, the total time
        spent planning the statement, etc. This feature is useful to check
        the statements that it takes a long time to plan. Previously since
        pg_stat_statements tracked only the execution statistics, we could
        not use that for the purpose.

        The planning and execution statistics are stored at the end of
        each phase separately. So there are not always one-to-one relationship
        between them. For example, if the statement is successfully planned
        but fails in the execution phase, only its planning statistics are stored.
        This may cause the users to be able to see different pg_stat_statements
        results from the previous version. To avoid this,
        pg_stat_statements.track_planning needs to be disabled.

        This commit bumps the version of pg_stat_statements to 1.8
        since it changes the definition of pg_stat_statements function.

CREATE OR REPLACE FUNCTION public.buffer_dif(text, bigint)
 RETURNS bigint
 LANGUAGE plpgsql
AS $function$
DECLARE r bigint;
BEGIN
  r := $2 - current_setting($1, true)::bigint;
  PERFORM set_config($1, $2::text, false);
  RETURN r;
END;
$function$

SELECT datname,
       buffer_dif('x.xact_commit_' || datname, xact_commit) AS commits, 
       buffer_dif('x.tup_inserted_' || datname, tup_inserted) AS inserted
  FROM pg_stat_database
 WHERE datname IS NOT NULL;

^Cpostgres=# \watch 1
Čt 2. dubna 2020, 21:33:59 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Čt 2. dubna 2020, 21:34:00 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Čt 2. dubna 2020, 21:34:01 (every 1s)

┌───────────┬─────────┬──────────┐
│  datname  │ commits │ inserted │
╞═══════════╪═════════╪══════════╡
│ postgres  │       1 │        0 │
│ template1 │       0 │        0 │
│ template0 │       0 │        0 │
└───────────┴─────────┴──────────┘
(3 rows)

Recently I was talking in a more general way about some common auditing / change tracking approaches for PostgreSQL…but it also made me curious, how it roughly looks from the performance side?

To quickly recap the previous blog post: the most common approaches for tracking important changes are mostly solved with writing some triggers. There are two main variations: table specific triggers / audit tables and a more generic approach with only one trigger function and one (or also many) generic audit tables, usually relying on PostgreSQL’s NoSQL features that allow quite a high degree of genericness in quite a convenient and usable way.

Obviously one could “guesstimate” that the generic approach would perform worse than the tailored approach as this is commonly the case in software. But the question is – how much worse? Can it be considered negligible? Well there’s only one way to find out I guess….and finally after finding some time to set up a small test case I can say that I was in for a bit of a surprise! But do read on for details or see the last paragraph for the executive summary.

Test schema for the “table specific” approach

As usual I turned to my old buddy pgbench, that aims to effortlessly aid with a typical OLTP transaction scenario (3 updates, 1 select, 1 insert – detailed SQL seen here). Based on that it quickly hacked up some SQL to create the “background” auditing tables for the 3 pgbench tables that get UPDATE-s in the default mode (FYI – there’s also other modes built in + one can use custom scripts) and attached the per table triggers. Although it’s quite a simple schema, there’s probably still too much code to list here – so this is only an excerpt to get the gist of it. The full script can be found here. Note that I didn’t bother to drop the history table populated by pgbench itself as there are no indexes on it, so it shouldn’t slow things down too much, plus I left it there for both test cases so everything should still be equal.

CREATE TABLE pgbench_accounts_log (
  mtim

Postgres Ibiza 2020: cancelled. Mark 2021 in your calendars!

The World has been taken by storm by the COVID-19. Our thoughts are with the people who, for any reason, are suffering from this. We are with you.

At Fundación PostgreSQL we have been watching carefully the events, and trying to keep Postgres Ibiza happening, as long as we could ensure a perfectly safe and healthy condition for organizing it. Even though there is a ray of light and we think a period like September may be safe to organize it, unfortunately, organizing a conference requires quality time. The same is needed for speakers, attendees, sponsors, volunteers to plan. Even in the more optimistic case, we will not reach a clear perspective with time enough for successful organization of the event.

So we have decided to postpone Postgres Ibiza until 2021. Refunds for booked attendees will be processed at the earliest –you will be contacted individually– as well as for sponsors.

Postgres Ibiza is not a conference that may go virtual. Postgres Ibiza, if anything, is about human connection. It’s about the hallway track, as Bruce Momjian said. It is an experience to network with peers, colleagues, partners, customers, friends. It is a conference to innovate and disrupt the future of Postgres. It is about quality time with the Postgres ecosystem people. This, we haven’t found a way to do online. Ping us if you know how. Otherwise, we will hope to meet you, in person, in 2021.

We will announce the event later this year, but we have secured some initially tentative days: 23rd-25th of June, 2021. Mark them in your calendar!

CREATE OR REPLACE FUNCTION isnull(anyelement, anyelement)
RETURNS anyelement AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(NULL, 1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, CURRENT_DATE);
┌────────────┐
│   isnull   │
╞════════════╡
│ 2020-03-31 │
└────────────┘
(1 row)

-- but
postgres=# SELECT public.isnull(1, 1.1);
ERROR:  function public.isnull(integer, numeric) does not exist
LINE 1: SELECT public.isnull(1, 1.1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

CREATE OR REPLACE FUNCTION isnull(anycompatible, anycompatible)
RETURNS anycompatible AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(1, 1.1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, 1.1);
┌────────┐
│ isnull │
╞════════╡
│    1.1 │
└────────┘
(1 row)

PostgreSQL has a rich set of indexing functionality, and there are many articles explaining the syntax, usage, and value of the index. In this article, I will write basic and useful queries to see the state of database indexes. People develop databases and after some time, when there is a demand to do changes in the architecture of software, they forget to do the previous indexes’ cleanup. This approach creates a mess and sometimes slows down the database because of too many indexes. Whenever we do an update or insert, the index will be updated along with the actual table, therefore there is a need for cleanup.

There is a wiki page that has some queries related to PostgreSQL Index Maintenance.

Before writing the queries I want to introduce a catalog table pg_index. The table contains information about the index. This is the basic catalog table, all the index-based views use the same table.

1 – Sometimes you need to see how many indexes your table has. This query will show the schema-qualified table name and its index names.

db=# SELECT CONCAT(n.nspname,'.', c.relname) AS table,
    i.relname AS index_name FROM pg_class c
     JOIN pg_index x ON c.oid = x.indrelid
     JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'pgbench_accounts';
          table          | index_name       
-------------------------+------------------------
 public.pgbench_accounts | pgbench_accounts_pkey
 public.pgbench_accounts | pgbench_accounts_index
(2 rows)

2 – As we all know, an index is a performance feature, but along with that, it is also used to ensure uniqueness. But to ensure the uniqueness we need a separate type of index called a unique index. To check whether an index is unique or not, pg_index has a column named “indisunique” to identify the uniqueness of the index.

SELECT    i.relname AS index_name,
          indisunique is_unique
FROM      pg_class c
JOIN      pg_index x ON c.oid = x.indrelid
JOIN   

1. Overview

In previous two blogs, we explained how to setup Kerberos, and how to configure PostgreSQL to support GSSAPI user authentication. This blog will be focusing on how to check GSSAPI authentication, encryption and user principal information when given different connection options.

2. pg_stat_gssapi view

According to the official PostgreSQL document, “PostgreSQL supports GSSAPI for use as either an encrypted, authenticated layer, or for authentication only.“ To check the authentication, encryption and user principal, we need to use pg_stat_gssapi view, which is a dynamic statistics views containing one row per backend and showing the information about GSSAPI authentication and encryption used on this connection.

Before start the test below, make sure the PostgreSQL server and the psql client has the option --with-gssap enabled during build time.

3. Authentication and Encryption status

• Scenario 1:

Both authentication and encryption are enabled when the host-based authentication is configured with hostgssenc and gss in pg_hba.conf
Set below user authentication rule to pg_hba.conf and disable all other rules.

hostgssenc  postgres  postgres  192.168.0.102/32  gss include_realm=0 krb_realm=HIGHGO.CA

Initiate the user postgres credential cache using kinit, and then connect to PostgreSQL server with user postgres

postgres@pg:~$ psql -d postgres -h pg.highgo.ca -U postgres
psql (12.2)
GSSAPI-encrypted connection
Type "help" for help.

postgres=# SELECT pid, gss_authenticated, encrypted, principal from pg_stat_gssapi where pid = pg_backend_pid();
 pid  | gss_authenticated | encrypted |     principal      
------+-------------------+-----------+--------------------
 2274 | t                 | t         | postgres@HIGHGO.CA
(1 row)

postgres=#

From the result, we can see this connection is encrypted and the user is authenticated with principal postgres@HIGHGO.CA.

• Scenario 2:

The encryption will be disabled, but user authentication is still enabled when

This is the second part of the topic, the more historical version is described in the previous part, and here is the vitality of wal in PostgreSQL which born replication、logical replication and more performance related configure, let’s continue to redo it.

1. Replication(V9.0)

Replication is implemented here, and many corresponding GUC are added for replication. Corresponding to warm standby, replication can also be called hot standby, which helps to achieve the data synchronization using WAL record between the primary and the standby.

Most GUCs appear as [GUC6] in the image, will not talk about all of them but pick some important.

WAL_LEVEL

The current version of wal supports three levels: minimal, aichive and hot standby.

WAL_SENDER_DELAY

The wal sending process sends the wal logs generated by the master to the standby machine every other period of time, this parameter is used to configure the time interval.

WAL_KEEP_SEGMENTS

For some reasons, if the synchronization delay between the master and the standby is too long, it will cause the wal segments of the master to be removed before they are sent to the standby. In this case, the standby machine cannot synchronize the masterdata. Therefore, by configuring the wal ‘keep_segments’ parameter on the master, the WAL logs generated by the master will not be removed immediately, and the backup can have enough time complete data synchronization.

HOT_STANDBY

Configure whether to connect to this standby machine for query.

MAX_STANDBY_ARCHIVE_DELAY && MAX_STANDBY_STREAM_DELAY

When a wal redo operation conflicts with the currently executing query, you need to decide whether to wait for the query to complete redo or cancel the query to execute redo.This parameter sets a time to allow the query to continue executing. After this time, the query will be cancelled, and to finish the redo.

MAX_STANDBY_ARCHIVE_DELAY is used for file level wal transfer, that is, warm standby.

MAX_STANDBY_STREAM_DELAY is used for

WAL is one of the most important parts of PostgreSQL., WAL records all the database activity. Hense we can regard wal as a change roadmap of the history of PostgreSQL database, and the crash recovery, logical replication etc aren’t possible without WAL. The following picture describes the various wal related GUC (based on PG12) involved in the production and use of wal logs. It is very important for us to know the meaning of each parameter to optimize database performance and configure high availability cluster.. We can find the definition of each GUC in the PostgreSQL document, but we can hardly understand the intrinsic meaning of the parameter from a few simple lines, or we don’t know why it exists. What’s more, when you configure the database according to someone else’s blog, you find that your database version doesn’t know the configuration parameters in the blog. This blog will start from the PostgreSQL version 7.1 to review the development of wal log.

However, the history is so long, I want to divide it into two parts, first is for (V7.1-V8.3) which is a relatively old version, and another is for (V9.0-V12) which being developed and used.

1. WAL Genesis(V7.1)

I will not describe the POSTGRES project of Berkeley again. The topic today is a prehistoric civilization that is hard to search. Let us look at wal on PostgreSQL7.1 below, so clearly and we can hold everything.

The above diagram describes the WAL’s normal job only. The main concept of WAL is that any changes to data files must be written only after those changes have been logged – that is, when log records have been flushed to permanent storage. In this way, we don’t need to flush the data files in the shared cache from time to time, because if the database crashes, we can load the data from the wal. The purpose of this method is to replace random data writing with sequential writing wal to obtain higher execution efficiency.

“log records have been flushed to permanent storage”, there are similar words in the general i

[pavel@nemesis ~]$ touch ~/r1
[pavel@nemesis ~]$ touch ~/r2
[pavel@nemesis ~]$ touch ~/r3

[pavel@nemesis ~]$ pspg -f ~/r1 --inotify
[pavel@nemesis ~]$ pspg -f ~/r2 --inotify
[pavel@nemesis ~]$ pspg -f ~/r3 --inotify

psql
postgres=# select * from pg_class \g ~/r1
postgres=# select * from pg_proc \g ~/r2
postgres=# select * from pg_database \g ~/r3

After 20 years in professional PostgreSQL support and consulting we are finally able to answer one of the most frequently asked questions: “How can I see all active query plans?” Ladies and gentlemen, let me introduce you to pg_show_plans, an extension which does exactly that. pg_show_plans is Open Source and can be used free of charge as a standard PostgreSQL extension.

How does pg_show_plans work?

pg_show_plans uses a set of hooks in the PostgreSQL core to extract all the relevant information. These plans are then stored in shared memory and exposed via a view. This makes it possible to access these plans in an easy way.

The performance overhead of pg_show_plans will be discussed in a future blog post. Stay tuned for more information and visit our blog on a regular basis.

Installing pg_show_plans

pg_show_plans is available on GitHub for free and can be used free of charge.

Just clone the GitHub repo:

iMac:src hs$ git clone https://github.com/cybertec-postgresql/pg_show_plans.git

Cloning into 'pg_show_plans'...

remote: Enumerating objects: 54, done.

remote: Counting objects: 100% (54/54), done.

remote: Compressing objects: 100% (25/25), done.

remote: Total 54 (delta 31), reused 52 (delta 29), pack-reused 0

Unpacking objects: 100% (54/54), done.

Then “cd” into the directory and set USE_PGXS. USE_PGXS is important if you want to compile the code outside of the PostgreSQL source tree:

iMac:src hs$ cd pg_show_plans

iMac:pg_show_plans hs$ export USE_PGXS=1

Finally you can run “make” and “make install”

iMac:pg_show_plans hs$ make

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -g -O2  -I. -I./ -I/Users/hs/pg12/include/postgresql/server -I/Users/hs/pg12/include/postgresql/internal    -c -o pg_show_plans.o pg_show_plans.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-

1. Overview

In previous blog, we have setup Kerberos, added all required principals and verified each principal. This blog will explain all the necessary configuration, i.e. postgresql.conf, pg_hba.conf and pg_ident.conf, in PostgreSQL for user authentication using GSSAPI with Kerberos.

2. Build PostgreSQL with GSSAPI

The official PostgreSQL release for Ubuntu has GSSAPI enabled for user authentication with Kerberos, however if you want to build it from source code, you can simply enable it by giving the option --with-gssapi in configure process like below.

$ ./configure --with-gssapi --prefix=/home/postgres/pgapp

There is another option --with-krb-srvnam, PostgreSQL uses the default name of the Kerberos service principal. According to Postgres official document, “There’s usually no reason to change this unless you have a Windows environment, in which case it must be set to upper case POSTGRES“. In this blog, we keep it as default, and no extra configuration required for it. After enabled --with-gssapi option, you can build, install, initialize database, change configuration and start database service as normal. The commands used in this blog are list below.

make && make install
export PATH=/home/postgres/pgapp/bin:$PATH
export PGDATA=/home/postgres/pgdata/data 
initdb -D $PGDATA

3. Keytab file

If you have followed the previous blog “part-1: how to setup Kerberos on Ubuntu”, then you should already have the keytab file. Now, copy the keytab file to Service Server (Postgres Server) and put it to a folder with appropriate permissions to the user owning the Postgres process. For example, user postgres and folder /home/postgres/pgdata/data in this blog. The instance principal extracted from KDC server can be verified using ktutil on Service Server.

postgres@pg:~$ ktutil 
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
ktutil:  read_kt postgres.keytab 
ktutil:  list
slot KVNO Principal
---- ---- ------------------------------

This is my first blog in a series of SQL optimization blogs. So expect some basic information in here along with some nice insights. My aim is to help you walk through a complete process of understanding and optimizing queries for improved performance. A PostgreSQL server attempts to find the most effective way of building a result set for the query. The first step in that direction is the ability to understand what the “EXPLAIN” command is. 

When a statement is sent to a PostgreSQL server for execution, it must decipher various parts of that query and define an execution plan. Given that data can be retrieved and managed/manipulated in various different ways, a PostgreSQL server attempts to find the most effective way of building a result set for the query. Especially in case of long running queries where performance matters, an execution plan for our statement on a given platform with our existing server configuration determines overall query (and in most cases, application) performance. It is therefore important to be able to understand how a PostgreSQL server breaks down a query execution in form of an execution plan. This is where the “EXPLAIN” command comes in. In very basic terms, “EXPLAIN” command shows (explains) the execution plan of a statement.

Query processing in PostgreSQL is divided into 5 main components:

1. Parser
2. Analyzer
3. Rewriter
4. Planner
5. Executor

Whereas (1) parser and (2) analyzer are concerned with ensuring that query is written correctly, the result is a query tree which is passed onto (3) rewriter that may transform it based on rules defined in the pg_rules system catalog table. So this transformed query tree is passed on the (4) planner which generates a plan for execution by (5) executor.

The EXPLAIN Command

“EXPLAIN” command has a simple grammar as defined in PostgreSQL documentation. “EXPLAIN” command only explains a given statement, however when run with the “ANALYZE” option, it also executes it. The result set is never thrown back to the

We recently helped migrate a production PostgreSQL database running on a given DBaaS platform… to another DBaaS platform. When it comes to vendor “lock-in”, some providers are easier (and even friendlier) to deal with than others, but it is never a straightforward process. While in a traditional environment we would usually approach this problem by creating a replica of the server and switching over database requests at the right moment, the situation complicates when it is not possible to create a direct replication flow outside the provider’s platform.

In this post, we’ll share the approach we used for this particular case. It is not intended to provide a step-by-step, one-size-fits-all solution, but we hope it might serve you as “inspiration” when dealing with a similar situation. You may find some parts of our approach more useful than others; consider the ones that suit your unique needs and be creative. As always, regardless of the plan you design, make sure you test all involved steps in a testing/staging environment first as in practice we often step into yet another stone we didn’t know was laying in our path.

Intermediating DBaaS Instances

When we cannot have direct replication between DBaaS instances, we can still attempt to “chain it” somehow through an intermediate player (read: server):

source DBaaS → Intermediate server → target DBaaS

In this particular case, the source DBaaS was producing daily base backups and streaming WAL segments to externally accessible storage, an S3 bucket, for archiving purposes. We took advantage of this to create an intermediate PostgreSQL server from the latest base backup available and configured it to consume the WAL segments available on this storage. Be mindful of where to install this intermediate server: you may want to install it either closer to the source DBaaS or yet closer to the target DBaaS instead. When making your choice, consider network bandwidth as well as the availability of common tools by the infrastructure provider.

Howe

© Laurenz Albe 2020

Embedded SQL is by no means a new feature — in fact it is so old-fashioned that many people may not know about it at all. Still, it has lots of advantages for client code written in C. So I’d like to give a brief introduction and talk about its benefits and problems.

What is embedded SQL?

Typically you use a database from application code by calling the API functions or methods of a library. With embedded SQL, you put plain SQL code (decorated with “EXEC SQL”) smack in the middle of your program source. To turn that into correct syntax, you have to run a pre-processor on the code that converts the SQL statements into API function calls. Only then you can compile and run the program.

Embedded SQL is mostly used with old-fashioned compiled languages like C, Fortran, Pascal, PL/I or COBOL, but with SQLJ there is also a Java implementation. One reason for its wide adoption (at least in the past) is that it is specified by the SQL standard ISO/IEC 9075-2 (SQL/Foundation). This enables you to write fairly portable applications.

To be able to discuss the features in some more detail, I’ll introduce a sample C program using embedded SQL.

Sample embedded SQL program

The sample program operates on a database table defined like this:

CREATE TABLE atable(
   key integer PRIMARY KEY,
   value character varying(20)
);

The program is in a file sample-pgc and looks like this:

#include 
#include 

/* error handlers for the whole program */
EXEC SQL WHENEVER SQLERROR CALL die();
EXEC SQL WHENEVER NOT FOUND DO BREAK;

static void die(void)
{
        /* avoid recursion on error */
        EXEC SQL WHENEVER SQLERROR CONTINUE;

        fprintf(
                stderr,
                "database error %s:\n%s\n",
                sqlca.sqlstate,
                sqlca.sqlerrm.sqlerrmc
        );

        EXEC SQL ROLLBACK;
        EXEC SQL DISCONNECT;

        exit(1);

        /* restore the original handler */
        EXEC SQL WHENEVER SQLERROR CALL d

1. Introduction

PostgreSQL and MongoDB are two popular open source relational (SQL) and non-relational (NoSQL) databases available today. Both are maintained by groups of very experienced development teams globally and are widely used in many popular industries for adminitration and analytical purposes. MongoDB is a NoSQL Document-oriented Database which stores the data in form of key-value pairs expressed in JSON or BSON; it provides high performance and scalability along with data modelling and data management of huge sets of data in an enterprise application. PostgreSQL is a SQL database designed to handle a range of workloads in many applications supporting many concurrent users; it is a feature-rich database with high extensibility, which allows users to create custom plugins, extensions, data types, common table expressions to expand existing features

I have recently been involved in the development of a MongoDB Decoder Plugin for PostgreSQL, which can be paired with a logical replication slot to publish WAL changes to a subscriber in a format that MongoDB can understand. Basically, we would like to enable logical replication between MongoDB (as subscriber) and PostgreSQL (as publisher) in an automatic fashion. Since both databases are very different in nature, physical replication of WAL files is not applicable in this case. The logical replication supported by PostgreSQL is a method of replicating data objects changes based on replication identity (usually a primary key) and it would be the ideal choice for this purpose as it is designed to allow sharing the object changes between PostgreSQL and multiple other databases. The MongoDB Decoder Plugin will play a very important role as it is directly responsible for producing a series of WAL changes in a format that MongoDB can understand (ie. Javascript and JSON).

In this blog, I would like to share some of my initial research and design approach towards the development of MongoDB Decoder Plugin.

2. Architecture

Since it is not possibl

There is a lot of interest and discussions lately in the PostgreSQL world to make it a scale-out solution. Among other possible solutions, one of the most promising ones is to implement the sharding using FDW and table partitioning for distributing the data on multiple servers. As of now, PostgreSQL can only fetch the data from FDW in serial and that is one thing that needs to be improved to increase the performance. Other than that performance improvement currently we are missing two major features in PostgreSQL to make it full fledge distributed database. These are related to the ACID property, atomic commit, and atomic visibility.

This article is to explain what is atomic commit and atomic visibility and why we need it in PostgreSQL to make it a full fledge scale-out database solution.

Atomic commit

When we talk about database system that consists of multiple servers the first thing that comes in mind is two-phase commit protocol (2PC). Using a 2PC protocol we can achieve an atomic commit for transactions involving multiple servers (distributed transactions). As the name of the two-phase commit protocol suggests that, 2PC commits the transaction in two steps rather that one. In the first step, the transaction is prepared across all the participating servers and depending upon the result of prepare the second step performs the commit prepared or rollback prepared. Dividing the commit into two steps helps in building the consensus among all nodes if every node can commit the transaction or not, before performing the actual commit. But unlike simple transactions, the prepared transactions have a very interesting property that they are not bound to the database session and keep persisting even when the session that created that transaction dies.

This very property of the prepared transactions makes them very powerful and usable for 2PC. As to implement 2PC we don’t want to see a prepared transaction vanished (because of session disconnection or even a server crash) once after we get confirm