标签：#data

One thing that confused me when I first started learning ClickHouse was the word FINAL . Because eventually you'll come across both: SELECT * FROM events FINAL ; and: OPTIMIZE TABLE events FINAL ; At first glance, they sound like they should do roughly the same thing. After all, both contain the word FINAL . But they actually solve two completely different problems. One affects query results. The other affects how data is physically stored. Understanding this distinction can save a lot of confusion when working with MergeTree tables. Why This Confusion Happens Most people encounter FINAL while working with engines like: ReplacingMergeTree SummingMergeTree AggregatingMergeTree Sooner or later they notice something like: SELECT * FROM users ; returns duplicate versions of rows. Then they discover: SELECT * FROM users FINAL ; and suddenly the results look correct. Naturally, many people assume: FINAL merges the table. But that's not exactly what is happening. What SELECT FINAL Actually Does When you run: SELECT * FROM users FINAL ; ClickHouse applies merge logic during query execution. Think of it as: "Show me what the table would look like if all relevant merges had already happened." The important part: It only affects the query result. After the query finishes: parts remain unchanged storage remains unchanged nothing is rewritten on disk The merge logic happens temporarily while the query is running. Once the query completes, the table is exactly as it was before. What OPTIMIZE FINAL Actually Does Now let's look at: OPTIMIZE TABLE users FINAL ; This is a completely different operation. Instead of modifying query results, ClickHouse physically merges parts on disk. The operation: rewrites data merges eligible parts removes obsolete versions creates larger merged parts Unlike SELECT FINAL , the effects remain after the command completes. This is a storage operation, not a query operation. The Simplest Way to Remember It Whenever I think about these commands, I use a v

TL;DR Traditional decoupled ETL pipelines (like the "Modern Data Stack") are too brittle and complex to handle the unpredictable, heavily nested data generated by AI and LLM features. Agentic data serving solves this by focusing on dynamic query routing and semantic discovery, letting AI agents discover and query data autonomously using schema-resilient tools and codified business logic. You can build an agentic data stack by pairing S3 storage with DuckDB's native JSON handling and schema-agnostic Parquet reading ( union_by_name=true ), eliminating failure-prone parsing steps. The open Model Context Protocol (MCP) replaces custom, hacky LangChain tools by providing a standard interface for agents to discover schemas and execute queries securely. The open Model Context Protocol (MCP) and DuckDB's embeddable architecture make it practical to connect agents directly to your data with minimal infrastructure overhead and elastic, consumption-based compute. For years, broken ETL jobs powered my pager and my morning coffee. I am a staff engineer, and like many of you, I have spent a ridiculous amount of my career babysitting data pipelines. It is a thankless job that often feels like patching holes in a sinking ship. You are not alone in this. A Forbes survey shows data teams notoriously spend up to 80% of their time just moving and cleaning data instead of doing the interesting work of analysis. And the financial magnitude of this bottleneck is staggering: the ETL market is projected to reach $20.1 billion by 2032 at a 13% CAGR. This proves that massive industry capital is flowing into solving these pipeline bottlenecks, but throwing more money at the same old architecture was not going to save my mornings. This constant firefighting was frustrating, but manageable. Then came the new mandate: build the data backbone for our next-gen AI and LLM-based product features. The unpredictability of the queries and the sheer complexity of the data, nested JSON everywhere, were th

TL;DR: This is not a cryptographic construction. It is a pragmatic engineering compromise for applications where encrypted storage is required but approximate alphabetical ordering is still useful. I sort encrypted strings using an external index: the sum of weighted Unicode code points for the first N characters with exponential positional weights, followed by quantization. Monotonicity is preserved, but accuracy predictably degrades after the first few characters. Not a cryptographic scheme; some ordering information leaks by design. The problem Some time ago, while implementing a project, I ran into the problem of sorting encrypted data in a database. I’d like to share the solution. I won’t go into detail describing the entire application. I’ll just say that, according to the required architecture, almost all data in the database must be stored exclusively in encrypted form: usernames, file names, tags, comments, dates, and so on (with the exception of identifiers and some system fields). That is, the table structure should be open, but the contents should not be. The encryption is symmetric: the same key is used for both encryption and decryption. This means that without the encryption key, even with a full database dump an attacker should not obtain any original data. And this is where two problems immediately arose: searching by the data without fully decrypting it, and sorting encrypted data. The first problem, with some caveats, is solved fairly simply. To search encrypted data, it is enough to additionally store hashes of the original values you plan to search on. This allows exact-match lookups (for example, users by login or files by tag) without storing the original values in plaintext. Yes, this won’t allow pattern searches, but it’s quite acceptable for the project’s goals. But sorting encrypted data turned out to be significantly more difficult. The solution A quick search showed that the problem is far from new, but there are no standard approaches t

