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

Recently I came across a specific microservices pattern, and that in turn during research led me to a database theorem. I did not know it, so I decided collecting database theorems in one place. If you know of an additional database related theorem that is missing here, let me know…