标签：#database

I've been building a database engine from scratch in Rust, and I recently finished the lexer. The lexer itself wasn't the most interesting part. What I found more valuable was how my design evolved as I learned more about Rust and how compilers and database systems are typically implemented. My First Approach When I started, I stored the input as a Vec<char> . It felt straightforward because I could access characters directly without worrying about UTF-8 boundaries. I also represented identifiers like this: Ident ( String ) At first glance, this seems perfectly reasonable. Every identifier token carries its own text, making it easy for the parser to consume. The Problem As the lexer grew, I started asking myself a simple question: The identifier already exists in the original SQL query. Why am I allocating another string and copying the same data into every token? For a query like: SELECT username , email FROM users ; the source text already contains: username email users Creating separate String allocations for each identifier means duplicating data that already exists. I also learned an important detail about Rust enums. The size of an enum is influenced by its largest variant. Once variants start carrying additional data, every token instance becomes larger than it otherwise needs to be. Moving to a Span-Based Design Instead of storing identifier text directly inside tokens, I switched to storing only the token kind: Ident along with source location information: Span { start , end , line , column , } Now the token only answers two questions: What is this token? Where did it come from? If the parser needs the actual identifier text, it can recover it directly from the original SQL source using the stored byte range. Replacing Vec<char> with &str The second design change was moving away from: Vec < char > and operating directly on: & str using lifetimes. Instead of creating another collection containing the entire input, the lexer now walks over borrowed source tex

Cloudflare recently described how a slowdown in its billing pipeline was traced to contention inside the query planning stage of ClickHouse. The team profiled the bottleneck and patched ClickHouse to replace an exclusive lock with a shared lock, drop the per-query copy of the parts list, and improve part filtering. By Renato Losio

I've been frustrated with SQL learning resources for a while. Most are either: Dry reference docs Toy exercises with no context ("SELECT * FROM employees") Paid platforms with paywalls after level 3 So I built SQLwak — a free, browser-based SQL game where you're hired as a Graduate Analyst at Lion City Bank , a fictional Singapore bank. How it works Instead of abstract exercises, every challenge is a real business request from a colleague: "The Operations team needs all Central region branches for an upcoming audit." "Risk wants customers with credit scores below 600 who have active loans." "Finance needs vessels ranked by cargo revenue — use window functions." You write actual SQL against a realistic 9-table banking database and get immediate feedback. 57 levels across 4 tiers Tier Skills 🟢 Foundational SELECT, WHERE, ORDER BY, LIMIT 🟡 Intermediate JOINs, GROUP BY, HAVING, subqueries 🔴 Advanced CTEs, multi-table aggregations ⚫ Expert Window functions (RANK/DENSE_RANK OVER PARTITION BY), UNION ALL, compound CTEs The database schema Lion City Bank has two divisions: Retail Banking: customers, accounts, transactions, loans, branches, products Maritime Trade Finance (Advanced/Expert levels): vessels, cargo_shipments, trade_finance_facilities — covering voyages between Singapore, Port Klang, Bangkok, Jakarta, and Ho Chi Minh City. The maritime division exists because Singapore is a major trade hub. It makes the Expert levels genuinely interesting — you're ranking vessels by cargo revenue and analyzing trade finance utilisation rates, not just counting rows. Technical details Next.js 15 + TypeScript + Tailwind CSS SQLite via WebAssembly — all query execution is client-side, no backend needed Deployed on Vercel Fully open source: github.com/martinl5/sqlwak No signup. No download. Just SQL. Open the link and start writing queries: sqlwak.vercel.app Would love feedback on difficulty progression, new level ideas, or schema additions. What SQL concepts do you wish you'd pract

