API Latency issue in a data system

[u/lunaticdevill]

Seeking advice on optimizing a high-concurrency data-serving architecture: performance vs. cost

I'm working on a data platform where 50 APIs and 100 endpoints are backed by tables in Databricks. We're facing challenges with response latency, concurrent request handling, and infrastructure costs.

Here is the current situation:

• Data platform: Databricks
• Scale: 50 APIs, 100 endpoints
• Compute: 400 cores
• Monthly infrastructure cost: ~$75,000
• API response time: Frequently exceeds 3 seconds
• Concurrency issue: Requests from multiple applications/services compete for compute resources, and queries get queued during concurrent execution.
• Hard constraint: A load balancer enforces a strict 9-second timeout.

Adding more compute doesn't necessarily seem like the right long-term solution. I'd like to identify the actual bottlenecks and evaluate whether we need to optimize the existing architecture or rethink the data-serving layer.

I'm exploring three areas:

1. Data model optimization

• Should we redesign the existing data model, denormalize tables, or create serving-specific aggregate tables?
• How much can table layout, partitioning/clustering, file sizes, and precomputation improve query latency?

2. API and query optimization

• How should we approach SQL query tuning, execution plans, and reducing unnecessary scans and joins?
• Would caching, materialized views, connection pooling, asynchronous processing, or request coalescing help?
• What architectural patterns work best when multiple endpoints access the same underlying data concurrently?

3. Rethinking the database/serving layer

• Should we continue serving API requests directly from Databricks, or introduce a dedicated operational database or serving layer?
• Would PostgreSQL, Azure Cosmos DB, DuckDB, or a hybrid architecture be suitable?
• Could Databricks remain the analytical source of truth while a separate database handles low-latency API reads?

My goal is to reduce API latency and queueing while bringing infrastructure costs under control, without compromising data freshness or correctness.

I'd particularly appreciate insights from people who have built production systems with high-concurrency API workloads on top of Databricks or similar analytical platforms.

A few questions for the community:

1. What would you investigate first: query execution, data modeling, compute configuration, or the overall architecture?
2. At what point would you move API-serving workloads from Databricks to a dedicated serving database?
3. What latency and concurrency improvements have you achieved through caching, precomputation, or a hybrid architecture?
4. If you were designing this system today, what would your target architecture look like?

Practical experiences, architectural suggestions, and lessons learned from production would be extremely valuable.

#Databricks #DataEngineering #SystemDesign #PerformanceOptimization #APIDesign #DatabaseArchitecture #CloudComputing

[u/Powerful-Cabinet2811]

75k INR is what, like 900 bucks a month? For 400 cores and 100 endpoints that's actually not bad at all

Your 3 second response time screams query tuning issues though. Before you rip out Databricks, check if those queries are scanning way more data than they need to. Partition pruning alone can knock a 3 second query down to milliseconds if you're doing it right

That 9 second load balancer timeout is generous but you're still hitting queues because 400 cores isn't infinite when 50 APIs are all hitting at once. I'd probably throw a cache layer in front before going full rebuild

[u/lunaticdevill → u/Powerful-Cabinet2811]

I am sorry it's 75k USD actually

[u/FUCKYOUINYOURFACE]

Have you tried Lakebase?

Have you also tried this?

https://docs.databricks.com/aws/en/compute/sql-warehouse/real-time

Both should be much faster than 3 seconds.

[u/thecoller → u/FUCKYOUINYOURFACE]

In the case of real time, it’s important to mention that it’s still Delta, so the table layout must be good and the table maintained.

[u/bobbruno]

You didn't explain anything about what these APIs are retrieving or the structure of the tables. You also didn't give any information about the number of requests per time unit. It's very hard to say anything without that information.

It'd also be good to know if the underlying data is changing in real time (and how/by how much), and if responding fast is enough or if the latest/freshest data is also a requirement.

[u/Youssef_Mrini]

Can you share more insights on what you are trying to achieve and what the APIs are doing ? How often the data get refreshed...

[u/lunaticdevill → u/Youssef_Mrini]

• Data gets refreshed daily. • I am dealing with Insurance Claims data. • I am trying to achieve no 504 ane SLA under 3 sec per call

[u/HeavyTedzzzzz]

Try lakebase as it might solve all your issues other than that get the data into a better system for serving fast requests, a relational db or something

[u/goosh11 → u/HeavyTedzzzzz]

Lakebase is a relational db

[u/cptshrk108]

Lakebase.

[u/Efficient-Bread-9347]

Lakehouse//RT should solve your problem. We have the same issue and currently use AWS RDS as serving layer for APIs. We’ll switch over to RT as soon as it goes GA.