标签：#base

Target built a generative AI system to improve marketing campaign forecasting by retrieving and ranking similar historical campaigns. Using embeddings, vector search, and LLM ranking, it replaces rule-based workflows. Evaluation shows 75% top-1 and 100% top-3 coverage. The system reduces manual effort, improves consistency, and uses feedback loops to refine retrieval using campaign outcomes. By Leela Kumili

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

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

This article covers debugging and deploying a Rust backed WASM module with a Firebase hosted web app...

The decision to migrate an on-premises database to the cloud is rarely a simple one. It touches infrastructure, budget, team workflows, and application uptime all at once. Yet for most organizations, moving your on-premises database to the cloud is no longer a question of if but when — and more importantly, how to do it without bringing production systems to their knees. Whether you're running PostgreSQL on bare metal or SQL Server in a company-owned data center, the path to cloud-hosted data has predictable pitfalls and, when navigated carefully, significant payoffs. This guide walks through the full migration process: from pre-migration assessment to post-migration validation, with real configuration examples and the kind of hard-won detail that vendor documentation tends to skip. Why Organizations Make the Move On-premises databases carry hidden costs that compound over time. Hardware refresh cycles, licensing renewals, physical security, power, and cooling — these expenses are easy to underestimate when they're already baked into the operating budget. Cloud databases shift that burden to a consumption model, where you pay for what you use and scale without a procurement cycle. Beyond cost, there's the question of availability. Cloud providers like AWS, Google Cloud, and Azure run managed database services with built-in replication, automated failover, and point-in-time recovery that would take a dedicated DBA weeks to configure manually. A startup can get enterprise-grade durability out of the box. A mid-sized company can stop worrying about what happens when a hard drive fails at 2 a.m. Performance is another draw — though it comes with caveats. Managed cloud databases excel at read-heavy workloads and benefit from proximity to cloud-native application services. Latency-sensitive operations or highly custom storage configurations sometimes perform better on dedicated hardware. Knowing which category your workload falls into is the first real question to answer.

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

title: sqlex — A Modern Drop-in Replacement for jmoiron/sqlx published: false description: sqlex is a fully API-compatible modernization of jmoiron/sqlx that fixes 20+ long-standing bugs, adds pluggable hooks, auto IN expansion, and more. Built for Go 1.21+. tags: go, database, sql, opensource If you use sqlx, this is worth 3 minutes of your time jmoiron/sqlx has been the go-to SQL extension library for Go for years. Struct mapping, named parameters, IN clause expansion — it made database/sql actually pleasant to use. I've used it in almost every Go project I've worked on. But here's the reality: its activity has been modest at best, and has slowed to a crawl in recent years. Hundreds of issues sit untouched. PRs go unanswered. Bugs reported years ago are still there, waiting to cause production incidents. This isn't a knock on sqlx — it's a great library with solid design. But an unmaintained foundational library is a liability. So we built sqlex sqlex is a drop-in replacement for jmoiron/sqlx that is 100% API-compatible . All sqlx methods ( Get , Select , Exec , NamedQuery , Preparex , etc.) work identically. Migrating takes 30 seconds — just change the import path: - import "github.com/jmoiron/sqlx" + import "github.com/go-sqlex/sqlex" 🐛 20+ bug fixes from sqlx, all fixed 🚀 New features sqlx never had Auto-Rebind — write ? everywhere, works on PostgreSQL ($1), MySQL (?), SQLite (?), SQL Server ( @p1 ). No more manual db.Rebind(). SQL parsing fixes — colons in strings, :: type casts, ? in comments are correctly handled. Silent bugs from sqlx are gone. Auto IN expansion — slices in IN (?) are detected and expanded automatically on all methods. Hook system — pluggable SQL interceptors for logging, tracing, metrics (onion model). JSONValue[T] — generic JSON column type with auto serialize/deserialize. StrictMode — lenient by default (matching sqlx Unsafe()), optionally strict for debugging. Unified interfaces — Ext / ExtContext / NamedExt / BindExt with compile-time

Base Power is skipping the PJM's troubled interconnection queue by placing its batteries at people's homes, offering backup services in exchange.

