标签：#Database

NocoDB is an open-source no-code platform that puts a spreadsheet-style UI on top of a relational database, with grid, form, Kanban, and gallery views plus a REST API. This guide deploys NocoDB using Docker Compose with a PostgreSQL backend and Traefik handling automatic HTTPS, then exercises the API with a sample base. By the end, you'll have NocoDB serving a base over HTTPS with API access at your domain. Set Up the Directory Structure 1. Create the project directories: $ mkdir -p ~/nocodb/ { data,pgdata,letsencrypt } $ cd ~/nocodb 2. Create the environment file: $ nano .env DOMAIN = nocodb.example.com LETSENCRYPT_EMAIL = admin@example.com POSTGRES_DB = postgres POSTGRES_PASSWORD = strong_password POSTGRES_USER = postgres Deploy with Docker Compose 1. Create the Compose manifest: $ nano docker-compose.yaml services : traefik : image : traefik:v3.6 container_name : traefik command : - " --providers.docker=true" - " --providers.docker.exposedbydefault=false" - " --entrypoints.web.address=:80" - " --entrypoints.websecure.address=:443" - " --entrypoints.web.http.redirections.entrypoint.to=websecure" - " --entrypoints.web.http.redirections.entrypoint.scheme=https" - " --certificatesresolvers.letsencrypt.acme.httpchallenge=true" - " --certificatesresolvers.letsencrypt.acme.httpchallenge.entrypoint=web" - " --certificatesresolvers.letsencrypt.acme.email=${LETSENCRYPT_EMAIL}" - " --certificatesresolvers.letsencrypt.acme.storage=/letsencrypt/acme.json" ports : - " 80:80" - " 443:443" volumes : - " ./letsencrypt:/letsencrypt" - " /var/run/docker.sock:/var/run/docker.sock:ro" restart : unless-stopped db : image : postgres:16 container_name : nocodb-db hostname : root_db environment : POSTGRES_DB : ${POSTGRES_DB} POSTGRES_USER : ${POSTGRES_USER} POSTGRES_PASSWORD : ${POSTGRES_PASSWORD} volumes : - " ./pgdata:/var/lib/postgresql/data" healthcheck : test : [ " CMD" , " pg_isready" , " -U" , " ${POSTGRES_USER}" , " -d" , " ${POSTGRES_DB}" ] interval : 10s timeout : 5s retries :

I spent the last few months trying to understand why reconciliation errors keep appearing in high-throughput pipelines. Here is what I found. In the race to process millions of transactions daily, modern fintech ecosystems have achieved a genuine miracle of scale. But beneath the surface of that velocity lies a structural problem most engineering teams aren't measuring: causality violations in async event pipelines. Most teams assume that if a transaction shows "Success" in the database, the job is done. At high concurrency levels, that assumption breaks quietly. When "Eventual Consistency" Becomes "Eventual Loss" In distributed systems, Kafka partitions and database shards experience micro-millisecond timing gaps. When a network retry delays a validation webhook, the downstream ledger can commit a wallet update before the validation that should have preceded it completes. To the user, the app glitches. To the engineering team, it's a reconciliation ticket. To the CFO, it's untracked operational cost. The Reconciliation Tax I built a simulation modelling this exact failure mode across 5,000 concurrent transactions. With an 8% network retry probability, conservative for high-traffic payment rails, the causality violation rate was 8.3%. At one million daily transactions, that's over 80,000 unvalidated commits every day requiring manual review. The operational cost compounds across three dimensions: engineering hours spent patching database state, fraud model accuracy degrading on out-of-order training data, and audit trails that cannot demonstrate strict causal sequence to regulators. The Fix The solution is enforcing strict event ordering at the ingestion layer before state commits happen, not better monitoring after the fact. When safeguards including partition-aware routing, exponential backoff, and idempotency controls were added to the same simulation, the violation rate dropped to 0%. Full simulation code and methodology: github.com/yakuburoseline1-gif/cif-simul

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