Google recently announced that WebMCP is entering origin trials in Chrome 149. The new WebMCP standard proposal lets sites expose tools (e.g., JavaScript functions and HTML forms) to in-browser AI agents, which can thus reliably simulate user actions instead of resorting to possibly expensive (e.g., on-screen reading) and often unreliable guesswork (e.g., DOM scraping). By Bruno Couriol

DuckDB Data Inlining, SQLite Fossildelta OOB, Postgres 19 Temporal Data Today's Highlights Today's highlights include DuckDB's innovative data inlining for stream processing in data lakes, offering significant performance gains by eliminating the small files problem. Additionally, a critical out-of-bounds read vulnerability in SQLite's fossildelta extension and a peek into PostgreSQL 19's focus on temporal data capabilities are discussed. Data Inlining in DuckLake: Unlocking Streaming for Data Lakes (DuckDB Blog) Source: https://duckdb.org/2026/04/02/data-inlining-in-ducklake.html The DuckDB team has unveiled DuckLake’s new data inlining feature, designed to revolutionize how streaming data is managed in data lakes by effectively tackling the notorious “small files problem.” This issue, common in scenarios with frequent small updates or continuous ingestion, often leads to performance bottlenecks due to the overhead of managing numerous tiny files. DuckLake's solution involves intelligently storing these small updates directly within the catalog, thereby eliminating the need for physical small files on disk. This architectural innovation significantly improves the practicality of continuous streaming into data lakes, enabling more efficient real-time analytics. By inlining data, DuckDB reduces I/O operations and metadata management complexity, leading to substantial performance gains. A benchmark highlighted in the announcement demonstrates an impressive 926x speed improvement for certain operations, showcasing the feature's potential to transform data lake architectures for workloads requiring high-throughput ingestion and immediate query access without the traditional performance penalties. Comment: This DuckDB feature is a game-changer for data lake architectures, offering a simple yet powerful way to handle streaming data without the performance overhead of countless small files. Post: Out-of-bounds read in deltaGetInt() when input contains no in-buffer terminat

Winning fight against AI data centers gives people a "taste of political power."

Even moderately sized data centers can have an outsized local impact.