We have tools for checking whether a query is injectable. We have linters, scanners, ORMs, parameterized queries, and database policies. But after the database returns rows, most applications simply trust that the result set matches the operation that asked for it. queryguard starts there. The query may be safe. The result may still be wrong. SQL injection taught us to distrust query construction. Parameterized queries answered the question: Did the user control the query structure? That question is well understood. The tooling is mature. But it is a different question from the one queryguard asks: Did this operation receive only the rows and fields it was allowed to receive? Those two questions are not the same. A perfectly safe parameterized query can still return the wrong row — because a predicate was dropped, a join widened the result, a developer selected a column they shouldn't have, or a query was rewritten without updating its scope contract. queryguard is not a database firewall. It is not a SQL injection scanner. It is not an ORM plugin. It is a contract check for observed result sets. Where it sits The hook position is the core design decision. queryguard sits immediately after cursor execution — before any result shaping, filtering, serialization, or response mapping. cursor = conn . execute ( sql , bindings ) rows = [ dict ( row ) for row in cursor . fetchall ()] evidence = queryguard . run_check ( contract , { " contract_id " : " user_profile_lookup " , " contract_version " : " 0.1.0 " , " params " : { " user_id " : user_id }, " session " : { " tenant_id " : tenant_id }, " result " : rows , }) if evidence [ " verdict " ] != " PASS " : raise QueryguardViolation ( evidence ) return rows Not at the HTTP layer. Not inside the ORM. Not at the API gateway. Immediately after the cursor returns rows — while the result is still raw, before anything shapes or discards it. This is intentional. If rows are shaped before queryguard sees them, queryguard cannot det

Most developers think password hashing is about authentication. It's not. Authentication is just a side effect. Password hashing exists for a much darker reason: because databases get stolen. Every year, companies invest millions in firewalls, monitoring systems, cloud security, and access controls. Yet breach after breach continues to make headlines. The uncomfortable truth is that security teams don't assume a breach will never happen. They assume it eventually will. And when that day comes, one question determines whether the incident becomes a minor security event or a full-scale disaster: Did you hash the passwords? The Difference Between an Incident and a Catastrophe Imagine an attacker gains read access to your production database. Not a far-fetched scenario. A leaked backup. A vulnerable API. A compromised employee account. A misconfigured cloud bucket. The attacker runs a simple query: SELECT email , password FROM users ; If your system stores passwords in plain text, the breach is over. The attacker already won. No advanced techniques. No brute force. No expensive infrastructure. They now possess something more valuable than your database itself: your users' digital identities. Because users rarely reuse databases. They reuse passwords. The same password protecting an account on your platform might also unlock their Gmail, GitHub, LinkedIn, banking app, or company VPN. What started as your security problem instantly becomes everyone else's. This Is Not Theoretical History has repeatedly shown what happens when passwords are handled incorrectly. The RockYou breach exposed more than 30 million passwords stored in plain text. Attackers didn't need to crack anything. They simply read the data. Years later, those leaked passwords were still appearing in credential stuffing attacks across the internet. A single backend decision survived longer than the company itself. That's the thing about password leaks. They don't expire when the incident report is published.

Live demo: https://safestate.vercel.app , code: https://github.com/usv240/safestate A product recall today is basically a notice. It lives on a webpage, or a PDF, or an email that somebody is supposed to read. Say the problem out loud and it gets uncomfortable fast. A recalled crib can be listed and sold to another family, and nobody in that sale ever sees the recall. Reselling recalled goods is actually illegal, and recalled infant products have killed kids. I spent this hackathon building something to close that gap. I called it SafeState, and the idea is small: make the recall do something. When a second-hand item is listed or sold, the marketplace checks SafeState first, and recalled units get blocked right at checkout. It is precise down to the serial number, so safe units still sell. It runs on the stack this hackathon is about. A Next.js front end on Vercel, with Amazon Aurora DSQL behind it. Why DSQL is the whole point here The promise SafeState has to keep is this: the moment a recall lands in any region, no marketplace anywhere should ever read that product as "safe" again. That is a strong consistency problem, not a nice-to-have. If there is any window where a recalled product still looks safe, that is exactly when it gets sold. An eventually consistent store or a nightly sync leaves that window open. DSQL's active-active, multi-region setup with strong consistency is what closes it. I set up a real peered cluster across us-east-1 and us-east-2, with us-west-2 as the witness. Write a recall through one region's endpoint and you can read it back from the other region right away. There is a page in the app that lets you run that yourself. The one trick that makes it work DSQL runs on snapshot isolation (PostgreSQL REPEATABLE READ) with optimistic concurrency. It catches write-write conflicts at commit time. Snapshot isolation will not protect you from write skew, so I had to design around that. To guarantee that a recall and a sale of the same product actua

