标签：#SQL

title: sqlex — A Modern Drop-in Replacement for jmoiron/sqlx published: false description: sqlex is a fully API-compatible modernization of jmoiron/sqlx that fixes 20+ long-standing bugs, adds pluggable hooks, auto IN expansion, and more. Built for Go 1.21+. tags: go, database, sql, opensource If you use sqlx, this is worth 3 minutes of your time jmoiron/sqlx has been the go-to SQL extension library for Go for years. Struct mapping, named parameters, IN clause expansion — it made database/sql actually pleasant to use. I've used it in almost every Go project I've worked on. But here's the reality: its activity has been modest at best, and has slowed to a crawl in recent years. Hundreds of issues sit untouched. PRs go unanswered. Bugs reported years ago are still there, waiting to cause production incidents. This isn't a knock on sqlx — it's a great library with solid design. But an unmaintained foundational library is a liability. So we built sqlex sqlex is a drop-in replacement for jmoiron/sqlx that is 100% API-compatible . All sqlx methods ( Get , Select , Exec , NamedQuery , Preparex , etc.) work identically. Migrating takes 30 seconds — just change the import path: - import "github.com/jmoiron/sqlx" + import "github.com/go-sqlex/sqlex" 🐛 20+ bug fixes from sqlx, all fixed 🚀 New features sqlx never had Auto-Rebind — write ? everywhere, works on PostgreSQL ($1), MySQL (?), SQLite (?), SQL Server ( @p1 ). No more manual db.Rebind(). SQL parsing fixes — colons in strings, :: type casts, ? in comments are correctly handled. Silent bugs from sqlx are gone. Auto IN expansion — slices in IN (?) are detected and expanded automatically on all methods. Hook system — pluggable SQL interceptors for logging, tracing, metrics (onion model). JSONValue[T] — generic JSON column type with auto serialize/deserialize. StrictMode — lenient by default (matching sqlx Unsafe()), optionally strict for debugging. Unified interfaces — Ext / ExtContext / NamedExt / BindExt with compile-time

DuckDB 1.5.2, PostgreSQL Internal Stats, and SQLite Virtual Table xUpdate Deep Dive Today's Highlights This week brings a stable new patch release for DuckDB, enhancing performance and adding DuckLake support. We also delve into PostgreSQL's internal statistics for better tuning and explore advanced SQLite virtual table implementation via xUpdate . Announcing DuckDB 1.5.2 (DuckDB Blog) Source: https://duckdb.org/2026/04/13/announcing-duckdb-152.html DuckDB has released version 1.5.2, a patch update focusing on stability and performance. This release includes critical bugfixes that improve the reliability of the in-process analytical database, addressing various edge cases and enhancing overall robustness. Key enhancements also target performance bottlenecks, ensuring faster query execution for diverse analytical workloads. A significant new feature in this version is the official support for the DuckLake v1.0 lakehouse format. This integration positions DuckDB as a more robust tool for handling modern data architectures, allowing users to efficiently query and manage data stored in a lakehouse paradigm directly within their applications or analytical workflows. This update makes DuckDB even more compelling for embedded analytics and data pipeline use cases, providing a flexible and high-performance option for developers. Comment: Always good to see performance improvements and bug fixes for an embedded analytics powerhouse like DuckDB. DuckLake v1.0 support is a big step for managing structured data in lakehouse environments directly from DuckDB, enhancing its utility for complex data architectures. pg_stats: How Postgres Internal Stats Work (Planet PostgreSQL) Source: https://postgr.es/p/9mG This article from Planet PostgreSQL delves into the intricate mechanisms behind PostgreSQL's internal statistics, specifically focusing on pg_stats . Understanding how Postgres collects and utilizes these statistics is fundamental for effective database performance tuning and q