DuckDB Integrates Lance Lakehouse; SQLite CVE Fix; Postgres 19 Beta on K8s Today's Highlights This week, DuckDB introduces integrated vector and hybrid search with the Lance lakehouse format, enabling advanced AI workloads directly from SQL. Meanwhile, SQLite addresses a significant security vulnerability with a recent fix, and a new guide helps users test PostgreSQL 19 beta efficiently in Kubernetes clusters. Test-Driving the Lance Lakehouse Format in DuckDB (DuckDB Blog) Source: https://duckdb.org/2026/05/21/test-driving-lance.html DuckDB has announced a new integration allowing users to test-drive the Lance lakehouse format, a design specifically geared towards AI workloads. This collaboration between LanceDB and DuckLabs brings fast vector and hybrid search capabilities directly into DuckDB SQL, significantly streamlining data analysis for machine learning applications. The integration means users can perform complex queries involving vector embeddings and traditional tabular data without needing to move data between different systems or tools. This is a crucial development for data scientists and engineers who rely on efficient data access and processing for large-scale AI projects. The ability to perform sophisticated searches within the familiar DuckDB environment enhances productivity and reduces the operational overhead typically associated with managing specialized vector databases. This new feature democratizes access to advanced data structures for AI, making it simpler to build and prototype machine learning models on massive datasets. By supporting an open lakehouse format, DuckDB continues to position itself as a versatile analytical database, bridging the gap between traditional data warehousing and emerging AI data needs. Users are encouraged to try out this integration, which promises to unlock new possibilities for data exploration and feature engineering using a unified SQL interface. It's an excellent example of how embedded analytical databases

A post titled "Learn SQL Once, Use It for 30 Years" hit the front page of r/programming this week (307 points, 48 comments). The claim sounds like the kind of thing a database vendor would put on a billboard, so I went looking for the part that holds up. It turns out the longevity is not marketing. It is a property of how the language was designed, and it is the reason SQL is one of the few skills on a developer's resume that does not quietly expire. I run a site that compares developer tools, which means I spend a lot of time watching technologies rise, peak, and get replaced. Most of what you learn in this field has a half-life measured in single-digit years. The framework you mastered in 2019 is legacy by 2024. SQL is the strange exception, and the reasons are worth understanding before you decide where to spend your next month of learning. Where the staying power comes from SQL did not start as a language. It started as a math paper. In 1970, Edgar Codd published "A Relational Model of Data for Large Shared Data Banks," which proposed organizing data into tables of rows and columns with formal rules for combining them. IBM built a query language on top of that model in the mid-1970s, called it SEQUEL, and later renamed it SQL after a trademark conflict. The important detail is the order: the model came first, the language second. SQL is a surface over a mathematical foundation that has not needed to change. That foundation is why the skill compounds instead of decaying. When you learn SQL, you are not memorizing one vendor's API. You are learning the relational model, and the model is the same whether the data sits in Postgres, MySQL, SQLite, Oracle, or SQL Server. A join is a join everywhere. Move from one database to another and the syntax shifts at the edges, but the way you think about the problem carries over intact. Compare that to a frontend framework, where moving stacks means relearning how to think, not just how to type. Declarative is the whole trick

Managing a multi-million product catalog on Magento presents unique challenges around performance, scalability, and operational efficiency. At Rave Digital, we recently undertook a Magento performance optimization project for a large-scale eCommerce merchant struggling with slow site speed, infrastructure bottlenecks, and backend instability. This use case breakdown details how we modernized their Magento architecture, optimized database performance, and scaled infrastructure to deliver a stable, high-speed shopping experience. This post is tailored for eCommerce managers, directors, and Magento merchants—especially those running Adobe Commerce or Magento Open Source platforms—who want to understand practical strategies for Magento architecture scaling and performance tuning for large catalogs. The Problem: Performance Bottlenecks in a Complex Magento Environment: Our client operated an enterprise Magento store with a multi-million product catalog. Despite Magento’s robust capabilities, the site suffered from: Slow page load times impacting user experience and SEO Scalability challenges as product volume and traffic grew Infrastructure bottlenecks causing backend instability and downtime Complex integrations and manual processes limiting operational efficiency Platform limitations in handling large catalog management and real-time inventory updates These issues collectively threatened the site’s ability to support growth and deliver a seamless customer experience. The client sought a comprehensive Magento platform modernization to address these challenges. Context: Why Magento Architecture and Infrastructure Matter Magento’s flexibility and extensibility make it ideal for enterprise eCommerce, but large catalogs require careful architecture and infrastructure planning. Key technical pain points include: Database performance under heavy read/write loads Indexing delays and cache invalidation impacting site speed Integration complexity with third-party systems and API