We opened Chapter 1 with a single line, SELECT * FROM users WHERE id = 1 . For that line to leave the client and come back as a result row, the PostgreSQL backend went through five stages. First it decided which processing path the message should take; then the parser and analyzer turned the text into a tree and gave it meaning from the catalog. The rewriter expanded views and injected policies to transform the tree, the planner weighed the possible execution paths by cost and picked the cheapest one, and the executor followed that plan, pulling up one tuple at a time and sending them back to the client. Chapter 1 was a story about how a query is processed . What tree a given SQL becomes, what plan it turns into, in what order it runs. From start to finish, a chain of logical transformations. But what every one of those stages ultimately deals with is data. The executor pulls up tuples, yet where on disk those tuples lie and in what shape, how they come up into memory, Chapter 1 never asked. When the planner judged an index scan cheaper than a sequential scan, it never opened up what that index physically is. Chapter 1 followed only the logical journey of a query, leaving untouched the substance of the data that journey stands on. Chapter 2, Storage & Access Methods, opens up that substance. In what unit data sits on disk (page), where disk and memory meet (buffer manager), where and how a row survives (heap), and how that row is found quickly (B-tree and the specialized indexes). The very tuple the planner weighed by cost and the executor pulled up in Chapter 1, where it actually came from and how it came to be there, is what Chapter 2 reveals. If Chapter 1 was the logical life of a query, Chapter 2 is the physical dwelling of data. We now look at how the data a query reaches for actually lives on disk.

A scan node sits at the leaf of the tree and pulls rows from a single table. A join node sits in the middle and brings together the rows that its two children send up. It takes one row from users , one row from orders , checks whether they belong to the same user, and if they match, emits the combined row. PostgreSQL has three nodes for this one job: NestLoop, HashJoin, and MergeJoin. The reason a single task splits into three nodes is much like the reason scans did. There is more than one way to find matching pairs from two inputs, and which way is cheapest depends on the size of the inputs and the shape of the join condition. Deciding which way is cheapest, by costing the alternatives, was the planner's job in an earlier chapter. This section looks at what those three nodes actually do when they execute. Given the same two tables, the three find matches in completely different ways, and that difference in approach is exactly what tells them apart. How the three nodes route requests All three join nodes are internal nodes with two children. One child is called the outer, the other the inner. All three run on the Volcano model's pull framework: when the parent asks for the next row, the join node takes rows from its two children, builds one matched row, and sends it up. The only difference is the order and manner in which it routes pull requests to its two children. NestLoop pulls the inner from the start all over again for each outer row it receives. HashJoin slurps the inner in one pass to build an index in memory, then takes outer rows one at a time and probes that index. MergeJoin, on the assumption that both sides are sorted in the same order, advances both sides one step at a time in lockstep. NestLoop: rescan the inner for every outer row The simplest method is NestLoop. As the name says, the loops are nested. The outer loop takes one row from the outer; the inner loop scans the inner from beginning to end, looking for inner rows that match that outer row. Wh

