标签：#Database

Traditional databases just can't keep up with high concurrency and low latency at the same time. The term "real-time" has become kind of meaningless. Everyone claims it, from batch-oriented cloud data warehouses to transactional database extensions. This makes picking the right architecture really hard without expensive trial and error. The best real-time analytics database in 2026 depends entirely on your workload shape. Key takeaways Real-time analytics (in this guide) = sub-second p95/p99 analytical queries on billions of rows, high concurrency , and milliseconds-to-seconds freshness . Best overall in 2026 for most workloads: ClickHouse (ingest throughput, query speed at scale, compression/TCO). Best for strictly predefined query paths via star-tree indexes: Apache Pinot . Best for time-series operational dashboards and observability: ClickHouse . ClickStack is its full observability offering for logs, metrics, and traces. Best for rigid ingestion-time roll-up aggregations: Apache Druid . Best for unified OLTP + real-time analytics: ClickHouse paired with its managed Postgres offering and native sync to ClickHouse , giving you a purpose-built OLTP engine and a purpose-built OLAP engine without rolling your own CDC pipeline. SingleStore is an alternative if you prefer a single HTAP engine for both. Traditional Data Warehouses: Snowflake and BigQuery are fine for batch BI if you already have one, but face latency, concurrency, and cost challenges under sub-second, high-concurrency workloads. Evaluate using 4 axes: ingest/freshness, latency under concurrency, TCO, operational complexity. What 'real-time analytics' means (and why warehouses and OLTP databases fail) Strict engineering thresholds define true real-time OLAP : sub-second query latency on complex aggregations, the ability to serve tens to thousands of concurrent queries per second (QPS), and data freshness measured in milliseconds to seconds. Traditional cloud data warehouses like Snowflake and BigQuery a

If you're building a modern data stack that requires either high-throughput transaction processing or large-scale analytical workloads, you've likely come across both Vertica and VoltDB (now rebranded as Volt Active Data). While both are distributed relational database management systems (RDBMS), they are architected for completely opposite use cases — choosing the wrong one can lead to 10x higher costs, missed latency SLAs, and poor application performance. In this guide, we break down every key difference between OpenText Vertica and Volt Active Data, with practical examples, real-world use cases, and best practices to help you make the right choice for your team. Table of Contents What is OpenText Vertica? What is Volt Active Data (Formerly VoltDB)? Core Differences Between Vertica and VoltDB Real-World Use Cases: When to Pick Which Best Practices & Common Mistakes Conclusion & Key Takeaways References What is OpenText Vertica? OpenText Vertica (formerly Micro Focus Vertica) is a columnar relational DBMS built exclusively for analytical (OLAP) workloads, first launched in 2005. As of 2026, the latest stable version is 26.1, with native lakehouse and Apache Iceberg export support for modern data ecosystems. Core Vertica Architecture Vertica's design is optimized for fast queries across massive datasets: Columnar storage : Data is stored by column instead of row, enabling significantly higher compression ratios and faster aggregation queries that only access a small subset of columns Massively Parallel Processing (MPP) : Query execution and data are distributed across hundreds of nodes for parallel processing Dual deployment modes : Enterprise Mode : Shared-nothing architecture with data stored locally on nodes for maximum performance Eon Mode : Compute and storage separated, using shared object storage (S3, GCS, ADLS) to scale compute independently of storage for cloud workloads Projections : Physical, sorted copies of data optimized for common query patterns (ins

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

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

