Picodata: Renegade Underdogs

1. PostgreSQL-compatible in-memory database

2. What is an in-memory database?

An in-memory database (IMDB) keeps its data entirely in RAM.

100% of data in RAM
Persistence and replication
Specialized transaction manager
Horizontal scaling
Co-located compute next to data

3. In-memory: why?

10,000+ queries per second
2-100 TB of managed data
ACID transactions
Disaster recovery
Up to 2,000+ nodes in a cluster

4. PostgreSQL & ANSI SQL syntax

Picodata supports most of the ANSI SQL syntax:

LEFT / OUTER JOIN
SUBQUERY
UNION, DISTINCT
ORDER BY, GROUP BY, HAVING

See the full reference: SQL index

SQL syntax support

5. DBeaver, JDBC, Spark, BI tools

Picodata speaks the PostgreSQL wire protocol, so it works out of the box with DBeaver, JDBC, Apache Spark, and common BI tools.

6. New SQL features

Implemented or planned for 2025:

BACKUP
ALTER SYSTEM
ALTER TABLE ADD / DROP COLUMN
Common Table Expressions (CTE)
ON CONFLICT … DO UPDATE
BEGIN … COMMIT blocks

7. Distribution management

By default, data is partitioned across all cluster nodes by the distribution key:

CREATE TABLE ITEM (
    I_ID    INTEGER NOT NULL,
    I_IM_ID INTEGER,
    I_NAME  VARCHAR(24),
    I_PRICE NUMBER,
    I_DATA  VARCHAR(50),
    PRIMARY KEY (I_ID)
)
USING MEMTX DISTRIBUTED BY (I_ID);

A query for a key whose bucket is on a failed node returns an error:

SELECT I_ID FROM ITEM WHERE I_ID = 2;
error: requested bucket is unreachable

8. Global tables

For reference data, you can keep a full copy on every node, which speeds up queries that use joins:

CREATE TABLE WAREHOUSE (
    W_ID   INTEGER NOT NULL,
    W_NAME VARCHAR(10) NOT NULL,
    W_TAX  DOUBLE,
    W_YTD  DOUBLE,
    PRIMARY KEY (W_ID)
)
USING MEMTX DISTRIBUTED GLOBALLY;

9. SQL: summary

Global schema across the entire cluster
SQL query language
PostgreSQL wire protocol
Each tier stores its own data segment

10. Security

FSTEK Level 4 certification (re-certified twice a year)
Additional capabilities beyond FSTEK requirements:
- Intra-cluster encryption
- Full audit of selected roles
- SCRAM-SHA-256 (mutual authentication)
- LDAP + TLS, PostgreSQL protocol + TLS
- Up to 128 roles

11. Failure domains and tiers

Automatic topology management for complex clusters.

12. Architecture overview

One computer runs one DB process per CPU core
Each process has an independent replica (or replicas)
Replicas of one process form a replica set - the unit of cluster scaling

Architecture: 3 data centers

13. Scaling

Horizontal scaling is achieved by deploying new servers with Picodata instances.

14. 3 built-in disaster recovery mechanisms

Synchronous disk writes - durable persistence in sync mode
Synchronous replication - a full DB copy in 2+ data centers
Logical replication between clusters (similar to Oracle GoldenGate) - a full synchronous copy of a geo-distributed cluster

Picodata implements ACID guarantees with SERIALIZABLE isolation - the highest possible level.

The tier mechanism separates compute, operational, and archival storage. Each layer scales independently.

15. 2 data center topology

DC 1	DC 2
server 1: instance 1 (standby), instance 2 (active)	server 3: instance 5 (standby), instance 6 (active)
server 2: instance 3 (active), instance 4 (standby)	server 4: instance 7 (active), instance 8 (standby)

16. Tiers: automatic topology management

A third DC can host an arbiter tier - lightweight instances that participate in Raft consensus but don’t store data. This enables quorum with only 2 full data centers.

17. 3 data center deployment

