Using ECA rules for acting on messages in transactional queueing — This blog discusses an approach to execute ECA (Event-Condition-Action) rules on JSON objects within database transactions in PostgreSQL — specifically on transactional queues. This guarantees exactly once execution and consistency of rule execution in case of failures (independent of the underlying reason). Background ECA rules is an established concept in the…

Architecture Solution — Many organization strive to be prepared for cloud region or cloud service outages, as unlikely as they are. Other organizations must have a disaster recovery plan and process in place because of internal policies or external requirements or regulations. This blog presents a disaster recovery process that reestablishes the original…

Reusing existing function for correctness check — Syntactic correctness of stored expressions The evaluate() function is a useful approach for using stored expressions in WHERE clauses of SQL queries, as outlined in Part 1: evaluate() PostgreSQL Function for Evaluating Stored Expressions (Part 1). In Part 1, in section “Improvement”, I highlighted the fact that expressions are stored in a varchar column (since…

Standing on the shoulders of database giants — Query predicates as data I am working on a use case where a SQL query predicate (used in a WHERE clause) is stored in a table column as text. This predicate is used in SQL queries when selecting JSON objects from tables. A good example is customers shopping for cars. For each customer their…

Some performance numbers when inserting JSON into AlloyDB for PostgreSQL — As outlined in Part 1 (JSON in PostgreSQL (Part 1: Setup and Measurement)) this blog shows the performance numbers I got with AlloyDB for PostgreSQL. Overview of setup The table and query is exactly the same as in Part 1. …

Observations on JSONB insert elasticity — I was wondering about the JSON document insert performance in PostgreSQL and the extent it varies with document size. Overview PostgreSQL supports JSON as a data type and I was curious how insert performance changes with the size of a JSON document being inserted. To get an impression to the extent…

Some performance numbers when deploying into AlloyDB for PostgreSQL — Recently Google Cloud made AlloyDB for PostgreSQL available for preview. This is a great opportunity to run the queue example from Part 1 in AlloyDB for PostgreSQL to get performance numbers from a brand-new production system. AlloyDB for PostgreSQL configuration I decided to use the largest deployment option available in AlloyDB for PostgreSQL currently…

And some initial performance measurements — Out of curiosity (ha!) I implemented a queue in PostgreSQL with readers (enqueue) and writers (dequeue). I did some basic performance measurement on my laptop with pgbench to get a ballpark idea of how many queue elements can be enqueued and dequeued. Overview: queue A queue is a data structure that writers…

A brief software architecture thought experiment — Regularly I come across the assertion that structural distribution of deployment units or resources is inherently beneficial and can be used as a basic base pattern in the architecture design process. As a consequence a distributed architecture does not need rationalization or justification, or so I am told. I disagree. Thought experiment setup: two infinite networked computers