Multi-Model Transactions

ACID transactions spanning relational, document, graph, and time-series data

Overview

entropyDB is the first database to support ACID transactions across multiple data models in a single transaction. Update relational tables, modify JSON documents, traverse graphs, and insert time-series data—all with full consistency guarantees.

Why Multi-Model Transactions?

Maintain consistency across different data representations
Eliminate complex application-level coordination
Simplify architecture—one database for all models
Full rollback on failure across all models

Basic Multi-Model Transaction

BEGIN;

-- Relational: Update user account
UPDATE users 
SET balance = balance - 99.99 
WHERE id = 123;

-- Document: Create order document
INSERT INTO orders (user_id, data)
VALUES (123, '{
  "items": [{"product": "Laptop", "price": 99.99}],
  "status": "pending",
  "timestamp": "2024-03-15T10:30:00Z"
}'::jsonb);

-- Time-Series: Log event
INSERT INTO events (user_id, event_type, timestamp, metrics)
VALUES (123, 'purchase', NOW(), '{"amount": 99.99}'::jsonb);

-- Graph: Create relationship
INSERT INTO user_product_graph (user_id, product_id, relationship)
VALUES (123, 456, 'purchased');

COMMIT;  -- All or nothing!

Real-World Examples

E-Commerce Order Processing

BEGIN;

-- 1. Relational: Update inventory
UPDATE inventory 
SET quantity = quantity - 1 
WHERE product_id = 789 AND quantity > 0;

-- 2. Document: Store order details
INSERT INTO orders (data) VALUES ('{
  "order_id": "ORD-2024-001",
  "customer": {"id": 123, "email": "user@example.com"},
  "items": [{"product_id": 789, "quantity": 1, "price": 299.99}],
  "shipping": {"address": "123 Main St", "method": "express"},
  "payment": {"method": "credit_card", "status": "pending"}
}'::jsonb);

-- 3. Graph: Create purchase relationship
INSERT INTO user_purchases (user_id, product_id, purchased_at)
VALUES (123, 789, NOW());

-- 4. Time-Series: Log order event
INSERT INTO order_events (order_id, event_type, timestamp, data)
VALUES ('ORD-2024-001', 'created', NOW(), 
        '{"channel": "web", "device": "mobile"}'::jsonb);

-- 5. Key-Value: Invalidate cache
DELETE FROM cache WHERE key = 'inventory:789';

COMMIT;

Social Network Activity

BEGIN;

-- 1. Document: Create post
INSERT INTO posts (author_id, content)
VALUES (123, '{
  "text": "Just launched my new project!",
  "media": ["image1.jpg", "image2.jpg"],
  "tags": ["#tech", "#startup"],
  "visibility": "public"
}'::jsonb)
RETURNING id INTO post_id;

-- 2. Graph: Notify followers
INSERT INTO notifications (user_id, type, source_id)
SELECT follower_id, 'new_post', post_id
FROM follows
WHERE followee_id = 123;

-- 3. Time-Series: Log engagement metrics
INSERT INTO engagement_metrics (post_id, timestamp, metric_type, value)
VALUES (post_id, NOW(), 'created', 1);

-- 4. Relational: Update user stats
UPDATE user_stats 
SET total_posts = total_posts + 1,
    last_post_at = NOW()
WHERE user_id = 123;

COMMIT;

IoT Data Processing

BEGIN;

-- 1. Time-Series: Store sensor readings (bulk)
INSERT INTO sensor_data (device_id, timestamp, readings)
SELECT 
  'device-001',
  ts,
  jsonb_build_object(
    'temperature', 72.5 + random() * 5,
    'humidity', 45 + random() * 10,
    'pressure', 1013 + random() * 5
  )
FROM generate_series(
  NOW() - INTERVAL '1 minute',
  NOW(),
  INTERVAL '1 second'
) ts;

-- 2. Document: Update device state if anomaly detected
UPDATE devices
SET state = jsonb_set(
  state,
  '{alerts}',
  state->'alerts' || '["high_temperature"]'::jsonb
)
WHERE id = 'device-001'
  AND (SELECT readings->>'temperature' FROM sensor_data 
       WHERE device_id = 'device-001' 
       ORDER BY timestamp DESC LIMIT 1)::float > 75;

-- 3. Relational: Create alert record
INSERT INTO alerts (device_id, severity, message)
SELECT 'device-001', 'warning', 'Temperature threshold exceeded'
WHERE EXISTS (
  SELECT 1 FROM devices 
  WHERE id = 'device-001' 
  AND state->'alerts' ? 'high_temperature'
);

COMMIT;

Cross-Model Queries

Query across different data models in a single transaction:

-- Join relational, document, and graph data
SELECT 
  u.id,
  u.name,
  u.profile->>'bio' as bio,  -- Document field
  COUNT(DISTINCT p.id) as friend_count,  -- Graph traversal
  AVG(e.metrics->>'session_duration')::float as avg_session  -- Time-series
FROM users u
-- Graph: Get friends
LEFT JOIN friendships f ON u.id = f.user_id
LEFT JOIN users p ON f.friend_id = p.id
-- Time-Series: Get recent events
LEFT JOIN events e ON u.id = e.user_id 
  AND e.timestamp > NOW() - INTERVAL '30 days'
WHERE u.profile->>'account_type' = 'premium'  -- Document query
GROUP BY u.id, u.name, u.profile
HAVING COUNT(DISTINCT p.id) > 10
ORDER BY avg_session DESC;

Consistency Guarantees

Atomicity Across Models

Either all operations succeed across all models, or none do. No partial updates.

Isolation

Concurrent transactions don't see each other's intermediate states, regardless of data model.

Durability

Once committed, changes to all models are permanent and survive failures.

Foreign Key Integrity

Referential integrity works across models. Document can reference relational table.

Performance Considerations

Unified Transaction Manager

entropyDB uses a single transaction manager for all models, eliminating coordination overhead:

Traditional Multi-Database:
  - Separate transaction per DB
  - 2PC coordinator overhead
  - Network latency between DBs
  - Potential for inconsistency

entropyDB:
  - Single transaction
  - No coordination overhead
  - Local execution
  - Guaranteed consistency

Optimized Storage Engine

All models share the same LSM-tree storage engine with unified WAL, reducing I/O overhead.

Error Handling

-- All models rolled back on error
BEGIN;

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

INSERT INTO user_profiles (user_id, data)
VALUES (LASTVAL(), '{"bio": "Developer"}'::jsonb);

-- This fails due to constraint
INSERT INTO user_stats (user_id, score)
VALUES (LASTVAL(), -100);  -- Violates CHECK (score >= 0)

-- ROLLBACK automatically triggered
-- Both user and profile insertions are undone

-- In application code:
try {
  await db.query('BEGIN');
  await db.query('INSERT INTO users...');
  await db.query('INSERT INTO profiles...');
  await db.query('INSERT INTO stats...');
  await db.query('COMMIT');
} catch (error) {
  await db.query('ROLLBACK');
  console.error('Transaction failed:', error);
}

Best Practices

1. Group Related Operations

Keep logically related updates in the same transaction, even across models.

2. Minimize Transaction Scope

Only include operations that must be atomic. Keep transactions short.

3. Use Appropriate Indexes

Create indexes on foreign keys and frequently queried fields across all models.

4. Test Rollback Scenarios

Verify that your application correctly handles transaction failures and retries.

5. Monitor Transaction Duration

Long transactions hold locks across models. Set statement timeouts appropriately.

Next Steps

→ Distributed Transactions → Cross-Model Joins → Performance Tuning