TL;DR Reconciliation is the part of a payments stack nobody architects for on day one and everyone pays for on day 200. The job: prove that every internal transaction matches the acquirer's settlement file, in the right currency, with the right fees, on the right value date — or surface the diff fast. The mechanics: normalize files → land into an events table → project to a read model → diff against the internal read model → buckets for ops to resolve. The boring details (file formats, fee parsing, FX rounding, value dates) are where 90% of the work lives. If you've ever opened a CSV from an acquirer at the end of the month, sorted by amount, and tried to "just match it in Excel" — yes, this post is for you. What "reconciled" actually means A transaction is reconciled when, for the same logical payment, three views agree: What you sent — your internal record of the charge/payout (your read model). What the acquirer says happened — their settlement file or API report. What the bank actually credited / debited — the bank statement. Disagreements are normal. Persistent disagreements are how you lose money slowly and never know. The shape of a settlement file Across the major acquirers, settlement files look broadly similar — and broadly different in the places that matter: Field Variants you'll see Transaction reference acquirer's transaction_id , sometimes plus a merchant_reference round-tripped from you Gross amount minor units / decimal; transaction currency vs settlement currency Fees inline per-row, or aggregated at the file footer, or in a separate fees file FX inline rate vs separate FX file; sometimes only the converted amount Value date when the bank actually moves money — often T+1/T+2 from event date Adjustments refunds, chargebacks, fee corrections, reserves — usually mixed in Encoding UTF-8 if you're lucky; CP1252 / fixed-width / SWIFT MT940 if you're not Granularity one row per transaction or daily aggregates per merchant or both There's no industry-clean

TypeORM 1.0 is the first major release of the open-source TypeScript and JavaScript ORM since its inception in 2016. This version modernizes platform requirements, removes deprecated APIs, and introduces numerous bug fixes and new features. TypeORM now supports ECMAScript 2023, dropping older Node.js versions and dependencies while enhancing security and migration processes. By Daniel Curtis

If you’re building a generative AI chatbot, global e-commerce platform, or industrial IoT solution in 2026, picking the wrong database can sink performance, blow your budget, or delay your launch. For years, teams relied on one-size-fits-all relational databases for every workload, but modern applications demand specialized tools for specific use cases. AWS solves this challenge with 15+ purpose-built database engines across 8 distinct categories, optimized for performance, scalability, and cost efficiency for every imaginable workload. This guide breaks down every AWS database type, its core features, real-world use cases, and 2026 best practices to help you choose the right tool for your next project. Table of Contents Why Purpose-Built Databases Are the Standard in 2026 AWS Database Categories: A Deep Dive 2.1 Relational Databases 2.2 Key-Value Databases 2.3 In-Memory Databases 2.4 Document Databases 2.5 Graph Databases 2.6 Wide Column Databases 2.7 Time-Series Databases 2.8 Data Warehouse 2026 AWS Database Best Practices Common Mistakes to Avoid When Choosing AWS Databases Conclusion References Why Purpose-Built Databases Are the Standard in 2026 Modern workloads have vastly different requirements: a generative AI RAG system needs fast vector search, an IoT fleet needs high-throughput time-series data ingestion, and a global SaaS platform needs multi-region consistency with zero downtime. A single relational database cannot meet all these needs without tradeoffs. AWS purpose-built databases eliminate these tradeoffs by: Supporting open standard APIs to avoid vendor lock-in Offering serverless deployment options for all major engines Including built-in AI/ML and vector search capabilities Delivering up to 99.999% availability for mission-critical workloads Reducing TCO by 25-48% compared to self-managed or generic alternatives (per IDC) AWS Database Categories: A Deep Dive Relational Databases Relational databases store data in structured tables with fixed schema

