Living with Postgres (PostgreSQL)

You can read lots about PostrgeSQL on the internet so I won't go into the finer details of how it works and what features it has, as that information is readily accessible. Instead I will concentrate on how we came to use it at Theta, what we discovered, what worked well or didn't work so well, and how it integrated with other products.

How did we come to use it?

One of our customers asked us to use PostgreSQL rather than their standard relational database product for the database component of a new application. The application used near real time information sources from multiple sources and data suppliers and is a 24x7 operation. Traditionally we would use one of the commercially available relational databases for a high availability system such as this. As this wasn’t an available option in this case we installed PostgresSQL 9.4 on a Linux server and started the project.

What did we find?

We found that PostgreSQL fitted in well with our software development processes - we simply changed the output option on our data modelling tool (Toad Datamodeller) to PostgreSQL 9.4 and this generated the build script for our database. All the typical features you would expect when defining an SQL database (for example tablespaces, security, users, roles, check constraints etc) worked as expected - but that’s why you use a third party Datamodel / ERD tool isn’t it? Rather than the old school style of hand cranking database definitions in the a proprietary management tool (or worse – using notepad or directly to the database!)

What worked well?

We found that we could do pretty much everything in PostgreSQL that we would have in one of the commercially available equivalents.  This included for example stored procedures, table functions, asynchronous notifications, table triggers and integrating with other technologies such as Java and Perl. There was plenty of information available online including detailed examples.  Once we had mastered the subtle syntax differences we found productivity was broadly the same.

What didn't work so well?

The IDE that comes with PostgreSQL (pg admin) for developing stored procedures and triggers is reasonably basic. If you are using PostgreSQL to just to store / retrieve data this won't affect your project. If you are implementing business logic or data processes in the database using stored procedures / triggers, however, and your background is in other databases, then you will notice that compiling PostgreSQL procedural code doesn't mean that it will work at run time and your testing effort will be higher than expected as a result (ie you will think you are done but you aren’t really).  One way to think of this: the time to get to runnable code is less, but the initial unit testing time will be more.

Integration with other products

We found integration was basically the same, for example for Perl and Java. If it worked with another database then it worked in PostgreSQL.

Performance

Performance was excellent - our database has tens of millions of rows of data, loaded every minute 24x7, and as long as the tables are indexed correctly performance was great.

Would we use it again - is this a usable alternative to commercial database products?

Yep, we would. Unless you are using a software package or technology that is already integrated with another kind of database then there is no reason to not consider PostgreSQL.  You can’t beat it on cost.

Its open source - it’s an unsupported platform?

Well - so are Java and Perl and they are widely used. Also when did you last log a ticket with a database vendor for a database bug and get a resolution? Generally you need to upgrade or implement a workaround - it’s the same with PostgreSQL. There is a big community of users and hence lots of support and examples.

Take the plunge - we did!