Case study

Syndica

QuestDB is the database for real-time analytics and time-series dashboards at Syndica.

Syndica powers the Web3 infrastructure

Breafcase icon Use case: Application metrics, analytics, dashboards

Globe icon Industry: Web3

Deployment: QuestDB Cloud

Introduction to Syndica

Syndica provides highly scalable infrastructure for decentralized applications built on top of blockchains.

The company brings tools and services to the Solana dApp development community. It aims to bridge the gap between dApp development platforms and Web 2.0 developer tools, widely used to develop modern web apps. Syndica brings these two worlds together in a single, easy-to-use platform which reduces development time, improves dApp performance, and increases overall observability in the decentralized applications.

The offering includes a scalable RPC node infrastructure, with either elastic or dedicated nodes. Their custom-built API gateway routes requests across nodes to ensure network resiliency and high availability. Syndica also provides observability into each dApp, tracking metrics such as the number of users and their demographics, the number of RPC requests over time, average RPC response time, bytes transferred, as well as detailed logging for each RPC call. In addition, their service offers searchable logs to help assist in various stages of development.

Graph describing Fault-Tolerant Network of Syndica

In this case study, Ahmad Abbasi, Co-founder and CEO of Syndica, shares how QuestDB is at the core of their powerful offering.

Why do time series databases matter for Web3 infrastructure and real-time dashboards?

We offer user-facing analytics and real-time dashboards for companies building protocols and decentralized applications. To provide fast analytics to our users, we first need to collect a vast amount of time series data from the node infrastructure and API gateway that we provide to our users. Each observability and analytics request triggers real-time queries to our database to power dashboards tracking metrics such as RPC calls over time. We had to find a database that could handle a high throughput ingestion rate combined with fast querying capabilities simultaneously, without breaking down.

Traditional databases simply cannot handle the ingestion rate we require. They also cannot query the data fast enough for reactive dashboards; their aggregation and downsampling functionality tends to be slow, so we cannot rely on them to deliver snappy analytics to our users.

By contrast, time-series databases (“TSDBs”) are purpose-built for storing large amounts of timestamped data, making them more efficient for storage and querying. Their storage engines and data layouts are built to easily retrieve timestamped data efficiently and track specific metrics over time. Built-in analytical capabilities with languages such as SQL are often available out of the box.

At Syndica, we collect timestamped application metrics from dApps, such as RPC calls. The sheer volume of data that has to be collected and processed, as well as our visualizations needs with real-time dashboards, led us toward time-series databases.

Similarly, we use TSDBs for our internal infrastructure monitoring, as they can promptly alert us of issues within our systems. For instance, we can be notified if latencies are increasing in our response time, or if any errors are propagating to our clients. We wanted to pick one solution for all our time-series data needs.

Picking QuestDB for our time-series database

Our requirements were straightforward to start with: we were looking for a time series database that was easy to use, with excellent ingestion throughput as well as superior query performance to power our analytics and dashboards. At the same time, we were looking for simplicity and a straightforward querying language that we know such as SQL.

We have tried almost everything the market offers: ClickHouse, TimescaleDB, PostgreSQL, Amazon Timestream, and InfluxDB.

None of them met all of our requirements except QuestDB. For instance, TimescaleDB is not performant enough; the ingestion speed was lacking. ClickHouse is not a time series database and does not provide out-of-the-box SQL extensions for time series manipulation. InfluxDB is not developer friendly due to its query language, Flux, and the database soon hit high cardinality limitations.

After some research and testing, we concluded that QuestDB is the best candidate because of the following reasons:

High throughput ingestion via line protocol with out-of-order capabilities: we ingest non-stop ~300k rows per second on a single beefy instance.
Ease of use with the SQL language to query the data.
Time series extensions such as SAMPLE BY to power live dashboards (customer facing) that are responsive.
Fast queries for time interval searches. We can easily query vast amounts of data concurrently.

We built our product with QuestDB at the core before we launched.

Architecture

We have two QuestDB instances with applications communicating with them via InfluxDB line protocol ("ILP") for ingestion and PGWire for queries. To chart our infrastructure metrics, we plot the results on dashboards via Grafana. We leverage the integration with QuestDB through the Postgres plugin.

SQL queries to power time-series dashboards

We build reactive user-facing time-series dashboards to measure the number of RPC calls per user over time. We also calculate the total number of RPC requests over time, the average RPC response time and the total bytes transferred.

QuestDB uses SQL and features native extensions, such as SAMPLE BY. This is useful for downsampling the data by time intervals. As such, it is a good way to build a dashboard that tracks a metric over time.

To calculate the time-series dashboard with RPC requests for the last 30 days, the following is computed:

SELECT
  start_time AS ts,
  count(*)
FROM
  request_logs
WHERE
  timestamp > dateadd(‘ d ’, -30, now())
  AND kind = ‘ RPC ’ SAMPLE BY 1s FILL(NULL) ALIGN TO CALENDAR TIME ZONE 'UTC'

dateadd is a time-series-specific function to choose a time interval spanning the last number of days, hours or seconds. In this case, 30 days.

The data is then sampled in 1-second intervals. This is the time resolution chosen for the chart above.

If no data is shown for a given 1-second time interval, we will fill it with NULL.

"Bytes Transferred" is calculated via the following query:

SELECT
  sum(bytes_transferred)
FROM
  request_logs
WHERE
  start_time < '%s'
  AND start_time >= '%s'
  AND account_uuid = '%s'

"Avg RPC response" is calculated via the following query:

SELECT
  avg(duration)
FROM
  request_logs
WHERE
  start_time < '%s'
  AND start_time >= '%s'
  AND account_uuid = '%s'
  AND kind = 'RPC'

We also provide structured searchable logs for the Syndica RPC network thanks to this mechanism. Users can filter and drill down to very detailed time slices (milliseconds) of logging data to gain ultimate visibility and observability of the running application.

Examples of queries for the logs are:

Counting the number of logs for a given UUID account, over a defined time interval:

SELECT
  COUNT(*)
FROM
  request_logs
WHERE
  start_time < '%s'
  AND start_time >= '%s'
  AND account_uuid = '%s'

Showing logs for specific IP addresses:

SELECT
  distinct ip,
  ip_lat,
  ip_long
FROM
  request_logs
WHERE
  (
    project_uuid = '%s'
    and stack_uuid = '%s'
  )
  AND start_time > '%s'
LIMIT
  10000

By storing repetitive strings as symbols, and unique identifiers as UUID we can benefit from improved storage efficiency.

QuestDB Cloud for operational simplicity

Our infrastructure is fully managed by QuestDB Cloud. The main advantages are:

Enhanced security with all our endpoints encrypted via TLS and auth for the web console.
Ease of use: endpoints for InfluxDB Line Protocol, PostgreSQL and Rest API are provided, alongside client libraries for your programing language.
We get the benefit of dashboards to track the usage of the instance and the storage and quickly see if something went wrong and when.
Daily scheduled snapshots are included; it is easy to start from a previous snapshot, either spinning a new instance or from the same one.

What's next for Syndica?

We look forward to seeing the cold storage integration for QuestDB Cloud as the amount of data we have stored in QuestDB is edging toward the 16TB limit for a single instance. The ability to offload older and less valuable data to cold storage seamlessly and have the ability to retrieve it at any time will be a game changer for us.

“QuestDB outperforms every database we have tested and delivered a 10x improvement in querying speed. It has become a critical piece of our infrastructure.”

Ahmad Abbasi, Co-founder and CEO at Syndica