আমার colleague এখন প্ল্যান দেখতে পারছে। Scan types বুঝতে পারছে। Join types বুঝতে পারছে। Estimate আর actual এর gap দেখতে পারছে। BUFFERS ও দেখছে। কিন্তু সে প্রশ্ন করল। এসব দেখে কি করব? Step by step কোন পথে যাব? আমি বললাম। পাঁচটা step আছে। অর্ডার অনুযায়ী। পর্ব ২ এ আমি বলেছিলাম scan types, join types, estimate আর actual এর gap। BUFFERS কি। এবার আসি সমাধান এ। Diagnostic Workflow আপনার কাছে একটা slow query এসেছে। কিভাবে debug করবেন? এই পাঁচটা প্রশ্ন করুন অর্ডার অনুযায়ী। ৯০% slow query প্রথম বা দ্বিতীয় ধাপেই solve হয়ে যায়। ১. Deepest Seq Scan দেখুন Table বড় কি না? Filter selective কি না? Missing index থাকলে add করুন। আজই শুরু করুন যখন একটা Seq Scan দেখবেন big table এ, প্রথমে WHERE clause টা check করুন। Selective কি না? ৫% এর কম row return হওয়ার কথা? যদি তাই হয়, index missing। CREATE INDEX idx_name ON table(column) run করুন। ২. Join types দেখুন কোনো Nested Loop আছে কিন্তু দুই পাশেই বড় table? Hash Join force করুন বা ডান পাশে index add করুন। আজই শুরু করুন Nested Loop দেখলে ডান পাশের table এ index check করুন। যদি না থাকে, create করুন। Index থাকা সত্ত্বেও planner Nested Loop use করছে? SET enable_nestloop = off temporarily disable করে দেখুন। Hash Join আসবে কি না। ৩. Row estimates দেখুন Estimate vs actual ১০x এর বেশি difference? ANALYZE table দিন বা predicate rewrite করুন। আজই শুরু করুন rows=1 estimate কিন্তু rows=100000 actual দেখলে ANALYZE tablename run করুন। Statistics refresh হবে। তারপর plan আবার দেখুন। যদি তাও না আসে, WHERE clause rewrite করুন। Function call থাকলে remove করুন। Type mismatch থাকলে fix করুন। ৪. BUFFERS add করুন কোনো node এ অনেক disk reads? Caching investigate করুন। আজই শুরু করুন EXPLAIN (ANALYZE, BUFFERS) run করে দেখুন shared read high কোথায়। সেই node টাই bottleneck। Index add করলে reads কমবে। Pre-warm cache করতে পারেন। Data pre-load করতে পারেন। ৫. Sorts আর hashes দেখুন কোনো spill-to-disk আছে? work_mem raise করুন বা sort eliminate করুন। আজই শুরু করুন Plan এ external merge Disk: 421MB দেখলে spill-to-disk হয়েছে। SET work_mem = '256MB' temporarily rais

Pagination is a key component on web-applications that let users navigate through pages making easy to read/find records. Also, pagination is a great strategy to improve performance by avoiding to load entire dataset at once. However, while working with Kaminari, a popular pagination gem in Rails, I encountered an unexpected issue that revealed an interesting edge case. Identify the issue Basically, pagination in frontend was not working properly. Datatable should load 316 total rows, although when the user started to load 15 records per page, frontend is showing inaccurate total rows. Multiple of 15 should ends at 0 or 5. There were pages with 64 records, crazy world. Some pages were loading 12 or 11 rows. There is no issues or error messages in the frontend or backend. Lost in debugging-land After discarding Angular frontend errors, I started to dig into backend controller and Kaminari configuration. Nothing seems wrong. Everything looked good: test suite, smoke tests, desktop debugging. Despite of test results, I started to wonder: what if returned-data is wrong after all? and... Bingo! Finally, after checking every response I noticed that there were duped records in two different pages(pagination requests). Those duped records were skipped from Angular data-table and that's why loaded/total rows did not match. Bingo: a sort of mistake This tricky bug has a simple explanation: bad sorting. Kaminari uses a SQL query using LIMIT/OFFSET strategy: SELECT * FROM posts ORDER BY id LIMIT 25 OFFSET 0 ORDER BY : sort the collection. LIMIT : number of records per page. OFFSET : is used to skip a specified number of rows before starting to return rows from a query. This works perfectly using ORDER BY id because primary key is unique. Check table A. id title body created_at lock 101 Welcome to the Platform First post introducing the new platform features. 2026-06-16 08:15:22 false 102 Summer Update Announcing the latest improvements and updates. 2026-06-16 08:15:22 true 103