You have explained your tech stack to your coding agent four times this month. You mentioned your preferred approach to a problem in January, and your agent has no idea it ever happened. You corrected a decision last week and the old version is still surfacing. You set up context at the start of every session because there is nowhere for it to go at the end. This is not a model problem, as GPT-4, Claude, and Gemini all have the same limitations. The model is stateless. They all have inbuilt memory, and still every session starts from zero unless you have the infrastructure to persist what matters and surface it at the right moment. That sophisticated memory infrastructure is what most developers do not have. VEKTOR Slipstream v1.6.3 is a local-first memory SDK for AI agents. This release adds the layer most memory systems skip: not just storing what you tell it, but managing what should still be there months later: curation. What you actually get Before the architecture: What changes for you as a developer embedding this SDK. Every AI memory system forces decisions you didn’t realise you were making. Where does your agent’s context actually lives, is it on your machine or on someone else’s server? Are you paying per token every time your agent understands a memory, or does that happen locally? When you connect your GitHub, your calendar, your files — where does all that data go, and who can see it? Most memory systems answer all four questions for you, quietly, in their terms of service. VEKTOR’s answer to all four is the same: your machine, your data, your rules. Memory lives in a single SQLite file you own. Embeddings run locally on CPU — no API calls, no per-token cost, no data leaving the process. MCP connectors spawn as local stdio processes; nothing is routed through an external service. There is no telemetry, no cloud sync, no account required. If you want to understand exactly what your agent knows about you, you open the database with any SQLite browser and

SQLite Optimizer Deep Dive, Change-Set Internals & Azure PostgreSQL Architecture Today's Highlights This week, we explore SQLite's query planner optimizations, delve into a critical flag for change-set replication, and dissect the architectural choices behind Azure's managed PostgreSQL. These insights offer valuable perspectives on performance, data integrity, and cloud database deployment strategies. Extend "Omit OUTER JOIN" optimization to COUNT(*) (SQLite Forum) Source: https://sqlite.org/forum/info/b949c721db6f0289104db944d6a7e3bbb94b7770915c42e5ae89f67fe6be6d84 A recent discussion on the SQLite forum highlights a potential enhancement to SQLite's query optimizer regarding OUTER JOIN clauses combined with COUNT(*) . Currently, SQLite can sometimes omit an OUTER JOIN if it determines that the LEFT JOIN semantics are not required for the query result, for instance, when only columns from the left table are selected. The proposed extension seeks to apply this optimization even when COUNT(*) is used, which can be more complex due to the way COUNT(*) inherently handles NULLs from unmatched rows. This optimization is crucial for improving the performance of analytical queries that often involve counting records across joined tables. By intelligently removing unnecessary OUTER JOIN operations, SQLite can reduce the amount of data processed and improve query execution times. Developers often encounter scenarios where they use LEFT JOIN out of caution, but if the optimizer can determine it's effectively an INNER JOIN for the given projection, significant speedups are possible. This discussion delves into the intricacies of the query planner's logic, revealing how subtle changes can lead to substantial performance gains in real-world applications. Understanding these internal mechanisms allows developers to write more efficient SQL and anticipate SQLite's behavior. Comment: This directly impacts how efficiently SQLite executes analytical queries, making it vital for anyon

🚀 Overview — Why Semantic Search Matters Semantic search surpasses simple keyword matching because embeddings place texts in a high‑dimensional vector space where cosine similarity directly reflects intent. A dedicated vector store is therefore required to persist those embeddings and serve nearest‑neighbor queries efficiently. This post demonstrates a pinecone fastapi semantic search tutorial that wires a FastAPI service to Pinecone, showing the full data flow from embedding generation to similarity lookup. 📑 Table of Contents 🚀 Overview — Why Semantic Search Matters 🛠 Environment Setup — How to Install Dependencies 🐍 Python Virtual Environment 📦 Required Packages 📦 Building the FastAPI Service — How to Create the API 🧩 Data Model with Pydantic 🔗 Core FastAPI Application 🔎 Integrating Pinecone — How to Store and Query Vectors 🗂 Index Creation and Configuration 📤 Upserting Documents 🔎 Performing a Semantic Search 📊 Performance & Scaling — How Indexes Influence Latency 🟩 Final Thoughts ❓ Frequently Asked Questions How do I secure the Pinecone API key in production? Can I use a different embedding model? What happens if I need to change the index dimension? 📚 References & Further Reading 🛠 Environment Setup — How to Install Dependencies Creating a reproducible environment guarantees that the tutorial runs identically on any machine. 🐍 Python Virtual Environment $ python3 -m venv venv $ source venv/bin/activate (venv) $ python -V Python 3.11.5 Activating the virtual environment isolates package installations from the global interpreter. 📦 Required Packages $ pip install fastapi[all] uvicorn pinecone-client sentence-transformers Collecting fastapi[all] Downloading fastapi-0.109.0-py3-none-any.whl (48 kB) Collecting uvicorn Downloading uvicorn-0.24.0-py3-none-any.whl (66 kB) Collecting pinecone-client Downloading pinecone_client-2.2.2-py3-none-any.whl (81 kB) Collecting sentence-transformers Downloading sentence_transformers-2.2.2-py3-none-any.whl (1.1 MB) ... Successful

