Friday, November 24

Umair’s PlanetPostgreSQL

v10, The Best PostgreSQL Yet?

v10, The Best PostgreSQL Yet?

Umair's PlanetPostgreSQL
The short answer … Hell Yeah! The long answer lies in extensive improvements and the impressive new feature list that makes up this major release - which, by the way, changes the version scheme of PostgreSQL as well (more details on that here). This wiki page lists out, in detail, all the new features in PostgreSQL 10, but for the purpose of this blog, I will focus on some of the exciting features contributed by 2ndQuadrant. (more…)

Java Object Oriented Querying (jOOQ)

Umair's PlanetPostgreSQL
jOOQ is an ORM-alternative that is relational model centric rather than domain model centric like most ORMs. For example, while Hibernate lets you write Java code and then automatically translates it to SQL, jOOQ let’s you write relational objects in your database using SQL and then automatically generates Java code to map to those relational objects. The writers of jOOQ believe in the power of SQL and assume that you want low level control of the SQL running for your application. This means that you can write your database and your schema without having to worry about how (and if!) it can be handled in Java. Why Use jOOQ? While JPA provides a huge framework with a great deal of flexibility and power, it can very quickly become quite complex. jOOQ provides a simpler interface for (more…)

Processing PostgreSQL JSON & JSONB data in Java

Umair's PlanetPostgreSQL
This is part of a series of blogs on Java & PostgreSQL. For links to other blogs in this series, please scroll to the end of this post. Starting v9.2, PostgreSQL is providing native data type support for JSON objects. Subsequent releases introduced JSONB (binary formatted JSON objects) and many data manipulation functions for JSONs, making it a very powerful tool for NoSQL operations. String manipulation and parsing are very expensive operations in a database, so although you could have potentially stored JSON objects in strings in PostgreSQL before, introduction of the native data type has taken away overheads and made throughput a lot faster for JSON manipulation. JSON & JSONB JSONB was introduced as a native data type in v9.4 of PostgreSQL and it stores JSON objects (more…)

The PostgreSQL License – What Does It Mean For My Business?

Umair's PlanetPostgreSQL
When I talk to various people about PostgreSQL, based on where they are in the process of analyzing it, I get asked many similar questions. One of the most frequently asked questions is: What is the PostgreSQL licensing model? Here is my attempt to address this question from the perspective of what the PostgreSQL license means for your business. Go Ahead, Read It - It’s Really Not That Scary Let’s be honest with ourselves, we are really not in the habit of reading license agreements - they are best left to the legal department to decipher. And seriously, licenses have become so complex and so detailed that even if I try really really hard to read just a few clauses, my head starts to hurt. The PostgreSQL License is different though. I have reproduced the license in full at (more…)

2UDA – Installing PostgreSQL 9.6 the easy way

Umair's PlanetPostgreSQL
PGDG announced the General Availability of PostgreSQL 9.6 on the 29th of September. Like each year, this new release brings with it a lot of exciting new features. Using these features, of course, requires installing PostgreSQL ... a task that can be a touch daunting for novice users. 2UDA, a graphical installer for PostgreSQL, is designed to make the installation as easy as 1-2-3. Admittedly, the primary purpose of 2UDA is to provide a unified package for data analytics using PostgreSQL. But it can just as easily be used to install PostgreSQL on the most popular platforms - Windows, OSX, & Linux. This is especially helpful for Windows & OSX, where users are more accustomed to graphical point-and-click installers. PostgreSQL 9.6 is now available in 2UDA installers and (more…)
Committed to the PostgreSQL Community, 2ndQuadrant Contributes to 9.6

Committed to the PostgreSQL Community, 2ndQuadrant Contributes to 9.6

2ndQuadrant, Featured, Umair's PlanetPostgreSQL
The latest version of PostgreSQL 9.6 is planned to be released later today, bringing with it some much anticipated features and updates. As the most advanced open source database, PostgreSQL strives to release a major version roughly once every year. With an active and collaborative community, this PostgreSQL release boasts impressive features and updates thanks to contributions from many of the highly knowledgeable community members.   The expanding team at 2ndQuadrant has continued to show dedication to the PostgreSQL database project by contributing heavily to the PostgreSQL 9.6 release. Parallel execution of large queries has been a known shortcoming of PostgreSQL for some time, but this is no longer an issue with the 9.6 release. David Rowley and Simon Riggs contributed to (more…)

My experience with the Indonesia PUG meetup

Umair's PlanetPostgreSQL
I was in Jakarta a couple of weeks ago and there happened to be a meetup of the Indonesia PUG in Bandung while I was there. Because it is just a 2 hour, rather picturesque drive, from Jakarta, I thought it was too good of an opportunity to miss. So I showed up.   The meetup was hosted by Julyanto Sutandang of Equnix Business Solutions and the conversation was mostly centered around convincing the local technology industry about PostgreSQL in comparison to Oracle. We got into fairly detailed discussions on the typical challenges of moving an Oracle production database to PostgreSQL. I especially love talking about hierarchical queries - Oracle's CONNECT BY PRIOR and PostgreSQL's WITH RECURSIVE. It was very interesting to find out how popular BDR - the Multi Master  (more…)

Using Querydsl with PostgreSQL

Umair's PlanetPostgreSQL
Querydsl is a domain model centric ORM that was created out of a need to add typesafety to HQL. As demonstrated in my blog on HQL, to write queries using HQL, at times long string concatenations need to be used. Such strings are not only hard to read, they are prone to runtime errors, avoiding which is the main driver for using ORMs. Although it was initially primarily targeted at HQL, Querydsl is now also available for JPA, JDO, JDBC, Lucene, Hibernate Search, MongoDB, Collections and RDFBean. Why Use Querydsl? The biggest advantage Querydsl offers is typesafety. Rather than relying on strings, it provides you the ability of querying your database using functions. This takes care of syntactic correctness and ensures that you don’t get runtime errors. With typesafety, comes the (more…)

Using Java Object Oriented Querying (jOOQ) with PostgreSQL

Umair's PlanetPostgreSQL
jOOQ is an ORM-alternative that is relational model centric rather than domain model centric like most ORMs. For example, while Hibernate lets you write Java code and then automatically translates it to SQL, jOOQ let’s you write relational objects in your database using SQL and then automatically generates Java code to map to those relational objects. The writers of jOOQ believe in the power of SQL and assume that you want low level control of the SQL running for your application. This means that you can write your database and your schema without having to worry about how (and if!) it can be handled in Java. Why Use jOOQ? While JPA provides a huge framework with a great deal of flexibility and power, it can very quickly become quite complex. jOOQ provides a simpler interface for (more…)

Using Hibernate Query Language (HQL) with PostgreSQL

Umair's PlanetPostgreSQL
In my previous blog, I talked about using Java arrays to talk to PostgreSQL arrays. This blog is going to go one step further in using Java against the database. Hibernate is an ORM implementation available to use with PostgreSQL. Here we discuss its query language, HQL. The syntax for HQL is very close to that of SQL, so anyone who knows SQL should be able to ramp up very quickly. The major difference is that rather than addressing tables and columns, HQL deals in objects and their properties. Essentially, it is a complete object oriented language to query your database using Java objects and their properties. As opposed to SQL, HQL understands inheritance, polymorphism, & association. Code written in HQL is translated by Hibernate to SQL at runtime and executed against the (more…)