Where Postgres Wins

PgConf India 2018 was an eye-opener both for me and my team. As testimonials to the growing popularity of Postgres, we witnessed and heard from many companies about their migration from proprietary databases, especially from Oracle, to Postgres. So it comes as no surprise why Postgres won the DB-Engines DBMS of the year 2017 award.

When a community grows, newbies expect more introductory contents and talks about the power of Postgres from Postgres experts. Unfortunately, the experts mainly concentrate on fairly advanced topics, and most of the content is delivered towards DBAs and System Administrators rather than regular users and application developers. Understanding some of the key benefits and differences is important. So I thought of blogging about some of the key features of Postgres, which gives users edge over other database systems or at par with the so-called ‘best’ in the Industry.

ACID compliant transaction capabilities for DDLs

Most of the database systems claim that they support a fully compliant ACID transaction. However, in many databases, including Oracle, this transaction capability is limited to DMLs only. The moment a DDL is executed, the transaction breaks.

In the following example, Oracle fails to rollback DDL statement

SQL> alter table t1 add id2 int;  
Table altered.

SQL> desc t1;
 Name                    Null?    Type
 ------------------- --------  ---------------
 ID                               NUMBER(38)
 ID2                              NUMBER(38)

SQL> rollback;
Rollback complete.

SQL> desc t1;
 Name                      Null?    Type
 ------------------- -------- ----------------------
 ID                                NUMBER(38)
 ID2                               NUMBER(38)

As we cab see above, even though it claims that “Rollback completed”, the DDL change is commited

To make things worse, there is no proper isolation from other sessions. All changes will be immediately visible to the other sessions.

In short, as a user we are faced with two challenges here:

  1. We cannot really test any DDL without affecting others, as there is no isolation.

  2. Before every DDL, we need to manually prepare rollback steps as another set of DDLs, which will reverse the change.

But Postgres supports DDL as part of transactions. which is of its key strength.

Simple use-cases of DDL transaction capabilities.

1. Testing of DDL.

Consider a situation where you may want to check the syntax of a DDL statement, which needs to be committed to source control, but do not want to really make that change. Postgres even provides the flexibility of having a separate schema, which will not be visible to anyone else unless you commit (Isolation ensured).

postgres=# \set AUTOCOMMIT off

postgres=# create schema test;

postgres=# set search_path TO test;

Play with more DDLs, and associated DMLs, SQL queries etc.

postgres=# create table t1 (id int);

postgres=# insert into t1 values (2),(4);

postgres=# select * from t1;
(2 rows)

During this entire testing of scripts, everything you do remains isolated from the rest of the world. Once you are done, you can just issue a ‘rollback’.

postgres=# rollback;

Everything will be reverted back to its original state; every schema you created, every object you created or modified, etc.

2. Testing possible Indexes and estimations

Before creating an index, one needs to be aware if that index is really going to help the SQL query and how much extra space that index is going to consume.

In my performance testing environment, I can just start a transaction and create index

postgres=# \set AUTOCOMMIT off

postgres=# create index idx_pgbench_accounts on pgbench_accounts USING btree (aid);

Now, we can answer questions like how much space this extra index is going to use:

postgres=# \di+ idx_pgbench_accounts;
                                     List of relations

 Schema |         Name         | Type  |  Owner   |      Table       |  Size  | Description 


 public | idx_pgbench_accounts | index | postgres | pgbench_accounts | 214 MB | 

In this case, the index is going to consume 214 MB.

In the same session, we can also check whether it is helping SQL queries.

postgres=# explain select min(aid) from pgbench_accounts;
                                                         QUERY PLAN                                                         

 Result  (cost=0.46..0.47 rows=1 width=4)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.43..0.46 rows=1 width=4)
           ->  Index Only Scan using idx_pgbench_accounts on pgbench_accounts      
                                      (cost=0.43..286526.90 rows=10000941 width=4)
                 Index Cond: (aid IS NOT NULL)
(5 rows)

Yes, it is !!

Once we have done every assessment, it is time for reverting everything back

postgres=# rollback;

We can verify that new index no longer exists and query plan is back to original

postgres=# \di+ idx_pgbench_accounts;
No matching relations found.

postgres=# explain select min(aid) from pgbench_accounts;
                                    QUERY PLAN                                    
 Aggregate  (cost=288946.76..288946.77 rows=1 width=4)
   ->  Seq Scan on pgbench_accounts  (cost=0.00..263944.41 rows=10000941 width=4)

During the entire testing, all the other sessions remain unaware of the new index that was created and tested. They continue to use the old execution plan.

Important Note:

Oracle’s command line client tool, sqlplus, by default has the setting autocommit = off. It means that every statement that is being issued becomes part of a transaction; however, the Postgres command line client tool psql comes with the default setting autocommit = on. Hence, every statement gets committed automatically. If you do not want the autocommit option to be on, you can specify \set AUTOCOMMIT off. This tells the client not to send commits automatically, To verify the autocommit setting, run the command

postgres=# \echo :AUTOCOMMIT