GBase 8a fully supports OLAP window functions, making it a powerful gbase database for analytical workloads. This guide uses real sales scenarios to demonstrate ROW_NUMBER / RANK , moving aggregates, LAG / LEAD for period‑over‑period comparisons, ROLLUP subtotals, and how these functions execute in an MPP environment. Window Function Syntax function_name () OVER ( [ PARTITION BY column ] -- window group [ ORDER BY column ] -- ordering within window [ ROWS | RANGE BETWEEN ... AND ...] -- frame definition ) Unlike GROUP BY , window functions do not collapse rows. Every row remains in the result set while still being able to “see” other rows in the window. Ranking Functions ROW_NUMBER / RANK / DENSE_RANK SELECT dept_id , salesperson , sale_amount , ROW_NUMBER () OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS row_num , RANK () OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS rnk , DENSE_RANK () OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS dense_rnk FROM sales WHERE sale_date >= '2024-01-01' ; Sample output: dept_id salesperson sale_amount row_num rnk dense_rnk 101 Zhang San 95000 1 1 1 101 Li Si 95000 2 1 1 101 Wang Wu 82000 3 3 2 101 Zhao Liu 71000 4 4 3 Top 3 Sales per Department WITH ranked AS ( SELECT dept_id , salesperson , sale_amount , ROW_NUMBER () OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS rn FROM sales WHERE YEAR ( sale_date ) = 2024 ) SELECT dept_id , salesperson , sale_amount FROM ranked WHERE rn <= 3 ORDER BY dept_id , rn ; NTILE: Bucketing SELECT dept_id , salesperson , sale_amount , NTILE ( 4 ) OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS quartile FROM sales WHERE YEAR ( sale_date ) = 2024 ; Cumulative and Moving Aggregates Year‑to‑Date SELECT dept_id , sale_month , monthly_amount , SUM ( monthly_amount ) OVER ( PARTITION BY dept_id ORDER BY sale_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS ytd_amount FROM ( SELECT dept_id , DATE_FORMAT ( sale_date , '%Y-%m' ) AS sale_month ,

I inherited a SQL Server database with 2.3 million rows. Queries took 45 seconds. Users were frustrated. Dashboards timed out. Here is exactly what I did. Step 1: Find the slowest queries I used SQL Server's query store to identify the top 10 worst performing queries. Step 2: Check the execution plan Missing index warnings everywhere. Also saw table scans on a 2 million row table. Step 3: Add targeted indexes Created two non-clustered indexes on the most filtered columns. No over-indexing. Just what the queries actually needed. Step 4: Rewrite the worst join One query was joining 6 tables with a cross apply that made no sense. Restructured to inner joins with proper filter ordering. The result 45 seconds down to 8 seconds. An 82% improvement. Real-time dashboards started working again. Key lesson Check what is actually slow before changing anything. Most people skip this and waste time optimizing the wrong thing. What is your fastest query optimization win?

I inherited a SQL Server database with 2.3 million rows. Queries took 45 seconds. Users were frustrated. Dashboards timed out. Here is exactly what I did. Step 1: Find the slowest queries I used SQL Server's query store to identify the top 10 worst performing queries. Step 2: Check the execution plan Missing index warnings everywhere. Also saw table scans on a 2 million row table. Step 3: Add targeted indexes Created two non-clustered indexes on the most filtered columns. No over-indexing. Just what the queries actually needed. Step 4: Rewrite the worst join One query was joining 6 tables with a cross apply that made no sense. Restructured to inner joins with proper filter ordering. The result 45 seconds down to 8 seconds. An 82% improvement. Real-time dashboards started working again. Key lesson Check what is actually slow before changing anything. Most people skip this and waste time optimizing the wrong thing. What is your fastest query optimization win?

Let's consider a table user . When we execute a query to find Emily , we are actually going through each record , looking whether the name column equals Emily . Indexes comes in when you want to speed this up. Let's create an Index with the name idx_users_name (the name can be anything, and it doesn't matter functionally): CREATE INDEX idx_users_name ON users ( name ); Now when you run SELECT * FROM users WHERE name = 'Emily' ; Postgres will use the index we just created (not by the name, the name is just for us) to execute that query, and the time complexity is reduced from O(n) to O(log n) .

Google's Angular team has released a repository called angular/skills, focusing on Agent Skills that enhance AI coding agents' ability to write modern Angular code. The repository includes skills for generating code and scaffolding applications, reinforcing current Angular conventions. It serves as a snapshot, aiming to improve AI suggestions by providing updated context. By Daniel Curtis

Pinecone has announced a new integration between its Nexus knowledge engine and Microsoft OneLake, aiming to fundamentally change how enterprise AI agents access and reason over corporate data. By Craig Risi

SaaS teams using AI-driven experimentation platforms (also called A/B testing automation or CRO...

On June 9, 2026 (US time), two big announcements landed on the same day. At the keynote of Datadog's annual event DASH 2026 in New York, the Bits AI family expanded significantly: Detection, Investigation, Remediation, Infrastructure, Code, Release, Testing, Data Analysis, Chat, Memories, and Evals. Counting by agent, that is more than ten, with over 100 new features announced together. The full picture is laid out in the keynote roundup. https://www.datadoghq.com/blog/dash-2026-new-feature-roundup-keynote/ The same day, AWS announced FinOps Agent as a public preview. It bundles four data sources, Cost Explorer, Cost Anomaly Detection, Cost Optimization Hub, and Compute Optimizer, and delivers automated cost-anomaly investigation, natural-language cost questions, periodic cost reports, and aggregated optimization opportunities straight into Slack and Jira. The details are in the AWS blog. https://aws.amazon.com/blogs/aws-cloud-financial-management/aws-finops-agent-is-now-public-preview/ AWS DevOps Agent had already gone GA in March, handling incident response. With FinOps Agent now added, AWS-built standard agents line up across the main operational domains. That said, DevOps Agent also covers multicloud and on-premises environments, so its scope differs from FinOps Agent, which targets AWS cost data. https://aws.amazon.com/blogs/mt/announcing-general-availability-of-aws-devops-agent/ On the surface, this looks like two separate stories: Datadog the monitoring platform, AWS the cloud provider. But read the two announcements side by side, and you see both reaching for the same territory, Ops, through different entrances. Line up their features and most of them overlap, so a surface spec comparison won't show the difference. This article sorts out the same-day releases by the two companies' positioning, asks what these very similar agent lineups are actually fighting over, and goes as far as the axes for telling them apart and the predictions that follow. This is writ

Something is slow. Maybe a page takes forever to load, maybe a migration is hanging, maybe your Supabase dashboard just spins. You suspect a query is stuck somewhere in your database, but you can't see what's happening — Postgres doesn't exactly surface this on its own. Turns out it does. You just need to ask. Seeing what's running Postgres keeps track of every active connection and what it's doing in a system view called pg_stat_activity . You can query it like any table: SELECT pid , state , query , age ( clock_timestamp (), query_start ) AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC ; That gives you every non-idle process — its process ID, current state, the SQL it's running, and how long it's been at it. If something has been running for minutes when it should take milliseconds, you've found your problem. A few things worth knowing about the columns: pid — the process ID, which you'll need if you want to kill it state — usually active (running right now), idle in transaction (sitting inside an open transaction doing nothing), or idle (waiting for work) query — the actual SQL text query_start — when the current query began If you want to include the user and database to narrow things down: SELECT pid , usename , datname , state , query , age ( clock_timestamp (), query_start ) AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC ; The dangerous one — idle in transaction An active query that's been running for a while is usually just slow. An idle in transaction connection is a different kind of problem — it means someone (or some code) opened a transaction and never committed or rolled it back. The connection is doing nothing, but it's still holding locks, which can block other queries from running. These are the ones that tend to cause cascading slowdowns. If you see one that's been sitting there for longer than expected, it's almost certainly a bug in application code — a missing COMMIT , an unhandled e

South Korean authorities issued the record-breaking fine following a data breach that affected over 30 million customers.

Database Migration Strategies for Next.js and Supabase Production Apps You've built your Next.js app with Supabase. It works perfectly in development. Now you need to deploy to production and realize: how do I safely change the database schema without breaking everything? Database migrations are how you version control your schema and deploy changes safely. This guide covers everything from basic migrations to zero-downtime production deployments. Prerequisites Supabase project (local and production) Supabase CLI installed Next.js application Git for version control Understanding Migrations A migration is a SQL file that changes your database schema: -- supabase/migrations/20260314120000_add_posts_table.sql CREATE TABLE posts ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (), title TEXT NOT NULL , content TEXT , user_id UUID REFERENCES auth . users ( id ), created_at TIMESTAMPTZ DEFAULT NOW () ); ALTER TABLE posts ENABLE ROW LEVEL SECURITY ; CREATE POLICY "Users can view own posts" ON posts FOR SELECT USING ( auth . uid () = user_id ); Migrations are: Versioned: Timestamped filenames ensure order Tracked: Supabase knows which migrations have run Repeatable: Same migrations produce same result Reversible: You can write rollback logic Setting Up Migrations Initialize Supabase locally: npx supabase init This creates: supabase/ config.toml seed.sql migrations/ Link to your remote project: npx supabase link --project-ref your-project-ref Creating Your First Migration Create a new migration: npx supabase migration new create_posts_table This creates: supabase / migrations / 20260314120000 _create_posts_table . sql Write your schema changes: -- Create posts table CREATE TABLE posts ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (), title TEXT NOT NULL , content TEXT NOT NULL , slug TEXT UNIQUE NOT NULL , user_id UUID REFERENCES auth . users ( id ) ON DELETE CASCADE , published BOOLEAN DEFAULT FALSE , created_at TIMESTAMPTZ DEFAULT NOW (), updated_at TIMESTAMPTZ DEFAULT NOW

When a RAID array fails, the worst thing you can do is panic and start poking at it immediately. I've seen too many cases where an impatient rebuild attempt overwrote the only good copy of data. This walkthrough covers how to safely approach a degraded or failed RAID — with ddrescue as your best friend. Step 0: Stop. Don't touch the array yet. Before running mdadm --assemble , before doing anything, clone your physical disks . A RAID 5 with one failed drive can lose everything the moment a second drive throws a read error during rebuild. This isn't hypothetical — it's how most total RAID losses happen. The golden rule: image first, recover second . Step 1: Assess the damage # Check current RAID state cat /proc/mdstat # More detail mdadm --detail /dev/md0 Look for: [UUU_] — one drive failed (underscore = missing) [UU__] — two drives failed (catastrophic for RAID 5) State: degraded , recovering , or failed Do NOT run mdadm --manage /dev/md0 --add /dev/sdX yet. Stop the array instead: mdadm --stop /dev/md0 Step 2: Clone each disk with ddrescue ddrescue is the right tool because it handles read errors gracefully: it maps bad sectors, retries them, and lets you resume interrupted sessions. Never use dd for a failing disk. Install it: # Debian/Ubuntu sudo apt install gddrescue # RHEL/CentOS sudo dnf install ddrescue Clone each RAID member to a separate image file (you need enough storage — same total size as all disks combined): # First pass: copy everything readable, skip bad sectors fast sudo ddrescue -d -r0 /dev/sda /mnt/backup/sda.img /mnt/backup/sda.log # Second pass: retry bad sectors up to 3 times sudo ddrescue -d -r3 /dev/sda /mnt/backup/sda.img /mnt/backup/sda.log Key flags: -d — direct disk access (bypass kernel cache) -r0 / -r3 — retry bad sectors 0 or 3 times The .log mapfile is critical: it lets you resume if the clone is interrupted Repeat for every disk in the array ( sdb , sdc , etc.). Step 3: Work from the images Once you have image files, assemble a soft

Most developers never have to design a network protocol from scratch. You use HTTP, gRPC, WebSockets, or something else that already exists and has been debugged by thousands of people over many years. That is the right call for most situations. I did not take that path when building Vaylix, a key-value database engine. I designed a custom binary protocol called VTP2, and the process taught me things about networking that I would not have picked up any other way. This is not an argument that you should also build a custom protocol. For most things, you should not. This is an honest account of what I ran into. Why not HTTP The first question anyone reasonably asks is: why not just use HTTP? HTTP is everywhere. The tooling is excellent. Every language has a client. Debugging with curl is trivial. If I had used HTTP, I would have had working client libraries in a dozen languages before writing a single line of server code. The problem is that HTTP is stateless by design. Every request is independent. Every request carries headers. Every response carries headers. The model assumes that each round trip is a fresh conversation with no memory of what came before. A database session is the opposite of that. A client connects, authenticates, and then issues many commands over the same connection. The authentication should happen once. The session should carry state. Pipelining requests without waiting for each response to return should be natural, not something you fight the protocol to achieve. HTTP/2 closes some of this gap. But using HTTP/2 correctly for a stateful session model involves working against the grain of what HTTP was designed for. I would have been spending a lot of time on infrastructure that exists to make HTTP behave less like HTTP. The other issue is overhead. HTTP headers are verbose. For small key-value operations, the headers can easily exceed the payload. That felt wrong for something designed to be a tight operational data store. So I went with TCP d