Skip to content
Drivine

Postgres

Postgres 14 or later (tested on 16), as drivine4j states. Postgres keeps a graph in two tables, as Oracle does, read with joins and written through a function the store keeps. No extension is needed but pgvector, for vector search, which Drivine makes where the server has it, and pg_trgm, which a full-text index is made with (it ships with Postgres’s contrib modules). Models, the DSL and the object manager are the same as on the graph engines; PersistenceManager runs plain SQL, with cursors and transactions, as it always has.

Terminal window
DATABASE_TYPE=POSTGRES
DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_NAME=app
DATABASE_USER=app
DATABASE_PASSWORD=secret
DATABASE_DEFAULT_GRAPH_PATH=drivine   # names the graph's tables; this is the default

The graph’s name names tables, so it is letters, digits and underscores, begins with a letter, and is at most 49 characters; Postgres folds it to lower case. The tables are made on the first connection, when they are not there, as on Oracle, whichever manager is first: a database you connect to through Drivine gets the graph’s tables, even if you send it only plain SQL. A user who may only read connects to a graph that is already there and up to date. Postgres is a graph store like the others, so a schema catalog that names no database applies to it too, and makes its indexes at startup: to keep a database you use only for plain SQL free of them, narrow the catalog with .forDatabase(...).

How a graph is stored. drivine4j’s store, byte for byte, so the JVM, Node and Python read and write one graph: <graph>_node (nid, labels, props, stamp) and <graph>_edge (eid, src, dst, type, props), with labels and props as jsonb and a GIN index over the labels, so a node carries any number of them; <graph>_schema, the indexes and constraints Drivine made; and <graph>_write, the function a save’s steps are carried out by, as one statement that is applied whole or not at all.

Notes:

  • A statement you write yourself is SQL. Parameters are named $name, as in Cypher, and a statement is bound only the ones it names.
  • A Cypher statement, cypherStatement('MATCH …'), is compiled to SQL over the graph’s tables and gives the rows a graph engine gives: see Cypher on a relational database to start, and Cypher on Oracle and Postgres for how it differs.
  • A write’s loop over its rows that reads the node or edge table for each row, where it adds to that table or joins it to the rows of a CALL { … }, runs with enable_seqscan, enable_hashjoin, enable_mergejoin and enable_material off, set back after it to what they were (#628, #646, drivine4j#609). Postgres kept a plan made while the table was empty, or took a CALL’s one row for 100, and read the whole table for each row: MATCH (n) MERGE (n)-[:R]->(n) took 28 s over 20,000 nodes where it now takes under 1 s. The settings are set for the transaction (set_config(…, true)), so a statement that fails leaves them as its transaction or savepoint had them.
  • A statement’s rows, a JSON list, read each row’s node or relationship back from its table by its id through a probe of the table’s key, LEFT JOIN LATERAL (SELECT * FROM … WHERE nid = … OFFSET 0) … ON TRUE, not a plain join (#655, drivine4j#644). A plain join is planned by the table’s statistics, and where they said the table was empty while it still had pages, as an ANALYZE just after a DELETE leaves them, each row was compared with every row of the table: a FOREACH over 20,000 nodes took 19 s where it now takes under 1 s. The probe is a scan of the key’s index whatever the statistics say.
  • A predicate you write yourself, for a load or a delete, is SQL over the root’s row: its props, labels and stamp, e.g. (n.props ->> 'name') = $name. A property of the root may be written as Cypher writes it, n.name = $name (or n.`a name` ), and is read as that property, as text; a property compared with true or false is compared with the text a JSON boolean is read as. To compare one with a number, read it as a number in SQL.
  • A number is compared as a number only where one is stored: '7' is not 7, as on the graph engines.
  • A compiled MATCH that compares a property by = with a value a row holds (UNWIND $rows AS row MATCH (n:Person {id: row.id}), UNWIND $ids AS id MATCH (n:Person {id: id}), or WHERE n.id = row.id) is read through the range index or the uniqueness constraint over the property, as one compared with a text given is: beside the comparison stands a test of the property’s text among the texts the value can be stored as, a number’s with no zeros after its point and with each count of them up to 16, since numeric keeps the scale a product makes (0.25 * 10 is stored as 2.50) (#636, drivine4j#617). Compared as JSON alone, inside a CASE, each row read every node of the label: in drivine4j, APOC’s export replay took 1.77 s for each statement of 20 rows. This holds where the rows are known to hold no list or map, and no null, at the key: a parameter’s, or a list written of literals, maps, lists and parameters, as APOC’s export writes its rows. A list’s numbers may be stored at any scale, so a row that may hold a list is compared as JSON alone, as before. A statement is compiled for the rows it is given each time it runs, so the same statement given a list later finds it.
  • Text is ordered by the database’s collation, so a page of mixed-case text may not be in the order Neo4j gives (a, b, B rather than B, a, b). The range indexes are made in that collation, so an order and a seek are read through them. Compiled Cypher orders text by its code points instead (Cypher on Oracle and Postgres).
  • A save checks the stamp, as on the graph engines. A save turned away by a serialization failure (40001) or a deadlock (40P01) is run again.
  • Index the id of each class you save: a range index or a uniqueness constraint is a partial index over the label’s nodes, which finds a node by its id.
  • matches takes a Postgres regular expression, matched against the whole value.
  • A vector index is a pgvector HNSW index over the embeddings of the label’s nodes of its size; loadNearest is approximate through it, and exact with no index made. A full-text index is a trigram index over the text a search reads, which narrows the search; loadMatching scores the same with it and without it, and reads words, a "phrase", +must, -not and a prefix*. A word is a run of letters as Unicode’s Alphabetic holds them (with the vowel signs of Indic scripts), decimal digits and underscores, as the text’s [[:alnum:]_] reads it: m²x is the phrase m x. In a phrase a * is text, as on Neo4j: "of sou*" is the phrase of sou. It scores by BM25, as Lucene and so Neo4j’s full-text index do (k1 1.2, b 0.75): a term weighs as it is rare among the label’s nodes, and a text’s counts against its length, in characters, beside the label’s average. A prefix* adds the same to every node that holds it, however often and however rare, as Lucene’s prefix query does: in kiw* melon, a node with kiwi and no melon scores about 0.54 of one with both, as on Neo4j. Scored by the counts alone, a common word outweighed the word a query was about, and a long text won by holding more of everything: over drivine4j’s docs, the top hit was Neo4j’s for 6 of 21 queries, and is for 17. How rare a term is comes out the same narrowed; only the label’s count of nodes and their average length are read from every node of it, and no term is matched in that read. A dotted name, as org.example.models, is words here, split at each dot, as it is on Oracle and SQLite: example models finds it, and the name itself is the phrase of its words. Neo4j’s default analyzer keeps such a name one token, which the whole name finds and example models doesn’t. drivine4j’s FullTextIndexSpec picks another analyzer there; Drivine for TypeScript doesn’t take one yet.