标签：#SQL

DuckDB 1.5.3 & Quack Protocol Release; PostgreSQL File Descriptor Tuning Today's Highlights This week's database news highlights significant advancements for DuckDB, including a feature-packed 1.5.3 release and the innovative Quack client-server protocol. We also delve into a critical PostgreSQL performance tuning guide on managing file descriptors. DuckDB 1.5.3: Not an Ordinary Patch Release (DuckDB Blog) Source: https://duckdb.org/2026/05/20/announcing-duckdb-153.html DuckDB has announced the release of version v1.5.3, a "patch release" that, despite its designation, delivers a substantial upgrade to the ecosystem. While the core DuckDB engine sees limited bugfixes, the true power of this release lies in the significantly upgraded extensions that ship alongside it. These extensions introduce a wealth of new features that enhance DuckDB's capabilities across various data processing tasks, making it much more than a routine update. Key among the new features is the integration of the Quack client-server protocol, which is highlighted as a major advancement. This allows DuckDB instances to communicate and operate in more distributed, concurrent environments, expanding its utility beyond purely embedded scenarios. Developers are encouraged to explore the updated extensions for improved functionality, ranging from new data formats to enhanced analytical operations. This release underscores DuckDB's commitment to continuous innovation through its modular extension system, providing users with powerful new tools without requiring major core engine overhauls for every new feature. Comment: This release is a great example of how DuckDB's extension model brings rapid innovation. Developers should check the extension changelogs, as that's where the real new features are. Quack: The DuckDB Client-Server Protocol (DuckDB Blog) Source: https://duckdb.org/2026/05/12/quack-remote-protocol.html The DuckDB team has introduced Quack, a new client-server protocol designed to enable s

Every headless WordPress conversation starts the same way — someone draws an architecture diagram with arrows pointing from a REST API to a shiny Next.js frontend, and it looks clean. Too clean. This is a post about what happens when you close the whiteboard and open the actual codebase. The Stack Decision: GraphQL vs. REST vs. Direct MySQL This is usually the first fork in the road. For this build, the client already had a well-indexed WooCommerce site. The product catalog, slugs, and taxonomy structure were already doing heavy SEO work. So the constraint was simple: nothing about the data layer changes, only how we consume it. WPGraphQL was a real option — but it meant adding a plugin dependency to a WordPress install we were actively trying to slim down. The WP REST API was already there, no installation required, and exposed exactly what we needed: products, categories, pages, and media — all queryable by slug. The decision: WP REST API, consumed server-side via Next.js fetch in Server Components. // Fetching a product by slug — preserving the existing URL structure const res = await fetch ( ` ${ process . env . WP_API_BASE } /wp/v2/product?slug= ${ params . slug } &_embed` , { next : { revalidate : 3600 } } ); const [ product ] = await res . json (); No new dependencies on the WordPress side. The legacy install runs as a lean shell — no active theme, minimal plugins, just the REST API and the data. The Site Kit Problem: Bridging Familiar Workflows This is where most migrations quietly fail the client. The previous team lived inside WordPress admin. Google Site Kit gave them traffic stats, Search Console data, and Analytics — all surfaced in a UI they knew. Ripping that away and telling them "just use Google Analytics directly" is a workflow regression, not an upgrade. The pivot here was building a lightweight admin dashboard as part of the Next.js project — not a full replacement for Site Kit, but a mirror of the metrics they actually checked daily: Page views

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

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

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

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

