Author Archives: ksbhaskar

TL;DR

This is a post about connecting R to VistA via Octo and generating interesting statistical analysis and diagrams. At least look at the diagrams, which were all directly generated from R!

Introduction

I learned Statistics in college 20 years ago. I hoped to specialize in the area, but I eventually found that you must have a stomach for sitting for hours at a screen examining data and cleaning it up. No data ever comes clean.

Back then, the most popular software was SAS and SPSS. I learned SAS in school, but I barely remember any of its syntax. Fast-forward 15 years — my work with OSEHRA, WorldVistA, and the National Library of Medicine (NLM) got me in contact with a remarkable Open Source community called OHDSI. I first met them at an RxNorm conference at NLM. OHDSI was most remarkable because it was a functional open-source community. I had conversations with great people on how the community worked, but that’s a discussion for another time.

This community used a software package for statistics called “R”. I learned more about R over the years — it’s extremely capable (as you will see later in this blog post) — but it famously has very obscure syntax: assignment is <-, piping %>%. Any operator can be overloaded, so you can embed surprises for your users. And you have to get used to think of your variables as “vectors” — natural in statistics, as that is how you have to think.

Today, R is (depending on the industry) the first/second most commonly used statistics package in the world. A thriving ecosystem of academics who use it to conduct studies but whose pay is not tied to the software itself means that maintainers can be easily found, sidestepping a common problem in open-source where people who maintain software are often never paid for it.

The object of the post is connecting R to VistA. Before we jump in, a brief introduction to VistA is in order: VistA is an Electronic Medical Record (EMR) originally developed by the Veterans Administration of the US Government, and was a leader for a long time in usability and innovation. It found some commercial success in the private sector in the US between 2005 to 2020, but has found its usage wane recently in favor of Epic and Cerner in line with the rest of the EMR industry. In Jordan, it is successfully used to run the health care system of the entire country. VistA code is public domain.

Statistics using R from VistA

I created 1,160 patients using Synthea, and loaded them into a VEHU image. Then, I used R with the PostgreSQL connector to connect to a Rocto listener. Rocto is part of Octo, YottaDB’s SQL-to-globals mapping software. VistA globals are “projected” to SQL tables using the scripts in YDBOctoVistA, including the new lab_chem_result.sql which makes lab data in VistA accessible from SQL.

I came in with specific ideas on which statistics to run. I used Claude Code to connect to the VistA database and run various ad hoc analyses on the data. I will refer to Claude directly when the work done is its own rather than mine.

I broke down this long post into the following sections:

• Connecting to the data
• Getting select vitals for analysis
• Analyzing the growth of children (chart, t-test)
• Analyzing hypertension diagnoses vs BMI (chart, chi-square)
• Analyzing blood pressure measurements vs BMI (chart, Pearson’s coefficient)
• Distribution of HbA1c (chart)
• Correlation of glucose and HbA1c (chart, Pearson’s coefficient)
• Trying all the analyses for yourself
• Software used

These are synthetic patients. The cohort was generated by Synthea and loaded into a full VistA instance. Except for the blogroll image and featured image (above), which were generated from a public domain dataset, no real patient data appears anywhere in this post.

Connecting to the data

R connects to a PostgreSQL database — the Octo protocol emulates PostgreSQL; the Octo documentation covers this under Connecting from R:

library(DBI)

con <- dbConnect(RPostgres::Postgres(),
                 dbname = "octo", host = "localhost", port = 1338,
                 user = "admin", password = "admin")

Getting select vitals for analysis

After connection, this query gets vitals from VistA and joins it to the patient file, restricted to Synthea patients. It returns 48,493 rows of blood pressures, heights and weights.

