Demystifying Vector Distance in PostgreSQL

A look into sliding window comparisons for image analysis.

When you need to perform complex similarity comparisons inside a database, you often have to bridge the gap between application-level data structures (like JSON) and low-level math (like vectors).

The PostgreSQL snippet provided is a classic example of this bridge. It sets up the foundation for a sliding window comparison—a technique where you analyze overlapping sequences of data—by extracting features from a JSON payload and calculating how "far apart" two sets of image data are using Euclidean distance.

Part 1: Extracting the Features

Before you can compare two sets of data, you have to format them into a mathematical structure. The get_vector_values function handles this transformation.

This function takes a JSONB array of objects (the cluster_vector) and flattens it into a single, one-dimensional array of real numbers (real[]).

Notice the specific keys being extracted: a, b, and l. This strongly implies the data represents CIELAB (L*a*b*) color space values. In image processing, extracting the average L*a*b* values of images and comparing them is a common way to detect duplicates or cluster similar images together.

The function loops through the JSON array three separate times to group all the a values together, then the b values, and finally the l values. This creates a continuous vector signature representing that specific "window" of image data.

Part 2: Calculating the Distance

Once your image data is flattened into arrays, the distance function determines how mathematically similar they are.

This function implements the standard Euclidean Distance formula. It iterates through both arrays up to a specified length, calculates the squared difference between each corresponding element, sums them up, and finally returns the square root (using PostgreSQL's square root operator |/).

The smaller the returned number, the more similar the two image vectors are. A distance of 0 means they are identical.

The Source Code

/*
 * @rpi1337
 */

module.exports = `

CREATE OR REPLACE FUNCTION get_vector_values(cluster_vector jsonb) RETURNS real[] AS $$
DECLARE
  list real[] [];
  i jsonb;
BEGIN
  FOR i IN SELECT * FROM jsonb_array_elements(cluster_vector) LOOP
    list := array_append(list, (i->>'a')::real);
  END LOOP;
  FOR i IN SELECT * FROM jsonb_array_elements(cluster_vector) LOOP
    list := array_append(list, (i->>'b')::real);
  END LOOP;
  FOR i IN SELECT * FROM jsonb_array_elements(cluster_vector) LOOP
    list := array_append(list, (i->>'l')::real);
  END LOOP;
  RETURN list;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION distance(l real[], r real[], length INT) RETURNS real AS $$
DECLARE
  s real;
BEGIN
  s := 0;
  FOR i IN 1 .. length LOOP
    s := s + ((l[i] - r[i]) * (l[i] - r[i]));
  END LOOP;
  RETURN |/ s;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

`;

Modernization Considerations

For modern, large-scale production applications, iterating over JSON elements multiple times and performing custom mathematical loops in PL/pgSQL can be CPU-intensive.

A modernized approach often employs extensions like pgvector, which offloads operations like Euclidean distance computations to optimized C libraries, allowing indexes like HNSW and IVFFlat to significantly speed up similarity searches across large image datasets.

---

*Authored by @rpi1337 — View more at arpi.im*