By the time 1.4 ends, the planner has produced one PlannedStmt. Inside it is an execution tree built from Plan nodes, frozen into a form you can follow step by step, something like "go into the primary key index on users, fetch the one matching row, then output that whole row." But that is still only a blueprint. Reading actual pages off disk, picking out the rows that match the condition, handing results back to the caller: none of that has happened yet. The stage that takes that blueprint and produces actual rows is the executor. The difference between the planner and the executor is the difference between deciding and doing. The planner was the stage that weighed "which index, in what order, with what join method" by cost and chose . The executor takes the chosen approach and carries it out as is . There is nothing left to choose. It just runs the nodes baked into the plan tree and pulls rows out of them. To run it, the executor takes the Plan tree it received and turns it into a PlanState tree. The Plan tree is the static blueprint the planner made, and it does not change during execution. But to actually run, each node needs state that changes as execution proceeds: which row it is reading now, whether the hash table is fully built, what tuple it has buffered from a child. So when execution begins, a PlanState tree with the exact same shape as the Plan tree is created. The blueprint Plan tree is left untouched, and the running state lives in that PlanState tree instead. How the executor produces result rows is the heart of the stage. The executor does not build the entire result set at once and stack it up. Instead, it asks the topmost node of the tree for "the next row," and that request travels down the tree to the leaves. When a leaf scan node reads one row from a page and passes it up to its parent, that row climbs up one level at a time through joins and filters until it reaches the top. The top sends that single row to the caller (the client, or the targe

Everything from 1.4.1 through 1.4.9 happened inside a single function, standard_planner() . Building paths, costing them, searching for a join order, estimating cardinality from statistics: all of it runs inside that one function. Yet PostgreSQL does not call standard_planner() directly. It puts another function, planner() , one step ahead of it, and has planner() call standard_planner() . And planner() can be made to call some other function instead of standard_planner() . That replacement is what the planner hook enables. When pg_stat_statements measures per-query planning time, or pg_hint_plan rewrites a plan according to hints, it all goes through this hook. Let's look at how PostgreSQL provides a way to observe or change planning behavior without touching a single line of the core, and how external code plugs into it. All planner() does is check the hook The body of planner() is essentially this. if ( planner_hook ) result = ( * planner_hook ) ( parse , query_string , cursorOptions , boundParams ); else result = standard_planner ( parse , query_string , cursorOptions , boundParams ); planner_hook is a global function pointer. Its default value is NULL , in which case standard_planner() is called right away. A plain PostgreSQL build always takes this path: planner_hook is empty, so the incoming query goes straight to standard_planner() . The key here is the type of planner_hook . typedef PlannedStmt * ( * planner_hook_type ) ( Query * parse , const char * query_string , int cursorOptions , ParamListInfo boundParams ); This signature is identical, down to the character, to that of planner() and standard_planner() . It takes the same Query and returns the same PlannedStmt (the execution plan). So external code only has to write a planner function matching this type and store its address in planner_hook . Let's call this function, written by external code to register in planner_hook , a custom planner function. The moment its address is stored, every planning reque

In the earlier posts of this series, we looked at practical query tuning tips and how to read and interpret query plans . A recurring theme in both was: "add an index here." But "add an index" is a bit like saying "use the right tool" — the interesting part is which one. PostgreSQL ships with several index types, each tuned for a different kind of data and query. Picking the wrong one means PostgreSQL quietly ignores your index and goes back to a sequential scan. In this post, we'll walk through the main index types, when each shines, and the special index variations (composite, partial, covering, expression) that often matter more than the type itself. Setting the Scene: Schema and Sample Data We'll reuse the same schema from the previous posts, with one small addition — a metadata JSONB column and a tags array on orders , so we can explore the more exotic index types. CREATE TABLE customers ( id SERIAL PRIMARY KEY , customer_name VARCHAR ( 255 ), email VARCHAR ( 255 ), created_at TIMESTAMPTZ DEFAULT NOW () ); CREATE TABLE orders ( id SERIAL PRIMARY KEY , customer_id INT REFERENCES customers ( id ), order_date TIMESTAMPTZ DEFAULT NOW (), total_amount NUMERIC ( 10 , 2 ), status VARCHAR ( 20 ), tags TEXT [], metadata JSONB ); -- Insert sample customers INSERT INTO customers ( customer_name , email ) SELECT 'Customer ' || i , 'customer' || i || '@example.com' FROM generate_series ( 1 , 1000000 ) AS s ( i ); -- Insert sample orders INSERT INTO orders ( customer_id , order_date , total_amount , status , tags , metadata ) SELECT ( RANDOM () * 1000000 ):: INT , NOW () - interval '1 day' * ( RANDOM () * 365 ):: int , ( RANDOM () * 500 + 20 ), ( ARRAY [ 'pending' , 'shipped' , 'delivered' , 'cancelled' ])[ FLOOR ( RANDOM () * 4 + 1 )], ARRAY [( ARRAY [ 'gift' , 'priority' , 'fragile' , 'bulk' ])[ FLOOR ( RANDOM () * 4 + 1 )]], jsonb_build_object ( 'channel' , ( ARRAY [ 'web' , 'mobile' , 'store' ])[ FLOOR ( RANDOM () * 3 + 1 )]) FROM generate_series ( 1 , 1000000 ) AS s ( i

I've been working on ctrodb — a client-side database for TypeScript that runs in the browser (IndexedDB) and Node.js (in-memory). Zero runtime dependencies. It started as a personal project to stop rewriting IndexedDB wrappers. Every new client-side app needed the same boilerplate: open a connection, create object stores, handle version upgrades, write CRUD helpers. After the sixth time, I wrote it once and got it right. What it does ctrodb gives you MongoDB-like CRUD with schema validation at write time: import { Database } from " ctrodb " const db = new Database ({ name : " my-app " , schema : { version : 1 , collections : { notes : { fields : { title : { type : " string " , required : true }, body : { type : " string " }, pinned : { type : " boolean " , default : false }, tags : { type : " array " , items : { type : " string " } }, createdAt : { type : " string " , default : () => new Date (). toISOString () }, }, indexes : [{ field : " createdAt " }], }, }, }, }) await db . connect () const notes = db . collection ( " notes " ) const note = await notes . create ({ title : " Hello " , body : " World " }) const results = await notes . query () . where ( " pinned " , true ) . sort ({ createdAt : " desc " }) . limit ( 10 ) . fetch () Every record is a Model — a Proxy wrapper with typed field access. note.title works. note.update() handles writes. Direct property assignment logs a warning telling you to use .update() instead. What's included The core package ships with three plugins: Full-text search — inverted index, stop word removal, auto-indexed on create/update/delete Relations — has_many, belongs_to, has_one with lazy accessors built into every Model and eager loading via .with() Custom validation — extendable rules beyond the built-in validators (email, URL, regex) Plus React hooks (separate import, same package): import { DatabaseProvider , useQuery , useMutation } from " ctrodb/react " Signal-based reactivity. When data changes, useQuery re-fetches and your

This guide covers three core areas of daily GBase 8a operations: tracking data loads and collecting error details, configuring audit logs and analysing slow queries, and hierarchically tuning memory parameters. It also provides a standard daily and weekly inspection checklist for your gbase database . 1. Data Load Monitoring 1.1 Load Methods GBase 8a supports two main load methods: gload for large‑scale offline imports (recommended), and LOAD DATA INFILE for single‑file loads with MySQL‑like syntax. 1.2 Checking Load Progress Monitor running and historical loads through system tables: -- Currently executing load tasks SELECT task_id , table_name , status , start_time , loaded_rows , error_rows , TIMESTAMPDIFF ( SECOND , start_time , NOW ()) AS elapsed_sec FROM gclusterdb . load_task WHERE status IN ( 'RUNNING' , 'PENDING' ) ORDER BY start_time DESC ; -- Last 50 load history records SELECT task_id , table_name , status , start_time , end_time , loaded_rows , error_rows , TIMESTAMPDIFF ( SECOND , start_time , end_time ) AS duration_sec FROM gclusterdb . load_task ORDER BY start_time DESC LIMIT 50 ; 1.3 Retrieving the Last Load Task ID SELECT @@ gbase_loader_last_task_id ; Then query error details with that ID: SELECT * FROM gclusterdb . load_error_log WHERE task_id = 'your_task_id' LIMIT 100 ; 1.4 Error Data Collection Enable error collection in the gcluster configuration file ( gbase.cnf ) for production: gbase_loader_logs_collect = ON 1.5 Load Performance Parameters Parameter Scope Description Recommended gcluster_loader_max_data_processors gcluster Max concurrent load processing threads CPU cores / 2 gcluster_loader_min_chunk_size gcluster Chunk size sent to gnode (bytes) 64 MB gbase_loader_parallel_degree gnode Parallel write threads on gnode 4 – 8 gbase_loader_buffer_count gnode Number of load buffers 4 2. Audit Log Configuration and Analysis 2.1 Enabling Audit Logs Configure in both gcluster and gnode gbase.cnf files: audit_log = ON log_output = FILE # or TABLE

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

ClickHouse HTTP API: A Complete Beginner's Guide Introduction When most people think about interacting with a database, they usually imagine connecting through a database client or application. However, ClickHouse also provides a simple and powerful HTTP API that allows you to query and manage your database using standard HTTP requests. The ClickHouse HTTP API provides a universal interface for communicating with your ClickHouse server. Since almost every programming language and automation tool supports HTTP, it becomes an excellent choice for integrations, monitoring, scripting, and lightweight applications. In this guide, you'll learn what the ClickHouse HTTP API is, why it's useful, and how to perform common database operations using simple HTTP requests. What Is the ClickHouse HTTP API? The ClickHouse HTTP API is a built-in interface that enables clients to communicate with a ClickHouse server using the HTTP protocol. Instead of connecting through the native TCP protocol, you simply send HTTP requests and receive responses in formats such as JSON, CSV, TSV, XML, or plain text. The HTTP interface is: Language agnostic Easy to integrate with web applications Firewall friendly Simple to test using tools like cURL, Postman, or a web browser Because of its simplicity, the HTTP API is widely used for automation, dashboards, data pipelines, and monitoring systems. Why Use the HTTP API? The ClickHouse HTTP API offers several advantages: No dedicated database driver is required. Works with virtually every programming language. Easy integration with REST-based applications. Supports multiple output formats. Ideal for automation and scripting. Perfect for cloud-native applications and microservices. Common Operations Using the HTTP API, you can: Execute SQL queries Insert data Create and modify tables Retrieve query results Export data in different formats Automate database operations Authentication Options ClickHouse supports multiple authentication methods when using th

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

pgrac is an open attempt to rebuild Oracle RAC's core machinery (shared-everything storage, multiple active nodes all writing one database, a cluster-wide change number) on top of PostgreSQL 16. Build log #1 laid out the four problems that fight back. This one is about the problem that turns a node failure into silent data corruption, and the first, deliberately modest, layer pgrac ships against it. The failure mode In a shared-nothing cluster an evicted node is mostly harmless: it owns its own disks, so the cluster routes around it. In a shared-everything cluster the same event is dangerous, because every node writes the same storage. Picture the classic split: node 2 misses heartbeats, the cluster declares it dead and remasters its work elsewhere, but node 2 is not actually dead. It is frozen on a long GC pause, or its interconnect NIC flaked, and it is about to wake up and finish the write it started. Now two nodes believe they own the same blocks, and shared storage will accept both writes. That is not a crash. It is corruption you find three days later. Oracle RAC's answer is I/O fencing: before remastering a dead node's resources, you make certain it can no longer touch the storage, with STONITH, SCSI-3 persistent reservations, or a hardware watchdog. The node is fenced at a layer below its own software, because the whole point is that you cannot trust the dead node's software to behave. That hardware layer is real work, and it is not what pgrac built first. What it built first is the layer above it: an in-process cooperative write-fence, now default-ON. The rest of this is precise about what that does and does not buy you, because "we have fencing" is the kind of claim that is worth less than nothing if it is overstated. A fence needs an authority everyone can agree on You cannot fence on local opinion, because the whole problem is that the dead node disagrees about being dead. Authority has to live on durable, shared, quorum-backed storage. pgrac writes a sm

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 ->

You type "k8s deployment troubleshooting" into your documentation search. The top result is a page about Kubernetes architecture that never mentions the word "troubleshooting." It is exactly what you need. BM25 would have missed it entirely. This is the promise of vector search: finding documents by meaning, not just matching words. In 2025 and 2026, vector search has moved from niche ML engineering to a core Elasticsearch capability. If you are building search for AI applications - RAG pipelines, semantic Q&A, recommendation systems - understanding how Elasticsearch handles vectors is no longer optional. I have spent the past year building RAG pipelines at Cloudera, and I have learned that vector search is powerful but easy to misuse. This post covers what works, what does not, and how to implement it in production. Why Vector Search Matters (And When It Does Not) BM25, which we covered in a previous post, is brilliant at matching exact terms. But it is fundamentally lexical. It does not understand that: "k8s" and "kubernetes" are the same thing "docker container" and "containerization" are related concepts "out of memory error" and "heap exhaustion" describe the same problem Vector search solves this by converting text into high-dimensional numerical vectors (embeddings) where semantically similar content lives close together in vector space. A query for "k8s deployment troubleshooting" gets embedded into a vector, and Elasticsearch finds the nearest document vectors - even if they do not share a single keyword. But vector search is not a replacement for BM25. It is a complement. BM25 is faster, requires no ML infrastructure, and excels at exact-term matching. Vector search is slower, requires embedding models, and shines at conceptual similarity. The best search systems in 2026 use both. How Elasticsearch Stores and Indexes Vectors Elasticsearch introduced the dense_vector field type in version 7.x and has dramatically improved it through 8.x and into 2026. Here

TL;DR — UUID v7 trie 13× plus vite que v4 en simulation B-tree (1M entrées), expose une empreinte mémoire identique, mais révèle son timestamp d'émission. UUID v4 reste le choix "zéro réflexion" pour les identifiants isolés. Le reste de cet article vous donnera les données pour décider. Introduction Les UUIDs sont omniprésents dans les systèmes modernes : clés primaires de bases de données, identifiants de sessions, tokens de traçabilité. Pourtant, le choix de la version impacte directement les performances en écriture et en lecture, la fragmentation des index, et — dans certains contextes — la confidentialité des données. UUID v4 (RFC 4122, 2005) est aujourd'hui la version par défaut de presque tous les ORM et frameworks. UUID v7 (RFC 9562, 2024) est son successeur moderne, conçu pour corriger son principal défaut : le désordre lexicographique. Dans cet article, nous allons mettre les deux en face avec des benchmarks réels sur des volumes de 100 000 à 10 millions d'UUIDs , analyser leur structure bit par bit, et vous donner une grille de décision claire. Structure interne : ce que contiennent ces 128 bits UUID v4 xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx Bits Contenu 0–47 Aléatoire 48–51 Version (0100 = 4) 52–63 Aléatoire 64–65 Variant (10) 66–127 Aléatoire 122 bits d'entropie pure. Aucune information temporelle. Chaque UUID est statistiquement indépendant des autres. UUID v7 019ed5c8-2a2f-7974-91f2-6ba1f313dcfa └──────────────┘ 48 bits = timestamp Unix en millisecondes Bits Contenu 0–47 Timestamp Unix (ms) 48–51 Version (0111 = 7) 52–63 Aléatoire (sub-ms ou compteur) 64–65 Variant (10) 66–127 Aléatoire 74 bits d'entropie + 48 bits de temps. Naturellement monotone : deux UUIDs générés dans la même milliseconde sont toujours distincts et ordonnés de façon cohérente. Lecture du timestamp (Python) : import uuid6 u = uuid6 . uuid7 () b = u . bytes ts_ms = int . from_bytes ( b [: 6 ], ' big ' ) # → 1781703125553 (ms depuis epoch Unix) Depuis la sortie de nos benchmarks : [00

Time-series data is one of the most common types of data generated by modern applications. Every log entry, API request, metric, transaction, sensor reading, or user interaction is recorded with a timestamp, making time the primary dimension for analysis. As organizations collect billions of these records, efficiently storing and querying them becomes increasingly challenging. This is where ClickHouse® excels. Although ClickHouse is not a dedicated time-series database, its columnar storage architecture, vectorized query execution, high compression ratios, and massively parallel processing make it an excellent choice for time-series analytics at scale. It is capable of ingesting large volumes of data while delivering analytical queries in milliseconds. The article begins by explaining the fundamentals of time-series data and highlighting common real-world use cases such as application monitoring, IoT sensor data, financial market analysis, server metrics, user activity tracking, and business analytics. These workloads typically involve continuous data ingestion, time-based filtering, aggregations, and trend analysis. One of ClickHouse's biggest strengths is its optimization for analytical workloads. Since data is stored column-wise rather than row-wise, only the required columns are read during query execution. Combined with compression and vectorized processing, this significantly reduces I/O and improves query performance over massive datasets. The article also demonstrates how to create an optimized table for time-series workloads using the MergeTree engine. Proper partitioning by month and ordering data by dimensions and timestamps help ClickHouse prune unnecessary partitions and efficiently locate relevant data during queries. Several practical SQL examples are covered, including: Filtering records within a specific time range Aggregating metrics by hour, day, week, or month Calculating averages, sums, minimums, and maximums Grouping events over time Working wi