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.

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

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.

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.

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

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:

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:

• Hypertables
• Continuous aggregates