PostgreSQL 9.6 usingcube

First install thecube extension

CREATE EXTENSION cube;

Now we will create some n-dimensional space with 100,000 points in 50 dimensions. In addition we'll add a GIST index.

CREATE TEMP TABLE space_ndAS  SELECT i, cube(array_agg(random()::float)) AS c  FROM generate_series(1,1e5) AS i  CROSS JOIN LATERAL generate_series(1,50)    AS x  GROUP BY i;CREATE INDEX ON space_nd USING gist ( c );ANALYZE space_nd;

Now we will generate a single point and use the<-> operater to find the nearest point using Eucledian distance.

WITH points AS (  SELECT cube(array_agg(random()::float)) AS c  FROM generate_series(1,50)    AS x)SELECT i,  pg_typeof(space_nd.c),  pg_typeof(points.c),  cube_distance(space_nd.c, points.c)FROM space_ndCROSS JOIN pointsORDER BY space_nd.c <-> points.cLIMIT 5;

PostgreSQL 9.6+ supports other distance operators overcube. All of which can use the GIST index we created. Namely,

a <-> b float8  Euclidean distance between a and b.a <#> b float8  Taxicab (L-1 metric) distance between a and b.a <=> b float8  Chebyshev (L-inf metric) distance between a and b.

That said there is one caveat,

To make it harder for people to break things,there is a limit of 100 on the number of dimensions of cubes. This is set in cubedata.h if you need something bigger.

You ask for 150 dimensions. That may present a minor complication.

• 1
  The edit tocubedata.h doesn't work past 130 dimensions in my experience. Maybe you can also change all thedoubles orfloat8s in the extension tofloat4, since Postgres has a limit on per-row index size that you can stay away from by halving how many bytes you use on each number. I did some testing and got more dimensions in that way, and IIRC I got past 150, but I'm not totally sure.

  sudo

  – sudo

  2018-11-03 02:33:13 +00:00

  CommentedNov 3, 2018 at 2:33
• 2
  I had same problem with limit on dimensions and created docker image with 2048 limit:hub.docker.com/r/expert/postgresql-large-cube

  expert

  – expert

  2019-04-29 09:37:33 +00:00

  CommentedApr 29, 2019 at 9:37

Consider performing dimension reduction first (eg. Principle Component Analysis).

Then your are doing NN in small number of dimensions with higher performance.

You can use Pl/R to perform PCA inside postgres if needed.

Have a look atFLANN and OpenCV.

Unfortunately I am not aware of an integration of that into a RDBMS system. But there is for exampleintegration of chemical structure information with Posgres. So in principle this can be done.

Take a look athttps://github.com/a-mma/AquilaDB it is a vector database to store Feature Vectors along with JSON Metadata. Keep it along with your RDBMS and do use metadata to maintain cross reference between data.

• database-design
• performance
• nearest-neighbor