Supporting a PostgreSQL back-end

[domMayhew]

This discussion is about a new initiative to add support for optionally using PostgreSQL for the data persistence layer. This post summarizes a discussion I had with @KtorZ.

Project

Allow Kupo to be conditionally compiled to connect to a PostgreSQL database. The existing SQLite version will still be supported and not changed as a result of this project.

Motivation

Improve horizontal scalability of Kupo for PaaS/IaaS solutions and multi-dapp infrastructure. Despite Kupo’s target use case being a small back-end service for a single dapp, it is now used in services such as Demeter. As a result, there is demand to improve horizontal scalability through the use of a one-to-many relation of managed database instances to Kupo instances.

This project is not designed to increase the performance of Kupo as measured by throughput or latency. Kupo is already highly optimized and very performant. However, using a managed database will reduce the time and storage required to create new Kupo instances.

Relevant Discussions

See #110 for more information about why PostgreSQL is not expected to be more performant than SQLite.
See #147 for a discussion about why Kupo has not supported a managed database for horizontal scalability in the past: namely, Kupo is opinionated and targets a particular use case that does not require horizontal scalability.

Design

The data persistence layer in Kupo is already well-isolated, and changes in this project will be kept to that layer.

Implementation

Kupo/App/Database.hs provides an API that the rest of the application uses to interact with the database. This API will not be changed. Instead, two new modules will be created, Kupo/App/Database/SQLite.hs and Kupo/App/Database/PostgreSQL.hs, and Kupo/App/Database.hs will conditionally compile to export the relevant implementation.

Testing

All of the black-box tests in KupoSpec should work regardless of the database used.
The tests in DatabaseSpec are specific to SQLite in at least two ways, and will need to be augmented:

1. They use SQLite’s in-memory configuration.
   1. One way to implement PostgreSQL-specific tests is to only run them when appropriate environment variables are set (in the same manner that some tests only run when cardano-node environment variables are set).
   2. Another option is to use tmp-postgres to spin up a temporary PostgreSQL instance. Perhaps @KtorZ can provide advice about which method to pursue?
2. They use SQLite’s query planner to confirm that queries are optimized by actually using the intended indexes.
   Finding a way to adapt these tests to PostgreSQL may be left for a future project, unless there is a clear need to do so.

[xray-robot]

It's a good idea to make it as an option, but I think cardano-db-sync does what you described, but without the indexing constructor (db-sync literally uses match=* mode). As far as I know @KtorZ sees a little different niche for this product, which by the way suits us very well in our development.

[domMayhew]

Hi @xray-robot , sorry for the slow response.

Demeter (by TxPipe) is currently providing Kupo as a service, and is therefore running Kupo in clusters. Since TxPipe will benefit from the horizontal scalability of a managed back end, they completing this work (through me) and Kupo will benefit from a wider scope without negative impact on the currently intended use case.

To be honest, I'm new to the Cardano ecosystem, so I'm not sure why Demeter uses Kupo instead of cardano-db-sync. But I think the reason this work is being done now, is that despite @KtorZ 's intended niche for Kupo, it is increasingly being used in the wild in ways that will benefit from this work. Forgive me if I've misrepresented you, @KtorZ !

[KtorZ]

I'm not sure why Demeter uses Kupo instead of cardano-db-sync

Try to use one and the other, and the answer should become obvious 😶.

Forgive me if I've misrepresented you, @KtorZ !

All good. And @xray-robot is right. I've been advocating against PostgreSQL in the past due to its complexity and because the only argument people brought forward was "performance considerations" around latency without any real numbers to back those claims -- and while every numbers I ever saw from people actually running both db-sync and kupo were always in favor of Kupo.

On the other hand, TxPipe does want to reach for PostgreSQL for different reasons: horizontal scaling. Because while the latency is generally not an issue with Kupo, the number of concurrent clients that it supports is. And on large indexes, it is unpractical to spin up new instances (as the entire disk space needs to be duplicated, which rapidly gets out of hand). Deployment and operation on large clusters is where PostgreSQL really shines and clearly where SQLite falls short. Hence the current effort.