Dalla libreria embedded che ha invaso ogni dispositivo a un’implementazione moderna con concorrenza, async I/O e vector search: cosa cambia davvero per chi sviluppa app. Nel frontend e nel full‑stack capita spesso di parlare di database come servizi: Postgres gestito, cluster, repliche, connessioni, pooling, credenziali e una lunga lista di “cose che possono rompersi”. Ma esiste un’altra filosofia, più vicina all’idea di “dipendenza” che di “infrastruttura”: un motore SQL che vive dentro l’applicazione. Questa è la ragione per cui SQLite è ovunque. È una libreria, non un server. Legge e scrive su un singolo file su disco. Riduce drasticamente configurazione, porte, processi separati e complessità operativa. Ed è proprio questa semplicità a renderla una delle fondamenta silenziose dell’informatica moderna: la usi in browser, smartphone, desktop app, tool CLI, IoT… spesso senza nemmeno accorgertene. Ora immagina di riscrivere tutto da capo, in Rust, cercando di essere compatibile al 100% e allo stesso tempo più “moderna”. Sembra un’idea folle per definizione—finché non inizi a guardare ai limiti pratici che oggi emergono in molte applicazioni. Perché toccare SQLite, se funziona così bene? SQLite non è “il problema”. Anzi: è considerata estremamente robusta perché è conservativa, minimalista, e custodita con un rigore quasi maniacale. Il punto è un altro: il suo modello di sviluppo e manutenzione è atipico rispetto a quello che molti intendono per open source collaborativo . Il codice è disponibile e utilizzabile liberamente, ma l’evoluzione è guidata da pochissime persone e—di fatto—non segue la dinamica classica delle contribution esterne. Questa scelta ha un effetto collaterale positivo: riduce il rischio di regressioni introdotte da cambiamenti non coerenti con la visione del progetto. Ma ha anche un costo: se la tua azienda o il tuo prodotto hanno esigenze nuove (concorrenza più spinta, I/O non bloccante, funzionalità specifiche), “aspettare che arrivi upstream” n

DuckDB 1.4.5 LTS, pgEdge ColdFront Beta, and SQLite's FCNTL_PDB Internals Today's Highlights This week's highlights feature the latest DuckDB 1.4.5 LTS release, a new open-source beta for PostgreSQL data tiering, and a deep dive into an obscure SQLite internal file control operation. These updates offer performance, architectural flexibility, and internal insights across the SQLite ecosystem. Announcing DuckDB 1.4.5 LTS (Andium) (DuckDB Blog) Source: https://duckdb.org/2026/06/17/announcing-duckdb-145.html The latest Long Term Support (LTS) release of DuckDB, version 1.4.5 named "Andium", has been announced, primarily focusing on bugfixes and performance enhancements. DuckDB, an in-process analytical processing database, continues to refine its engine for enhanced stability and efficiency in embedded and edge computing environments. While the announcement is concise, LTS releases are crucial for developers and organizations relying on a stable and well-tested version for their data pipelines and analytical workloads, ensuring long-term compatibility and reliability. This update is vital for maintaining the robustness of applications that utilize DuckDB for local data transformations, complex analytical queries, and other high-performance data operations. Users of previous 1.4.x versions are encouraged to upgrade to benefit from the accumulated stability improvements and minor speedups, all without introducing major breaking changes. This commitment to incremental improvements and stable releases solidifies DuckDB's position as a premier solution for embedded analytical database needs, making it a reliable choice for critical projects. Comment: An LTS release, even with bugfixes, is always welcome from DuckDB. It reinforces their commitment to a stable and performant analytical database that I frequently use for local data processing and reporting. Introducing ColdFront: Seamlessly Uniting OLTP, Analytics and AI Workloads on PostgreSQL (Planet PostgreSQL) Source: htt

