Monday, January 22

Author: Umair Shahid

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…)

Using Java arrays to insert, retrieve, & update PostgreSQL arrays

Umair's PlanetPostgreSQL
Arrays are a powerful programming feature frequently used by developers, both in Java and in PL/pgSQL. Interfaces can potentially become tricky, however, when the two try and talk to each other. This section explores how you can write simple code that uses the java.sql.Array Interface to insert, retrieve, & update arrays in PostgreSQL. To illustrate the functionality, let’s set up a simple table that stores country names in one column as TEXT and a list of some of the country’s cities in the second column as a TEXT ARRAY. CREATE TABLE city_example ( country TEXT, cities TEXT[] ); Now we will use the JDBC interface to insert data into this table, retrieve it, and update it. Inserting ARRAYs Anyone familiar with Java has used arrays in one form or the other. Before these (more…)

2ndQ talks at PGDay FOSDEM 2016

Umair's PlanetPostgreSQL
It is always exciting to meet community members and exchange ideas about PostgreSQL and the eco-system around the database. I was lucky enough to be a part of FOSDEM PGDay in Brussels on 29th January this year. 3 of 2ndQuadrant's very best spoke at the event. If you missed the talks, you can take a look at their slide decks shared below. Craig's talk on 'Logical Replication with pglogical' Gulcin's talk on 'Managing PostgreSQL with Ansible' Tomas' talk on 'PostgreSQL Performance on EXT4, XFS, F2FS, BTRFS, & ZFS' I will be sure to direct any queries you might have to the experts themselves (more…)

Postgres-XL 9.5: Gearing Towards the Beta Release

Umair's PlanetPostgreSQL
PostgreSQL has typically been described as an OLTP-friendly database and has generally been put to the side for OLAP workloads. While I disagree with this notion to begin with, the new Big Data features of the latest 9.5 release makes such thinking even more obsolete. This is also why I am so excited about the upcoming Beta release of Postgres-XL 9.5. XL, based on PostgreSQL, rose from the phenomenal work done in the GridSQL and PostgresXC projects, that later combined as TransLattice Storm (StormDB). The community actively worked on the project through the 9.2 release, but the activity slowed down after that. Until now, that is … when the EU-funded Big Data project, AXLE, brought it back to life and Postgres-XL has now successfully caught up with the recent PostgreSQL 9.5 release, (more…)

The process that created pglogical

pglogical, Umair's PlanetPostgreSQL
pglogical (logical replication for PostgreSQL) is the latest in the series of awesome products developed & supported by 2ndQuadrant. One of the key ingredients to making any product great is the process followed in developing it. We have tried to notch up our game with pglogical, let me describe some of the measures we have taken to ensure reliability. Version control Like all our other PostgreSQL tools, pglogical is hosted on 2ndQuadrant's private github. Version control tools like github not only have provisions for team coordination, allowing multiple people to work collaboratively on the same project, they also allow for maintaining multiple branches and multiple tags. This ability is extremely important to be able to support and patch production releases, sending out hotfixes (more…)

Creating a PostgreSQL Service on Ubuntu

Umair's PlanetPostgreSQL
Ok, so you have a PostgreSQL distribution on your Ubuntu server. You have created the data directory and initialized the database. Now you want to run the server in a way that users can start, stop, & restart the server in pretty much the same way they do for other daemons and services running on your server. How do you setup a PostgreSQL service on Ubuntu? Once you get to know it, the process is pretty straightforward. The first step is to create a script. Below is a sample. #!/bin/bash ## EDIT FROM HERE # Installation prefix prefix=/path/to/PostgreSQL # Data directory PGDATA="/path/to/data" # Who to run the postmaster as, usually "postgres". (NOT "root") PGUSER=postgres # Where to keep a log file PGLOG="$PGDATA/serverlog" # It's often a good idea to protect (more…)

Enterprise Support – The final hurdle for PostgreSQL?

Howard's PlanetPostgreSQL, United Kingdom News
It's been an interesting 9 months for me personally, joining the 2ndQuadrant team in April 2013 to lead marketing. I've been particularly impressed by the quality and skills of the people I've met - not just from 2ndQuadrant but also the Postgres Community, from many countries around the world. Of course what has also impressed me is the quality of PostgreSQL.  The more I learn, the more I can see there is a hugely exciting opportunity ahead for everyone involved. What's puzzled me more than anything though is how little I personally knew about Postgres. With 18 years of commercial marketing in the IT industry, I confess I knew little about Postgres until joining 2ndQuadrant, so I'm frequently making comments like 'wow' as I learn more! I'm always intrigued to find out what (more…)