Friday, November 22, 2013

Feedback from my sesion at PgConfEU

I had recently done a session at PgConf.EU.

It was interesting and encouraging to see the feedback  from it.
Thanks to the people who took time to give the feedback.

Session feedback: My experience with embedding PostgreSQL

Speaker: Jignesh Shah

A total of 4 feedback entries have been submitted for this session.


Topic importance
(5 is highest, 1 is lowest)


Content quality
(5 is highest, 1 is lowest)

Speaker knowledge
(5 is highest, 1 is lowest)

Speaker quality
(5 is highest, 1 is lowest)


  • Very good presentation - IMHO please add Oracle License Costs to your slides. As on the first view - Business people wont understand that they are variable and WAY HIGHER as expected.
  • One of the best talks this year.

Thursday, October 31, 2013

My experience with embedding PostgreSQL - #pgconfeu

At my current company, we embed PostgreSQL based technologies in various applications shipped as shrink-wrapped software. In this session we talk about the experience of embedding PostgreSQL where it is not directly exposed to end-user and the issues encountered on how they were resolved.
We will talk about business reasons,technical architecture of deployments, upgrades, security processes on how to work with embedded PostgreSQL databases.

Tuesday, September 17, 2013

PostgreSQL replication? I need temp tables for my reports

One of the frequent things I hear that many times  PostgreSQL users avoid running PostgreSQL replication  because they want to offload reports that use temporary tables on the slaves. Since PostgreSQL replicas are pure read only, it cannot support temporary tables.

There is one way to overcome this with postgres_fdw - PostgreSQL Foreign Data Wrapper which are improved in PostgreSQL 9.3 which is now released.
  • Create a Master-Slave setup with PostgreSQL 9.3 (with synchronous or asynchronous replication as per your needs).
  • On the slave setup, setup another PostgreSQL 9.3 instance (with different port) with postgres_fdw and map all tables from slaves as foreign tables with same names as their remote counterparts.
  • Run reports which requires temporary tables using this new instance
Of course there are few caveats for this setup
  • Query plans: Currently they are still inefficient but as postgres_fdw improves, this will likely go away. Infact more usage of this use-case scenario will force it to be improved
  • Lot of data moving: Most DW reports do read lot of rows. However by setting it up on the same server most of it are loopback and dont go on the wire outside. 
  • More Resources: This will do require more memory/cpu on the server but it is still cheaper since the management of such a server is still more simpler compared to other complex designs to achieve the same goal
I would like to hear about your experiences on the same too so feel free to send me comments.

Tuesday, May 21, 2013

How to do Postgres Replication and Failover in VMware vFabric Data Director 2.7?

Last week VMware released vFabric Data Director 2.7. Among the many new features for various database, I wish to give a little more insight into my favorite ones which are regarding Postgres.

One of the big feature add from a broad perspective is support of Postgres 9.2 based managed database servers along with replication.  Lets look at how it is done in brief.
First vFabric Postgres 9.2 base DBVM needs to be uploaded into Data Director system resource pool and then converted into template. Note this template is different from vFabric Postgres 9.2 Virtual appliance and is available on the same location where Data Director 2.7 download exists.  Also this DBVM template is based on the latest PostgreSQL 9.2.4 core.
Next once the template is  loaded into the system resource pool it will show up in Base DBVMs section in System->Manage and Monor-> Templates-> BaseDBVMs

Then you would then  right click on the base DBVM and select “Convert to Base DB Template”.  Here you also have a new feature to add more disks (think Tablespaces in PostgreSQL) to the template. In Data Director 2.7, disks are added at the template level.

This process take some time and then it should show up in Base DB Templates. On the Base DB Template section right click on the newly created template and select “Validate”. This one creates a test database to see if it is successful or not before it can be rolled out to the Organizations and Database groups . Once the validation is a success, right click and select “Assign it to the resource bundle” and select the resource bundle that will have access to the template or create a new resource bundle to use the template.
Note in the Resource bundle creation steps lies yet another new  way to separate out IO on separate datastores since these may need different IO characteristics

The above image shows how the various types of datastores for your OS, Backup, Data and Logs (Data can be multiple location if you need multiple tablespaces)

Now all the Orgs/Database groups using the resource bundle will see the new vPostgres 9.2 template.
In a sample demo I created a database called MyDB using few wizard questions.
Now on the DB List we do a right click on the database to create more replicas

Couple of more quick wizard questions and a slave is ready. Similarly you can create more slaves of the same master. Now actually behind the scenes it does all the plumbing right from creating a new clone virtual machine from the master and setting it up using standard PostgreSQL replication on the newly created VM but from a user point of view it it is all point , click and done.

The database dashboard also has a new portlet to show bit more information about the replication.

To make a slave as a new master and it will give an option to  move all the other slaves to the new master select as follows:

After completion of the failover, the status shown will be similar to:

Where the original slave replica is now redirected to the new Master. The original master is kept as it is for Port-Mortem or other activities. This also gives a way to use it as new use cases like separating a slave for test purpose/DR purpose or separating “mirrors” etc. Best of all it uses all Standard PostgreSQL replication.
Of course one of the thing that it does not do is re-purpose the original Master as a slave of the new Master. For more details there is a perfect opportunity to find Heikki Linnakangas from VMware at PGCon in Ottawa this week and ask him the question after his session .. WHY??? :-)

Tuesday, May 14, 2013

How can PostgreSQL 9.3 beta1 help you?

PostgreSQL 9.3 beta1 is now available. Giving early access to software is always a good idea to test out evolutionary, revolutionary, radical ideas because unless it is field tested, it has not gone through its trial by fire to be proven gold.

There are many new changes introduced in PostgreSQL 9.3 beta1 and I do have few favorites in them.

For example Disk page checksums to detect filesystem failures. In fact this would allow VMware to use the now standard disk page checksum instead of a custom feature. This highly debated feature is required to identify silent bit corruptions (or deter malicious ones). I have been told in talks with database administrators (not just PostgreSQL DBAs) that typically in a year they would face one such incident atleast where one of the disk would show such a bit rot which goes unnoticed without any instrumentations to catch it.

Another change that goes in the right direction is how PostgreSQL maps the shared memory. This small change now allows no kernel changes to be done to start the database with a bigger shared buffer pool. This now allows one less cookbook step to be done to get the database working. Considering that in this cloud world where there are 100,000 VMs running databases one less step is a huge increase in productivity since this step actually required privileges higher than the database instance owner.

Yet another favorite feature is the custom background workers. This new mechanism is certainly a popular one in our team at VMware where are using it heavily to move some of the changes that we had done into custom background workers deployed as extensions and allowed us to align with core PostgreSQL and extra features enabled as extensions as needed.

Next I want to talk of three features : Writeable Foreign Tables and pgsql Foreign Data Wrapper and Automatically update VIEWs together. These features on its own itself are very useful and generic. However when used together it actually opens new possibilities using multiple federated PostgreSQL databases shards with single logical view of the whole database as one. Quite Powerful if you think about it. I hope to see people trying these fundamental features into new derived features now made possible.

Also new JSON functions help PostgreSQL on its evolution to be the Data Platform not just for relational data but also document data.

While I have barely scratched the surface of all the new features in PostgreSQL 9.3 beta1, I am already excited with this release and the possibilities I see going forward  in the world of data.