A practical walkthrough of SQL-First persistence: no XML, no Mapper interfaces, no generated queries. I maintain a Java backend that handles ~1M requests/day. For persistence, we used to run MyBatis. The XML was manageable at first, then it wasn't. Dynamic conditions became <if> tag soup. A simple join query needed three files and two languages. We switched to a simpler approach. Here's how it works for the most common case: single-table CRUD. What You Need Java 21+ Spring Boot (any 3.x) A database (H2 for the demo, MySQL/PostgreSQL for production) That's it. No XML parser, no code generator, no annotation processor. The Project pom.xml src/main/java/example/ DemoApplication.java user/ User.java -- entity UserDao.java -- data access UserCond.java -- query conditions src/main/resources/ application.yml schema.sql Three Java files for a complete CRUD API. The Entity @Data @Builder @Table ( "sys_user" ) public class User { @Id private Long id ; private String name ; private Integer age ; private String email ; // ... other fields // These four are auto-managed: private LocalDateTime createTime ; private Long createBy ; private LocalDateTime updateTime ; private Long updateBy ; private Byte dr ; // 0 = active, 1 = soft-deleted } @Table maps to the database table. @Id marks the primary key (Snowflake ID by default). The audit fields and soft-delete marker are handled automatically—you don't set them in business code. The DAO @Repository public class UserDao extends BaseDao < User > { // Empty. All CRUD methods inherited. } BaseDao provides save , saveBatch , update , delete , findById , list , page , count , exists . For single-table operations, this is all you need. The Conditions @Getter @Setter @Builder public class UserCond extends BaseCondition { private String name ; private Integer ageMin ; private Integer ageMax ; private Byte dr ; private Object [] ids ; @Override protected void addCondition () { and ( "name LIKE" , name , 3 ); // 3 = %value% and ( "age >=" , ag

PostgreSQL Error 22036: non numeric sql json item PostgreSQL error code 22036 ( non numeric sql json item ) occurs when a SQL/JSON path expression attempts to perform a numeric operation on a JSON item that is not a number — such as a string, boolean, array, or object. This error was introduced alongside the SQL/JSON Path feature in PostgreSQL 12 and typically surfaces in queries using jsonb_path_query , jsonb_path_exists , or the @@ and @? operators. Top 3 Causes 1. Numeric Values Stored as Strings The most common cause is JSON data where numbers are stored as quoted strings (e.g., "price": "100" instead of "price": 100 ). This frequently happens with data from external APIs or legacy systems that don't enforce type consistency. -- Triggers 22036: "price" is a string, not a number SELECT jsonb_path_query ( '{"price": "100"}' , '$.price + 50' ); -- ERROR: non numeric SQL/JSON item -- Fix: Use the .double() conversion method in JSON Path SELECT jsonb_path_query ( '{"price": "100"}' , '$.price.double() + 50' ); -- Result: 150 -- Alternative fix: Cast at the SQL level SELECT ( data ->> 'price' ):: numeric + 50 FROM ( SELECT '{"price": "100"}' :: jsonb AS data ) t ; -- Result: 150 2. Arithmetic Applied Directly to Arrays or Objects Developers sometimes write JSON Path expressions that target an entire array or object instead of individual elements, then attempt arithmetic on the result. -- Triggers 22036: $.scores returns an array, not a number SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores + 10' ); -- ERROR: non numeric SQL/JSON item -- Fix: Target a specific array index SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores[0] + 10' ); -- Result: 90 -- Fix: Use wildcard to apply operation to each element SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores[*] + 10' ); -- Results: 90, 100, 80 -- Fix: Use unnest for aggregation use cases SELECT elem :: numeric + 10 FROM jsonb_array_elements ( '{"scores": [80, 90, 70]}' :: jsonb ->

Lately I keep reaching for SQLite where, before, I'd have reached for Postgres without thinking. It started with small services, then a bigger question: could a multi-tenant SaaS actually run on SQLite? And for AI agents specifically, isn't a local, embedded database the more natural home for their state? Turso is the version of this stack I've found most compelling so far, especially when paired with Cloudflare. I wish D1 would reach embedded-replica parity with Turso, and that AWS offered a managed SQLite-style service the way it offers RDS for Postgres. This isn't a "Postgres is over" argument. I still use Postgres more often than SQLite. And it isn't advice. It's just where my thinking has drifted recently — written down mostly so I can find out where it's wrong. Read it as one person's notes, not a recommendation. Where I've landed for now (and expect to keep revising): SQLite isn't replacing Postgres. For work state , it's increasingly my first reach, not my last. AI agents push this harder: their state is high-churn, local, and mostly private. The answer isn't all-local. It's a local workbench plus a central ledger . Why the old default existed For years, "where does the data live?" had one practical answer: a server, behind an API, in a shared Postgres. A lot of that wasn't architecture — it was the cheapest shape available. SQLite was already everywhere, but it lacked the operational layer that makes a database viable as SaaS infrastructure: networking, replication, managed backups, and a way to run many small databases without drowning in tooling. So centralizing was the path of least resistance, and a tenant_id column in shared Postgres became the reflex. What changed isn't SQLite. It's that the ecosystem grew the missing parts — and for a growing class of workloads, the thing doing the most frequent writing moved onto my own machine. The constraint that's lifting SQLite itself is, by design: Embedded, not networked — a library, nothing listens on a port.

Data privacy regulations such as GDPR, LGPD, and HIPAA demand that organizations know exactly where Personally Identifiable Information (PII) lives inside their databases. Yet in practice, most teams rely on manual inventories, tribal knowledge, or external scanning tools that require data to leave the database engine — a process that itself creates privacy and security risks. This article presents an MVP that takes a different approach: it runs PII detection inside InterSystems IRIS using Embedded Python, analyzing data where it lives and never exporting it to an external process. The result is a lightweight, non-intrusive utility that scans your tables, identifies PII using AI, and produces a structured CSV report — all without data ever leaving the IRIS process. The Problem: PII You Don't Know You Have Organizations today face a painful blind spot. A typical IRIS instance may contain hundreds of tables across dozens of schemas, some holding decades of accumulated data. Columns named ContactInfo , Notes , or Description might silently contain social security numbers, email addresses, or government IDs — sometimes intentionally, sometimes as a side effect of free-text fields that capture whatever users type in. Traditional approaches to PII discovery share a common flaw: they require data extraction. You export samples, send them to an external service, or pipe them through a standalone tool. Every step in that pipeline is an additional attack surface and a potential compliance violation. The principle of data sovereignty — keeping data within its jurisdiction and under controlled access — suggests a better path: bring the analysis to the data, not the data to the analysis. This is not just a technical preference; it is a governance requirement: GDPR (EU) — Article 28 requires that any processing of personal data by a third-party processor be governed by a binding contract covering subject-matter, duration, purpose, data types, and obligations [ Art. 28 GDPR ]. Art

PostgreSQL 19 Beta has been announced, with general availability expected in September, following the project's yearly major-release cadence. This release introduces native SQL Property Graph Queries (SQL/PGQ), concurrent table repacking to reclaim storage without downtime, and a broad set of performance, observability, and administration improvements. By Renato Losio

This week's Java roundup for June 8th, 2026, features news highlighting: the GA release of A2A Java SDK 1.0; an update on Jakarta EE 12; point releases of Micrometer Metrics and Micrometer Tracing; maintenance releases of GraalVM Native Build Tools and OpenXava; the second release candidate of Gradle 9.6; and the first milestone release of Eclipse JNoSQL 1.2. By Michael Redlich

PostgreSQL HA Risks, Replication Internals, & Rapid Branching Today's Highlights Today's highlights include critical insights into Patroni's replication slot management, an architectural deep dive into PostgreSQL's synchronous commit behavior, and a look at achieving sub-second database branching for enhanced developer workflows. When Patroni Silently Deletes Your Replication Slots (Planet PostgreSQL) Source: https://postgr.es/p/9lM This article uncovers a critical operational pitfall when using Patroni, a popular high-availability solution for PostgreSQL, with logical replication. It details how Patroni, under specific failure scenarios or configuration changes, can silently remove replication slots without warning. Replication slots are vital for ensuring that standbys or logical replication consumers do not miss any changes, making their deletion a potentially severe data integrity issue. The author explains the underlying reasons for this behavior, often related to how Patroni manages pg_basebackup or restores, and how it might not re-create logical replication slots automatically. The post provides concrete scenarios where this can occur, such as when a new primary is elected and old slots aren't re-established, or during certain recovery operations. It emphasizes the importance of diligent monitoring of replication slot status and proposes strategies to mitigate the risk of silent deletion, including careful Patroni configuration and robust alerting mechanisms. This insight is crucial for database administrators and developers relying on Patroni for resilient PostgreSQL deployments, highlighting a subtle but dangerous interaction between these two powerful components. Comment: This is a must-read for anyone running Patroni with PostgreSQL, especially if using logical replication. Understanding this specific behavior of Patroni deleting replication slots silently is essential to prevent unexpected data loss or integrity issues in production. Why Postgres Doesn'

Sometimes you genuinely need the set of columns to update to be data, not code. An operator maps configuration fields to database columns, and you want to honor that mapping without redeploying every time it changes. The naive solution — build an UPDATE string from those column names — is also one of the easiest ways to hand-write a SQL injection vulnerability. This is how to get the flexibility without the hole. We'll build it up in three layers: make it work, make it safe, then count the cost. Layer 1: The dynamic update, the wrong way The tempting version concatenates column names into SQL: // DO NOT do this. var sql = $"UPDATE products SET { columnName } = { value } WHERE id = { id } " ; If columnName comes from configuration that an operator can edit, you've just made your schema writable by whoever controls that config. A value of name = 'x'; DROP TABLE products; -- is now your problem. Even "trusted" config is an injection surface the moment it flows into a SQL string. Layer 2: The same feature with EF.Property EF Core's ExecuteUpdateAsync lets you set a property by name without ever building SQL yourself. EF.Property<T> takes the property name as a string, and EF parameterizes the value and validates the property against the model: await db . Products . Where ( p => p . Id == id ) . ExecuteUpdateAsync ( setters => setters . SetProperty ( p => EF . Property < float ?>( p , columnName ), value )); This is already a different security posture: the value is a parameter, not interpolated text, and EF will throw rather than emit SQL if columnName isn't a real mapped property. But "EF will throw" is a runtime backstop, not a policy. We want to reject bad names before they reach the database, fail closed, and control exactly which columns are writable. Layer 3: Reflection as a whitelist The guard is to validate every incoming column name against the entity's actual properties, using reflection, and to keep an explicit blacklist of fields that must never be touched d

Paginated queries with automatic total count calculation. Supports specifying result fields. public < E > LimitResult < List < E >, E > query ( Class < E > cls , LimitSelect limitSelect ) public < E > LimitResult < List < E >, E > query ( Class < E > cls , LimitSelect limitSelect , LimitRange range ) public < E > LimitResult < List < E >, E > query ( Class < E > cls , LimitSelect limitSelect , List resultFields ) public < E > LimitResult < List < E >, E > query ( Class < E > cls , LimitSelect limitSelect , LimitRange range , List resultFields ) Returns: LimitResult — contains getResult() (data list) and getTotal() (total count). Example: // Define pagination query LimitSelect limitSelect = new LimitSelect () { public SelectOrderByStep from ( SelectSelectStep select ) { return select . from ( T ( "user_table" )) . where ( jt . conditions ( "name%" , name , "birthday>=" , beginDate )); } public List < OrderField > orderBy () { return Arrays . asList ( F ( "birthday" ). desc ()); } }; // Mode 1: return all data, no total count LimitResult res1 = jt . query ( User . class , limitSelect ); // Mode 2: return limit rows, no total count LimitResult res2 = jt . query ( User . class , limitSelect , LimitRange . of ( 20 )); // Mode 3: paginate (offset starts at 0), calculate total count LimitResult res3 = jt . query ( User . class , limitSelect , LimitRange . of ( 20 , 0 )); // res3.getResult() returns data, res3.getTotal() returns total count // Mode 4: specify result fields LimitResult res4 = jt . query ( User . class , limitSelect , LimitRange . of ( 20 , 0 ), Arrays . asList ( "id" , "name" )); // LimitRange.all(): return all data, no total count LimitResult res5 = jt . query ( User . class , limitSelect , LimitRange . all ()); // Access results List < User > data = res3 . getResult (); int total = res3 . getTotal (); About the LimitSelect interface: // LimitSelect is a interface: public interface LimitSelect { // Build the FROM clause; the select parameter allows specifyi

MVCC, WAL, vacuum, and replication slots under sustained delete load - and how to delete billions of rows without your database noticing Most "how to delete a lot of rows" articles stop at "batch it and delete children before parents." That advice is correct, it's table stakes, and everyone already knows it. This article is about everything after that - the parts that actually decide whether your cleanup runs quietly in the background for a week or pages you at 3 a.m. with a full disk and a replica that's six hours behind. The thesis: at scale, your DELETE statement is the easy part. The adversaries are the subsystems a delete feeds - MVCC tuple versioning, the write-ahead log, autovacuum, and the replication machinery. Bulk deletion is really an exercise in flow control across those subsystems . Get the SQL right and the systems wrong, and you'll still take production down. We'll assume PostgreSQL (the internals are PG-specific), a live OLTP primary with at least one physical replica and one or more logical/CDC consumers, and a target of hundreds of millions to billions of rows across many related tables. The one paragraph of "basics," so we can move on: delete in dependency order (referencing rows before referenced rows); collect parent keys once; never rely on ON DELETE CASCADE for huge deletes because you can't throttle a cascade. Done. Now the real material. 1. What a DELETE actually costs A delete is not "remove a row." Under MVCC it's "mark a row version dead and write that fact everywhere." For each deleted tuple, PostgreSQL: Sets xmax on the heap tuple to your transaction id. The row is still physically present; it becomes a dead tuple once your transaction commits and no snapshot can still see it. Writes a WAL record for the heap change. If this is the first modification of that page since the last checkpoint, it also writes a full-page image (FPI) - potentially 8 KB of WAL for a single-row change. Touches every index. Index entries aren't removed at delet

PostgreSQL Error 22P01: Floating Point Exception PostgreSQL error code 22P01 is raised when a floating-point operation produces an exceptional result that cannot be represented as a valid number. This typically occurs during division by zero on float types, operations involving NaN (Not a Number), or arithmetic that yields Infinity . It is most commonly encountered in analytics, financial calculations, and data pipelines processing external or sensor data. Top 3 Causes 1. Division by Zero on Float Types Unlike integer division (which raises 22012 ), dividing a float by zero triggers a floating-point exception. This is especially common in ratio and rate calculations where the denominator can become zero at runtime. -- Problematic query SELECT total_sales :: float / total_orders :: float AS avg_order_value FROM daily_stats ; -- Safe fix using NULLIF SELECT date , total_sales :: float / NULLIF ( total_orders , 0 ):: float AS avg_order_value FROM daily_stats ; 2. NaN Values in Arithmetic Operations Data ingested from external systems, CSVs, or APIs may silently introduce NaN values into float columns. Once NaN participates in arithmetic, results become unpredictable and can trigger exceptions downstream. -- Detect NaN values (NaN is the only value not equal to itself) SELECT id , value FROM sensor_readings WHERE value != value ; -- Replace NaN with NULL safely UPDATE sensor_readings SET value = NULL WHERE value != value ; -- Filter NaN in aggregations SELECT device_id , AVG ( value ) FILTER ( WHERE value = value ) AS clean_avg FROM sensor_readings GROUP BY device_id ; 3. Infinity Arithmetic Conflicts Storing 'Infinity'::float or '-Infinity'::float is valid in PostgreSQL, but performing certain operations on them produces mathematically undefined results (e.g., Infinity - Infinity = NaN ), which can cascade into a floating-point exception. -- Check for Infinity values SELECT id , measurement FROM raw_data WHERE measurement IN ( 'Infinity' :: float , '-Infinity' :: float

Dulu, nyari query yang bikin database spike itu bisa makan berhari-hari. Yang nyari capek, yang nge-fix juga capek. Sekarang? Hitungan jam — dan bonusnya, sambil belajar hal baru juga. Ceritanya begini. Kalau kamu pernah kerja di aplikasi yang pakai ORM (Object-Relational Mapping — semacam "penerjemah otomatis" antara code dan database), pasti familiar sama situasi ini: database tiba-tiba lambat, kamu dapet raw query yang jadi biang kerok, tapi di codebase kamu nulis pakai syntax ORM yang bentuknya beda jauh dari SQL mentah itu. Buat yang belum pernah deal sama ORM, bayangin gini: kamu nulis pesan dalam bahasa Indonesia, lalu ada "penerjemah otomatis" yang convert jadi bahasa Jepang sebelum dikirim ke penerima. Suatu hari ada masalah di pesan yang terkirim — tapi kamu cuma bisa lihat versi bahasa Jepang-nya. Nyari bagian mana dari tulisan Indonesia kamu yang bikin terjemahan-nya bermasalah? Itu effort-nya yang bikin pengen balik tidur aja. Sekarang dengan bantuan Kiro, cukup kasih raw query + akses ke codebase, dia otomatis nyari bagian mana di code yang nge-generate query bermasalah itu. Yang dulu butuh berhari-hari, sekarang bisa selesai dalam hitungan jam — dan itu baru tahap investigasi, belum termasuk fixing-nya. Ceritanya Kenapa Bisa Pakai Kiro Akhir-akhir ini lagi aktif pakai Kiro di tempat kerja. Awal tahun lalu kantor dapat credits melalui program Kiro for Startup , jadi ya sekalian dimaksimalkan. Selain buat debug dan explore query di MS SQL Server, kadang pakai Kiro juga buat analisa log AWS CloudWatch — sambil kasih context aplikasi yang running biar analisa-nya lebih akurat dan gak generic. Di tulisan kali ini, saya mau sharing gimana pakai Kiro sebagai partner beberapa minggu terakhir buat improve query performance di aplikasi .NET Core. Kenapa "partner"? Karena Kiro-nya gak boleh langsung akses ke database — jadi wajib melalui perantara saya. Kita discuss, kolaborasi, dan nge-solve bareng. Bukan AI yang dikasih tombol terus disuruh jalan sendiri. Wakt

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