One thing that confused me when I first started learning ClickHouse was the word FINAL . Because eventually you'll come across both: SELECT * FROM events FINAL ; and: OPTIMIZE TABLE events FINAL ; At first glance, they sound like they should do roughly the same thing. After all, both contain the word FINAL . But they actually solve two completely different problems. One affects query results. The other affects how data is physically stored. Understanding this distinction can save a lot of confusion when working with MergeTree tables. Why This Confusion Happens Most people encounter FINAL while working with engines like: ReplacingMergeTree SummingMergeTree AggregatingMergeTree Sooner or later they notice something like: SELECT * FROM users ; returns duplicate versions of rows. Then they discover: SELECT * FROM users FINAL ; and suddenly the results look correct. Naturally, many people assume: FINAL merges the table. But that's not exactly what is happening. What SELECT FINAL Actually Does When you run: SELECT * FROM users FINAL ; ClickHouse applies merge logic during query execution. Think of it as: "Show me what the table would look like if all relevant merges had already happened." The important part: It only affects the query result. After the query finishes: parts remain unchanged storage remains unchanged nothing is rewritten on disk The merge logic happens temporarily while the query is running. Once the query completes, the table is exactly as it was before. What OPTIMIZE FINAL Actually Does Now let's look at: OPTIMIZE TABLE users FINAL ; This is a completely different operation. Instead of modifying query results, ClickHouse physically merges parts on disk. The operation: rewrites data merges eligible parts removes obsolete versions creates larger merged parts Unlike SELECT FINAL , the effects remain after the command completes. This is a storage operation, not a query operation. The Simplest Way to Remember It Whenever I think about these commands, I use a v

TL;DR: This is not a cryptographic construction. It is a pragmatic engineering compromise for applications where encrypted storage is required but approximate alphabetical ordering is still useful. I sort encrypted strings using an external index: the sum of weighted Unicode code points for the first N characters with exponential positional weights, followed by quantization. Monotonicity is preserved, but accuracy predictably degrades after the first few characters. Not a cryptographic scheme; some ordering information leaks by design. The problem Some time ago, while implementing a project, I ran into the problem of sorting encrypted data in a database. I’d like to share the solution. I won’t go into detail describing the entire application. I’ll just say that, according to the required architecture, almost all data in the database must be stored exclusively in encrypted form: usernames, file names, tags, comments, dates, and so on (with the exception of identifiers and some system fields). That is, the table structure should be open, but the contents should not be. The encryption is symmetric: the same key is used for both encryption and decryption. This means that without the encryption key, even with a full database dump an attacker should not obtain any original data. And this is where two problems immediately arose: searching by the data without fully decrypting it, and sorting encrypted data. The first problem, with some caveats, is solved fairly simply. To search encrypted data, it is enough to additionally store hashes of the original values you plan to search on. This allows exact-match lookups (for example, users by login or files by tag) without storing the original values in plaintext. Yes, this won’t allow pattern searches, but it’s quite acceptable for the project’s goals. But sorting encrypted data turned out to be significantly more difficult. The solution A quick search showed that the problem is far from new, but there are no standard approaches t

DuckDB Data Inlining, SQLite Fossildelta OOB, Postgres 19 Temporal Data Today's Highlights Today's highlights include DuckDB's innovative data inlining for stream processing in data lakes, offering significant performance gains by eliminating the small files problem. Additionally, a critical out-of-bounds read vulnerability in SQLite's fossildelta extension and a peek into PostgreSQL 19's focus on temporal data capabilities are discussed. Data Inlining in DuckLake: Unlocking Streaming for Data Lakes (DuckDB Blog) Source: https://duckdb.org/2026/04/02/data-inlining-in-ducklake.html The DuckDB team has unveiled DuckLake’s new data inlining feature, designed to revolutionize how streaming data is managed in data lakes by effectively tackling the notorious “small files problem.” This issue, common in scenarios with frequent small updates or continuous ingestion, often leads to performance bottlenecks due to the overhead of managing numerous tiny files. DuckLake's solution involves intelligently storing these small updates directly within the catalog, thereby eliminating the need for physical small files on disk. This architectural innovation significantly improves the practicality of continuous streaming into data lakes, enabling more efficient real-time analytics. By inlining data, DuckDB reduces I/O operations and metadata management complexity, leading to substantial performance gains. A benchmark highlighted in the announcement demonstrates an impressive 926x speed improvement for certain operations, showcasing the feature's potential to transform data lake architectures for workloads requiring high-throughput ingestion and immediate query access without the traditional performance penalties. Comment: This DuckDB feature is a game-changer for data lake architectures, offering a simple yet powerful way to handle streaming data without the performance overhead of countless small files. Post: Out-of-bounds read in deltaGetInt() when input contains no in-buffer terminat