Each data center holds a full copy of the data
A transaction is committed when written to disk in at least 2 DCs
Replication algorithm: Raft
If any DC fails, the system remains fully operational (RTO 0, RPO 0). Failover is automatic
All DCs are active for storage, compute, and serving queries
Rolling upgrades with zero downtime
Future: data geo-pinning

18. Active standby with Ouroboros

Available as an add-on for any deployment scheme:

Fast standby cluster bootstrap from scratch - 3 Gbit/s
A permanent staging environment identical to production
Load testing of application code on production data
Application upgrades with zero downtime and zero business risk

19. Cluster assembly

# Start first node
picodata run --data-dir tmp/i1 --listen :3301 \
    --peer :3301 --init-replication-factor 2 \
    --failure-domain '{"dc": "dc1"}'

# Add more nodes
picodata run --data-dir tmp/i2 --listen :3302 --peer :3301
picodata run --data-dir tmp/i3 --listen :3303 --peer :3301
picodata run --data-dir tmp/i4 --listen :3304 --peer :3301

# Remove a node
picodata expel --instance-uuid a022c8f5-8d7e-4fe9-b3f7-24755e2d5c81

Cluster assembly

20. Failure domains

Failure domains let the DBA manage complex deployment configurations declaratively, and radically simplify recovery in case of failure.

DC   = 1
S    = 2    (servers per DC)
NCPU = 2    (instances per server)
RF   = 2    (replication factor)

21. Tiers: compute/storage separation

The data schema is global across the cluster
Data and code (plugins) are local to a tier

22. Key terms

Term	Meaning
failure domain	Declarative topology management
tier	Separation of storage and compute
plugin	Stored procedure module/package
blue/green deploy	Zero-downtime upgrades
backward compatibility	Versioning and code updates
migration	Schema and data migration

23. Plugins

Extensions shipped as part of the DBMS.

24. Co-located compute

Picodata provides a unified, horizontally scalable, fault-tolerant environment for both data and compute.

By running computations in the DBMS address space, keeping data in RAM, and computing directly on the instances that store the data, data access time is reduced by 100-1000x.

In Picodata: data access time is 1-10 microseconds.

25. Stored procedures: the challenges

How to manage source code?
How to test?
How to debug?
How to update?

26. Lua in Tarantool: the problem

Production crashes: OOM, FFI finalizers table, data corruption
Unpredictable performance:
- No generational GC in LuaJIT
- Broken hash function in LuaJIT tables
- Unpredictable JIT trace heuristics
Lua syntax and semantics - designed for small programs

27. Picodata: replacing Lua with Rust

Advantages:

No garbage collection
Strict typing
Compiled language
Popular language in the community

Result:

Higher quality of shipped software
Delivered as a single bundle
No hard-to-find production issues related to dependency management

28. Plugin key terms

Term	Meaning
version	All plugins are versioned; at most 2 versions in a cluster
manifest	Plugin metadata for installation
configuration	Strictly consistent, uniform across all nodes
migration	Rollback on error
status	Automatic shutdown on failure

29. Manifest

name: weather_cache
description: That one is created as an example of Picodata's plugin
version: 0.1.0
services:
    - name: weather_service
      default_configuration:
        openweather_timeout: 5
migration:
    - migrations/0001_weather.db

30. Migration

ALTER PLUGIN weather_cache MIGRATE TO 0.1.0;

Migration file (0001_weather.db):

-- pico.UP
CREATE TABLE "weather" (
    id UUID NOT NULL,
    latitude NUMBER NOT NULL,
    longitude NUMBER NOT NULL,
    temperature NUMBER NOT NULL,
    PRIMARY KEY (id)
)
USING memtx
DISTRIBUTED BY (latitude, longitude);

-- pico.DOWN
DROP TABLE "weather";

31. Configuration and status

ALTER PLUGIN myplugin 0.1.0
    SET migration_context.red_tier = 'my_tier';

ALTER PLUGIN weather_cache 0.1.0
    ADD SERVICE weather_service TO TIER default;

ALTER PLUGIN weather_cache 0.2.0 ENABLE;