CAP Theorem Explained: Choosing Between Consistency, Availability, and Partition Tolerance in Databases Imagine you're trying to book a flight online, and just as you're about to pay, the website crashes. When you try to book again, you find that the flight is now sold out, even though the website initially showed available seats. This frustrating experience is a classic example of a database trade-off between consistency, availability, and partition tolerance. The CAP theorem, first introduced by Eric Brewer in 2000, states that it's impossible for a distributed data store to simultaneously guarantee more than two out of these three principles. In this post, we'll delve into the world of CAP theorem, exploring its fundamentals, real-world database examples, and design implications. Introduction to CAP Theorem Understanding the Basics of CAP Theorem The CAP theorem is based on three primary principles: Consistency : Every read operation will see the most recent write or an error. Availability : Every request receives a response, without guarantee that it contains the most recent version of the information. Partition Tolerance : The system continues to function and make progress even when network partitions (i.e., splits or failures) occur. Importance of CAP Theorem in Distributed Systems In distributed systems, where data is spread across multiple nodes, the CAP theorem plays a crucial role in understanding the trade-offs between these principles. By grasping the CAP theorem, developers can design more resilient and scalable databases that meet the specific needs of their applications. Brief Overview of the Blog Post This post will explore the CAP theorem in depth, using real-world database examples to illustrate the trade-offs between consistency, availability, and partition tolerance. We'll discuss the fundamentals of CAP theorem, examine CA, CP, and AP systems, and provide guidance on designing for each combination. By the end of this post, you'll have a solid un

Hello! I'm Satoshi Kaneyasu, DevOps engineer at Serverworks. In this article, I'll introduce the basic concepts and terminology of vector databases for those who are just starting to learn about them. Target Audience This article is aimed at beginners to vector databases. You may have heard that vector databases are related to LLMs and RAG, but aren't quite sure what they actually are. Think of this as written with that kind of reader in mind. What Is a Vector Database? A vector database is a database that stores data as vectors (arrays of numbers) and searches for data using "distance" or "similarity" between vectors. Traditional relational databases search for data using "exact match" or "partial match" (LIKE queries), but vector databases can search for things that are semantically similar . For example, searching for "weather in Tokyo" might return results like "temperature in Tokyo" or "weather conditions in Kanto" — data that differs as a string but is semantically related. Visualizing Vector Space In a vector database, all data is represented as points in a multidimensional space. When searching, the query is also converted into a vector, and data that is "close in distance" within that space is retrieved. This diagram represents it in two dimensions, but in a real vector database, proximity and distance are defined across many dimensions. Use Cases for Vector Databases Vector databases are used across a wide range of applications: Use Case Description RAG (Retrieval-Augmented Generation) Knowledge base search to provide external knowledge to LLMs. Allows internal documents and up-to-date information to be reflected in LLM responses Semantic Search Searching internal documents or FAQs by meaning rather than keywords. Handles spelling variations and synonyms Recommendation Recommending products and content whose vectors are close to a user's preference vector. Used as an alternative or complement to collaborative filtering Image Search Searching for similar im