Recently, we encountered a case on a RHEL 9 machine where an RPM query did not show any information about installed package even though the package files were present in the filesystem. Although RPM reported that the package was not installed, I was still able to run tcpdumpcommand and use it without any issues. The binary was present there in the filesystem and I was able to run it successfully. Fixing RPM Database Desynchronization on RHEL - bidhankhatri.com.np Recently, we encountered a case on a RHEL 9 machine where an RPM query did not show any information about installed package even though the package files were present in the filesystem. bidhankhatri.com.np

While working on a MERN project, I came across these indexes: transactionSchema . index ({ user : 1 , date : - 1 }); transactionSchema . index ({ user : 1 , type : - 1 }); transactionSchema . index ({ user : 1 , category : - 1 }); My first reaction was: "Why are we creating 3 different indexes for the same schema? Isn't one index enough?" At that time, my understanding was: "Indexes help MongoDB find records faster." Which is true, but it wasn't enough to explain why multiple indexes existed for the same collection. That simple doubt led me down a rabbit hole of learning about indexes, compound indexes, how MongoDB stores them, and the famous Prefix Rule. Here's what I learned. What is an Index? Imagine a collection with millions of transactions. db . transactions . find ({ user : " Aarthi " }); Without an index, MongoDB may need to inspect every document until it finds the matching records. This is called a Collection Scan . Think of it like searching for a chapter in a book without a table of contents. You'd have to flip through page after page until you find it. An index works like a book's table of contents. Instead of scanning every document, MongoDB can jump directly to the relevant records. Example: db . transactions . createIndex ({ user : 1 }); Now MongoDB can quickly locate all transactions belonging to a specific user. What is a Compound Index? A compound index contains multiple fields. Example: db . transactions . createIndex ({ user : 1 , date : - 1 }); This means MongoDB organizes the index by: user └── date Conceptually, it looks something like: Aarthi 2025-08-10 2025-08-09 2025-08-08 John 2025-08-10 2025-08-05 The data is first grouped by user , and within each user, it is ordered by date . Now queries like: db . transactions . find ({ user : " Aarthi " }). sort ({ date : - 1 }); become very efficient. MongoDB can jump directly to Aarthi's records and retrieve them in date order. The Prefix Rule: The Concept That Finally Made It Click Consider this i

Introduction Every serious backend developer eventually faces the same problem: you need to make multiple changes to a database as part of a single business operation, and you need all of them to succeed or none of them to go through. Partial updates are worse than no updates at all - they leave your data in an inconsistent state that can be nearly impossible to debug in production. This is not a new problem. Enterprise developers have been solving it for decades, and Martin Fowler documented the canonical solution in his 2002 book Patterns of Enterprise Application Architecture : the Unit of Work pattern. In this article we are going to go deep on what Unit of Work is, why it exists, how it works internally, and how to build a clean, production-quality implementation from scratch in Python using only the standard library. By the end you will have a working implementation you can adapt to any project, and a solid understanding of how popular frameworks like SQLAlchemy and Django ORM implement this pattern under the hood. The full source code is available on GitHub: 👉 github.com/diegocastillo12/unit-of-work-python - ## Background: What is the Unit of Work Pattern? The Unit of Work pattern is part of Martin Fowler's catalog of Patterns of Enterprise Application Architecture (PoEAA), a collection of battle-tested solutions for common problems in enterprise software design. Fowler defines it as follows: > "A Unit of Work maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems." Let's unpack that definition carefully. "Maintains a list of objects affected by a business transaction" - this means the Unit of Work acts as a tracker. When your business logic creates a new object, modifies an existing one, or marks one for deletion, it does not immediately write to the database. Instead, it registers the change with the Unit of Work, which keeps an in-memory list of everything that ne

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

Ribbie lets you follow along live with MLB games with a delightful, arcade-inspired interface.

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

