Support Postgres as database

[retrodaredevil]

CouchDB has served us well. We likely won't lose support for it anytime soon, but I'm hoping to at least experiment with Postgres or TimescaleDB support. Both of these have JSONB support.

I think it's unlikely that the SolarThing database will ever truly become relational, as packets will be stored in a JSON format for the foreseeable future. However, I could see packet groups/collections being represented in a relational way.

Imagine something like this

-- Create packet_group table
CREATE TABLE packet_group (
    id SERIAL PRIMARY KEY,                -- Primary key (implicitly indexed)
    timestamp TIMESTAMPTZ NOT NULL,       -- Stores the timestamp of the group
    fragment_id SMALLINT NOT NULL         -- Some small identifier
);

-- Create packet table
CREATE TABLE packet (
    id SERIAL PRIMARY KEY,                -- Primary key for each packet
    group_id INTEGER NOT NULL,            -- Foreign key to packet_group(id)
    data JSONB,                           -- Your packet data in JSONB form

    CONSTRAINT fk_packet_group
        FOREIGN KEY (group_id)
        REFERENCES packet_group (id)
        ON DELETE CASCADE                 -- Optional: delete packets if group is deleted
);

I suspect we could get massive performance improvements by using partitioning of the data. (Yeah we could try partitioning the CouchDB database, but that would cause breaking changes).