T-SQL on Microsoft Fabric - Episode 1: Mastering Data Retrieval with SELECT, WHERE, and ORDER BY Learning Goals In this lesson, you will learn how to: Read data from tables using SELECT Filter rows with WHERE Sort query results with ORDER BY Get familiar with standard T-SQL syntax Practice directly in Microsoft Fabric Warehouse 1. Understanding Database and Schema In Fabric Warehouse, objects are commonly organized like this: Warehouse | |-- sales | |-- Customers | |-- Orders | |-- hr | |-- Employees | |-- finance |-- Transactions Schemas help you: Group related tables Manage permissions Organize large systems more effectively 2. Create a Schema Create a schema for the sales dataset: CREATE SCHEMA sales ; Check existing schemas: SELECT * FROM sys . schemas ; 3. Create Tables Create the Customers table: CREATE TABLE sales . Customers ( CustomerID INT , CustomerName VARCHAR ( 100 ), City VARCHAR ( 50 ), Country VARCHAR ( 50 ) ); Create the Orders table: CREATE TABLE sales . Orders ( OrderID INT , CustomerID INT , OrderDate DATE , Amount DECIMAL ( 10 , 2 ) ); 4. Insert Sample Data Customers INSERT INTO sales . Customers VALUES ( 1 , 'John Smith' , 'New York' , 'USA' ), ( 2 , 'Emma Brown' , 'Chicago' , 'USA' ), ( 3 , 'David Wilson' , 'London' , 'UK' ), ( 4 , 'Sophia Taylor' , 'Manchester' , 'UK' ), ( 5 , 'Michael Lee' , 'Singapore' , 'Singapore' ); Orders INSERT INTO sales . Orders VALUES ( 101 , 1 , '2026-01-10' , 1200 . 00 ), ( 102 , 1 , '2026-01-15' , 800 . 00 ), ( 103 , 2 , '2026-01-20' , 2500 . 00 ), ( 104 , 3 , '2026-02-01' , 500 . 00 ), ( 105 , 5 , '2026-02-05' , 3200 . 00 ); 5. SELECT Get all columns: SELECT * FROM sales . Customers ; Get specific columns: SELECT CustomerName , Country FROM sales . Customers ; 6. Alias Rename columns in the output: SELECT CustomerName AS Customer , Country AS Nation FROM sales . Customers ; 7. WHERE Filter rows using conditions. Customers in the USA: SELECT * FROM sales . Customers WHERE Country = 'USA' ; Orders greater than 100

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

Switching focus from Frontend development to Data Engineering means shifting from building user interfaces to architecting robust data pipelines. It’s a completely different mindset, and the learning curve is exciting! As I dive deeper into the world of Data, these are the 4 essential tools and concepts that have become the absolute backbone of my daily learning roadmap: 1️⃣ Python (The Swiss Army Knife): Coming from JavaScript/TypeScript, picking up Python has been a breath of fresh air. From writing custom ETL scripts to data manipulation with Pandas, it's the ultimate language for data manipulation. 2️⃣ Advanced SQL (The Core): It's not just about simple SELECT statements anymore. Mastering Window Functions, CTEs (Common Table Expressions), and query optimization is where the real magic happens when interacting with Data Warehouses. 3️⃣ ETL/ELT Pipelines: Understanding how to efficiently Extract, Transform, and Load data without breaking downstream analytics. Moving from UI state management to Data state management is a game-changer. 4️⃣ Cloud Ecosystems & Modern Stack: Exploring how data flows through modern cloud environments and learning how big data tools manage scale. The transition requires patience, but applying my previous engineering background to these new tools makes the journey incredibly rewarding. 💡 To the Data Engineers in my network: What is the one tool or concept you believe is a "must-have" for someone transitioning into the field today? Drop your advice below!

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

