# b-tree Published articles for b-tree. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## AgensGraph 2.18 Released: Graph Database on PostgreSQL 18 with Expanded ISO GQL Support DevFeed: [AgensGraph 2.18 Released: Graph Database on PostgreSQL 18 with Expanded ISO GQL Support]() Original publisher: [Read original article]() Author: SKAI Worldwide Published: 2026-11-07T00:00:00Z Content type: release Language: en Sources: [PostgreSQL news]() Topics: [graph-database](), [PostgreSQL](), [SQL](), [Open Source]() Tags: [17](), [authentication](), [b-tree](), [call](), [collect](), [count](), [cypher](), [database](), [delete](), [exists](), [filter](), [gql](), [graph](), [graph-database](), [index](), [insert](), [iso](), [postgresql](), [postgresql-18](), [support]() ### AI overview AgensGraph 2.18.6.1 adds an initial set of ISO GQL clauses alongside openCypher and changes variable-length traversals to use the edge label's index at each hop. In the team's test on the 30-million-edge soc-Pokec graph, a two-hop query that took over 20 seconds without finishing on 2.17 completed in milliseconds on 2.18. The release also adds graph subqueries and PostgreSQL function calls via CALL ... YIELD. It is based on PostgreSQL 18.6 and includes typed property columns and security changes for Cypher writes and fixed-length patterns. A 2.17 data directory cannot be used directly: upgrade with pg_upgrade or dump and restore using 2.18's pg_dump. The supplied text is partial, so it does not support a complete account of all release features. ### Source excerpt The AgensGraph Development Team is pleased to announce the release of AgensGraph v2.18.6.1, the latest version in the 2.18 series. AgensGraph is an open-source graph database, built on PostgreSQL 18.6, that runs SQL and graph queries over the same data, in the same transaction and even in the same statement. AgensGraph 2.18 is a major step toward the ISO GQL standard (ISO/IEC 39075), the international standard query language for property graphs. We are committed to bringing AgensGraph in line with GQL: 2.18 adds the first set of GQL clauses alongside openCypher, and more will follow in upcoming releases. Performance was the other focus of this release. Variable-length traversals now use the edge label's index at each hop, whereas 2.17 scanned the entire label at every hop. On the public soc-Pokec graph (30 million edges), a two-hop traversal that did not finish within 20 seconds in our tests on 2.17 now completes in milliseconds. Highlights GQL clauses: LET, FOR, FILTER, NEXT, FINISH and INSERT, plus standalone ORDER BY, OFFSET and LIMIT clauses. Graph subqueries: CALL { }, OPTIONAL CALL, EXISTS { }, COUNT { }, COLLECT { } and VALUE { }, plus CALL ... YIELD to invoke PostgreSQL functions from Cypher. Based on PostgreSQL 18.6: asynchronous I/O, uuidv7(), virtual generated columns, OAuth authentication, B-tree skip scan and all PostgreSQL fixes through 18.6. Faster graph queries: variable-length traversals, pattern matching, label scans, EXISTS subqueries, path functions, sorting, grouping and counting of vertices and edges, ordering by vector distance, and the SET and DELETE clauses. Typed property columns: frequently queried properties can be stored as native typed columns and indexed like any other PostgreSQL column for faster filtering and sorting. Security: Cypher writes now enforce row-level security policies, and fixed-length patterns also enforce row-level security and privileges on labels they match without naming them. AI and Python agensgraph-python 2.1: a ## How Databases Work Internally: 15 Small Open-Source Databases to Learn From, with Projects DevFeed: [How Databases Work Internally: 15 Small Open-Source Databases to Learn From, with Projects]() Original publisher: [Read original article]() Author: Shrey Vijayvargiya Published: 2026-10-29T12:00:00Z Content type: article Language: en Sources: [iHateReading]() Topics: [Learning](), [Open Source]() Tags: [agents](), [ai](), [ai-agents](), [apache-2-0](), [api](), [b-tree](), [backend](), [badger](), [bbolt](), [benchmark](), [benchmarks](), [blockchain](), [bloom-filters](), [branch](), [build-your-own-database-from-scratch](), [c](), [cockroachdb](), [coding](), [database-internals-tutorial](), [databases](), [dbms-projects-for-beginners](), [development](), [embedded-database-examples](), [frontend](), [how-databases-work-internally](), [javascript](), [lsm-tree-vs-b-tree](), [mongodb](), [mongodb-alternatives-open-source](), [node-js](), [open-source](), [postgres](), [product](), [programming](), [projects](), [react](), [sqlite](), [sqlite-internals](), [typescript](), [web-development]() ### AI overview The article uses small open-source databases and four hands-on projects to show how database internals fit together: protocols feed parsers, planners and executors, while storage engines and write-ahead logs handle data and recovery. It contrasts B-trees, which update sorted pages in place, with LSM trees, which append writes and merge sorted files later, shifting costs between writes, reads and compaction. Projects demonstrate Egres using PostgreSQL's wire protocol over an LSM engine, PGlite exposing real PostgreSQL query plans in WebAssembly, examining SQLite pages and indexes, and building a log-structured key-value store with recovery and compaction. The author reports Egres's limited SQL support, large initial data folder and unverified benchmark claims; PGlite shows the planner choosing a sequential scan when many rows match; and the toy store omits range scans, disk-based indexes and concurrency. A four-weekend reading and building sequence suggests ways to continue. ### Source excerpt Postgres and MongoDB are too big to read. Here are 15 small open-source databases you can actually finish, plus four projects I ran: Egres, PGlite, a SQLite fi... ## Synchronized Rebuilds:The Hidden Cost of Concurrent Inserts in Learned Indexes DevFeed: [Synchronized Rebuilds:The Hidden Cost of Concurrent Inserts in Learned Indexes]() Original publisher: [Read original article]() Author: Emmilly Immaculate Namuganga, Laurent Bindschaedler Published: 2026-10-27T04:00:00Z Content type: article Language: en Sources: [arXiv: Databases]() Topics: [synchronization](), [database-consistency]() Tags: [abstract](), [array](), [arxiv](), [b-tree](), [background](), [benchmark](), [benchmarks](), [best](), [blocks](), [buffer](), [concurrency](), [concurrent](), [cost](), [cs-db](), [data](), [design](), [designs](), [exists](), [hidden](), [indexes](), [rebuilds]() ### AI overview Across more than 4,000 runs of 13 learned indexes, the study finds distinct costs for concurrent inserts: gapped-array indexes rebuild nodes in synchronized bursts, model-placement indexes use three to four times the memory, and buffered indexes take two to three times longer. Three of five concurrent indexes lost keys or stopped making progress, while standard benchmarks missed both these failures and rebuild bursts. Unlike B-tree page rebuilds, learned-index rebuilds copy much larger nodes and block operations reaching them. Randomizing node density spreads bursts; smaller nodes trade bursts for longer runtimes at scale. A temporary buffer used during rebuilds and background rebuilds remove the bursts. On 400 million real keys, these techniques reduced inserts exceeding 100 μs by 100-fold at one thread and tenfold at sixteen threads when spare cores ran background work, with runtimes one-third to one-half those of buffered designs. ### Source excerpt arXiv:2610.33770v1 Announce Type: new Abstract: Nine years after learned indexes were proposed, their advantage on read-mostly data is well established, and a standard benchmark rates their best concurrent version as almost ready for use. Across more than 4000 runs of 13 indexes, we find that each design pays for concurrent inserts in a different way. Gapped-array indexes rebuild their nodes in synchronized bursts, at positions that follow from three constants in the source. Model-placement indexes use three to four times the memory, buffered indexes take two to three times as long. Three of five concurrent learned indexes lost keys or stopped making progress. Standard benchmarks miss both the bursts and the lost keys. The bursts cost far more than in B-trees because each rebuild copies a node a thousand times larger than a page and, under concurrency, blocks every operation that reaches the node. The typical B-tree fix of randomizing node density only spreads the bursts out,while smaller nodes trade them for longer run times at scale. Two standard systems techniques remove them: a buffer that exists only while a node is rebuilt, and background rebuilds.We show that on 400M real keys, these techniques reduce the number of inserts whose latency exceeds 100$\mu$s by two orders of magnitude at one thread, and by one at sixteen when spare cores run the background threads, at a third to half the run time of the buffered designs. ## Load terabytes of data in minutes into Lakebase Postgres DevFeed: [Load terabytes of data in minutes into Lakebase Postgres]() Original publisher: [Read original article]() Author: Yecheng Yang; Nolan Biscaro; Szu-Po Wang; Pranav Aurora Published: 2026-10-08T18:49:31Z Content type: article Language: en Sources: [Databricks]() Topics: [Supabase](), [Load Balancing](), [table-partitioning]() Tags: [autoscaling](), [b-tree](), [benchmark](), [database](), [databases](), [distributed](), [lakebase](), [postgres]() ### AI overview The article describes Databricks Lakebase Postgres's LTAP architecture for moving large bulk loads from the primary database to distributed Spark processing. It details how Spark-generated heap and index data are validated and staged in object storage before the destination database publishes it, and reports an internal benchmark of loading 1 TB in under five minutes, with index builds not yet parallelized. ### Source excerpt Operational databases like Postgres are built to reliably execute highly concurrent... ## Malicious B-tree NPM Package Accumulates Millions of Downloads DevFeed: [Malicious B-tree NPM Package Accumulates Millions of Downloads]() Original publisher: [Read original article]() Author: Ionut Arghire Published: 2026-09-22T11:33:59Z Content type: news Language: en Sources: [SecurityWeek]() Topics: [Malware](), [npm](), [Security](), [C2](), [Blockchain](), [GitHub](), [JavaScript](), [smart contract]() Tags: [application-security](), [b-tree](), [blockchain](), [command-and-control](), [github](), [javascript](), [malware](), [npm](), [npm-package](), [security](), [smart-contract](), [supply-chain-attack](), [supply-chain-security]() ### AI overview SecurityWeek reports an ongoing npm supply-chain campaign in which the malicious indexed-btree package impersonated sorted-btree and reached 2 million weekly downloads. Hidden JavaScript code collected system information and used a blockchain contract for command and control. ### Source excerpt Posing as the legitimate sorted-btree package, indexed-btree hides a malware trigger in its prototype method. The post Malicious B-tree NPM Package Accumulates Millions of Downloads appeared first on SecurityWeek. ## Anatomy of a (Postgres) search engine DevFeed: [Anatomy of a (Postgres) search engine]() Original publisher: [Read original article]() Author: Eric Ridge Published: 2026-09-22T00:00:00Z Content type: article Language: en Sources: [PlanetScale]() Topics: [Database](), [Data structures](), [Query (disambiguation)](), [search-engines]() Tags: [b-tree](), [data-structure](), [database](), [engineering](), [full-text-search](), [indexes](), [postgres](), [queries]() ### AI overview An explanation of how full-text search engines work, focusing on inverted indexes and their implementation in Postgres. It contrasts them with b-tree indexes and describes term dictionaries, postings lists, positional data, frequency data, and query processing. ### Source excerpt How a full-text search engine works, and how it works in Postgres ## Diagnosing PostgreSQL Index Bloat, Write Amplification, and Buffer Cache Hit Ratios DevFeed: [Diagnosing PostgreSQL Index Bloat, Write Amplification, and Buffer Cache Hit Ratios]() Original publisher: [Read original article]() Author: SitePoint Team Published: 2026-09-20T14:38:13Z Content type: tutorial Language: en Sources: [SitePoint]() Topics: [PostgreSQL](), [SQL](), [Database](), [Cache](), [Docker]() Tags: [b-tree](), [computing](), [databases](), [diagnose](), [docker](), [downtime](), [mvcc](), [postgresql](), [programming](), [sql]() ### AI overview A tutorial on diagnosing PostgreSQL index bloat, write amplification, and buffer cache hit ratios using reusable SQL queries. It explains how MVCC, dead tuples, and B-tree page fragmentation can degrade query performance, and describes testing the diagnostic process in a Docker-based PostgreSQL environment before production use. ### Source excerpt Diagnose PostgreSQL index bloat, write amplification, and buffer cache hit ratios with system-catalog SQL queries, then reindex with zero downtime. Continue reading Diagnosing PostgreSQL Index Bloat, Write Amplification, and Buffer Cache Hit Ratios on SitePoint. ## Covering Indexes and Index-Only Scans in PostgreSQL DevFeed: [Covering Indexes and Index-Only Scans in PostgreSQL]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-08-18T00:00:00Z Content type: tutorial Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [PostgreSQL](), [Query (disambiguation)](), [data]() Tags: [b-tree](), [cache](), [covering](), [covering-index](), [database-performance](), [explain](), [heap](), [indexes](), [indexing](), [innodb](), [pages](), [postgresql](), [query](), [storage](), [vacuum]() ### AI overview This tutorial explains why a normal index scan may still be slow: after finding matching entries, the database follows a pointer into the table for each row. Covering indexes store the selected columns in the index and can avoid those heap reads. In PostgreSQL, index-only scans also depend on the visibility map marking pages all-visible, while SELECT * prevents the technique from being fully effective. ### Source excerpt A normal index scan finds matching rows and then follows a pointer into the table for every one of them, which is a random read per row. A covering index stores the columns the query selects, so the engine answers entirely from the index and skips those reads. In PostgreSQL this only works when the visibility map marks the pages all-visible, so an unvacuumed table will report Heap Fetches in EXPLAIN and give back most of the gain. SELECT star defeats the technique completely. ## How to Write to SSDs - Co-Designing DBMS and Flash Storage DevFeed: [How to Write to SSDs - Co-Designing DBMS and Flash Storage]() Original publisher: [Read original article]() Published: 2026-06-22T00:00:00Z Content type: article Language: en Sources: [Gaurav Sarma's]() Topics: [Database](), [systems](), [Optimization](), [Parallelism]() Tags: [b-tree](), [checkpoint](), [cycles](), [database](), [databases](), [latency](), [mysql](), [optimize](), [optimizing](), [postgresql](), [ssd](), [storage](), [systems](), [wal](), [write-amplification]() ### AI overview The article explains how database management systems and SSDs jointly amplify writes. It reports that a 4 KiB logical page write can become about 18.85 KiB of flash writes on a Samsung PM9A3, and presents DBMS-SSD co-design, including avoiding in-place updates, as a way to address the combined amplification. ### Source excerpt . [How to Write to SSDs](optimising-ssd-writes-for-dbms-cover... ## PostgreSQL 18 Upgrades for AI-Era Workloads and Operations DevFeed: [PostgreSQL 18 Upgrades for AI-Era Workloads and Operations]() Original publisher: [Read original article]() Author: Sebastian Insausti Published: 2026-03-20T08:00:00Z Content type: article Language: en Sources: [SeveralNines]() Topics: [Insight for DBAs](), [Production Engineering](), [aio](), [Transactions]() Tags: [ai](), [aio](), [analytics](), [async](), [asynchronous](), [b-tree](), [clustercontrol](), [i-o](), [operations](), [postgresql](), [postgresql-18](), [saas](), [transactions](), [upgrades](), [upgrades-patches](), [vector](), [vector-search]() ### AI overview PostgreSQL 18 targets operational pressure in mixed workloads with practical changes, including asynchronous I/O (AIO) and B-tree skip-scan. AIO can issue multiple storage requests without waiting for each one, potentially improving read-heavy scans and some maintenance operations when I/O is the bottleneck. The excerpt recommends evaluating it under realistic concurrent load, comparing P95/P99 latency with and without AIO, including while background VACUUM runs. For multi-column B-tree indexes, skip-scan addresses queries that filter without specifying the index's first column. The excerpt ends mid-explanation, so it provides no further detail on skip-scan behavior or results. ### Source excerpt Today, we're asking PostgreSQL to do more and more, like handling both transactions and analytics, powering huge SaaS platforms, managing event data, and even dipping into AI-related tasks like vector search. This intense pressure highlights some pain points: slow, unpredictable reads, rigid indexing, complex setups, and risky major upgrades. PostgreSQL 18 directly addresses these real-world [...] The post PostgreSQL 18 Upgrades for AI-Era Workloads and Operations appeared first on Severalnines. ## The RUM Conjecture: You Cannot Optimize Reads, Updates, and Memory at Once DevFeed: [The RUM Conjecture: You Cannot Optimize Reads, Updates, and Memory at Once]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-03-17T00:00:00Z Content type: article Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [systems](), [Database](), [Apache Cassandra](), [rocksdb](), [MySQL](), [PostgreSQL]() Tags: [b-tree](), [capacity](), [cassandra](), [database](), [databases](), [dram](), [indexing](), [latency](), [memory](), [mysql](), [node](), [performance](), [rocksdb](), [storage-engine](), [system-design]() ### AI overview The article explains the RUM Conjecture, which describes a tradeoff among read overhead, update overhead, and memory overhead in database indexes and storage engines. It compares B-Trees, LSM-Trees, and hash indexes to show how each optimizes different tradeoffs. ### Source excerpt You can strictly optimise at most two of read overhead, update overhead, and memory overhead; the third will be expensive. B-Trees optimise reads and memory and pay on writes. LSM-Trees optimise writes and memory and pay on reads. Hash indexes optimise reads and writes and pay in RAM. The useful question is not whether a database is good but which corner it optimises and what you are willing to pay for the other two. ## Pagination at Scale: Why OFFSET and SKIP Will Eventually Break Your API DevFeed: [Pagination at Scale: Why OFFSET and SKIP Will Eventually Break Your API]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-03-17T00:00:00Z Content type: tutorial Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [API](), [Database](), [MongoDB]() Tags: [api](), [api-design](), [b-tree](), [cursor](), [database-performance](), [latency](), [mongodb](), [pagination](), [scalability](), [scale](), [sql]() ### AI overview This article explains why OFFSET and SKIP pagination become slower and more memory-intensive as page depth increases. It presents cursor-based, or keyset, pagination as an alternative that uses an indexed range seek and reads a fixed number of rows, keeping fetch cost largely constant with list depth. The trade-off is that cursor pagination does not provide reliable absolute page numbers. ### Source excerpt OFFSET does not seek. The engine fetches offset plus limit rows, sorts them, and discards the first N, so cost grows linearly with page depth. Cursor pagination passes the last seen key instead of a page number, letting the B+ tree seek in logarithmic time and read a fixed number of rows, which keeps latency flat at any depth. The price is losing absolute page numbers. ## How UUIDv4 Primary Keys Affect Database Insert Performance DevFeed: [How UUIDv4 Primary Keys Affect Database Insert Performance]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-03-16T00:00:00Z Content type: article Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [Database](), [MySQL](), [ordering]() Tags: [b-tree](), [database](), [database-performance](), [fragmentation](), [innodb](), [latency](), [mysql](), [performance](), [system-design](), [uuid]() ### AI overview This article explains how random UUIDv4 primary keys can reduce insert performance in clustered indexes. Random inserts land in the middle of B+ tree pages, causing page splits, fragmentation, additional writes, and buffer-pool churn. It recommends UUIDv7 or ULID, or an auto-increment clustered key with UUIDs in a unique secondary index. ### Source excerpt The problem with UUIDv4 primary keys is not uniqueness, it is that random keys fight the storage engine's physical layout. In a clustered index the primary key is the table, so random inserts land mid-page, trigger page splits, leave pages half-full, and can inflate a 50 GB dataset to roughly 100 GB while wasting buffer pool on empty space. Use UUIDv7 or ULID, or keep an auto-increment clustered key with the UUID in a unique secondary index. ## Cursor Pagination vs Offset Pagination: Which One Should You Use? DevFeed: [Cursor Pagination vs Offset Pagination: Which One Should You Use?]() Original publisher: [Read original article]() Published: 2026-03-11T00:00:00Z Content type: tutorial Language: en Sources: [Gaurav Sarma's]() Topics: [API](), [Database](), [SQL](), [Query (disambiguation)]() Tags: [api](), [apis](), [b-tree](), [database](), [index](), [indexes](), [query-planner](), [rest-apis]() ### AI overview This tutorial compares offset, cursor, and keyset pagination for REST APIs. It explains that offset pagination becomes slower at deep pages because the database scans and discards preceding rows, while concurrent inserts can cause duplicates or skipped results. Cursor pagination uses an indexed position for more consistent performance but does not support random page access; keyset pagination generalizes the approach to arbitrary sort orders. ### Source excerpt . [Cursor vs Offset Pagination](cursor-pagination-vs-offset-pagination-cover... ## SQLite Query Optimisation - How the Planner Thinks and Where It Goes Wrong DevFeed: [SQLite Query Optimisation - How the Planner Thinks and Where It Goes Wrong]() Original publisher: [Read original article]() Published: 2026-03-08T00:00:00Z Content type: tutorial Language: en Sources: [Gaurav Sarma's]() Topics: [SQLite](), [SQL](), [Database]() Tags: [b-tree](), [index](), [optimisation](), [planner](), [query](), [query-planner](), [sql](), [sqlite]() ### AI overview A tutorial on how SQLite's query planner chooses between index searches and full table scans. It explains planner behavior, the effects of query patterns and data distribution, and how to avoid conditions that make indexes inaccessible or scans appear cheaper. ### Source excerpt . [SQLite Query Optimisation](sqlite-query-optimisation-cover... ## SQLite Overflow Pages - When Your Rows Don't Fit DevFeed: [SQLite Overflow Pages - When Your Rows Don't Fit]() Original publisher: [Read original article]() Published: 2026-03-06T00:00:00Z Content type: tutorial Language: en Sources: [Gaurav Sarma's]() Topics: [SQLite](), [Database](), [Data structures]() Tags: [b-tree](), [data-structure](), [database](), [performance](), [sqlite]() ### AI overview This tutorial explains how SQLite stores rows in fixed-size pages and uses overflow-page chains when a row does not fit within a single page. It describes how those chains are reconstructed during reads and why large rows can significantly increase query costs. ### Source excerpt . [SQLite Overflow Pages](sqlite-overflow-pages-cover... ## Efficient COUNT, SUM, MAX with the Aggregate Component DevFeed: [Efficient COUNT, SUM, MAX with the Aggregate Component]() Original publisher: [Read original article]() Author: Stack Published: 2025-08-16T05:46:00Z Content type: tutorial Language: en Sources: [Stack]() Topics: [database-optimization](), [database-consistency](), [cosine similarity search]() Tags: [aggregate](), [aggregates](), [b-tree](), [database](), [migrations](), [triggers]() ### AI overview This tutorial demonstrates using Convex's Aggregate Component for efficient pagination, ranking, per-user statistics, random selection, and latency statistics. It covers keeping aggregates synchronized with database changes using triggers and custom functions, backfilling existing data with migrations, and the risks of edits made through the dashboard that bypass triggers. ### Source excerpt Convex omits built-in aggregates because full-table scans don't scale; the video shows how @convex-dev/aggregate (B-Tree powered) enables fast pagination, ranking, per-user stats, and randomization with fully reactive queries. It also covers keeping aggregates in sync via triggers/custom functions, backfilling with migrations, and the trade-offs that hint at possible platform-level support. ## Databases are Spreadsheets DevFeed: [Databases are Spreadsheets]() Original publisher: [Read original article]() Author: Stack Published: 2024-04-16T16:00:00Z Content type: tutorial Language: en Sources: [Stack]() Topics: [Deployment Strategies](), [search-engines](), [NoSQL](), [Skiplists]() Tags: [b-tree](), [binary-search](), [database-indexes](), [databases](), [full-text-search](), [linear-scan](), [perspectives]() ### AI overview The article explains database indexes through a spreadsheet analogy, showing how sorted indexes support efficient queries and how index design depends on query patterns and product needs. It discusses tradeoffs such as write overhead and storage, and contrasts indexed queries with scans or filtering. ### Source excerpt I want to share my mental model of databases: - Databases are just big spreadsheets - An index is just a view of the spreadsheet sorted by one or more columns - Binary search over a sorted list is faster than a linear scan (for large lists) ## Hash Indexes in Prisma ORM: Fast Equality Lookups in Postgres DevFeed: [Hash Indexes in Prisma ORM: Fast Equality Lookups in Postgres]() Original publisher: [Read original article]() Author: Alex Ruheni Published: 2022-10-12T00:00:00Z Content type: tutorial Language: en Sources: [Prisma]() Topics: [hashing](), [PostgreSQL](), [Prisma](), [Database](), [Data structures]() Tags: [b-tree](), [big-o-notation](), [database](), [hash](), [indexes](), [orm](), [performance](), [postgresql](), [prisma]() ### AI overview A tutorial on PostgreSQL hash indexes in Prisma ORM. It explains how hash indexes support equality lookups, compares them with B-tree indexes, and demonstrates a measured query-performance improvement on 500,000 rows. ### Source excerpt Add a hash index in Prisma with @@index(type: Hash): a 64ms sequential scan becomes a 1.6ms lookup, verified on Prisma ORM 7 and PostgreSQL 17. ## B-Tree Indexes in Prisma ORM: Make Slow Postgres Queries 80x Faster DevFeed: [B-Tree Indexes in Prisma ORM: Make Slow Postgres Queries 80x Faster]() Original publisher: [Read original article]() Author: Alex Ruheni Published: 2022-09-16T00:00:00Z Content type: tutorial Language: en Sources: [Prisma]() Topics: [PostgreSQL](), [Prisma](), [Database](), [Object-relational mapping](), [Data structures]() Tags: [analyze](), [b-tree](), [complexity](), [data-structure](), [explain](), [indexes](), [orm](), [postgres](), [postgresql](), [prisma](), [queries](), [time-complexity]() ### AI overview A practical tutorial on using B-tree indexes in a Prisma ORM schema to improve PostgreSQL query performance. It explains how B-tree indexes work and reports a verified reduction in query time from roughly 66 milliseconds to under 1 millisecond on 500,000 rows using Prisma ORM 7.8 and PostgreSQL 17. ### Source excerpt Add a B-tree index in your Prisma schema with @@index and turn a 66ms sequential scan into a sub-millisecond lookup, an 80x improvement verified on Prisma ORM 7 and PostgreSQL 17 with EXPLAIN ANALYZE. ## Database Indexes Explained: What They Are and When to Use Them DevFeed: [Database Indexes Explained: What They Are and When to Use Them]() Original publisher: [Read original article]() Author: Alex Ruheni Published: 2022-09-06T00:00:00Z Content type: tutorial Language: en Sources: [Prisma]() Topics: [Database](), [Data structures](), [Query (disambiguation)](), [PostgreSQL](), [Prisma]() Tags: [b-tree](), [cost](), [data-structure](), [database](), [hash](), [indexes](), [postgresql](), [prisma](), [search]() ### AI overview An introduction to database indexes: how they reduce search scope to speed up read queries, the main index types, their costs for writes and storage, and when adding an index is worthwhile. Later parts cover B-tree and hash indexes in Prisma ORM. ### Source excerpt A database index is a secondary data structure that speeds up reads by reducing search scope. Learn the main index types, what they cost, and when to add one. ## How Postgres Finds a Row - Pages and B-tree Indexes DevFeed: [How Postgres Finds a Row - Pages and B-tree Indexes]() Original publisher: [Read original article]() Author: umer Published: 2021-04-21T00:00:00Z Content type: article Language: en Sources: [Code Ahoy - Articles]() Topics: [rdbms](), [database-optimization]() Tags: [b-tree](), [indexes](), [pages](), [postgres]() ### AI overview PostgreSQL stores table data in fixed 8 KB pages and identifies each row by a page-and-slot address. Without an index, it may scan pages sequentially to find a row. A B-tree index keeps sorted keys and row addresses in linked leaf pages, with root and, for larger indexes, internal pages directing the lookup. PostgreSQL follows those pages to find the key, then reads the addressed table page; index leaves contain addresses, not row data. In the excerpt's examples, a one-million-row table lookup reads four pages versus 6,370 for a sequential scan. Small tables may still use a sequential scan when it costs about the same. The text also notes that PostgreSQL's B+tree leaves support range scans and `ORDER BY` by linking leaves in order. ### Source excerpt Everyone who uses Postgres knows the advice: if a query is slow, add an index. It works. But what happens under the hood? Let's explore that in this blog posts. Let's start with how Postgres stores tables on disk. Postgres stores each table as one file. It reads and writes that file in 8 KB chunks called pages. Page 0 is bytes 0 to 8191 of the file, page 1 is bytes 8192 to 16383, and so on. Nothing is physically split. A page is just an offset. Bytes 0 8192 16384 24576 __________ __________ __________ ___________________ | page 0 | page 1 | page 2 | more pages... | |__________|__________|__________|___________________| 8 KB 8 KB 8 KB Why pages? Two reasons. First, disks and operating systems don't hand out single bytes. Data comes off a disk in fixed-size blocks, usually 4 KB. Ask for one byte and you get the whole block. Postgres had to read in chunks anyway, so it picked 8 KB. The disk read is the slow part. Once a page is in memory, picking rows out of it is cheap, so a bigger page means more rows per slow read. Second, fixed-size pages make every row addressable. Page 88 starts at byte 88 x 8192, always. So Postgres can identify any row by (page, slot), where slot is the row's position within the page. That address is what an index stores. Tables & Pages Let's create a table and insert some rows to see how this works in practice. Let's create a users table and insert nine rows. CREATE TABLE users ( username text, email text ); INSERT INTO users VALUES ('tyler', 'tyler@yz.ab'), ('diego', 'diego@yz.ab'), ('james', 'james@yz.ab'), ('mary', 'mary@yz.ab'), ('ryan', 'ryan@yz.ab'), ('babar', 'babar@yz.ab'), ('ellen', 'ellen@yz.ab'), ('ali', 'ali@yz.ab'), ('pat', 'pat@yz.ab'); Assume each row is about 2.5 KB, so each page fits 3 rows. Rows are inserted in the order they are added so the first 3 rows go on page 0, the next 3 on page 1, and the last 3 on page 2. _________ page 0 _________ _________ page 1 _________ _________ page 2 _________ | (0,1) tyler tyler@yz.ab | |