7.5
/
Table of Contents

Database engines

DipDup officially supports SQLite, PostgreSQL and TimescaleDB as a database engine. This table will help you choose a database engine that mostly suits your needs.

SQLitePostgreSQLTimescaleDB
Recommended versionslatest14, 1514, 15
Best applicationearly developmentgeneral usageworking with timeseries
SQL scripts🟢🟢🟢
Immune tables⚠️🟢🟢
Hasura integration🔴🟢🟢

By default, DipDup uses an in-memory SQLite database that is destroyed after the process exits.

Warning
Immune tables support for SQLite is experimental and requires advanced.unsafe_sqlite flag set. Use this feature only for early experiments with database structure, and switch to PostgreSQL/TimescaleDB later.

The latest PostgreSQL and TimescaleDB versions are recommended due to significant performance improvements in recent releases (see Feature matrix page).

Usually, it's okay to use different database engines for development and production, but be careful with SQL scripts and column types that can behave differently.

Immune tables

You might want to keep several tables during schema wipe if the data in them is not dependent on index states yet heavy. A typical example is indexing IPFS data — changes in your code won't affect off-chain storage, so you can safely reuse this data.

dipdup.yaml
database:
  immune_tables:
    - ipfs_assets

immune_tables is an optional array of table names that will be ignored during schema wipe. Once an immune table is created, DipDup will never touch it again (unless schema wipe --immune is called). If you need to change the schema of an immune table, you should perform a migration manually. Check schema export output before doing this to ensure the resulting schema is the same as Tortoise ORM would generate.

TimescaleDB

TimescaleDB is a PostgreSQL extension that provides time-series data management capabilities. You can use it with DipDup as a drop-in replacement for PostgreSQL.

You can choose timescale/timescaledb or timescale/timescaledb-ha Docker images when initializing DipDup project.

Warning
Be careful! Unlike other PostgreSQL images timescale/timescaledb-ha uses /home/postgres/pgdata/data as a persistent volume.

Use SQL scripts in on_reindex directory to prepare the database. First, create a hypertable replacing the primary key with a composite one:

sql/on_reindex/00_prepare_db.sql
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

ALTER TABLE swap DROP CONSTRAINT swap_pkey;
ALTER TABLE swap ADD PRIMARY KEY (id, timestamp);
SELECT create_hypertable('swap', 'timestamp', chunk_time_interval => 7776000);

Now you can create a continuous aggregate on top of the hypertable:

sql/on_reindex/20_create_ca_quotes_1m.sql
CREATE MATERIALIZED VIEW
    candlestick_1m
WITH (timescaledb.continuous) AS

SELECT
    time_bucket('1 minute'::INTERVAL, timestamp) AS bucket,
    token0_id as token_id,
    candlestick_agg(
        timestamp,
        abs(amount_usd/amount0),
        amount_usd
    ) as candlestick
FROM swap
    WHERE
        amount_usd!=0
    AND
        amount0!=0

GROUP BY
    bucket,
    token0_id
ORDER BY
    bucket,
    token0_id
WITH NO DATA;

CREATE INDEX candlestick_1m_bucket ON candlestick_1m(bucket);
CREATE INDEX candlestick_1m_token_id ON candlestick_1m(token_id);

SELECT add_continuous_aggregate_policy(
    'candlestick_1m',
    start_offset => INTERVAL '1 hour',
    end_offset => INTERVAL '0 minutes',
    schedule_interval => INTERVAL '1 minute',
    initial_start := '2018-07-01'
);

For more information visit the official TimescaleDB documentation:

Help and tips -> Join our Discord
Ideas or suggestions -> Issue Tracker
GraphQL IDE -> Open Playground
Table of Contents