One of our clients recently inquired about scaling the database tier to account for high volumes of eCommerce transactions on their Broadleaf Commerce installation. They also wanted to account for possible database failures. Broadleaf Commerce provides a linearly scalable application tier. However, the database can often become a bottleneck as it does not scale as easily due to restrictions related to ACID compliance (Autonomy, Consistency, Isolation, and Durability). It’s difficult to achieve ACID compliance in a performant way in a distributed database system. Many newer NoSQL systems, for example, soften the Consistency requirement in favor of eventual consistency. This allows them to be Automic, Isolated, and Durable, but it means that future reads may return inconsistent results prior to the eventual consistency occurring.

Our client’s initial plan was to simply introduce Pgpool-II, a JDBC-compliant load balancer for PostgreSQL, their preferred database. Their intent was to use Pgpool to route all traffic to multiple database nodes. As a result, all write operations (INSERT, UPDATE, DELETE) would be routed to a master, and all reads (SELECTS) would be routed to any one of the nodes, including slaves. This effectively provided eventual consistency, since the master node would replicate all data changes to slave nodes in an asynchronous way.

They originally set up their database, configured Pgpool, and attempted to route database traffic from the application to Pgpool and ultimately to one of the PostgreSQL nodes. The transactions were guaranteed to be ACID compliant between the application and Pgpool, and by proxy, the master node. But this approach failed almost immediately. Why?

First, a little background:

Current production versions of Broadleaf make heavy use of:

  • Spring Framework (including AOP and Spring Transactions)
  • Java Persistence API (specifically Hibernate)
  • A Hibernate table-based Sequence Generator, which stores database sequences in a specific database table called SEQUENCE_GENERATOR to allow us to generically support multiple database platforms without database-specific sequences
  • A good deal of database interaction from background threads (e.g. for jobs, events, and cluster management)

So, why didn’t Pgpool just work?

The first thing that happened when our client tried to use Broadleaf with Pgpool and a Master/Slave configuration is that they got a HibernateException complaining about duplicate primary keys. What happened was as soon as one of the application nodes started interacting with the database it fetched batches of primary keys from the SEQUENCE_GENERATOR table in the DB, and then updated that table so that the next batch that was retrieved would not overlap. Perfectly reasonable since this is the prescribed pattern. But other nodes also started grabbing primary key ranges from the same table. Since database updates are eventually consistent and since SELECT operations are distributed to slaves, we started seeing the same ID ranges being selected by multiple nodes. Essentially we were seeing dirty reads associated with eventual consistency in the PostgreSQL cluster because the update to the ID range had not yet propagated to the other PostgreSQL nodes.

How did we address the problem?

It turns out that PostgreSQL allows query hints to tell Pgpool not to load balance certain queries. Remember that writes (INSERTs, UPDATEs, and DELETEs) are automatically routed to the master. It’s only reads (SELECTs) that are load balanced. So in order to route certain queries to the master, we can add /* NO LOAD BALANCE */ to the beginning of the query.

Hibernate provides sequence generator implementation called org.hibernate.id.enhanced.TableGenerator. Broadleaf Commerce extends that for reasons that are beyond the scope of this blog. This class is called org.broadleafcommerce.common.persistence.IdOverrideTableGenerator. We simply overrode the method to generate the query for the sequence like this:

Incidentally, in order to register your new sequence generator(s), you add them to your persistence.xml (in Broadleaf it’s called persistence-core.xml) as you define your persistence unit. For example:

This forced all queries for sequences to go directly to the master database node.

What about other data?

Our client had a huge catalog of products (many millions). They also had CMS content, pricing data, store data, and other things that don’t change more than every few days for the most part. However, they also have transactional data (e.g. Cart, Customer, Order, Fulfillment Group, etc.) that changes quite often as a user interacts with the site. In order to route volatile transactional data such as this to the master node, we implemented a Hibernate Interceptor (org.hibernate.Interceptor). This allows us to add the same query hint to SELECT statements for certain tables. For example:

This Interceptor is again registered in the persistence-core.xml file:

It looks for table names in queries that are known to be volatile and that cannot be read in a stale way and adds the query hint to force the queries to go to the master PostgreSQL node.

The result of all of this was that queries to the database for catalog, content, pricing, store, and other “master data” continues to be served across multiple slave PostgreSQL nodes, while queries for more volatile “transactional data” such as Customer, Cart, Order, Address, Payment, etc. are routed to the master node so that the results never stale. The master replicates to the slaves for eventual consistency (usually within seconds). This allows for a slave to become master in the event of a failure and provides horizontal scalability of the data tier.

Why not use Synchronous Commit Streaming Replication?

Pgpool does offer the concept of synchronous commit, and allows mechanisms to block until a configured number of nodes are consistent. Our client does a lot of bulk data imports and batch processing through Pgpool. Their DBAs felt strongly that synchronous commit with blocking was not appropriate for their environment. As a result, they chose the master/slave setup. If you can tolerate blocking until all nodes (or all configured nodes) are updated, then the application doesn’t need to worry about such things. As always, it depends on your requirements and situation.

Afternote:
Broadleaf Commerce is moving towards a Microservices architecture and will be supporting NoSQL databases such as MongoDB in the process, in addition to continued support for transactional RDBMS systems such as PostgreSQL.