DuckDB 1.5.2, PostgreSQL Internal Stats, and SQLite Virtual Table xUpdate Deep Dive Today's Highlights This week brings a stable new patch release for DuckDB, enhancing performance and adding DuckLake support. We also delve into PostgreSQL's internal statistics for better tuning and explore advanced SQLite virtual table implementation via xUpdate . Announcing DuckDB 1.5.2 (DuckDB Blog) Source: https://duckdb.org/2026/04/13/announcing-duckdb-152.html DuckDB has released version 1.5.2, a patch update focusing on stability and performance. This release includes critical bugfixes that improve the reliability of the in-process analytical database, addressing various edge cases and enhancing overall robustness. Key enhancements also target performance bottlenecks, ensuring faster query execution for diverse analytical workloads. A significant new feature in this version is the official support for the DuckLake v1.0 lakehouse format. This integration positions DuckDB as a more robust tool for handling modern data architectures, allowing users to efficiently query and manage data stored in a lakehouse paradigm directly within their applications or analytical workflows. This update makes DuckDB even more compelling for embedded analytics and data pipeline use cases, providing a flexible and high-performance option for developers. Comment: Always good to see performance improvements and bug fixes for an embedded analytics powerhouse like DuckDB. DuckLake v1.0 support is a big step for managing structured data in lakehouse environments directly from DuckDB, enhancing its utility for complex data architectures. pg_stats: How Postgres Internal Stats Work (Planet PostgreSQL) Source: https://postgr.es/p/9mG This article from Planet PostgreSQL delves into the intricate mechanisms behind PostgreSQL's internal statistics, specifically focusing on pg_stats . Understanding how Postgres collects and utilizes these statistics is fundamental for effective database performance tuning and q

আমার colleague এখন প্ল্যান দেখতে পারছে। Scan types বুঝতে পারছে। Join types বুঝতে পারছে। Estimate আর actual এর gap দেখতে পারছে। BUFFERS ও দেখছে। কিন্তু সে প্রশ্ন করল। এসব দেখে কি করব? Step by step কোন পথে যাব? আমি বললাম। পাঁচটা step আছে। অর্ডার অনুযায়ী। পর্ব ২ এ আমি বলেছিলাম scan types, join types, estimate আর actual এর gap। BUFFERS কি। এবার আসি সমাধান এ। Diagnostic Workflow আপনার কাছে একটা slow query এসেছে। কিভাবে debug করবেন? এই পাঁচটা প্রশ্ন করুন অর্ডার অনুযায়ী। ৯০% slow query প্রথম বা দ্বিতীয় ধাপেই solve হয়ে যায়। ১. Deepest Seq Scan দেখুন Table বড় কি না? Filter selective কি না? Missing index থাকলে add করুন। আজই শুরু করুন যখন একটা Seq Scan দেখবেন big table এ, প্রথমে WHERE clause টা check করুন। Selective কি না? ৫% এর কম row return হওয়ার কথা? যদি তাই হয়, index missing। CREATE INDEX idx_name ON table(column) run করুন। ২. Join types দেখুন কোনো Nested Loop আছে কিন্তু দুই পাশেই বড় table? Hash Join force করুন বা ডান পাশে index add করুন। আজই শুরু করুন Nested Loop দেখলে ডান পাশের table এ index check করুন। যদি না থাকে, create করুন। Index থাকা সত্ত্বেও planner Nested Loop use করছে? SET enable_nestloop = off temporarily disable করে দেখুন। Hash Join আসবে কি না। ৩. Row estimates দেখুন Estimate vs actual ১০x এর বেশি difference? ANALYZE table দিন বা predicate rewrite করুন। আজই শুরু করুন rows=1 estimate কিন্তু rows=100000 actual দেখলে ANALYZE tablename run করুন। Statistics refresh হবে। তারপর plan আবার দেখুন। যদি তাও না আসে, WHERE clause rewrite করুন। Function call থাকলে remove করুন। Type mismatch থাকলে fix করুন। ৪. BUFFERS add করুন কোনো node এ অনেক disk reads? Caching investigate করুন। আজই শুরু করুন EXPLAIN (ANALYZE, BUFFERS) run করে দেখুন shared read high কোথায়। সেই node টাই bottleneck। Index add করলে reads কমবে। Pre-warm cache করতে পারেন। Data pre-load করতে পারেন। ৫. Sorts আর hashes দেখুন কোনো spill-to-disk আছে? work_mem raise করুন বা sort eliminate করুন। আজই শুরু করুন Plan এ external merge Disk: 421MB দেখলে spill-to-disk হয়েছে। SET work_mem = '256MB' temporarily rais

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