The LedgerSync pipeline was inserting 1.5 million rows into PostgreSQL using pandas.to_sql() . It took four minutes per run. I switched to psycopg2's COPY command and it dropped to 18 seconds. Same data, same schema, same machine. That is not an optimization tip. It is the difference between a pipeline that fits in an Airflow schedule and one that does not. This article is about patterns like that: the ones that matter when you are building pipelines that run on a schedule, not when you are writing ad-hoc queries. Loading Data: to_sql vs execute_values vs COPY There are three ways to write rows from Python into PostgreSQL, and the performance gap between them is significant. pandas to_sql issues one INSERT statement per row by default, or a multi-row INSERT with method="multi" . It is the easiest to write and the slowest for any serious volume. psycopg2 execute_values batches many rows into a single multi-row INSERT VALUES statement. About 5x faster than to_sql for medium-sized loads. psycopg2 COPY streams rows directly to PostgreSQL using its native bulk-load protocol. No statement parsing, no row-by-row overhead. For LedgerSync at 1.5M rows, this was the one that mattered. import psycopg2 import io import pandas as pd conn = psycopg2 . connect ( " host=localhost dbname=proj_db user=proj_user password=proj_pass " ) def bulk_copy ( df : pd . DataFrame , table : str , columns : list [ str ]): buf = io . StringIO () df [ columns ]. to_csv ( buf , index = False , header = False ) buf . seek ( 0 ) with conn . cursor () as cur : cur . copy_from ( buf , table , sep = " , " , columns = columns ) conn . commit () print ( f " Loaded { len ( df ) } rows into { table } " ) Use COPY for initial loads and large backfills. For incremental daily writes of a few thousand rows, execute_values is fine and gives you more control over conflict handling: from psycopg2.extras import execute_values def bulk_insert ( rows : list [ dict ], table : str ): if not rows : return columns = list

Your API is fast. Your code is clean. Your architecture looks solid on paper. Then you hit 500,000 records and everything slows down. Queries that ran in 12ms now take 4 seconds. Your dashboards lag. Users start filing support tickets. Your on-call engineer is staring at a query plan at midnight wondering what went wrong. Nine times out of ten, the answer is indexing. Not missing indexes — wrong indexes. Indexes that exist but don't help. Indexes that actively hurt write performance without meaningfully improving reads. This is a breakdown of the most damaging database indexing mistakes in production SaaS systems — and how to fix them before they become incidents. Mistake 1: Indexing Everything "Just in Case" The most common mistake isn't under-indexing. It's over-indexing out of anxiety. New engineers especially fall into this pattern — add an index on every column that appears in a WHERE clause, just to be safe. Seems responsible. It isn't. Every index you add is a write tax. On every INSERT, UPDATE, and DELETE, PostgreSQL (or MySQL) has to update every index on that table. On a table with 8 indexes, every write touches 8 data structures. At low volume, this is invisible. At 10,000 writes per minute, it becomes your bottleneck. The fix: Audit your indexes regularly. In PostgreSQL: SELECT schemaname , tablename , indexname , idx_scan , idx_tup_read , idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC ; Any index with idx_scan = 0 or near zero hasn't been used since your last stats reset. That's a candidate for removal — not immediately, but after investigation. Mistake 2: Not Understanding Index Selectivity An index on a boolean column ( is_active , is_deleted ) is almost always useless. Here's why: selectivity measures how many distinct values exist relative to total rows. A boolean column has two values. If 95% of your rows have is_active = true , an index on that column tells the query planner almost nothing useful. It will often skip the index entire

AI applications are generating more data than ever before. From model telemetry and user interactions to observability events and real-time analytics, modern systems need infrastructure that can ingest, process, and query massive datasets with low latency. That's exactly the problem ClickHouse is targeting with its latest release. The update introduces improvements across query performance, memory management, Kafka integration, lakehouse support, and developer tooling. While many of these changes appear incremental on the surface, together they highlight a much larger shift happening across the industry. One of the most notable additions is improved memory management for large joins. ClickHouse can now automatically spill hash joins to disk when memory usage exceeds configured thresholds. Instead of failing due to memory pressure, queries can continue running using more efficient execution strategies. For teams working with large feature tables, event enrichment, AI telemetry, or observability data, this can significantly improve reliability. The release also expands ClickHouse's Kafka capabilities with Schema Registry integration, AvroConfluent write support, metadata mapping, and zone-aware communication. These improvements make it easier to integrate ClickHouse into real-time event pipelines while reducing latency and unnecessary cross-zone traffic in cloud environments. Another major focus is support for modern lakehouse architectures. Improvements for Apache Iceberg and Apache Paimon strengthen ClickHouse's ability to query data stored in open table formats while maintaining high analytical performance. As more organizations separate storage and compute, ClickHouse is increasingly positioning itself as a high-speed query layer on top of cloud-native data lakes. Performance optimization remains a major theme throughout the release. Improvements include faster JOIN execution, better ORDER BY LIMIT performance, enhanced JSON processing, smarter index pruning, redu

