Johnny's dev corner

Selecting a database platform

Grails database access is provided through GORM which implements connections to many different database platforms.

The main implementation of is for Hiberate ORM which uses JDBC to connect to a relational database.

There are also GORM implementations for MongoDB, Neo4j, Cassandra and REST.

Given support for JDBC and the special implementations you have a rich selection.

If you already have chosen a platform, you will most likely be able to use it with Grails.

Our selection process

We started out with a set of criterias:

  • open source
  • built in replication
  • scalable and robust
  • be able to run satisfactory on older hardware
  • row level security

We ended up with the choice between mySql and PostgreSQL.

We concluded that with regards to replication, scalability and row level security PostgreSQL came out on top.

I have read that these differences are not there today, but we haven’t done any production on mySql.

Replication

We have been used to replication of data and application from day 1 on IBM Domino platform. Here’s a setup we have been using for many years.

This replication and backup model is based on:

  • The production server runs continuously serving users.
  • Backup of a running production server is problematic for several reasons
    • performance reduction during backup
    • database locking
  • Changes are replicated between production and backup server.
  • The data is backed up from the backup server, which may or may not be taken down for backup. The IBM Domino server is more best to stop before backup, while PostgreSQL is best to back up while running.

We chose to go for logical replication in PostgreSQL. Logical means you have to define which tables in which database should be replicated. The decision fell down on logical replication because of the flexibility.

There were a few surprises:

  • logical replication is very close to instant.
  • a lot of connections is needed
  • the database schema is not replicated. I.e. updating the production server schema will break replication until the backup server is updated
  • a subscription to all tables will not include tables added after the subscription is created, hence the subscription must be renewed.
  • setting up logical replication requires quite a few sql-statements that must be 100% correct. A maintenance application or some maintenance scripts is advised.

This means that physical replication would probably require less maintenance than logical replication.

We haven’t tried physical replication in production, so there will be surprises there too.

A side note on row level security short circuits due to lack of experience

The IBM Domino platform has excellent security configuration possibilities. Our application has built in document editing facilities connected to all processes, including HR. Some documents must be access restricted. On the IBM Domino platform this is done by setting reader and author fields based on logged in user.

With row level security we saw that this could be achieved with PostgreSQL.

BUT – the database connection in Grails is configured with one common database user for all application users. I.e:

Grails user != PostgreSQL database user

=> row level security will not work

Writing up this gives me an idea that it might be possible to generate a dynamic database connection for each logged in user. I’ll make note for a later blog post.

Johnny Haugen Sørgård