NexaGres

PolyCache

Two real, live cache tiers — an arbitrary-SELECT result cache shared across six protocols, and a narrower exact-primary-key point-lookup cache that also brings in DynamoDB and MongoDB — both automatically invalidated on write, with real terminal walkthroughs proving each one below.

Cache once. Access across protocols.#

A result cached through one PolyWire protocol can be served through another — across PolyWire instances — without another Postgres round trip. One distributed cache, not a per-protocol silo. Confirmed live: a row read via a Postgres client, then asked for again through a MySQL client, comes back as a cache hit — zero Postgres round trips on the second read.

Cross-Protocol · Distributed · Automatically Invalidated · No Application Cache Code

See cross-protocol caching happen

$ mysql -h 127.0.0.1 -P 13306 -u app -p

mysql> SELECT * FROM orders WHERE order_id = 1001;

+----------+-------------+--------+
| order_id | customer_id | amount |
+----------+-------------+--------+
|     1001 |          42 | 129.99 |
+----------+-------------+--------+
1 row in set

# admin console: PolyCache MISS → PostgreSQL round trip → result cached

$ sql app/password@//localhost:11521/postgres

SQL> SELECT * FROM orders WHERE order_id = 1001;

ORDER_ID  CUSTOMER_ID  AMOUNT
--------  -----------  -------
    1001           42   129.99

1 row selected.

# admin console: PolyCache HIT → 0 Postgres round trips

Written through one protocol. Cached once. Served through another — see the real metrics behind this.

Point lookups: DynamoDB and MongoDB now share cache with SQL too#

The result cache above is unchanged, but there's now a second, narrower cache tier for exact primary-key point lookups — GetItem, find by _id, or a plain SQL WHERE pk = ? — and this one does cross into DynamoDB and MongoDB. Both protocols store their data in Postgres tables with a fixed, known physical shape — dynamowire's pk_value/sk_value/item columns, mongowire's id/doc columns — where the whole item or document lives as one jsonb blob rather than exploded into typed relational columns. That's why the SQL panels below read back a single JSON column instead of the multi-column rows the MySQL/Oracle demo above shows: it's not a formatting choice, it's the real physical schema dynamowire/mongowire create, and any SQL client sees exactly that table. It's also what makes a point-lookup entry cheap to key — the physical table and key value alone, no dialect translation needed to prove two requests mean the same row. Each protocol shares its entries with any SQL client (Oracle, Postgres, MySQL, SQL Server) reading that exact same physical table — DynamoDB and MongoDB don't share entries directly with each other, since a table named orders in DynamoDB and a collection named orders in MongoDB are two different physical Postgres tables under the hood.

Point-Lookup Cache · DynamoDB ↔ SQL · MongoDB ↔ SQL · Automatically Invalidated

See it happen: DynamoDB and MongoDB warm it, Oracle hits it

$ aws dynamodb put-item --endpoint-url http://localhost:18000 \
    --table-name orders \
    --item '{"id":{"S":"1001"},"amount":{"N":"129.99"}}'

$ aws dynamodb get-item --endpoint-url http://localhost:18000 \
    --table-name orders --key '{"id":{"S":"1001"}}'
{
    "Item": {
        "id": {"S": "1001"},
        "amount": {"N": "129.99"}
    }
}

# admin console: row cache MISS → PostgreSQL round trip → row cached

$ sql app/password@//localhost:11521/postgres

SQL> SELECT item FROM dynamo_item_orders WHERE pk_value = '1001';

ITEM
--------------------------------------------
{"id":{"S":"1001"},"amount":{"N":"129.99"}}

1 row selected.

# admin console: row cache HIT → 0 Postgres round trips

$ mongosh --port 28000

test> db.orders.insertOne({ _id: "1001", amount: 129.99 })
test> db.orders.find({ _id: "1001" })
[ { _id: '1001', amount: 129.99 } ]
# row cache MISS → PostgreSQL round trip → row cached

$ sql app/password@//localhost:11521/postgres

SQL> SELECT doc FROM "test"."orders" WHERE id = '"1001"';

DOC
--------------------------------
{"_id": "1001", "amount": 129.99}

1 row selected.

# admin console: row cache HIT → 0 Postgres round trips

Same key, "1001", in both — and no collision, because the cache key is namespaced by physical table (dynamo_item_orders vs. "test"."orders"), not by key value alone. One Oracle client reads both from cache regardless — see exactly how the point-lookup cache key works.

It's just Postgres: JOIN a DynamoDB or MongoDB table with your own#

A different, simpler consequence of the same fact the cache sharing above relies on: dynamo_item_orders and "test"."orders" aren't virtual views or an API bridge — they're ordinary Postgres tables sitting in the same database as everything else. So a plain SQL client can JOIN them against a real relational table it owns, using Postgres's own jsonb operators (->>) to pull a field out of the stored item/document. Orawire's own SQL translation is narrow by design — it rewrites a couple of known Oracle idioms (bind variables, DUAL) and otherwise passes SQL through — so this ->> is genuine Postgres syntax reaching genuine Postgres, not something orawire invented from Oracle's own JSON_VALUE. One thing this is not: a cache hit. The point-lookup cache only recognizes one exact single-table shape (WHERE pk_value = ? / WHERE id = ?, nothing else) — a JOIN always falls through to a real, live Postgres query.

$ sql app/password@//localhost:11521/postgres

SQL> SELECT c.name, o.item->>'amount' AS amount
  2  FROM customers c
  3  JOIN dynamo_item_orders o ON o.pk_value = c.customer_id
  4  WHERE c.customer_id = '1001';

NAME    AMOUNT
------  -------
Ada      129.99

1 row selected.

# a real join, Oracle relational table + DynamoDB's own physical table -- not a cache hit

$ sql app/password@//localhost:11521/postgres

SQL> SELECT c.name, o.doc->>'amount' AS amount
  2  FROM customers c
  3  JOIN "test"."orders" o ON o.id = '"' || c.customer_id || '"'
  4  WHERE c.customer_id = '1001';

NAME    AMOUNT
------  -------
Ada      129.99

1 row selected.

# mongowire stores id as Mongo's own Extended-JSON text, so a join predicate
# must match that literal quoted form -- not a cache hit either

Two entirely different write paths — one via DynamoDB's API, one via MongoDB's — landing in tables one Oracle JOIN reads alongside its own relational data, no ETL, no federation layer, no separate query engine.

Real measured cache-hit vs. Postgres-round-trip benchmarks, per protocol, with the full methodology →