0A000 feature not supported 는? PostgreSQL 에러 코드 0A000 은 현재 사용하려는 기능이 PostgreSQL에서 지원되지 않거나, 특정 컨텍스트에서는 사용할 수 없음을 의미합니다. 주로 트랜잭션 내부에서 허용되지 않는 명령을 실행하거나, 해당 버전의 PostgreSQL에서 아직 구현되지 않은 SQL 표준 문법을 사용할 때, 또는 복제(Replication) 환경의 제약으로 인해 발생합니다. 실무에서는 특히 CREATE DATABASE , VACUUM , CLUSTER 같은 명령을 트랜잭션 블록 안에서 실행하거나, Logical Replication 슬롯과 관련된 작업을 수행할 때 자주 마주치는 에러입니다. 주요 발생 원인 1. 트랜잭션 블록 내에서 허용되지 않는 DDL 명령 실행 PostgreSQL은 일부 DDL 명령을 트랜잭션 블록( BEGIN ... COMMIT ) 내에서 실행하는 것을 허용하지 않습니다. CREATE DATABASE , DROP DATABASE , CREATE TABLESPACE , DROP TABLESPACE , VACUUM , CLUSTER 등의 명령은 트랜잭션 컨텍스트 밖에서 단독으로 실행되어야 하며, 이를 무시하고 트랜잭션 내부에서 호출하면 0A000 에러가 발생합니다. 2. 특정 PostgreSQL 버전에서 지원하지 않는 문법 또는 기능 사용 SQL 표준에는 정의되어 있지만 PostgreSQL의 해당 버전에서 아직 구현되지 않은 기능을 사용할 때 이 에러가 발생합니다. 예를 들어, 구버전 PostgreSQL에서 LATERAL JOIN , MERGE 문, GENERATED ALWAYS AS (expression) STORED 컬럼 정의 등을 사용하거나, 특정 윈도우 함수 옵션 조합을 사용하는 경우 해당 에러를 만날 수 있습니다. 3. 논리 복제(Logical Replication) 또는 스트리밍 복제 환경에서의 제약 위반 Standby 서버나 Logical Replication 구독자(Subscriber) 측에서 쓰기 작업이나 특정 관리 명령을 실행하려 할 때 0A000 에러가 발생합니다. Hot Standby 상태의 서버에서 DDL을 실행하거나, Logical Replication 슬롯이 활성화된 상태에서 지원되지 않는 방식으로 복제 슬롯을 조작하려는 경우 이 에러를 마주치게 됩니다. 해결 방법 원인 1: 트랜잭션 블록 내 허용되지 않는 DDL 실행 트랜잭션 블록을 제거하고 해당 명령을 단독으로 실행하는 것이 핵심입니다. 아래는 잘못된 예제와 올바른 예제를 비교한 것입니다. ❌ 잘못된 예 (0A000 에러 발생) BEGIN ; CREATE DATABASE myapp_db WITH OWNER = myapp_user ENCODING = 'UTF8' LC_COLLATE = 'ko_KR.UTF-8' LC_CTYPE = 'ko_KR.UTF-8' ; COMMIT ; -- ERROR: 0A000: CREATE DATABASE cannot run inside a transaction block ✅ 올바른 예 (트랜잭션 블록 밖에서 실행) -- 트랜잭션 블록 없이 단독 실행 CREATE DATABASE myapp_db WITH OWNER = myapp_user ENCODING = 'UTF8' LC_COLLATE = 'ko_KR.UTF-8' LC_CTYPE = 'ko_KR.UTF-8' ; VACUUM 명령도 동일한 원칙이 적용됩니다. -- ❌ 잘못된 예 BEGIN ; VACUUM ANALYZE public . orders ; COMMIT ; -- ✅ 올바른 예: 트랜잭션 밖에서 단독 실행 VACUUM ANALYZE public . orders ; -- ✅ 특정 테이블만 선택적으로 VACUUM VACUUM ( VERBOSE , ANALYZE ) public . orders ; 애플리케이션 코드(예: Python psycopg2)에서 자동 커밋을 끈 상태로 VACUUM을 호출하는 경우도 흔한 실수입니다. import psycopg2 conn = psycopg2 . conn

DuckDB has recently announced Quack, a new remote protocol over HTTP that lets multiple DuckDB instances connect to and work with the same database over a network. The protocol introduces client-server capabilities to a database that was previously mostly local and embedded. By Renato Losio

SQL-like Queries in FSRS Plugin for Obsidian Spaced repetition in Obsidian usually works as "show all cards with due earlier than today." That's enough for simple cases, but once you have hundreds of notes, you want to filter, sort, and select. My FSRS plugin now has a query language resembling SQL. It turns a markdown block into a live table that updates with every review. ``` fsrs-table SELECT file as "Note", r as "Retrievability", date_format(due, '%d.%m.%Y') as "Due" WHERE r < 0.7 ORDER BY r ASC LIMIT 20 ``` → the table shows the 20 most "forgotten" cards, sorted by retrieval probability. From Simple Settings to an Embedded DB Initially I planned to offer table settings using standard SQL syntax. But pretty quickly the syntax became a real query language, and the implementation itself — an embedded lightweight DB. High-level test coverage in TypeScript made it easy to iterate on functionality located in the WASM module via an AI agent. When faced with dual-language testing (TypeScript + Rust), the artificial intelligence prefers to do the job properly rather than fake it. After implementing the lexer → parser → AST → evaluator pipeline for numeric values, I extended it to strings, added filtering via WHERE, then functions. Extending the syntax or adding a function came down to a single request to the agent — and a feasibility check. What's Inside fsrs-table Supported Features SELECT — choose fields, rename via AS . WHERE — conditions with = , != , < , > , <= , >= , AND , OR . ORDER BY — sort ascending ( ASC ) or descending ( DESC ). LIMIT — cap the number of rows. date_format() — convert the due date to any text format. Available fields: Field (alias) Type Description file string path to the note due date next review date stability (s) number stability in days difficulty (d) number difficulty retrievability (r) number probability of recall (0…1) reps number total number of reviews state string New, Learning, Review, or Relearning elapsed number days since last r

The engineering team at Meta recently outlined how the company migrated a data ingestion platform that transfers several petabytes of MySQL social graph data daily to improve reliability and operational efficiency. The team used techniques like reverse shadowing and continuous checksum monitoring to ensure zero downtime during the transition. By Renato Losio

