Key-Value Store

High-performance key-value operations with sub-millisecond latency

Overview

entropyDB provides optimized key-value storage for caching, session management, and high-throughput workloads. Achieve Redis-like performance with SQL consistency.

Performance Characteristics

Sub-millisecond GET operations
100k+ ops/sec per core
TTL and automatic expiration
Transactional consistency

Creating Key-Value Tables

-- Simple key-value store
CREATE TABLE cache (
  key TEXT PRIMARY KEY,
  value BYTEA NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- With TTL support
CREATE TABLE sessions (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Create index for automatic cleanup
CREATE INDEX idx_sessions_expires 
ON sessions (expires_at)
WHERE expires_at IS NOT NULL;

-- With metadata
CREATE TABLE feature_flags (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  metadata JSONB,
  version INT DEFAULT 1,
  updated_at TIMESTAMP DEFAULT NOW()
);

Basic Operations

SET (Insert/Update)

-- Simple SET
INSERT INTO cache (key, value)
VALUES ('user:123', 'John Doe'::bytea)
ON CONFLICT (key) DO UPDATE 
SET value = EXCLUDED.value;

-- SET with TTL (expire in 1 hour)
INSERT INTO sessions (key, value, expires_at)
VALUES (
  'session:abc123',
  '{"user_id": 123, "role": "admin"}'::jsonb,
  NOW() + INTERVAL '1 hour'
)
ON CONFLICT (key) DO UPDATE 
SET value = EXCLUDED.value,
    expires_at = EXCLUDED.expires_at;

-- SET multiple keys
INSERT INTO cache (key, value)
VALUES 
  ('key1', 'value1'::bytea),
  ('key2', 'value2'::bytea),
  ('key3', 'value3'::bytea)
ON CONFLICT (key) DO UPDATE 
SET value = EXCLUDED.value;

GET (Retrieve)

-- Simple GET
SELECT value FROM cache WHERE key = 'user:123';

-- GET with expiration check
SELECT value 
FROM sessions 
WHERE key = 'session:abc123'
  AND (expires_at IS NULL OR expires_at > NOW());

-- GET multiple keys
SELECT key, value 
FROM cache 
WHERE key IN ('key1', 'key2', 'key3');

-- GET with pattern matching
SELECT key, value 
FROM cache 
WHERE key LIKE 'user:%';

DELETE

-- Delete single key
DELETE FROM cache WHERE key = 'user:123';

-- Delete multiple keys
DELETE FROM cache WHERE key IN ('key1', 'key2');

-- Delete by pattern
DELETE FROM cache WHERE key LIKE 'temp:%';

-- Delete expired entries
DELETE FROM sessions WHERE expires_at < NOW();

Advanced Operations

Atomic Increment

-- Increment counter
INSERT INTO cache (key, value)
VALUES ('counter:views', '1'::bytea)
ON CONFLICT (key) DO UPDATE
SET value = ((cache.value::text::int + 1)::text)::bytea;

-- JSONB counter
UPDATE sessions
SET value = jsonb_set(
  value,
  '{counter}',
  to_jsonb(coalesce((value->>'counter')::int, 0) + 1)
)
WHERE key = 'user:123:stats';

Conditional Operations

-- SET if not exists
INSERT INTO cache (key, value)
VALUES ('lock:resource', 'locked'::bytea)
ON CONFLICT (key) DO NOTHING
RETURNING *;

-- Compare and swap
UPDATE feature_flags
SET value = '{"enabled": true}'::jsonb,
    version = version + 1
WHERE key = 'new_ui' 
  AND version = 5  -- Only update if version matches
RETURNING *;

Batch Operations

-- Batch SET with transaction
BEGIN;
INSERT INTO cache (key, value)
SELECT 
  'user:' || id,
  name::bytea
FROM users
WHERE active = true
ON CONFLICT (key) DO UPDATE 
SET value = EXCLUDED.value;
COMMIT;

-- Batch GET with JSON aggregation
SELECT jsonb_object_agg(key, value) as all_values
FROM cache
WHERE key LIKE 'config:%';

TTL and Expiration

Automatic Cleanup

-- Create cleanup function
CREATE OR REPLACE FUNCTION cleanup_expired_keys()
RETURNS void AS $$
BEGIN
  DELETE FROM sessions WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql;

-- Schedule periodic cleanup (using pg_cron extension)
SELECT cron.schedule(
  'cleanup-expired-sessions',
  '*/5 * * * *',  -- Every 5 minutes
  'SELECT cleanup_expired_keys()'
);

Refresh TTL

-- Extend expiration on access
UPDATE sessions
SET expires_at = NOW() + INTERVAL '1 hour'
WHERE key = 'session:abc123'
  AND expires_at > NOW()
RETURNING value;

Performance Optimization

Indexing Strategy

-- Primary key is automatically indexed
-- For pattern queries, use prefix indexes
CREATE INDEX idx_cache_key_prefix 
ON cache (key text_pattern_ops);

-- For range scans
CREATE INDEX idx_cache_created 
ON cache (created_at DESC);

-- Partial index for active entries
CREATE INDEX idx_sessions_active 
ON sessions (key)
WHERE expires_at > NOW();

Connection Pooling

-- Use prepared statements for repeated queries
PREPARE get_key (text) AS
SELECT value FROM cache WHERE key = $1;

EXECUTE get_key('user:123');

-- Use connection pooler (PgBouncer recommended)
-- Configure in session mode for KV workloads

Common Use Cases

Session Storage

Store user sessions with automatic expiration

INSERT INTO sessions (key, value, expires_at)
VALUES ('session:' || gen_random_uuid(),
        '{"user_id": 123}'::jsonb,
        NOW() + INTERVAL '24 hours');

Rate Limiting

Track API usage per user/IP

-- Increment request count
UPDATE cache
SET value = ((value::text::int + 1)::text)::bytea
WHERE key = 'rate:user:123:' || 
            to_char(NOW(), 'YYYY-MM-DD-HH24');

Feature Flags

Dynamic configuration management

SELECT value FROM feature_flags
WHERE key = 'enable_beta_features';

Distributed Locking

Coordinate access to shared resources

INSERT INTO cache (key, value, expires_at)
VALUES ('lock:job:123', 'worker-1'::bytea,
        NOW() + INTERVAL '5 minutes')
ON CONFLICT (key) DO NOTHING;

Best Practices

Use Appropriate Data Types

BYTEA for binary data, JSONB for structured data, TEXT for strings.

Set Reasonable TTLs

Always use expiration for cache and session data to prevent unbounded growth.

Use Prepared Statements

Reduce parsing overhead for high-throughput workloads.

Monitor Table Size

Set up alerts for rapid growth and schedule periodic VACUUM operations.

Consider Partitioning

For very large KV stores, partition by key range or time.

Next Steps

→ Document Store → Time-Series Data → Performance Tuning