Use case: Application metrics, analytics, dashboards
Deployment: QuestDB Cloud
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.
In this case study, Ahmad Abbasi, Co-founder and CEO of Syndica, shares how QuestDB is at the core of their powerful offering.
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.
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:
SAMPLE BYto power live dashboards (customer facing) that are responsive.
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.
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:
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
"Bytes Transferred" is calculated via the following query:
"Avg RPC response" is calculated via the following query:
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:
Showing logs for specific IP addresses:
By storing repetitive strings as
symbols, and unique identifiers as
UUID we can benefit from improved storage efficiency.
Our infrastructure is fully managed by QuestDB Cloud. The main advantages are:
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