标签：#sql

It's Friday evening. An endpoint that normally answers in 200 milliseconds is suddenly taking eight seconds. You open Grafana. Every graph is green. CPU is calm, memory is fine, the disk isn't full. By every dashboard you have, the database is healthy. It is not healthy. This is the failure mode monitoring is worst at: the server is unmistakably alive , so nothing alerts, while inside the database something is slowly rotting. A table has bloated. An index nobody uses is dragging down every INSERT . A forgotten transaction is sitting open, holding a lock and quietly making everything worse. None of it crashes. It just degrades, a little at a time, until one Friday evening it tips over. The good news is that Postgres will tell you all of this — you just have to ask. The queries below run on bare PostgreSQL (13 or newer; one version note along the way), need no agent and no paid monitoring, and use an extension in exactly one place where it genuinely earns it. Open psql and check your own database as you read. 1. The cheapest signal: dead rows Start here, because it costs nothing and catches the most. Postgres never deletes a row in place. An UPDATE or DELETE leaves behind a dead tuple — an old version of the row — and autovacuum cleans those up later. Until it does (or if it can't keep up), the dead rows sit in the table, taking space and forcing every scan to page past them. The fastest look is pg_stat_user_tables , always available, no extension: SELECT schemaname , relname AS table , n_live_tup , n_dead_tup , round ( n_dead_tup * 100 . 0 / nullif ( n_live_tup + n_dead_tup , 0 ), 1 ) AS dead_ratio , last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC LIMIT 20 ; A dead_ratio above ~20% on a large table is worth investigating. And watch for a table where the ratio is high and last_autovacuum is empty — that means autovacuum has never successfully run on it, which is its own red flag (we'll see why in section 5; the whole story conver

Abstract Cada vez más equipos quieren consultar datos sin escribir SQL a mano. El problema es que un sistema Text-to-SQL no solo debe “traducir preguntas”, sino también entender el esquema, restringir permisos, validar consultas y explicar por qué una consulta es rápida o lenta. Ese enfoque coincide con la ruta propuesta por el tutorial oficial de LangChain para agentes SQL: listar tablas, inspeccionar esquemas, generar la consulta, revisarla, ejecutarla y corregir errores hasta obtener una respuesta; y el propio tutorial advierte que ejecutar SQL generado por modelos tiene riesgos y exige permisos mínimos. En paralelo, la documentación oficial de Python recomienda usar placeholders en sqlite3 para enlazar parámetros y evitar inyección SQL, mientras que la documentación de SQLite explica que EXPLAIN QUERY PLAN permite inspeccionar si una consulta hace SCAN, SEARCH y si usa índices. manueldongo23 / sql_ai_sales_assistant_demo SQL AI Sales Assistant A safe Text-to-SQL demo that converts natural language business questions into SQL queries, executes them on a local SQLite retail database, and shows the generated SQL plus EXPLAIN QUERY PLAN . This project was created as evidence for an article about SQL AI Database Solutions . Topic Talk to your database with AI: build a safe SQL query extractor with Streamlit and SQLite. Features Natural language prompts such as sales by month , top customers , sales in Lima . Rule-assisted NL→SQL generation, designed to be transparent and auditable. SQLite demo database with customers, products, orders and order items. Read-only SQL validator that blocks destructive commands. Parameterized queries for user-provided filters. Streamlit interface. CLI demo for quick testing. Query plan inspection with EXPLAIN QUERY PLAN . Architecture User question ↓ NL → SQL interpreter ↓ Read-only SQL validator ↓ SQLite execution ↓ Results + EXPLAIN QUERY … View on GitHub Cuerpo del artículo La promesa de SQL + IA suena sencilla: le haces una pregunta

PostgreSQL provides several planner configuration parameters, such as enable_seqscan and enable_indexscan , that influence how execution plans are generated. These settings affect planning, not the execution of an already-generated plan. With prepared statements, this raises an interesting question. Should planner settings be applied before PREPARE, before EXECUTE, or both? Let's look at a simple example: a "tasks" table with a due date and a "done" status: \ c drop table if exists tasks ; -- a table of tasks with status (done or not) and due date create table tasks ( id bigint generated always as identity primary key , due timestamptz , done boolean ); -- insert 500 tasks, with 1% not done insert into tasks ( due , done ) select now () + interval '1 day' * n , 42 != n % 100 from generate_series ( 1 , 500 ) n ; -- index the todo (partial index) create index on tasks ( due , id ) where done = false ; vacuum analyze tasks ; With a partial index, I indexed only the tasks that are not yet done ( done = false ) because that's my most frequent query pattern: postgres =# explain select id , due , done from tasks where done = false and id > 0 order by due limit 1 ; QUERY PLAN --------------------------------------------------------------------------------------- Limit ( cost = 0 . 13 .. 3 . 60 rows = 1 width = 17 ) -> Index Scan using tasks_due_id_idx1 on tasks ( cost = 0 . 13 .. 17 . 47 rows = 5 width = 17 ) Index Cond : ( id > 0 ) ( 3 rows ) With partial indexes, the condition covered by the index is not even visible in the execution plan because the index itself enforces the condition. Prepared statement I decided to use a prepared statement with all values as parameters. It is probably not a good idea in this case. When a parameter can have only a few different values and you expect different cardinalities for each, you should probably define one query per value, using literals. I'm doing this to illustrate what can happen, with a simple, extreme example: postgres =# pr

Introduction As data grows exponentially, accessing and querying databases efficiently...

SQLite Internals, Postgres 19 Checksums, & PL/CBMBASIC Extension Today's Highlights This week, we delve into SQLite's secure deletion and blob updates, explore upcoming data integrity features in PostgreSQL 19, and discover a unique PostgreSQL extension bringing Commodore 64 BASIC to your database. These updates offer insights into database internals, future resilience, and creative extensibility for the SQLite ecosystem. Secure Delete and BLOB Updates in SQLite (SQLite Forum) Source: https://sqlite.org/forum/info/6f3e886a1149c97e0ede9a243281efb05a043705393ea94437ed7c0556315972 This SQLite forum discussion delves into the nuances of secure data deletion and efficient BLOB updates within SQLite databases. Secure deletion is a critical concern for applications handling sensitive data, where simply deleting a row might not zero-out the underlying storage, leaving recoverable remnants. The thread explores methods and implications for ensuring data is truly eradicated when removed, potentially touching on PRAGMA settings or specific file system interactions. Understanding these mechanisms is crucial for developers building secure, embedded applications with SQLite. The conversation also extends to optimizing updates for BLOB (Binary Large Object) data. Efficiently handling large binary data, such as images or documents, in an embedded database like SQLite requires careful consideration to avoid performance bottlenecks and excessive disk I/O. The discussion likely covers strategies for in-place updates, managing free space, and the internal workings of SQLite's storage engine when dealing with variable-length BLOBs. This insight helps developers make informed decisions on schema design and update patterns for improved application performance and data integrity. Comment: This thread offers valuable insights into SQLite's low-level data management, essential for anyone needing to implement robust security or optimize BLOB storage. PostgreSQL 19 to Feature Checksums For All

Introduction Fixing N+1 queries with select_related / prefetch_related or selectinload (see the previous post ) gets you down to a small, sane number of queries per request. The next bottleneck is what each query costs once the table has millions of rows — and that is almost always about indexing. An index turns "scan every row" into "look it up directly." Skip it, and a query that's instant in development takes seconds once real data volume shows up in production. How Indexes Work: The B-Tree Intuition Without an index, a WHERE clause forces a sequential scan : the database reads every row and checks the condition — O(n) , cost grows linearly with table size. An index is a separate, sorted structure (almost always a B-tree ) mapping column values to row locations. Because it's sorted and balanced, finding a value is a tree walk: O(log n) . On a 10-million-row table, that's the difference between reading 10 million rows and roughly 23 tree nodes. This isn't free: Writes get slower — every INSERT / UPDATE / DELETE on an indexed column also updates the index. Storage grows — each index is a sorted copy of (part of) the data. An index trades write cost and storage for read speed. Indexing a column you rarely filter or sort on is pure cost, no benefit. Reading Query Plans: EXPLAIN ANALYZE Postgres' EXPLAIN ANALYZE shows what the planner actually did, not an estimate. Before an index , filtering orders by customer_id : EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 48291 ; Seq Scan on orders (cost=0.00..21453.00 rows=42 width=96) (actual time=0.021..118.442 rows=41 loops=1) Filter: (customer_id = 48291) Rows Removed by Filter: 1199959 Planning Time: 0.112 ms Execution Time: 118.471 ms Seq Scan means Postgres read all ~1.2 million rows and discarded all but 41. actual time is real elapsed time — 118ms for one lookup. After CREATE INDEX idx_orders_customer_id ON orders (customer_id); : Index Scan using idx_orders_customer_id on orders (cost=0.42..8.53 rows=42 wid

Introduction Fixing N+1 queries (see the previous post ) gets your Hibernate app down to a handful of queries per request. The next bottleneck is what each of those queries costs once your tables have millions of rows — and that is almost always a question of indexing. An index turns "scan every row" into "look it up directly." Get the index wrong — or skip it — and a query that took 2ms in development takes 4 seconds in production once real data volume shows up. How Indexes Work: The B-Tree Intuition Without an index, a WHERE clause forces a sequential scan : the database reads every row and checks the condition. That's O(n) — cost grows linearly with table size. An index is a separate, sorted data structure (almost always a B-tree ) that maps column values to row locations. Because it's sorted and balanced, finding a value is a tree walk: O(log n) . On a 10-million-row table, that's the difference between reading 10 million rows and reading roughly 23 tree nodes. The cost is not free: Writes get slower. Every INSERT / UPDATE / DELETE on an indexed column must also update the index structure. Storage grows. Each index is a copy of (part of) the data, sorted differently. An index is a trade: you pay on every write so that specific reads become fast. Indexing a column you rarely filter or sort on is pure cost with no benefit. Reading Query Plans: EXPLAIN ANALYZE Postgres' EXPLAIN ANALYZE shows what the planner actually did — not what you hope it did. Before an index , filtering orders by customer_id : EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 48291 ; Seq Scan on orders (cost=0.00..21453.00 rows=42 width=96) (actual time=0.021..118.442 rows=41 loops=1) Filter: (customer_id = 48291) Rows Removed by Filter: 1199959 Planning Time: 0.112 ms Execution Time: 118.471 ms Seq Scan means Postgres read all ~1.2 million rows and threw away all but 41 of them. actual time is the real elapsed time, not an estimate — 118ms for one lookup. After CREATE INDEX idx_orders

SokoFlow Build Log — Month 1 of 4 Last semester I set out on a new strategic plan to level up my software development skills through deliberate, project-based learning. That work produced one of the most ambitious things I've built so far: Sim-Pesa , a local-first transactional appliance that lets developers working in the M-Pesa ecosystem test and simulate STK Push workflows entirely on their own machines, without depending on the Daraja sandbox. I documented that build in 16 weekly posts, which you can find here . This semester, the focus shifts — from fintech foundations to cloud-native integration and real-world systems. The flagship project is SokoFlow , a conversational ERP for small Kenyan shopkeepers to track inventory and record sales entirely through WhatsApp chat. No app to download, no training session required — just natural language. Where Sim-Pesa lived in a controlled, predictable transactional world, SokoFlow steps into the mess of cloud-native reality: third-party API failures, webhook signature verification, the statelessness of HTTP, and container orchestration. The target audience shifts too — Kenyan SMEs operating on infrastructure that is often unreliable by design, not by exception. It's an ambitious project, but the goal was always to learn as much as possible from it. With the plan in place, I got to work. 1. The Vision of a Headless ERP The first real question I had to answer before writing a line of code: what does "headless" actually mean? Headless architecture decouples the frontend — the "head," or user interface — from the backend, the "body" that holds the data and business logic. A conventional ERP bundles both: backend plus a dashboard or UI on top. A headless ERP, by contrast, is just the engine. The brain. There's no built-in screen. So how do users interact with a system that has no interface of its own? SokoFlow doesn't actually care. It could be: WhatsApp SMS A web app A mobile app A voice assistant In this case, the "frontend

Hello Dev Community! 👋 It is officially Day 89 of my 100-day full-stack engineering run! 🎯 Yesterday, I kicked off my competitive solving streak on HackerRank. Today, I advanced from standard linear filters into the powerful world of textual pattern recognition by mastering: SQL Regular Expressions (REGEXP) and String Anchors! 🔍🛡️ When processing real-world data pipelines—like validating structured phone inputs, email domains, or parsing specific text queries—standard LIKE operators can make your code messy and repetitive. Today, I solved these constraints elegantly. 🧠 Shifting from Bulky LIKE Statements to Sleek REGEXP As tracked inside my workspace files across "Screenshot (193).png" and "Screenshot (195).png" , I solved two distinct core challenges from the HackerRank series: 1. Match from the Start: Weather Observation Station 6 The Goal: Query the list of CITY names from STATION that start with vowels ( a , e , i , o , u ), ensuring no duplicates are returned. The Evolution: Instead of chaining multiple LIKE queries or cutting sub-strings with LEFT() , I utilized the caret anchor ( ^ ) inside a regular expression array to verify the string's starting boundary instantly: sql SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP "^(A|E|I|O|U)";

If you've ever worked with e-commerce data, you know that "a pair of jeans" is never just one product. A single style might come in 5 washes, 8 sizes, and 3 inseam lengths. That's 120 potential SKUs. Handling this correctly in an API can be tricky, so let me share a pattern I've used for structuring product variants. The core problem is balancing flexibility with performance. You want customers to filter by size, color, and fit without making dozens of API calls. Here's a simple but effective approach using a normalized database schema with a flat query layer: -- Products table (the "parent") CREATE TABLE products ( id UUID PRIMARY KEY , name TEXT NOT NULL , description TEXT , base_price DECIMAL ( 10 , 2 ), category TEXT ); -- Variants table (the actual sellable items) CREATE TABLE variants ( id UUID PRIMARY KEY , product_id UUID REFERENCES products ( id ), sku TEXT UNIQUE NOT NULL , size TEXT , color TEXT , wash TEXT , inseam TEXT , price DECIMAL ( 10 , 2 ), -- can override base price stock_quantity INT , image_url TEXT ); The key insight? Keep the product metadata (description, care instructions, brand story) in the products table, but put all the sellable attributes in variants. This lets you run queries like: -- Find all size 28 jeans in "mid wash" under $80 SELECT p . name , v . color , v . wash , v . price , v . stock_quantity FROM products p JOIN variants v ON p . id = v . product_id WHERE p . category = &# 039 ; women - jeans &# 039 ; AND v . size = &# 039 ; 28 &# 039 ; AND v . wash LIKE &# 039 ; % mid %&# 039 ; AND v . price & lt ; 80 AND v . stock_quantity & gt ; 0 ORDER BY v . price ; For the frontend, I usually return a flattened structure: { "product": { "id": "abc -123 " , "name": "Classic Straight Leg Jean" , "description": "High-rise fit in stretch denim..." , "availableSizes": [ " 24 " , " 25 " , " 26 " , " 27 "

Denormalization has been the standard approach to analytical data modeling for good reason. Moving joins, lookups, and business rules out of query time and into ingestion gives you the fastest possible reads for a known access pattern. For most of the past decade, it was often the practical default for latency-sensitive analytics. Earlier columnar engines and distributed query processors could execute joins, but many workloads paid for them through higher latency, higher compute cost, spill-to-disk, or distributed coordination overhead. That constraint has loosened. Modern columnar databases with advanced join algorithms have reduced the cost of runtime joins enough that normalization is now a genuinely viable option for many analytical workloads. Denormalization still delivers faster reads, but normalization can bring operational benefits: simpler pipelines, flexible schemas, and cleaner governance. Engineers can now make the decision based on their actual workload characteristics, rather than being forced into one approach by engine limitations. This guide is a decision framework for making that choice in ClickHouse. It starts with why denormalization became the default, explains what has changed in join performance, then compares the tradeoffs on both sides so you can decide where to denormalize, where to join, and where to use ClickHouse primitives that bridge the gap. For a broader evaluation framework covering latency, concurrency, ingest throughput, SQL flexibility, and cost across real-time OLAP options, see our guide to choosing a database for real-time analytics in 2026 . For a deeper comparison of how ClickHouse executes star schema joins against Druid, Pinot, and cloud DWHs, see our star schema and fast joins guide . TL;DR Denormalization and normalization are both valid modeling strategies. The right choice depends on your workload. Denormalization's tradeoffs are primarily operational : pipeline complexity, write-path overhead, data freshness lag, back

Have you ever wondered how apps like university portals know which courses a student is enrolled in, or how they pull up an instructor's full schedule in seconds? The answer lies in database relationships - one of the most important concepts in backend development. In this article, we'll explore: What database relationships are and why they matter The three types of relationships: One-to-One, One-to-Many, and Many-to-Many How relationship schemas work (primary keys, foreign keys) How SQL Joins let you pull connected data from multiple tables To keep things grounded, we'll use one running example throughout: a University Management System . By the end, you won't just understand the theory, you'll see exactly how these concepts connect in a real-world scenario. What Are Database Relationships? A database relationship defines how data in one table connects to data in another. Instead of storing the same information repeatedly, relational databases organize data into separate tables and link them using keys . Think about our university system. We have a table for students and another for courses . A student can enroll in multiple courses, and each course can have many students. Rather than storing a student's full details on every course record, we store the student's info once and create a relationship between the two tables. This keeps data clean, reduces duplication, and makes updates easy. If a student's email changes? Update it in one place - done. Here's a simple visual of what that looks like: +------------------+ +------------------+ | Students | | Courses | +------------------+ +------------------+ | student_id (PK) | | course_id (PK) | | name | | title | | email | | credits | +------------------+ +------------------+ \ / \ / \ / Enrollments (links students ↔ courses) Now let's look at the three types of relationships you'll encounter. Types of Database Relationships 1. One-to-One (1:1) Each record in Table A matches exactly one record in Table B and vice versa

Hello Dev Community! 👋 It is officially Day 76 of my 100-day full-stack engineering streak! For the past several weeks, I have been heavily immersed in NoSQL databases, using MongoDB documents to back my full-stack clones. Today, I decided to broaden my database engineering skill set by taking a deep dive into Relational Databases (SQL) ! 📊⚡ Stepping out of flexible JSON-like structures and adjusting to rigid, highly optimized tables is an essential step for any well-rounded backend developer. 🧠 What I Learned Today: SQL vs. NoSQL Before writing code, I mapped out the core architectural differences between the two paradigms: Feature NoSQL (e.g., MongoDB) SQL (e.g., MySQL / PostgreSQL) Data Model Flexible, schema-less collections & documents. Strict table-based structures with rows & columns. Relationships Typically nested embedded sub-documents or references. Explicit Relational Mapping via Primary & Foreign Keys. Scaling Horizontally scalable (distributed sharding across nodes). Vertically scalable (requires increasing horsepower on one machine). Transactions Great for high-write, unstructured or dynamic data shapes. Strict ACID compliance, making it excellent for financial or tabular data. 🛠️ Analyzing My First Query Block on Day 76 As showcased in "Screenshot (174).png" , I configured an entire relational lifecycle inside an independent database script: 1. Database Provisioning & Focus Selection I initialized the data cluster safely using standard syntax constraints to ensure execution safety and loaded the working context into the active engine: sql CREATE DATABASE IF NOT EXISTS XYZ_Company; USE XYZ_Company;CREATE TABLE employee_info ( id INT PRIMARY KEY, name VARCHAR(30), SALARY INT );

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