In this article, author Aaditya Chauhan discusses the limitations of RAG pipelines based purely on vector search and how an internal omni-search application using Reciprocal Rank Fusion (RRF) that combines BM25 and vector results, can enhance the search solution. By Aaditya Chauhan

PostgreSQL 19 Graph Queries & REPACK; SQLite Advanced SQL Patterns Today's Highlights PostgreSQL 19 introduces major features with SQL/PGQ for graph queries on relational data and a new REPACK command to combat table bloat. Meanwhile, the SQLite community explores advanced SQL patterns for complex 'first match' selection logic, pushing the boundaries of efficient embedded database querying. SQL/PGQ in PostgreSQL 19: Graph Queries Without the Graph Database (Planet PostgreSQL) Source: https://postgr.es/p/9kW PostgreSQL 19 is set to integrate native graph querying capabilities through the introduction of SQL/PGQ (Property Graph Queries) and the GRAPH_TABLE construct. This innovative feature allows users to perform sophisticated graph-like analytics, such as pathfinding and pattern matching, directly on their existing relational datasets without requiring data migration to a specialized graph database. The syntax employed for defining graph patterns closely resembles that of Cypher, making it intuitive for developers familiar with graph query languages. Developers will be able to define 'nodes' and 'edges' from their relational tables and then utilize the GRAPH_TABLE function within standard SQL queries to traverse and extract complex, interconnected information. This effectively transforms PostgreSQL into a multi-model database capable of handling both traditional relational workloads and demanding graph analytics side-by-side, within a single consistent environment. This significant enhancement expands PostgreSQL's utility across a wide range of applications, including social network analysis, fraud detection, and supply chain optimization, where understanding relationships between data points is critical. By embedding graph capabilities, PostgreSQL removes the need for separate graph database infrastructure, simplifying application architectures and leveraging the robustness and familiarity of the PostgreSQL ecosystem for new and existing projects. Comment: This is

During maintenance tasks like backup or inspection, you often need to switch a gbase database cluster to READONLY mode and back to NORMAL afterwards. This post shows how to perform the switch online with gcadmin — no downtime required. Tools and Prerequisites Tool : gcadmin , located on any Coordinator node. User : Must be the cluster installation user (default gbase ). Pre‑check : Ensure gcware services are running ( gcware_services status ). Scope : The mode change applies cluster‑wide; no need to repeat per node. Step‑by‑Step (READONLY → NORMAL) Step 1: Check the Current Mode # Cluster‑wide gcadmin showcluster # Specific VC in a multi‑VC environment gcadmin showcluster vc vc1 Look at the VIRTUAL CLUSTER MODE field. Proceed only if it shows READONLY . Step 2: Switch to Normal Read/Write Mode # Single VC or whole cluster gcadmin switchmode normal # Specific VC gcadmin switchmode normal vc vc1 The switch takes effect in seconds. The cluster synchronises the new mode to all nodes automatically — no process restart is needed. Step 3: Verify the Change Run gcadmin showcluster again. When VIRTUAL CLUSTER MODE shows NORMAL , the cluster is fully writable again. Production Notes Business impact : An online switch does not interrupt running read queries. Pending write operations queued during READONLY mode are executed automatically after the switch. Node to run on : Any single Coordinator node is enough. Privileges : Only the gbase user can switch modes. Reverse switch : To return to read‑only mode, use gcadmin switchmode readonly [vc vc_name] . Troubleshooting : If the command fails, check gcware service health and node status first, then retry. Companion Commands # Check all cluster processes gcluster_services all info gcware_services all info # View detailed VC information gcadmin showvc The mode‑switch mechanism in GBase 8a is built for high availability. Following the "verify → switch → re‑verify" workflow lets you change cluster modes safely and transparently in a g