GBase 8a fully supports OLAP window functions, making it a powerful gbase database for analytical workloads. This guide uses real sales scenarios to demonstrate ROW_NUMBER / RANK , moving aggregates, LAG / LEAD for period‑over‑period comparisons, ROLLUP subtotals, and how these functions execute in an MPP environment. Window Function Syntax function_name () OVER ( [ PARTITION BY column ] -- window group [ ORDER BY column ] -- ordering within window [ ROWS | RANGE BETWEEN ... AND ...] -- frame definition ) Unlike GROUP BY , window functions do not collapse rows. Every row remains in the result set while still being able to “see” other rows in the window. Ranking Functions ROW_NUMBER / RANK / DENSE_RANK SELECT dept_id , salesperson , sale_amount , ROW_NUMBER () OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS row_num , RANK () OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS rnk , DENSE_RANK () OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS dense_rnk FROM sales WHERE sale_date >= '2024-01-01' ; Sample output: dept_id salesperson sale_amount row_num rnk dense_rnk 101 Zhang San 95000 1 1 1 101 Li Si 95000 2 1 1 101 Wang Wu 82000 3 3 2 101 Zhao Liu 71000 4 4 3 Top 3 Sales per Department WITH ranked AS ( SELECT dept_id , salesperson , sale_amount , ROW_NUMBER () OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS rn FROM sales WHERE YEAR ( sale_date ) = 2024 ) SELECT dept_id , salesperson , sale_amount FROM ranked WHERE rn <= 3 ORDER BY dept_id , rn ; NTILE: Bucketing SELECT dept_id , salesperson , sale_amount , NTILE ( 4 ) OVER ( PARTITION BY dept_id ORDER BY sale_amount DESC ) AS quartile FROM sales WHERE YEAR ( sale_date ) = 2024 ; Cumulative and Moving Aggregates Year‑to‑Date SELECT dept_id , sale_month , monthly_amount , SUM ( monthly_amount ) OVER ( PARTITION BY dept_id ORDER BY sale_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS ytd_amount FROM ( SELECT dept_id , DATE_FORMAT ( sale_date , '%Y-%m' ) AS sale_month ,

I inherited a SQL Server database with 2.3 million rows. Queries took 45 seconds. Users were frustrated. Dashboards timed out. Here is exactly what I did. Step 1: Find the slowest queries I used SQL Server's query store to identify the top 10 worst performing queries. Step 2: Check the execution plan Missing index warnings everywhere. Also saw table scans on a 2 million row table. Step 3: Add targeted indexes Created two non-clustered indexes on the most filtered columns. No over-indexing. Just what the queries actually needed. Step 4: Rewrite the worst join One query was joining 6 tables with a cross apply that made no sense. Restructured to inner joins with proper filter ordering. The result 45 seconds down to 8 seconds. An 82% improvement. Real-time dashboards started working again. Key lesson Check what is actually slow before changing anything. Most people skip this and waste time optimizing the wrong thing. What is your fastest query optimization win?

I inherited a SQL Server database with 2.3 million rows. Queries took 45 seconds. Users were frustrated. Dashboards timed out. Here is exactly what I did. Step 1: Find the slowest queries I used SQL Server's query store to identify the top 10 worst performing queries. Step 2: Check the execution plan Missing index warnings everywhere. Also saw table scans on a 2 million row table. Step 3: Add targeted indexes Created two non-clustered indexes on the most filtered columns. No over-indexing. Just what the queries actually needed. Step 4: Rewrite the worst join One query was joining 6 tables with a cross apply that made no sense. Restructured to inner joins with proper filter ordering. The result 45 seconds down to 8 seconds. An 82% improvement. Real-time dashboards started working again. Key lesson Check what is actually slow before changing anything. Most people skip this and waste time optimizing the wrong thing. What is your fastest query optimization win?

Let's consider a table user . When we execute a query to find Emily , we are actually going through each record , looking whether the name column equals Emily . Indexes comes in when you want to speed this up. Let's create an Index with the name idx_users_name (the name can be anything, and it doesn't matter functionally): CREATE INDEX idx_users_name ON users ( name ); Now when you run SELECT * FROM users WHERE name = 'Emily' ; Postgres will use the index we just created (not by the name, the name is just for us) to execute that query, and the time complexity is reduced from O(n) to O(log n) .

Something is slow. Maybe a page takes forever to load, maybe a migration is hanging, maybe your Supabase dashboard just spins. You suspect a query is stuck somewhere in your database, but you can't see what's happening — Postgres doesn't exactly surface this on its own. Turns out it does. You just need to ask. Seeing what's running Postgres keeps track of every active connection and what it's doing in a system view called pg_stat_activity . You can query it like any table: SELECT pid , state , query , age ( clock_timestamp (), query_start ) AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC ; That gives you every non-idle process — its process ID, current state, the SQL it's running, and how long it's been at it. If something has been running for minutes when it should take milliseconds, you've found your problem. A few things worth knowing about the columns: pid — the process ID, which you'll need if you want to kill it state — usually active (running right now), idle in transaction (sitting inside an open transaction doing nothing), or idle (waiting for work) query — the actual SQL text query_start — when the current query began If you want to include the user and database to narrow things down: SELECT pid , usename , datname , state , query , age ( clock_timestamp (), query_start ) AS duration FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC ; The dangerous one — idle in transaction An active query that's been running for a while is usually just slow. An idle in transaction connection is a different kind of problem — it means someone (or some code) opened a transaction and never committed or rolled it back. The connection is doing nothing, but it's still holding locks, which can block other queries from running. These are the ones that tend to cause cascading slowdowns. If you see one that's been sitting there for longer than expected, it's almost certainly a bug in application code — a missing COMMIT , an unhandled e

Database Migration Strategies for Next.js and Supabase Production Apps You've built your Next.js app with Supabase. It works perfectly in development. Now you need to deploy to production and realize: how do I safely change the database schema without breaking everything? Database migrations are how you version control your schema and deploy changes safely. This guide covers everything from basic migrations to zero-downtime production deployments. Prerequisites Supabase project (local and production) Supabase CLI installed Next.js application Git for version control Understanding Migrations A migration is a SQL file that changes your database schema: -- supabase/migrations/20260314120000_add_posts_table.sql CREATE TABLE posts ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (), title TEXT NOT NULL , content TEXT , user_id UUID REFERENCES auth . users ( id ), created_at TIMESTAMPTZ DEFAULT NOW () ); ALTER TABLE posts ENABLE ROW LEVEL SECURITY ; CREATE POLICY "Users can view own posts" ON posts FOR SELECT USING ( auth . uid () = user_id ); Migrations are: Versioned: Timestamped filenames ensure order Tracked: Supabase knows which migrations have run Repeatable: Same migrations produce same result Reversible: You can write rollback logic Setting Up Migrations Initialize Supabase locally: npx supabase init This creates: supabase/ config.toml seed.sql migrations/ Link to your remote project: npx supabase link --project-ref your-project-ref Creating Your First Migration Create a new migration: npx supabase migration new create_posts_table This creates: supabase / migrations / 20260314120000 _create_posts_table . sql Write your schema changes: -- Create posts table CREATE TABLE posts ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4 (), title TEXT NOT NULL , content TEXT NOT NULL , slug TEXT UNIQUE NOT NULL , user_id UUID REFERENCES auth . users ( id ) ON DELETE CASCADE , published BOOLEAN DEFAULT FALSE , created_at TIMESTAMPTZ DEFAULT NOW (), updated_at TIMESTAMPTZ DEFAULT NOW

Most developers never have to design a network protocol from scratch. You use HTTP, gRPC, WebSockets, or something else that already exists and has been debugged by thousands of people over many years. That is the right call for most situations. I did not take that path when building Vaylix, a key-value database engine. I designed a custom binary protocol called VTP2, and the process taught me things about networking that I would not have picked up any other way. This is not an argument that you should also build a custom protocol. For most things, you should not. This is an honest account of what I ran into. Why not HTTP The first question anyone reasonably asks is: why not just use HTTP? HTTP is everywhere. The tooling is excellent. Every language has a client. Debugging with curl is trivial. If I had used HTTP, I would have had working client libraries in a dozen languages before writing a single line of server code. The problem is that HTTP is stateless by design. Every request is independent. Every request carries headers. Every response carries headers. The model assumes that each round trip is a fresh conversation with no memory of what came before. A database session is the opposite of that. A client connects, authenticates, and then issues many commands over the same connection. The authentication should happen once. The session should carry state. Pipelining requests without waiting for each response to return should be natural, not something you fight the protocol to achieve. HTTP/2 closes some of this gap. But using HTTP/2 correctly for a stateful session model involves working against the grain of what HTTP was designed for. I would have been spending a lot of time on infrastructure that exists to make HTTP behave less like HTTP. The other issue is overhead. HTTP headers are verbose. For small key-value operations, the headers can easily exceed the payload. That felt wrong for something designed to be a tight operational data store. So I went with TCP d

PostgreSQL Error 2200G: Most Specific Type Mismatch PostgreSQL error code 2200G ( most_specific_type_mismatch ) is a SQL-standard data exception that occurs when a value's type does not match the most specific (most derived) type expected in a context involving type hierarchies, XML schema types, or user-defined structured types. It most commonly appears when working with composite types, domain hierarchies, or XML processing functions where type inheritance or derivation is in play. While relatively rare in everyday CRUD operations, it can be a significant pain point in enterprise applications with complex type systems. Top 3 Causes and Fixes 1. Composite or Domain Type Hierarchy Mismatch When a function expects a specific domain or composite type but receives a parent/base type, PostgreSQL raises 2200G. Always cast explicitly to the most specific required type. -- Define types CREATE TYPE base_info AS ( name TEXT , value INTEGER ); CREATE DOMAIN specific_info AS base_info ; -- Function expecting the specific domain type CREATE OR REPLACE FUNCTION handle_info ( data specific_info ) RETURNS TEXT AS $$ BEGIN RETURN ( data ). name || ': ' || ( data ). value ; END ; $$ LANGUAGE plpgsql ; -- WRONG: passing base type causes mismatch -- SELECT handle_info(ROW('test', 42)::base_info); -- CORRECT: explicit cast to the most specific type SELECT handle_info ( ROW ( 'test' , 42 ):: specific_info ); 2. XML Type Processing Mismatch Using XML functions like XMLTABLE or XMLCAST without explicitly matching the expected schema type can trigger this error. Always declare column types explicitly. -- Correct: explicitly typed columns in XMLTABLE SELECT * FROM XMLTABLE ( '//product' PASSING XMLPARSE ( DOCUMENT ' <products> <product> <id>1</id> <price>29.99</price> </product> </products> ' ) COLUMNS product_id INTEGER PATH 'id' , price NUMERIC PATH 'price' ); -- Explicit XMLCAST to resolve type ambiguity SELECT XMLCAST ( XMLQUERY ( '//price/text()' PASSING XMLPARSE ( DOCUMENT '<data><pri

There is a design assumption baked into almost every vector database and AI memory implementation that sounds reasonable until you watch it grow nodes in production: that remembering more is always better. Through testing and refining our AUDN code, that is not exactly correct. After running VEKTOR Slipstream against real development sessions for 99 days, the database held 1,413 stored memories across four namespaces. Looking at the importance score distribution, 83 percent of those memories sat below 0.25 out of 1.0, what the system considers the noise floor. The remaining 17 percent, just 60 memories out of 1,413, sat above 0.75 and dominated every recall result. This is exactly what a curation layer is supposed to produce. Those 1,154 low-scored memories are accurate. They are not deleted. They are retrievable by direct query. What they are not is important enough to compete with the 60 high-signal entries every time the agent needs context. AUDN penalised them gradually over hundreds of writes because similar, more specific, or more frequently reinforced memories covered the same ground better. The system created a hierarchy. Without curation, all 1,413 memories would compete equally for every recall slot — and the agent would consistently surface redundant, lower-value context alongside the things that actually matter. That is what standard vector memory looks like without a curation layer. A slow, invisible degradation that nobody notices until the agent starts confidently giving you answers that are three months out of date. Every memory node in Vektor carries an importance score between 0 & 1. When a memory is first stored, it receives a score based on the content’s estimated significance. That score is not fixed. Every time a new memory arrives that is semantically related but not directly contradictory, the compatible verdict for that existing memory takes a small redundancy penalty. The penalty is intentionally modest: a factor based on how similar the in

SQLite ON CONFLICT DO SELECT Proposal, PostgreSQL 19 Features & SQLite Critical Bug Today's Highlights This week in databases, a proposal seeks to expand SQLite's ON CONFLICT clause to match PostgreSQL 19's DO SELECT for advanced conflict resolution. Concurrently, an early look at PostgreSQL 19 highlights key features improving performance and data management, while a critical out-of-bounds read bug in SQLite's fossildelta.c extension reminds us of the importance of low-level code security. Request: Support "ON CONFLICT DO SELECT" to match Postgres 19 (SQLite Forum) Source: https://sqlite.org/forum/info/81840ccfecf0885ba4418152d6c7f164de00d189b2cf7c682690151b0 This forum post proposes extending SQLite's ON CONFLICT clause to include a DO SELECT action, mirroring a feature anticipated in PostgreSQL 19. Currently, SQLite supports DO NOTHING and DO UPDATE for handling unique constraint violations. The proposed DO SELECT would allow an application to retrieve existing rows that caused the conflict, providing more granular control over conflict resolution beyond simply ignoring or updating the data. This feature would be particularly useful in complex data pipelines or replication scenarios where knowing which existing data caused a conflict is necessary for subsequent application logic, such as logging, merging, or initiating alternative processing paths. The discussion highlights the growing convergence of SQL features across different database systems and the desire for enhanced compatibility and expressiveness in SQLite. Implementing DO SELECT would empower developers to build more robust and intelligent conflict resolution strategies directly within their SQL statements, reducing the need for multi-step application-side logic involving separate SELECT queries after a failed INSERT or UPDATE attempt. Such an addition could streamline data ingestion processes and improve transactional integrity for embedded SQLite applications. Comment: This feature would be a game-ch

Stop writing SQL strings that look like a ransom note. Here's how to write queries that are readable, safe, and maintainable. The Problem With "Good Enough" SQL Formatting Most Python developers start here: user_id = 5 query = " SELECT * FROM users WHERE id = " + str ( user_id ) cursor . execute ( query ) It works. Until it doesn't — and when it breaks, it breaks badly : SQL injection, cryptic errors from mismatched types, and queries that take 45 minutes to debug at 2am. Let's fix that, permanently. 1. Never Concatenate User Input — Use Parameterized Queries This is rule #1 and it's non-negotiable. ❌ The Wrong Way (SQL Injection Waiting to Happen) username = request . args . get ( " username " ) # Could be: ' OR '1'='1 query = f " SELECT * FROM users WHERE username = ' { username } '" cursor . execute ( query ) If username is ' OR '1'='1 , your entire users table just got exposed. ✅ The Right Way: Parameterized Queries username = request . args . get ( " username " ) # psycopg2 (PostgreSQL) cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username ,)) # sqlite3 cursor . execute ( " SELECT * FROM users WHERE username = ? " , ( username ,)) # SQLAlchemy Core from sqlalchemy import text result = conn . execute ( text ( " SELECT * FROM users WHERE username = :name " ), { " name " : username }) The database driver handles escaping. You never touch it. This pattern is immune to SQL injection by design. Gotcha: Note the trailing comma in (username,) . Without it, Python treats the string as an iterable and passes each character as a separate parameter. This is one of the most common beginner bugs. # 💥 Bug: passes ('a', 'l', 'i', 'c', 'e') instead of ('alice',) cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username )) # ✅ Correct: single-element tuple cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username ,)) 2. Multi-Line Queries: Triple Quotes + Consistent Indentation For anything longer than one clause, use tri

Redis is fast. But self-hosting Redis on a serverless stack is a nightmare — cold starts, connection pool exhaustion, and managing a persistent server that your serverless functions keep hammering. Upstash solves this with an HTTP-based Redis API that scales to zero, charges per request, and works natively with Next.js App Router. This guide covers the patterns that actually matter in production: cache-aside with proper TTLs, SWR (stale-while-revalidate), session storage, and pub/sub. Real code, real trade-offs. Read the full article with all code examples at stacknotice.com Why Upstash Over a Traditional Redis Instance Standard Redis uses persistent TCP connections. Serverless functions don't maintain persistent connections — every invocation potentially opens a new one. At scale, you hit ECONNREFUSED or max connection errors that are annoying to debug and expensive to fix. Upstash's @upstash/redis client talks over HTTP/REST. No connection pool, no connection limit headaches. Each request is stateless. This is exactly what Next.js Server Components and Route Handlers need. Other advantages: Pay per request — a cache that never gets hit costs $0 Global replication — low latency from any Vercel edge region Native Edge Runtime support — works in Next.js middleware Free tier — 10,000 commands/day, no credit card needed Setup npm install @upstash/redis // lib/redis.ts import { Redis } from ' @upstash/redis ' export const redis = new Redis ({ url : process . env . UPSTASH_REDIS_REST_URL ! , token : process . env . UPSTASH_REDIS_REST_TOKEN ! , }) Pattern 1: Cache-Aside // lib/cache.ts import { redis } from ' ./redis ' export async function withCache < T > ( key : string , fetcher : () => Promise < T > , options : { ttl ?: number ; prefix ?: string } = {} ): Promise < T > { const { ttl = 300 , prefix = ' cache ' } = options const cacheKey = ` ${ prefix } : ${ key } ` const cached = await redis . get < T > ( cacheKey ) if ( cached !== null ) return cached const data = awai

PostgreSQL Error 2201X: invalid row count in result offset clause PostgreSQL error code 2201X ( invalid_row_count_in_result_offset_clause ) is thrown when the value provided to an OFFSET clause is not a valid non-negative integer. This commonly surfaces in applications that implement pagination using dynamic queries or user-supplied parameters, where the offset value may be negative, NULL, or a non-integer type. Top 3 Causes 1. Negative OFFSET Value The most frequent cause is a miscalculated page offset. When computing (page - 1) * page_size , a bad page number can produce a negative result. -- Triggers error 2201X SELECT * FROM orders ORDER BY created_at DESC OFFSET - 5 LIMIT 20 ; -- ERROR: invalid row count in result offset clause -- Fix: Use GREATEST to clamp the value to 0 SELECT * FROM orders ORDER BY created_at DESC OFFSET GREATEST ( 0 , - 5 ) LIMIT 20 ; 2. NULL Passed as OFFSET When an application fails to initialize or bind the offset parameter, NULL gets passed to the query. This often happens with ORMs or query builders where optional parameters are not explicitly set. -- Triggers error 2201X SELECT * FROM products ORDER BY id OFFSET NULL LIMIT 10 ; -- ERROR: invalid row count in result offset clause -- Fix: Use COALESCE to provide a default value SELECT * FROM products ORDER BY id OFFSET COALESCE ( NULL , 0 ) LIMIT 10 ; -- Parameterized query with safe fallback SELECT * FROM products ORDER BY id OFFSET COALESCE ( $ 1 :: BIGINT , 0 ) LIMIT COALESCE ( $ 2 :: BIGINT , 10 ); 3. Non-Integer Type (Float or String) Passing a float or a string that cannot be cleanly cast to a whole number causes this error. This typically happens when Python float values or unvalidated user input strings are interpolated directly into a query. -- Triggers error 2201X SELECT * FROM employees ORDER BY last_name OFFSET 10 . 7 LIMIT 5 ; -- ERROR: invalid row count in result offset clause -- Fix: Use FLOOR and explicit cast to BIGINT SELECT * FROM employees ORDER BY last_name OFFSET F