# One row per measurement, read straight from VistA's vitals global.
vitals <- dbGetQuery(con, "
  SELECT v.patient, v.vital_type, v.rate, v.date_time_vitals_taken,
         p.sex, p.date_of_birth
  FROM   gmrv_vital_measurement v, patient p
  WHERE  v.patient = p.patient_id                      -- vitals to demographics
    AND  v.patient >= 101076 AND v.patient <= 102235   -- the Synthea cohort
    AND  v.vital_type IN (1,8,9)                       -- BP, height, weight
")

 
VistA stores each measurement as text in a single field: blood pressure as "120/80", height in inches, weight in pounds.

The listing below massages the vitals data: a patient’s age is computed for every reading, and the systolic and diastolic blood pressures are split apart.

library(dplyr)

# Still one row per measurement. Add the fields the analysis needs:
vitals <- vitals %>% mutate(
  # how old the patient was, in years, when this reading was taken
  age_at = as.numeric(as.Date(date_time_vitals_taken) -
                      as.Date(date_of_birth)) / 365.25,
  # a blood pressure arrives as one string, "120/80", so split it in two
  sys    = as.numeric(ifelse(vital_type == 1, sub("/.*", "", rate), NA)),  # systolic
  dia    = as.numeric(ifelse(vital_type == 1, sub(".*/", "", rate), NA)),  # diastolic
  # height and weight are already a single number: keep it unqualified
  val    = as.numeric(ifelse(vital_type == 1, NA, rate)))

Analyzing the growth of children (chart, t-test)

Claude analyzes here the difference between boys and girls. Two statements of R produce the chart:

library(ggplot2)

# Every height reading (vital_type 8) taken before the patient turned 18.
peds <- vitals %>% filter(vital_type == 8, age_at < 18)

# Height against age, one curve per sex.
ggplot(peds, aes(age_at, val, colour = sex, fill = sex)) +
  geom_smooth(method = "loess", span = 0.4) +   # fitted curve + 95% band
  labs(x = "Age (years)", y = "Height (inches)")

 
The curves for boys and girls track each other through childhood and then separate in the mid-teens, which is what the literature describes: the adolescent growth spurt arrives “on the average, nearly 2 years later in boys than in girls”,¹ while girls are briefly the taller group; boys grow for longer, overtaking girls. At 17, boys average 69.4 inches and girls 64.4 — a difference of 5.0 inches. The Welch two-sample t-test below shows that the height difference at 17 is statistically significant.

> # every height reading taken during the patient's 17th year
> h17 <- peds %>% mutate(age_yr = floor(age_at)) %>% filter(age_yr == 17)
>
> # compare the sexes, without assuming their variances match
> t.test(val ~ sex, data = h17, var.equal = FALSE)

	Welch Two Sample t-test

data:  val by sex
t = -11.437, df = 162.73, p-value < 2.2e-16
alternative hypothesis: true difference in means between group F and group M is not equal to 0
95 percent confidence interval:
 -5.870369 -4.141760
sample estimates:
mean in group F mean in group M
       64.35644        69.36250

Analyzing hypertension diagnoses vs BMI (chart, chi-square)

Does a hypertension diagnosis go with high body-mass index (BMI)? First, the diagnosis: A VistA problem can be coded in ICD-9, ICD-10 or SNOMED CT. This query asks for all three:

htn <- dbGetQuery(con, "
  SELECT DISTINCT pr.patient_name AS patient
  FROM   problem1 pr, icd_diagnosis i
  WHERE  pr.diagnosis = i.icd_diagnosis_id
    AND  pr.patient_name >= 101076 AND pr.patient_name <= 102235
    AND  i.code_number IN ('401.0','401.1','401.9',   -- ICD-9  essential hypertension
                           'I10.','I15.0')            -- ICD-10 same, VistA spelling
  UNION
  SELECT DISTINCT pr.patient_name AS patient
  FROM   problem1 pr
  WHERE  pr.patient_name >= 101076 AND pr.patient_name <= 102235
    AND  pr.snomed_ct_concept_code IN ('59621000',    -- SNOMED essential hypertension
                                       '38341003')    -- SNOMED hypertensive disorder
")

 
Now the BMI. The chi-square test is by patient, not by reading, as the unit of analysis. The measurements are collapsed into one row per adult carrying a mean height, weight and systolic pressure, with the BMI computed from height and weight:

# Collapse to one row per adult patient, averaging their repeated readings.
pat <- vitals %>% filter(age_at >= 18) %>%
  group_by(patient) %>%
  summarise(sex    = first(sex),
            height = mean(val[vital_type == 8], na.rm = TRUE),   # inches
            weight = mean(val[vital_type == 9], na.rm = TRUE),   # pounds
            sys    = mean(sys, na.rm = TRUE)) %>%                # mmHg
  mutate(bmi = 703 * weight / height^2,   # 703 converts lb/in^2 to metric BMI
         # attach the diagnosis found by the query above
         htn = factor(ifelse(patient %in% htn$patient,
                             "Hypertension coded", "Not coded"),
                      levels = c("Not coded", "Hypertension coded")))

 
Cross-tabulating diagnosis against BMI class gives six cells:

 
A chi-square test of independence is the correct statistical test here: both variables (BMI and Hypertension Diagnosis) are categorical, and every expected cell count comfortably exceeds five. In R that is one line:

> # sort each adult into a BMI class at the standard cut-points
> pat$bmi_class <- cut(pat$bmi, c(-Inf, 25, 30, Inf),
+                      labels = c("Normal", "Overweight", "Obese"))
>
> # cross-tabulate class against diagnosis, and test for independence
> chisq.test(table(pat$bmi_class, pat$htn))

	Pearson's Chi-squared test

data:  table(pat$bmi_class, pat$htn)
X-squared = 32.773, df = 2, p-value = 7.646e-08

 
The same counts, as a chart:

 
The striking cell is the first one in Table 1: only 2 of 154 normal-weight adults carry a hypertension diagnosis. Independence predicts about 26 — the expected count for a cell is its row total times its column total, divided by the grand total, so 154 normal-weight adults × 154 coded hypertensives ÷ 918 adults = 25.8. That gap, repeated across the six cells, is what the chi-square statistic measures.

Analyzing blood pressure measurements vs BMI (chart, Pearson’s coefficient)

So weight predicts the diagnosis of hypertension; does it predict the actual blood pressure?

No. A Pearson correlation asks whether two continuous measures move together in a straight line. Between BMI and systolic pressure, it finds no association:

> # is there a straight-line relationship between BMI and systolic pressure?
> cor.test(pat$bmi, pat$sys)

	Pearson's product-moment correlation

data:  pat$bmi and pat$sys
t = 0.36357, df = 916, p-value = 0.7163
alternative hypothesis: true correlation is not equal to 0
95 percent confidence interval:
 -0.05273312  0.07665606
sample estimates:
       cor
0.01201175

 
An r of 0.012 means no association worth the name; an r of 1 would be a perfect one. The confidence interval straddles zero, so the data show no evidence of a straight-line link at all. Plotting one against the other makes it plain:

 
This is an artifact of Synthea; in real populations BMI and blood pressure are linked: a meta-analysis of 25 randomized trials covering 4,874 participants found that every kilogram of weight lost lowers systolic pressure by about 1 mmHg.²

Distribution of HbA1c (chart)

Claude plotted the distribution of HbA1c — the standard three-month measure of blood-sugar control. A surprise was lurking in the data:

 
An HbA1c below 4% does not occur in living people. Fifty-nine patients here have one; and all 59 are on insulin or metformin. Treated diabetics, the very patients whose HbA1c matters most, have impossible values. The other 88% of the cohort looks entirely normal.

Here’s how Claude chased the impossible data down:

# Mean HbA1c per patient. lab_data maps LRDFN to DFN via name field to allow us to limit to only Synthea patients
a1c <- dbGetQuery(con, "
  SELECT d.name AS patient, AVG(CAST(r.result_value AS NUMERIC)) AS a1c
  FROM   lab_chem_result r, lab_data d
  WHERE  r.lrdfn = d.lrdfn AND d.parent_file = 2
    AND  d.name >= 101076 AND d.name <= 102235
    AND  r.test = 462 AND r.result_value IS NOT NULL
  GROUP BY d.name")

# Who is on insulin (VA drug class HS501) or an oral hypoglycaemic (HS502)?
rx <- dbGetQuery(con, "
  SELECT p.patient, g.va_drug_class_code
  FROM   prescription p, drug g
  WHERE  p.drug = g.drug_id
    AND  p.patient >= 101076 AND p.patient <= 102235")

on_drug <- unique(rx$patient[grepl("^HS50[12]", rx$va_drug_class_code)])
a1c$treated <- a1c$patient %in% on_drug
table(impossible = a1c$a1c < 4.0, treated = a1c$treated)

          treated
impossible FALSE TRUE
     FALSE   384   49
     TRUE      0   59

 
Of note, the glucose values for the same patients were all physiologically normal, which brings us to the next section.

Correlation of glucose and HbA1c (chart, Pearson’s coefficient)

Blood glucose and HbA1c measure the same underlying reality on different timescales, and the relationship between them is well established: the ADAG study³ gives estimated average glucose = 28.7 × HbA1c − 46.7. In real life, glucose and HbA1c should be somewhat correlated. How do they correlate in the data?

 
Clearly, there is no correlation: r = 0.027, p = 0.56 across 492 patients, showing that Synthea data again doesn’t produce realistic data in this scenario. Examination of the Synthea source shows the issue: it selects a glucose at random from a range and derives their HbA1c separately, so the two never have to agree — reported as synthea#1694.

Trying all the analyses for yourself

I published the Docker image with VistA containing 1,160 Synthea patients (1,000 live ones) and Octo ready to go. Run it like this:

docker run -d -p 127.0.0.1:2222:22 -p 127.0.0.1:8001:8001 -p 127.0.0.1:9430:9430 \
    -p 127.0.0.1:8089-8090:8089-8090 -p 127.0.0.1:1338:1338 \
    --name=vehu yottadb/octo-vehu:2026-08-synthea1000

 
ROcto comes up with the container, so port 1338 is serving as soon as it starts. That is the connection Listing 1 opens, and from there every query in this post works as printed.

Then run analysis.R, the script used to generate the results for this post. It needs R with DBI, RPostgres, dplyr and ggplot2, and it regenerates every figure and every number quoted here — the chi-square, the t-tests, the correlations and all five charts — in a few seconds.

Software used

• VEHU VistA, which I maintain monthly with patches released from the VA; patched with
• VistA-FHIR-Data-Loader; and
• VistA-DataLoader.

I used Synthea to generate 1,000 live patients, then imported them into VistA; the instructions are in VistA-FHIR-Data-Loader.

The OSEHRA VistA repo scripts exported the data, which docker-vista reassembled into a new, smaller image yottadb/octo-vehu:2026-08-synthea1000.

The VistA image includes the SQL DDLs from YDBOctoVistA — notably lab_chem_result.sql, which lets you read lab data in VistA, which is not stored in a traditional FileMan format.

YottaDB r2.06 and Octo 1.1.0 inside that image project the M globals to SQL and serve them over the PostgreSQL wire protocol.

I ran the yottadb/octo-vehu:2026-08-synthea1000 image with Docker. It includes R 4.3.1, with sub-modules DBI 1.2.3, RPostgres 1.4.10, dplyr 1.2.1, and ggplot2 4.0.3.

I used Claude Code to investigate the database for potential stories for this blog, to write the R scripts and to prepare the diagrams. It is much better at statistics than I am. I only included statistical concepts I learned in college 20 years ago, including Pearson’s r coefficient, chi-square, and t-tests.

Documentation:

• Octo.
• Connecting from R.
• VistA mapping utilities.

We recently received a feature request for Octo to natively support “Piece of Piece”; e.g., to declare a column in Octo corresponding to a construct like $PIECE($PIECE(^A,"|",3),"^",2). Although Octo could previously represent such a use case with a computed column, a computed column cannot be indexed; so a query will be expensive. Octo now allows such a column to be defined and indexed.

As part of this work, I took the opportunity to implement the extraction of Lab Chemistry Data in VistA. What’s the connection? VistA data is almost always stored as ^ delimited pieces (e.g., A^B^C) or as Extract ranges (ABCXYZ range 1-3 = ABC). Only in the storage of Chemistry Data in the Lab Package do we get a sub-piece: A^B^C1!C2!C3. To make it concrete, here is some made-up Chemistry Data from VistA:

^LR(314452,"CH",6879595.9,0)="3120403.1^1^3120403.110048^111346^73^CH 0403 350^^CBS A^^184920^6D^^1738;SC(^648"
            41)="66^H^82550.0000!82550.0000!2157!4151!!!197^111346^73!28!181!!!!IU/L^^^^648^1"
        "NPC")=3
        "ORU")=1120940350

Notice how under the 41 node, the third ^ separated piece has ! delimited data within it.

Fileman to this day cannot read this data except using computed fields because it cannot represent “piece of piece”. But if you have the latest Octo, you can grab lab_chem_result.sql from YDBOctoVistA to unlock your Lab Data. Here’s how some of the data (the third ^ delimited piece above) is represented in Octo:

 -- Piece 3 (workload) -- "!"-sub-delimited:
  NATIONAL_LAB_CODE`      VARCHAR DELIMS ("^","!") PIECES (3,1), -- 3.1 National Lab Code
  RESULT_NLT_CODE`        VARCHAR DELIMS ("^","!") PIECES (3,2), -- 3.2 Result National Lab Code (+suffix)
  LOINC_CODE`             VARCHAR DELIMS ("^","!") PIECES (3,3), -- 3.3 LOINC (no checksum char)
  WORKLOAD_SUFFIX`        VARCHAR DELIMS ("^","!") PIECES (3,4), -- 3.4 Workload Suffix
  TEST_IEN_FILE_60`       INTEGER DELIMS ("^","!") PIECES (3,7), -- 3.7 IEN pointer to file 60 ^LAB(60,

Here is a sample query and results from fictitious data:

OCTO> select * from LAB_CHEM_RESULT WHERE LRDFN=27 AND RESULT_FLAG='H*';
lrdfn|invdt|test|result_value|result_flag|national_lab_code|result_nlt_code|loinc_code|workload_suffix|test_ien_file_60|verifying_tech|site_specimen|reference_low|reference_high|critical_low|critical_high|units|delta_check_type|delta_value|default_value|therapeutic_low|therapeutic_high|institution|equipment_id
27|6949291.8968|2|350|H*|84330.0000|||3096|175|11743|72|60|110|50|300|mg/dL||||||500|
27|6949681.984076|2|310|H*|84330.0000|||3096|175|11748|72|60|110|50|300|mg/dL||||||500|
27|6949681.984076|132|17.5|H*|81323.0000|||3096|440|11748|72|0|8.9||15|U/L||||||500|
27|6969570.894999|2|321|H*||||3096||20364|72|60|123|50|300|mg/dL||||||500|
27|6999477.92|2|400|H*|84330.0000|||||11748|72|60|123|50|300|mg/dL||||||500|
(5 rows)
OCTO>

Credits

• Video of Janus droplets Copyright Rakesh Narani used with permission.
• Visualization of haemoglobin by Zephyris used under the terms of the GNU Free Documentation License.

We again welcome Lothar Jöckel to the YottaDB blog, with his second guest post, based on a talk he gave at NimConf2026 about his software, Nimetic.

Modern web development is drowning in JavaScript fatigue, but there is a powerful alternative that allows you to build Single Page Applications (SPAs) with zero user client-side JavaScript. In our recent virtual presentation, Nimetic – Building Zero-JS SPAs with Nim, Datastar, and YottaDB, we showcased how to fuse a lightning-fast backend with a robust, hierarchical NoSQL database engine. By combining the hyper-efficient Nim programming language, the real-time event streaming of Datastar, and the battle-tested, daemonless power of YottaDB, developers can completely bypass heavy frontend frameworks without sacrificing reactivity.

Why This Stack Changes Everything

Modern SPAs usually require a massive node_modules folder, complex build pipelines, and heavy hydration steps. Nimetic changes the paradigm by handling state logic on the backend and streaming targeted HTML updates over Server-Sent Events (SSE).

Here is how the three core components interact:

• Nim Backend: Serves as the hyper-performant backbone using the multi-threaded MummyDS HTTP server. Nim gives us the raw compilation performance of C paired with the clean, elegant readability of Python.
• Datastar Frontend: A lightweight hypermedia framework (an 11.76KiB file) that uses simple HTML attributes to handle frontend reactivity, using SSE to patch the DOM automatically.
• YottaDB Storage: The ultimate language-agnostic NoSQL database. It is an ultra-low latency, in-memory, hierarchical key-value store that eliminates the typical database bottlenecks found in real-time web applications.

The Superpower of YottaDB in Nimetic

Integrating a database into a high-performance web app often means dealing with heavy daemons and network latency. YottaDB eliminates these hurdles via its unique architecture. Several key advantages of utilizing YottaDB are:

• Zero Cost Abstractions via Metaprogramming: Nim’s powerful macro system allowed us to build seamless, zero-cost abstractions on top of the official nim-yottadb language binding. This lets developers access hierarchical globals, native transactions, and database iterations using clean, idiomatic Nim syntax with no runtime performance penalty
• Daemonless Architecture: Unlike traditional databases, YottaDB runs directly in the process address space of the application. There is no separate daemon process to configure, maintain, or tune. The first process opens the database structures, and the last one cleans them up. This eliminates inter-process and network overhead, making data operations blazingly fast.
• Rock-Solid ACID Transactions: Even though it scales perfectly down to embedded hardware like a Raspberry Pi (while scaling up to the largest real-time core-banking and electronic health record systems), YottaDB delivers strict, enterprise-grade ACID transactions. For real-time applications handling rapid user state changes, this guarantees absolute data integrity under heavy concurrent loads.
• Session Persistence: Since the session state is stored in the database, a session can be resumed virtually instantaneously.

Curious About Nim? Check Out These NimConf 2026 Highlights!

If Nimetic has sparked your interest in the Nim programming language, you are not alone. Nim offers a unique blend of systems-level control, Python-like syntax, and an expressive compile-time macro system.

To see what else the Nim ecosystem is capable of, we highly recommend checking out these fantastic sessions from this year’s conference:

• Extending Enu and Building 3D Worlds with Claude: Learn how developers are using Nim alongside LLMs to script and generate rich, interactive 3D sandbox worlds.
• Why I Nim: A love letter to a technology that changed my life.

You can also look at other talks in the Social & Workflow and Project tracks on YouTube

Get Started Today!

The code and concepts shown at NimConf 2026 prove that backend-driven hypermedia is a viable, high-performance future for web development.

Ready to build your own high-speed, daemonless applications?

• Review the RSS Feed sample application to see examples of the bindings in action.
• Read Nim Meets YottaDB for a quick architectural guide.
• Go through the Nim documentation.

I hope you enjoy Nim and YottaDB as much as I do!

Credits

• Screenshots from NimConf 2026 web page and author’s presentation.

I spent the week of June 7, 2026 in Amman, Jordan with the DevOps team of Electronic Health Solutions (EHS). We monitored the health of the servers, reorganized the database, brought up a new server, and did training. YottaDB and EHS also signed an extension of our support agreement.

Unlike the slow-moving VistA replacement effort at the US Department of Veterans Affairs (VA), EHS has continued to invest in and modernize VistA, transforming it into Hakeem – a true Jordanian digital health success story. While the VA has largely underinvested in maintaining and evolving VistA, allowing it to be characterized as outdated, EHS has leveraged the platform as a foundation for innovation. Examples include the My Hakeem mobile app for patients, and the Dr@Hakeem app for healthcare providers which enables clinicians to manage appointments, access patient medical records, and support clinical decision-making from anywhere.

Today, several Hakeem implementations are operated or supported by EHS across Jordan.

• All Jordanians are entitled to free or low cost healthcare, through the Ministry of Health (MoH). The MoH Hakeem system is the largest healthcare information system in the country, connecting all public hospitals and most public clinics through a single VistA-based platform. As a result, a patient from Aqaba receiving treatment in Amman can have their complete medical history accessed instantly. During my visit, I observed peak usage of around 9,000 concurrent users with the infrastructure handling the load effortlessly. This may well be the largest VistA deployment in the world. As the rollout expands to additional clinics, the number of concurrent users is expected to grow further.
• Members of the Jordanian armed forces, military retirees, and their families receive care through the Jordanian Royal Medical Services (RMS), which operates its customized and integrated Hakeem system. Whie smaller than the MoH deployment, it serves a large and important patient population.
• The King Hussein Cancer Centre, one of the leading cancer institutions in the Middle East, also operates its own specialized and integrated Hakeem implementation tailored to the unique needs of oncology case.
• In addition, several smaller, specialized healthcare organizations across Jordan operate Hakeem systems designed to support their specific clinical and operational requirements.

It was not all work and no play. One evening after we were done in the office, my hosts took me to the Dead Sea, about an hour’s drive from Amman. The altimeter on my watch registered 330m below sea level. In comparison, it showed the parts of Amman I was in at around 1000m above sea level – since I did not calibrate it for ambient air pressure, the difference in height is more meaningful than the actual readings.

My hosts were the epitome of graciousness, especially when it came to food. They took me out to wonderful dinners at Khashoka, Alia and Fakhreldin restaurants, and a take-out lunch from one of my personal favorites, Abu Jbara. Jordan has the world’s best falafel in my opinion – you can’t go wrong ordering falafel anywhere, from a hole-in-the-wall to the finest restaurant. It is always crisp outside, soft inside, and tastes delicious. Some places have interesting embellishments such as sesame seeds on the crust. When he learned that I liked mujadara, one of my hosts brought in home-cooked mujadara for my lunch the next day.

Were there any clouds in the silver lining? Yes, two.

• Since seven out of ten Jordanian men, and almost a third of women, smoke, it’s hard to get away from the odor of tobacco. With a few exceptions, it would not be uncommon for diners at the next table to light up, often ordering shishas from the restaurant. Fortunately, my room at the Grand Hyatt Amman was on a non-smoking floor, and there was no smoking in any of the indoor public areas or at breakfast.
• From time to time, air-raid sirens would go off as missiles and drones flew through Jordanian airspace, followed by the all-clear a few minutes later. I had not heard air-raid sirens since my college days in India, at the time of the 1971 birth of Bangladesh. People in Amman ignored the sirens and life went on as normal, but they were upsetting to the young son of one of my hosts.

EHS and YottaDB have more than a vendor-customer relationship. We are proud to be a partner in the journey of EHS and Hakeem, the most successful VistA implementation I have seen. It was simultaneously exhilarating and sobering to realize that the medical records of almost everyone I set eyes on resided in a YottaDB database. I always enjoy Jordan, and look forward to my next visit.

Credits

• Photographs by the author’s camera.

We thank Jordan Lenchitz for his first guest blog post, and hope there are many more to follow. If you would like to post on the YottaDB blog please contact us at info@yottadb.com.

When it comes to experimental video art the database you choose shapes what is possible, what is fast, and what is sustainable.

My Creative Process

Step one: global generation. I build MUMPS routines that encode my compositional ideas into global subscripts. These routines embody the algorithmic decisions that define a unique work of experimental video art. Each run of a MUMPS routine generates thousands of nodes representing frequencies, timings, spatial coordinates, color values, and whatever my experimental video art requires.

Step two: replication filters. My generated globals flow through replication filters to apply the “business logic” for the experimental video art on SI replicas.

Step three: relationalization. SQL queries run on the replicas to map the global subscript structure onto relational tables. This is where the raw algorithmic output becomes queryable: I can ask “give me all frequencies between 400 and 500 hertz” or “find the points where two voices intersect” without regenerating any globals.

Step four: connection to Pure Data. I connect to the SQL database from Pure Data, the realtime visual programming environment, using its PostgreSQL connector. Pure Data reads the relationally mapped data and renders it, synthesizing the audio, driving the visuals, creating the final artwork. The database becomes the bridge between algorithmic conception and sensory experience.

Why YottaDB

I could have used a relational database for steps three and four and stopped there (and many people do!) but then I would have lost steps one and two. I would have written the algorithms in some other language, serialized them into SQL, and given up the expressiveness of MUMPS for algorithmic composition. YottaDB gives me the whole stack. MUMPS is extraordinarily good at manipulating hierarchical data, which is what deeply nested algorithmic composition actually looks like. Global subscripts are how these systems want to be written, not a workaround for something else. The replication layer means I don’t have to choose between computational power and queryability. I can generate at full speed in one environment and expose relational views to Pure Data without exporting or rebuilding anything. The separation between the algorithmic engine and the consumer interface happens at the database layer, not in application code. Performance matters too; generating thousands of data points, running replication filters across all of them, then querying subsets in real time as Pure Data renders is not a trivial workload and YottaDB has no trouble with it.

The Why Behind the Why

Databases are opinionated because they encode assumptions about what data looks like, how you access it, what’s fast, and what’s slow. Most of them assume relational structure from the start but YottaDB assumes you might want hierarchical algorithmic structure first and relational views of that same data later. For my creative work, this is the whole ballgame. Algorithmic composition lives in the hierarchical world where relations come later (as a view!). A database that forces you to think relationally first takes away the tools that make algorithmic thinking natural. YottaDB lets MUMPS be MUMPS and SQL be SQL in the same system, without translation layers or duplication. That has shaped every work of experimental video art I’ve made since 2023. If you’re building systems where algorithmic or hierarchical data generation matters and you need relational queryability later, YottaDB is worth a serious look. It trusts you to think in the structures that actually fit your problem and it will let you make strange and beautiful things.

About Jordan Lenchitz

Jordan Lenchitz is a MUMPS programmer and experimental video artist based in Verona, Wisconsin. He holds degrees in mathematics, French, and music composition from Indiana University and is completing a PhD in music theory and composition at Florida State University. (His dissertation examines listening to a cappella vocal musics as an extension of Eleanor Gibson‘s ecological approach to perception.) His creative work centers on extended just intonation and algorithmic composition as well as contributions to open source projects. You can listen to his video work and contact him through his website.

Credits

• Screenshots from pareidolia and in memoriam ben johnston used with permission of Jordan Lenchitz.

Koalas are some of the cutest animals around, but are listed as vulnerable by the International Union for Conservation of Nature and Natural Resources and listed as endangered by the Australian Department of Climate Change, Energy, the Environment and Water.

I wanted to explore a JSON dataset with the ZYDECODE command introduced in YottaDB r2.04. I found a JSON dataset of Koala records for twenty years to 2014 in Noosa Shire in Queensland, downloaded it, and renamed the file to NoosaShireKoalaRecordsto2014.json. I then loaded the raw data into the line local variable from which I parsed the JSON into the ^koala global variable. Note that ZYDECODE expects the top level of line to be the count of lines that make up the JSON file, which is one less than i, since the last value of i corresponds to when YottaDB encountered the end-of-file.

YDB>set file="/Distrib/Datasets/NoosaShireKoalaRecordsto2014.json" open file use file

YDB>for i=1:1 read line(i) quit:$zeof

YDB>use $principal set line=i-1 zydecode ^koala=line

YDB>

With some knowledge of the schema of the dataset, I used AIM to cross reference the number of koalas seen at each sighting, and determine the maximum number of these asocial animals that were observed at a sighting (which turns out to be 4).

YDB>kill sub set sub(1)="""features""",sub(3)="""properties""",sub(4)="""NUMBER_SEE"""

YDB>set koalanumx=$$XREFDATA^%YDBAIM("^koala",.sub,,,,,,1)

YDB>write $order(@koalanumx@(0,""),-1)
4
YDB>set x="" for  set x=$order(@koalanumx@(0,4,x)) quit:""=x  write x,!
480
744

YDB>

So what are these locations at which four koalas were sighted at a time?

YDB>for x=480,744 write ^koala("features",x,"properties","TOWN"),!
Doonan
Noosa Heads

YDB>

So, if I want to see koalas in Noosa Shire, Doonan and Noosa Heads are the places I should visit!

More importantly, I hope you can see how useful YottaDB r2.04’s ZYDECODE is when used with AIM to explore and analyze JSON datasets. YottaDB r2.04 also has a complementary ZYENCODE command to convert global and local [sub]trees to JSON, using an encoding that allows [sub]trees with values at roots to be converted to JSON and back without data loss using ZYDECODE. We look forward to hearing from you as you try the functionality

Credits

• Photograph of Koala used under the Creative Commons Attribution 4.0 International licence (CC BY 4.0) license.
• Photograph of Noosa Heads used under the Creative Commons Attribution-Share Alike 4.0 International license.

Although it has been over a year since we released r2.02, we have not been idle. Unlike Santa’s elves, who must be ready in time for Christmas no matter what, r2.02 was such a robust release that we had the luxury of taking our time to get things into r2.04 that we wanted to. We couldn’t get everything in – in the software world, there is always something that has to be deferred – but we believe r2.04 was worth the wait.

We originally intended r2.04 to focus on performance, and it does. We blogged about critical section performance in Critical Section Performance in r2.04. But performance took on a life of its own, and we did so much more. Every release adds functionality, and the major functionality added in r2.04 is the ability to convert between M and JSON. And, as with performance, there is so much more in the release than that. You can read the release notes and see the development details. With everything in it, r2.04 is our biggest release yet, reminiscent of the Antonov An-225 Mriya above, which at 253 metric tons had the largest carrying capacity of any aircraft ever built.

Performance

In addition to the critical section performance enhancement, performance related enhancements in r2.04 include:

• Faster object code using the naked reference template – the compiler automatically detects places where it can use the faster object code template for naked references, including code where the M language does not support naked references.
• Garbage collection with no sorting – sorting consumes a major part of the time spent in garbage collection; eliminating it allows applications to run faster, while using more memory. In r2.04, an application can choose between speed and memory.
• Faster runtime code for both inside and outside critical sections – in addition to critical section performance, r2.04 includes many other optimizations.
• A JOB command that is as much as five times faster – applications that JOB processes to handle incoming TCP connections should benefit from this.

Below are two graphs to demonstrate the performance improvements in r2.04. The benchmarks were run on a Red Hat Enterprise Linux 10 system using the default garbage collection, i.e, with sorting. In our testing, we did not observe any material difference in performance between ext4 and xfs filesystems.

The benchmarks compare performance on the following YottaDB releases and GT.M versions:

• YottaDB r2.02, our previous release.
• GT.M V7.1-002, which has been merged into r2.04.
• GT.M V7.1-010, the latest GT.M version as of the r2.04 release. Although V7.1-010 is not merged into r2.04, you can compare the performance of r2.04 against V7.1-010.
• YottaDB r2.04.

In both cases, the y axis is runtime in seconds, i.e., less is better.

The first benchmark simulates interest posting on accounts by a real-time core-banking application.

The second benchmark uses cooperating concurrent processes to calculate the lengths of 3n+1 sequences for integers from 2 through one million.

Observations

• Both benchmarks show that GT.M V7.1-002 and V7.1-010 perform comparably.
• Both benchmarks show that with large numbers of processes, many times the number of CPUs, both GT.M versions scale better than YottaDB r2.02, i.e., GT.M V7.1-002 has improved scalability compared to V7.0-005, the GT.M version merged into YottaDB r2.02.
• The interest posting benchmark clearly shows YottaDB r2.04 outperforming r2.02 and both GT.M versions.
• The 3n+1 benchmark shows r2.04 outperforming both GT.M versions at all loads, and at the high end scaling up  better than GT.M versions and r2.02 (i.e., having a lower slope).

Even as they highlight the performance of r2.04, the graphs illustrate an important fact about benchmarking: performance characteristics can vary widely between workloads.

Functionality

The most significant functional enhancement in r2.04 is the ability to export (encode / serialize / stringify) [sub]trees as JSON and to import (decode / deserialize / parse) JSON strings into [sub]trees. Since [sub]trees can have values at their roots as well as their own [sub]trees, which JSON does not support, the encoding format allows for such M [sub]trees (i.e., with a $DATA of 11) to be encoded and for the JSON to be re-decoded flawlessly, without any data loss. There are both C and M APIs (see example below); JSON parsing is done using the Jansson library. There are numerous additional enhancements to functionality, including:

• ZSHOW “V” able to display variables at a specific stack level – the ability to examine variable values obscured by called stack frames makes for easier debugging.
• For processes started with a JOB command, $ZYJOBPARENT has pid of parent process – there is no longer a need to explicitly pass this as a parameter when using the JOB command, or for a child process to look in the /proc filesystem.
• SET $ZROUTINES supports globbing of shared library filenames – simplifies application code that needs to include a number of shared libraries, e.g., plugins, in $ZROUTINES.
• Warning if a variable appears more than once in a NEW – catches inadvertent application programming errors.
• GT.M versions V7.1-000, V7.1-001, and V7.1-002 are merged into r2.04.

Fixes

Every YottaDB release must pass all the tests of predecessor YottaDB releases and more. We find and fix bugs and misfeatures, for example:

• WRITE /TLS does not set $TEST if no TIMEOUT was specified.
• Appropriate permissions for $ydb_tmp and, where appropriate, parent directory.

Since the upstream GT.M team only releases the source code for each version, but not the automated tests, we create our own automated tests when merging a GT.M version into YottaDB. During the process, we find and fix GT.M bugs and misfeatures, for example:

• MUPIP RUNDOWN runs down database files even when replication instance files do not exist.
• In Kubernetes pods, Source Server connects reliably with Receiver Server.

Note that the titles of issues describing fixes to bugs and misfeatures reflect the correct behavior after we fix the issues, and not issues as originally reported (since a fix may only be indirectly connected with original symptoms reported).

JSON Example

Here is an example of decoding a 29MB JSON string into a global variable, and encoding the global variable back into JSON.

YDB>set jsonfile=$zsearch("work/github/test-data/large-file.json") open jsonfile:readonly

YDB>use jsonfile for i=1:1 read line quit:$zeof  set json(i)=line

YDB>use $principal close jsonfile set json=$order(json(""),-1) write json
11356
YDB>set begin=$zut zydecode ^m=json set end=$zut write $fnumber(end-begin/1000,",")," msec"
553.165 msec
YDB>set begin=$zut zyencode jsonout=^m set end=$zut write $fnumber(end-begin/1000,",")," msec"
847.974 msec
YDB>

The above was executed on a laptop with an Intel i7-1260P CPU.

In Conclusion

Sorry to keep you waiting, but all good things take time. YottaDB r2.04 has landed, and we look forward to hearing from you as you use it.

Credits

• Photograph of Antonov An-225 Mriya coming in to land used under the Creative Commons Attribution-Share Alike 3.0 Unported license.
• Photograph of MSC Loreto used under the Creative Commons Attribution-Share Alike 4.0 International license.

It’s a wrap! The end of a long journey, and YDBGo v2 is finally here with its pristine finish and a V8 rumble that’s champing at the bit to get some traction in your code.

YDBGo is the Go language wrapper for YottaDB, arguably the world’s fastest key-value database, with strong ACID semantics at scale, and a mature code base which has been in production since the mid 1980s.

YDBGo v2 is more concise, easier to read, and more “Go-like”. Version 1 was long-winded, hard to read, had complex function signatures, and the syntax was not “Go-like”. While v1 had two command sets called “SimpleAPI” and “EasyAPI”, v2 is simpler and easier than both (see the syntax comparison below). Version 2 is also faster than both, and better protects you from inadvertent bugs. These features are presented below along with other functional additions, risk reductions, internal improvements, and a final section on migration from v1 to v2 which includes a table of examples of syntax changes.

Speed Improvements

YDBGo v2 is faster than v1:

• For basic operations like setting a node (benchmarked here):
• 1-4% faster than the v1 SimpleAPI
• 4 to 7 times as fast as the v1 EasyAPI
• For a fully-loaded multi-process application (per this 3n+1 sequence benchmark):
• 1-3% faster than the v1 SimpleAPI
• v1 EasyAPI not reported as it was too slow to be worth benchmarking

How We Did It

Most of the speed gains were achieved in v2 by reducing memory allocations — database accesses use database objects (actually, the Go equivalent of objects: type instances). Specifically, v2 requires database access through a connection instance *Conn and node instance *Node.

Each Conn instance allocates buffers for API transfer of error strings, values, and, where necessary, M call-in parameters. A Node instance holds a database variable name and subscript names in API-call format so that rapid operations on that database subtree may be performed many times.

Although Child() nodes may be created from any Node instance, for fast iteration, Node instances may be Index()‘d to access sub-nodes without re-allocating a new Node instance for each subnode. Index() achieves this internally by yielding a “mutable” node instance which is re-used by changing (mutating) the stored subscripts. Iterators are provided to traverse the database that automatically use this mechanism by yielding mutable nodes as the loop variable.

Reduced memory allocations and mutating node objects both result in less garbage collection than the EasyAPI.

Calling M, the language embedded in YottaDB, is also faster with more intuitive syntax by allowing Go’s binary types (int, float, etc) to be passed as native parameter types to the API. This avoids needless conversion to and from strings with relatively slow conversion functions like strconv.Atoi(), and avoids associated error checking.

There are other minor speed gains. Database access via Node instances reduces the number of parameters stacked by each function call and also reduces the error checking needed by each function. Errors checks are now performed once when the Node instance is created rather than at each API call on that node, yielding a slight speed increase. The same mechanism allows fewer function calls to be made than with the SimpleAPI, achieving better readability and a small speed gain.

More specific details of speed improvements are documented in this Issue.

Functional Improvements

Since Go does not support C’s atexit() functionality, the API now provides an explicit handler function, ShutdownOnPanic(), which application code should defer at the start of every goroutine. This ensures that the database will be cleanly run down before the process exits, ensuring database structural integrity without an application needing to write its own panic handler. As for the main goroutine, the API Init() function now returns a handle that must be passed to defer Shutdown(): an intuitive cue to the programmer that the main goroutine also needs to be shutdown.

Version 2 correctly handles panics that occur in Go callback functions that traverse the CGo boundary: specifically, panics inside transactions or inside signal goroutines. Previously such panics would un-stack the CGo call directly, which is unsafe in Go. Now panics are caught inside the callback with recover, the error is returned through the CGo boundary, and re-panicked once back in Go. The re-panic error message also includes the traceback of the original panic so that errors inside transactions and signals may still be easily located. This paragraph can be fleshed out with examples in a future blog message. Let us know if you’re interested in such an article.

Documentation and Source Improvements

For v2, the official location of the API documentation has moved to the Go Packages website, where Go developers look for package documentation. The YottaDB Multi-Language Programmers Guide points to the Go Packages site for the Go v2 API. Documentation for each function now includes a leading purpose statement and numerous executable examples have been added.

When you click a function name from Go Packages website to take a peek at the YDBGo source code, you’ll find that v2 is more readable and maintainable. Internally, it uses more idiomatic Go, such as the range keyword, iterators, and atomic variables instead of pseudo-C versions of the same. Extensive use of struct type instances cleans up internal as well as API function signatures. The codebase is now fully compliant with the Go lint tool staticcheck. Yoda conditions have been replaced with more familiar Go syntax.

All the original unit tests have been implemented against v2, and automated test coverage for code has improved from 75% to 96%.

Syntax Improvements

The use of type instances to access the database simplifies the v2 API:

• API method names have been renamed according to Go policy, to be whole words, and the scope omitted now that it is clear from the parent type.
• tptoken and errstr need no longer be passed to every API function as they are stored in the Conn instance. Each Node instance also references its Conn instance, so the latter doesn’t need to be passed to Node API functions.
• The Conn and Node type instances harness Go’s garbage collector to automatically free its allocated buffers when each instance is no longer used. This means an application no longer has to call Alloc and Free functions, and prevents inadvertent memory leaks that were possible with the v1 SimpleAPI.
• Calling M from Go is now simpler and more Go-like using the new Import() function.

See also: Examples of syntax changes.

Error Handling

Error handling has been standardized and simplified for readability:

• All API errors (returned or panicked) may now be identified by an error Code (negative codes for YottaDB errors and positive codes are for YDBGo errors). This allows the programmer to identify and handle specific errors as desired, even for panicked errors captured by a Go recover statement.
• Node methods now automatically handle expected YottaDB return codes that are not errors. For example:
• Iterators terminate on NODEEND.
• Get() automatically allocates more space and retries on INVSTRLEN.
• Get() allows the user to supply a default value rather than returning GVUNDEF and LVUNDEF.
• Node methods now panic on all programmer errors (per Go policy), and also on database system errors like out of memory.
• Panicking on system errors greatly improves readability of most database operations, which no longer have to check for errors. It also means that robust applications should use Go recover to capture database system errors in order to handle them gracefully rather than panic.

Signals

As with v1, signals that are used by YottaDB require special treatment if an application also needs to use them. The v2 API has changed the signal handling as follows:

• RegisterSignalNotify() has become SignalNotify() and now matches the function signature of Go’s built-in signal capture function signal.Notify().
• In addition, a v2 API signal handler no longer has to specify whether to pass the signal on to YottaDB before, during, or after the user’s handler. Instead, user handlers must simply call NotifyYDB() at the point in their handler where they wish to notify YottaDB of the signal.

Migration from v1 to v2

Applications that use YDBGo v1 continue to operate without change, and YottaDB continues to support the v1 API. To aid migration of large applications from YDBGo v1 to v2, it is possible to use v1 and v2 APIs in the same application code. For details, see the migration section of the README.

• Here is an example Go program migrated from YDBGo v1 EasyAPI to YDBGo v2 as a diff.
• Similarly, here is a diff of another Go program migrated from v1 SimpleAPI to v2.

Below is a table mapping v1 syntax to v2. For further information, refer to the official documentation.

Examples of Syntax Changes

import "lang.yottadb.com/go/yottadb/v2"

import "lang.yottadb.com/go/yottadb"

db := yottadb.MustInit()
conn := NewCon()
yottadb.Shutdown(db)

err := yottadb.init()
handle(err)
yottadb.exit()

Node (type)

KeyT (type)

n := conn.Node(“varname”, “sub1”, “sub2”, ...)

var n yottadb.KeyT
err1 := n.Alloc(varSize, numSubs, subSiz)
defer n.Free()
err2 := n.Varnm.SetValStr(tptoken, &err, “varname”)
err3 := n.SubAry.SetValStr(tptoken, &err, “sub1”)
err4 := n.SubAry.SetValStr(tptoken, &err, “sub2”)
<handle errors>

n := Node(var, subs…)
n.Set(value)
x := n.Get()

err1 := SetValE(tptoken, &err, value, var, subs)
x, err2 := ValE(tptoken, &err, var, subs)
<handle errors>

x := n.Incr(3)

x, err2 := IncrE(tptoken, &err, “3”, var, subs)

hasValue := n.HasValue()

data, err := DataE(tptoken, &err, varname, subs)
hasValue := (data == 1)
<handle errors>

n.Kill()

err := DeleteE(tptoken, &err, 1, varname, subsarray)
<handle errors>

n.Clear()

err := DeleteE(tptoken, &err, 2, varname, subsarray)
<handle errors>

conn.KillLocalsExcept(localNames, var1, var2)

err := DeleteExclE(tptoken, &err, []string{var1, var2)

locked := n.Lock(timeout)

err := LockIncrE(tptoken, &err, timeout, var, subsarray)
locked := (err != LOCKTIMEOUT)
<handle errors>

n.Unlock()

err := LockDecrE(tptoken, &err, timeout, var, subsarray)
<handle errors>

locked := conn.Lock(timeout, node1, node2, ...)

err := LockE(tptoken, &err, timeout, var, subs, …)
locked := (err != LOCKTIMEOUT)
<handle errors>

for node := range n.Children() {
    println(node)
}

for node := range n.Tree() {
    println(node)
}

success := conn.Transaction(“BATCH”, nil, func() {
    n.Set(value)
}()

err := TpE(tptoken, &err, func(tptoken uint64, errptr *yottadb.BufferT) int32 {
    SetValE(tptoken, &err, value, var, subsarray)
}(“BATCH”, nil)
success := (err != YDB_TP_ROLLBACK)
<handle errors>

table := conn.MustImport("calltab.ci")
retval := table.Call("mfunc", "param1", 2, 3)

table, err := CallMTableOpenT(tptoken, &err, "calltab.ci")
<handle errors>
_, err = table.CallMTableSwitchT(tptoken, &err)
<handle errors>
var routine yottadb.CallMDesc
routine.SetRtnName("mfunc")
retval, err := routine.CallMDescT(tptoken, &err, 64, "parm1", "2", 3)
<handle errors>

ch := make(chan os.Signal, 1)
SignalNotify(ch, syscall.SIGINT)
go func(ch chan os.Signal) {
    defer yottadb.ShutdownOnPanic()
    for {
        sig := <-ch
        NotifyYDB(sig)
        fmt.Printf("\nSignal %s\n", sig)
    }
}(ch)

ch := make(chan bool, 1)
ack := make(chan bool, 1)
RegisterSignalNotify(syscall.SIGINT, ch, ack, NotifyAfterYDBSigHandler)
go func(ch, ack chan bool) {
    defer usersOwnPanicSafety()
    for {
        <-ch
        sigAck <- true
        fmt.Printf("\nSignal SIGINT\n")
    }
}(ch, ack)

println(n) =>
    varname("sub1","sub2")

println(n.Dump()) =>
    BufferT.Dump(): cbuftptr: 0x12341234, buf_addr: 12341234, len_alloc: 12341234, len_used: 24
    BufferTArray.Dump(): cbuftary: 0x12341234, elemAlloc: 3, elemUsed: 3, elemLenAlloc: 20
    0: sub1
    1: sub2

n.Set(1)
n.Child(“age”).Set(42)
println(n.Dump()) =>
    varname("sub1", "sub2")=1
    varname("sub1", "sub2", "age")=42

There you have it, folks. Let us know how you like it.

Credits

• Images generated by Google Gemini in response to prompting by K.S. Bhaskar.
• Code highlighted using Online Code Syntax Highlighter.

We have heard repeatedly from database users that for truly ACID transactions, no database matches the scalability of YottaDB, or its upstream GT.M. Our recent blog post blog post ACID Transactions Are Hard At Scale … Part 1 discusses why Consistency and Isolation at scale are hard, and ACID Transactions Are Hard At Scale … Part 2 discusses how YottaDB meets that need. This blog post discusses why ACID transaction Durability is hard at scale, and how YottaDB achieves it.

What is Durability?

We summarize Durability thus:

Durability means that once the transaction is completed, it is permanently recorded in the database and cannot be erased even if the computer crashes.

Since YottaDB, or any other database, is software, it relies on the correct operation of the underlying hardware. Without a guarantee of correct operation of the underlying hardware,¹ correct operation of software cannot be guaranteed. Since the perfect hardware that never fails has not yet been invented, a more practical requirement is that hardware failure does not damage stored information, i.e., it is OK for computers to crash, but when a computer crashes, data that has been committed to nonvolatile storage survives intact. Database Durability starts with the premise that while hardware failure can make data unavailable, the data is intact and recoverable. (In a subsequent blog post, we will discuss application availability and Durability when this premise is violated.)

Why is Durability Hard to Implement at Scale?

As committing a transaction simply changes blocks in the database file in the filesystem, committing a transaction is theoretically just a matter of writing those blocks. But there are gaps between theory and practice, especially for performance at scale.

• Since the database file is a random access file, updating the file involves writing blocks at multiple offsets in the file. While this may be a lesser performance concern with solid-state storage which, unlike spinning disks, does not have to move heads, random writes on solid state storage are still slower than sequential writes.
• Since a computer can crash in the middle of writing multiple blocks, when a computer is rebooted after a crash, you need to know whether or not all the blocks of a transaction were written. If all of them were written and can be read back, the transaction is committed, but if only some of them were written, then the transaction is not committed and the blocks that were written need to have their prior contents restored.²
• Even when all the blocks are written to the filesystem by the database, owing to buffering by Linux, the data may not immediately be committed to non-volatile storage.

How Does YottaDB Implement Durability at Scale?

All databases ultimately implement Durability through the file system, and there are only a limited number of ways to do this, e.g., by calling fsync(). While we cannot tell you how the myriad other databases use these filesystem APIs, we can tell you how YottaDB implements Durability at scale for ACID transactions.

YottaDB has multiple ways to access database files. This blog post describes the most common Buffered Global (BG) access method with before-image journaling. This technique writes update information to a journal file.

Each database file has a cache of database blocks in a shared memory segment. This cache sits in front of the Linux file buffer cache because it is much faster for processes to read from and write to shared memory than the filesystem. Each database file also has an active journal file that is written to sequentially, i.e., by appending to it.

An epoch is a periodic checkpoint such that if the system crashes immediately after an epoch, the database file has structural integrity and is up to date, i.e., it does not need repair. At an epoch, all dirty (modified) global buffers are written to the filesystem and dirty buffers in the filesystem are written to nonvolatile storage with an fsync(). By default, epochs occur every 300 seconds, but can occur more frequently if YottaDB encounters an earlier opportunity.

There are two types of data records in a journal file: before-image records and update records. The first time that a block in a database file is modified after an epoch, a before-image record of the unmodified block is written to the journal file. Setting or deleting nodes generates update records, with each update record preceded in the journal file by before-image records of the block(s) it is modifying. If multiple updates within an epoch modify the same block, only the first update of the series causes a before-image record of the block to be written to the journal file.

Committing a transaction conceptually consists of two steps:

1. Writing the journal records to the journal file, and ensuring that they are committed to nonvolatile storage with an fsync(), ensuring Durability.
2. Updating the global buffers for the transaction’s updates. YottaDB processes cooperate to manage the database, and the blocks in global buffers eventually get written to the filesystem, at or before the next epoch.

While Consistency and Isolation are relevant when an application is running, Durability is relevant when recovering from system crashes.

When recovering from a system crash, YottaDB reads the end of the journal file. If it indicates that the system crashed immediately following an epoch, recovery is only a matter of cleaning up some metadata. If the end of the journal file indicates a crash that happened between epochs YottaDB recovers the database, i.e., provides Durability, as follows:

1. It finds the end of the last committed transaction from the records in the journal file. Any transaction update records after that are an incomplete transaction, i.e., the system crashed before the transaction was committed, and so the transaction did not happen – Atomicity requires that all updates of a transaction be committed.
2. Reading the journal file backward from the end, it reads before-image records, and applies those to the database file, a process referred to as recovery or rollback. When it reaches an epoch, the database file has been restored to the state it had at the epoch, i.e., it is structurally sound and up to date as of the epoch.
3. Reading the journal file forward from the epoch, it processes and applies the update records of each transaction to the database. This brings the database up to date with the last committed transaction, thus delivering Durability.

Of course, while conceptually simple, there is virtually unlimited complexity in implementing this Durability mechanism so that it scales. Here are just a few examples:

• While committing a transaction conceptually involves just the two steps listed above, the actual commit code involves multiple phases internally; a straightforward implementation might deliver perhaps half the throughput YottaDB actually delivers.
• As bypassing Linux filesystem buffering for journal files on high-end IO subsystems such as NVMe drives and SANs connected by fiber channels can yield higher write performance, the SYNC_IO option instructs YottaDB to bypass the buffering.
• Since even real-time core-banking systems have batch processes (see 100.000 Foot View of Core-Banking Systems), if a batch process can restart after a crash based on database state, such a batch process may require YottaDB to provide just Acidity, Consistency and Isolation. YottaDB provides such applications with an option to declare a transaction as a “BATCH” transaction that can accept relaxed Durability. Of course, if a subsequent transaction requires Durability, database serialization mandates that YottaDB make all prior transactions Durable.
• A production database usually consists of multiple database files, whose epochs typically happen at different times.
• While database updates that are not within ACID transactions do not demand the same guarantee of Durability, they do need to be made Durable, and this Durability typically happens in a fraction of a second.

Over the years, the YottaDB code base has provided transaction Durability to mission critical applications around the world, in banking & finance, healthcare, and more. We have tried to explain here how YottaDB implements that Durability, and why you should trust it to.

Looking Ahead – When Hardware Failure Damages Or Destroys Filesystems

Hardware failures can damage or destroy filesystems. Failures can also make datacenters unavailable (even datacenters of Amazon, Google, and Microsoft, no matter what any marketing hype says). Our blog post to follow, on Replication, will discuss how YottaDB responds to this need.

In Conclusion

If you have questions, or would like to learn more about how YottaDB transaction processing can meet your transaction processing application’s need to scale while providing five nines availability, please contact us.

Footnotes

1. Memory and storage have error rates (e.g., see DRAM Errors in the Wild: A Large-Scale Field Study) that are unacceptable for financial transaction processing. Techniques like mirroring and error-correcting RAM reduce the error rates to acceptable levels. Ideally, the error correction technique would be single-error correction, double error detection so that when uncorrected errors occur, they are detected rather than resulting in incorrect software operation
2. Some databases use copy-on-write (CoW) to deal with this requirement, but copy-on-write has scalability limitations, e.g., from Copy-On-Write – When to Use It, When to Avoid It, “CoW is an expensive process if done aggressively. If on every single write, we create a copy then in a system that is write-heavy, things could go out of hand very soon.”

Credits

1. Photo of Stone tablet with cuneiform inscription by Dr. Osama Shukir Muhammed Amin used under Creative Commons Attribution-Share Alike 4.0 International. It documents a land purchase by a man named Tupsikka. From Dilbat, Iraq. 2400-2200 BCE.
2. Photo of Cuneiform clay tablet by user Zinkir used under Creative Commons Attribution-Share Alike 4.0 International. It complains to the merchant Ea-Nasir about delivery of the wrong grade of copper.

… But YottaDB Does the Hard Work for You

Part 1 discussed what ACID transactions are and why they are hard to scale. This post discusses how YottaDB does the hard work for you.

Concurrency Control

At a very high level, there are two types of concurrency control: pessimistic (known as locking) and optimistic.

In pessimistic concurrency control, as the transaction logic executes, it “locks” the data it accesses. To ensure fully ACID properties, this lock must prevent concurrent processes executing their transaction logic from both reading as well as writing the data. Reading is blocked so that the other processes do not see the intermediate states of data. The consequent performance loss has led databases to provide transaction variants that are not fully ACID, such as multiversion concurrency control (MVCC; also known as “stable reads”) where data a process has read within a transaction will not change for that process as a result of a commit by another transaction. While this suffices for some types of business logic (in the balance transfer example, if there is a concurrent change to a service charge, it is probably acceptable for the transaction to use the prior service charge), it is unacceptable for others (such as two concurrent transactions withdrawing funds from the same account).

In optimistic concurrency control (OCC), a process executing a transaction keeps track of the data that it reads, but does not update the database until the time comes to commit the transaction. If no data that it has read has changed (data that it intends to update must first be read), it commits the transaction; if any data has changed (“collided”), it starts over from the beginning and re-attempts the transaction. Optimistic concurrency control when implemented in hardware, is called transactional memory. Software transactional memory also exists.

The primary pitfall of pessimistic concurrency control is deadlock: if process 1 locks data A and process 2 locks data B, then as they execute program logic process 1 finds it needs B and process 2 finds that it needs A. Deadlocks can of course be more complex: for example, A needs a resource that B has locked, B has a resource that C has locked, and C has a resource that A has locked. In a complex environment where there might be thousands or tens of thousands of concurrent transactions, there might be a deadlock involving a few transactions. The system is busy, and the application is making progress overall, but those few transactions are mutually unable to progress further. Much research has gone into detecting and preventing deadlock, but the problem remains a hard one to solve at scale. We know of at least one database that provides Atomicity and Durability, but makes application software responsible for Consistency and Isolation.

The primary pitfall of optimistic concurrency control is livelock: if two processes update the same data, one of them will update it first. The other will detect a collision and retry the transaction, thus executing the transaction logic twice. At scale, the system is busy, but wasting resources because processes are executing transaction logic multiple times for each successful commit; at least they are not deadlocked.

While most databases use pessimistic concurrency control and ameliorate conflicts with MVCC, MVCC is unacceptable for real-time core-banking systems. YottaDB uses OCC with mechanisms to detect and limit livelock.

YottaDB Concurrency Control

Every database region has a transaction number that is incremented on every update.¹ Every database block that is updated during a transaction has its transaction number set to the transaction number of the region, which is incremented in anticipation of the next transaction.

A process inside a transaction notes the transaction number of every database block it reads (a block must be read before it can be updated). Updates are kept in process-private memory. When the process commits the transaction, it checks whether one or more blocks it has read were updated since it read them, and if no block has changed, it commits the transaction. If even one block has changed, it restarts the transaction.

To prevent persistent livelock, if a transaction is forced to restart the transaction thrice, on the fourth attempt it locks other processes from updating the database region(s) in the transaction, and completes the transaction.

As the probability of multiple concurrent transactions updating the same blocks is low, this works well in practice. Nevertheless, YottaDB has mechanisms for applications to detect and avoid livelock. Each database region has a count of the number of first, second, third, and subsequent² transaction restarts. In the typical scenario of random collisions, one would see a low rate of first restarts, a lower rate of second restarts, and virtually no third restarts. A pattern other than this means that there are pathological restarts resulting from application design that creates “hot” data areas where concurrent transactions update the same data. YottaDB has tools to enable application developers to identify such hot data areas, so that they can avoid pathological restarts.

To avoid a locking problems such as that described in Vendor’s secret ‘fix’ made critical app unusable during business hours, YottaDB also provides for transaction timeouts: a transaction that exceeds the specified time will abort the transaction with an error. Since the transaction will not have been committed, no other concurrent transaction is affected.

YottaDB’s OCC has additional benefits over locking:

• It is computationally easier to restart a transaction that has made no updates than to roll back a transaction that has.
• It is hard for pessimistic concurrency control to lock data that does not exist. With YottaDB’s OCC index blocks are part of the read set of a process executing a transaction. If another process adds a record when a process is inside a transaction, an index block will have a new transaction number, resulting in a restart. In other words, YottaDB transactions ensure Consistency and Isolation not just for the existence of data but also for the absence of data.
• Programming ACID transactions in YottaDB is extremely simple. For example:
  • There is no need to WATCH for changed data. YottaDB does the watching automatically with no need for application program action.
  • YottaDB automatically rolls back and restarts transactions, carrying them through to being committed, with no explicit programming required on the part of the application.

Yes, but does it really scale?

The proof of the pudding, as they say, is in the eating. YottaDB, and its upstream code base, GT.M (whose transaction processing implementation YottaDB inherits and improves), are live, in daily production use, at the world’s largest real-time core-banking systems, with tens of millions of accounts, and peak ACID transaction rates of tens of thousands of transactions per second.

YottaDB does the hard work for you by providing a clean, simple ACID transaction APIs in a variety of languages.

Contact us to discuss what YottaDB can do for your transaction processing application.

Footnotes

¹ YottaDB treats single updates as “mini transactions,” which simplifies both explaining and implementing how the database engine works.

² There are circumstances under which a transaction can restart more than three times. But that would be getting lost in the weeds for the level of detail of this blog post.

Credits

1. Photo of ATMs in Croatia by Gewild who dedicated this work to the public domain.
2. Photo of savings bank box of Second National Bank, Allentown PA is in the public domain.