Technical assignment for benchmarking SQL databases, to test how well these databases support associative operations. At the moment doublets are good only in these requests and support only these requests and we have ready benchmarks with this requests for Doublets and PostgreSQL.

We can use SQL dialect as is for Clickhouse and other databases.

Schema

The schema for the Links table is as follows:

CREATE TABLE IF NOT EXISTS Links (
    id bigint PRIMARY KEY,
    from_id bigint,
    to_id bigint
);

Indexes

Two indexes are created on the Links table:

CREATE INDEX IF NOT EXISTS source ON Links USING btree(from_id);
CREATE INDEX IF NOT EXISTS target ON Links USING btree(to_id);

SQL Queries

Insertion

1. Normal insertion: This query inserts a new record into the Links table. The id, from_id, and to_id fields are parameters that need to be provided.

INSERT INTO Links VALUES (id, from_id, to_id);

1. Point insertion: This is a special case where the same value is used for all three fields. The value used is the current size of the Links table plus one.

INSERT INTO Links VALUES (id, id, id);

Update

1. Update by id: This query updates the from_id and to_id fields for the record with the given id.

UPDATE Links SET from_id = new_from_id, to_id = new_to_id WHERE id = given_id;

1. Update by from_id and to_id: This query updates the from_id and to_id fields for the record that matches the given from_id and to_id.

UPDATE Links SET from_id = new_from_id, to_id = new_to_id WHERE from_id = given_from_id AND to_id = given_to_id;

Deletion