Thinking in terms of existence instead of lists SQL Pattern Series #1 of 21 A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems. What You'll Learn In this article you'll learn: When EXISTS and IN solve the same problem The difference between set membership and existence Why the underlying mental model matters When I typically reach for EXISTS Most SQL developers write a query like this at some point: SELECT c . CustomerID , c . CustomerName FROM Customers c WHERE c . CustomerID IN ( SELECT o . CustomerID FROM Orders o ); And it works. But sometimes it isn't the best way to think about the problem. The Question Behind the Query Many SQL problems can be framed in two different ways. Set Membership Is this value in a set? WHERE CustomerID IN (...) Existence Does at least one matching row exist? WHERE EXISTS (...) Both approaches often return the same result. But they represent different mental models. The Presence Pattern The Presence Pattern is useful when you do not actually care about the values being returned from a related table. You only care whether a matching row exists. For example: Customers who have placed an order Users who have logged in Employees assigned to a project Products that have sales In these cases, the question is often: Does a related row exist? rather than: What values are contained in this list? Example Using EXISTS SELECT c . CustomerID , c . CustomerName FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o . CustomerID = c . CustomerID ); The subquery is correlated to the outer query. Conceptually, SQL asks: For this customer, does at least one matching order exist? As soon as the answer becomes true, the condition is satisfied. Why This Pattern Matters Many SQL developers initially learn syntax. Over time, they discover that query writing is really about choosing the right mental model. The Presence Pattern encourages you to think in terms of: existence relationshi

DuckDB 1.5.3 Iceberg updates, PostgreSQL TDE extension & AI index tuning Today's Highlights Today's highlights include DuckDB's enhanced Iceberg integration with new DML and schema evolution features, alongside a deep dive into PostgreSQL's new open-source Transparent Data Encryption. Additionally, we explore AI-driven strategies for automating PostgreSQL index tuning, offering practical performance improvements. New DuckDB-Iceberg Features in v1.5.3 (DuckDB Blog) Source: https://duckdb.org/2026/05/29/new-iceberg-features.html The latest DuckDB v1.5.3 release introduces significant enhancements for working with Apache Iceberg tables, a critical component in modern data lake architectures. Key additions include full MERGE INTO support, allowing users to efficiently update, insert, and delete rows in Iceberg tables based on a source query. This release also brings ALTER TABLE commands for schema evolution, enabling operations like adding, renaming, or dropping columns, crucial for adapting to changing data requirements. Furthermore, DuckDB now supports partition transforms within Iceberg, providing more control over data organization and query optimization. Compatibility has been extended to Iceberg V3, ensuring support for the latest table format specifications, and improved handling for Iceberg REST Catalogs streamlines metadata management. These features position DuckDB as an even more powerful embedded analytical database for processing large-scale, evolving datasets directly in a data lake environment, making complex ETL/ELT operations more accessible and performant. Comment: The MERGE INTO and ALTER TABLE additions are game-changers for using DuckDB in production data pipelines with Iceberg, enabling robust upserts and schema changes. Open-Source TDE for PostgreSQL: What pg_tde Is, and Whether You Need It (Planet PostgreSQL) Source: https://postgr.es/p/9kM This article introduces pg_tde , PostgreSQL's new open-source Transparent Data Encryption (TDE) option, a l

In modern application development, exposing database logic as REST APIs is a powerful way to integrate systems. Oracle REST Data Services (ORDS) makes it easy to turn PL/SQL into RESTful APIs without needing a separate backend service. In this blog, we’ll walk through how to create a simple POST API using ORDS and PL/SQL to insert data into a table. Pre-requisites A cloud-based ATP wallet (I prefer) Let's start how we create the APIs on the top of any custom table which relies on databases Create a table in the oracle SQL Developer and followed by create an ORDS Module 1.Create an ORDS Module A module is a logical container for related REST endpoints. What this Module does ?? Creates a module named nj_api Defines base URL: http://server_name/ords/table_Schema/nj_api/ 2: Define a Template (Endpoint Path) A template represents the API endpoint path. It defines how Endpoint URL:/ords/table_schema/nj_api/insert_data 3: Define the Handler (Business Logic) The handler contains the logic executed when the API is called. Key Concepts: p_method => 'POST': Defines HTTP method p_source_type => ORDS.source_type_plsql: Uses PL/SQL block Bind variables (:name, :num, etc.) map directly to JSON request body parameters 4: Testing the API Using Tools like Postman,cURL,ORDS REST Workshop I tested with Postman FYR Let's call same in Oracle VBCS in new blog. .. Try other methods like Delete, PATCH & GET