Engineering team cuts 1.2B-row query time from 2,100ms to 48ms using partitioning and caching

·1 views

The engineering team behind Mattrx, a multi-tenant marketing-analytics SaaS, reduced dashboard KPI query latency from 2,100ms to 48ms on a 1.2-billion-row Azure SQL table. The original system ran raw aggregations across the entire table on every dashboard load, pushing database CPU to 78% under a peak load of around 3,200 requests per second from 110,000 monthly active users. The solution involved three key architectural changes: partitioning the CampaignEvents table by day, applying clustered columnstore indexing for compression and batch-mode aggregation, and pre-computing daily KPI rollups so dashboards query summaries instead of raw events. A Redis cache layer with a 30-second TTL was added on top to serve the hottest dashboard requests in roughly 2ms. After the changes, peak database CPU dropped to 22% and working memory shrank from 2.1GB to 380MB.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

Apache Kafka: what it is, when to use it, and its key trade-offs

Apache Kafka is an open-source distributed messaging system that stores data streams in a durable, ordered, and fault-tolerant manner. Messaging platforms like Kafka allow multiple systems to exchange information asynchronously through an intermediary, without services calling each other directly. Kafka stands out from traditional brokers such as RabbitMQ and SQS because messages persist after being consumed, making it well suited for high-volume, real-time scenarios like e-commerce order processing and IoT sensor networks. Among its main advantages are high throughput, real-time processing, resilience, and support for multiple simultaneous consumers. However, Kafka can be overkill for low-traffic applications and carries a steep learning curve along with significant configuration and operational complexity.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Anthropic Engineer Advises Deleting Claude.md Files as Opus 5 Needs Far Fewer Instructions

Boris Cherny, the engineer behind Claude Code, told a Y Combinator audience that users should periodically delete their CLAUDE.md files, custom skills, and hooks to let Opus 5 demonstrate its improved capabilities. Anthropic itself removed over 80% of Claude Code's internal system prompt before shipping the new model, having found that most instructions were compensating for limitations in earlier versions that no longer exist. Cherny explained that Anthropic used a line-by-line ablation process to determine which prompt instructions still carried weight and which had become redundant scaffolding. Opus 5's broader instruction retrieval, built-in self-verification, and long-horizon task handling mean that instruction files tuned for Claude 4.x can behave erratically on the newer model even without any changes by the user. Developers are advised to audit existing instruction files, as much of their content likely addressed past model weaknesses rather than encoding genuinely necessary guidance.

0 comments Read more at DEV Community

ProgrammingHacker News ·

Insufficient source content to generate a reliable headline

The source material provided does not contain enough article text to produce an accurate, factual summary. Only metadata such as the article URL, Hacker News comments link, and engagement metrics were included. Reproducing or inferring claims without the full article content would risk fabricating facts. Please provide the complete article text for proper summarization.

0 comments Read more at Hacker News

ProgrammingDEV Community ·

Developer audited a 5.5 GB AI dataset by downloading less than 1% of it

A developer discovered a 5.5 GB Chinese astrology AI training dataset on a popular repository and grew suspicious when the sample count of 518,400 matched a perfect nested loop formula rather than real observations. Using HTTP range requests, they downloaded only 48 MB — about 0.8% of the total — by fetching the ZIP central directory and targeted file shards. The audit revealed that the dataset's 781 data shards appeared algorithmically generated rather than empirically collected. More strikingly, a proprietary content library explicitly excluded from the open-source GitHub repo was found bundled inside the release archive, buried nearly 6 GB deep where few would look. The case highlights both a practical technique for sampling large archives cheaply and a cautionary note about assuming release contents match what a repository publicly discloses.

0 comments Read more at DEV Community