# 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. ## 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 Blog]() Topics: [Database](), [Databases](), [systems](), [optimize](), [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... ## 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](), [Databases](), [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 Blog]() Topics: [API](), [Databases](), [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 Blog]() Topics: [SQLite](), [SQL](), [Databases]() 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 Blog]() Topics: [SQLite](), [Databases](), [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... ## Dear Postgres DevFeed: [Dear Postgres]() Original publisher: [Read original article]() Author: Map Published: 2017-10-12T20:55:56Z Content type: opinion Language: en Sources: [Craig Kerstiens]() Topics: [Databases](), [Database](), [data](), [Geographic Information System](), [JSON](), [functions](), [XML]() Tags: [b-tree](), [database](), [databases](), [gis](), [indexes](), [json](), [jsonb](), [location-based](), [postgres](), [postgresql](), [window-functions]() ### AI overview An appreciative commentary on Postgres describes how it has expanded beyond traditional relational database capabilities while retaining a focus on data durability, standards, and steady improvement. The article discusses indexes, GIS and PostGIS, analytical features such as window functions and CTEs, and JSON and JSONB support. ### Source excerpt Dear Postgres, I've always felt an affinity for you in my 9 years of working with you. I know others have known you longer, but that doesn't mean they love you more. Years ago when others complained about your rigidness or that you weren't as accommodating as others I found solace in your steadfast values: Don't lose data Adhere to standards Move forward with a balancing act between new fads of the day while still continuously improving You've been there and seen it all. Years ago you were being disrupted by XML databases. As companies made heavy investment into what such a document database would do for their organization you proceeded to "simply" add a datatype that accomplished the same and brought your years of progress along with it. In the early years you had the standard format of index b-tree that most database engines leveraged. Then quietly but confidently you started adding more. Then came K-nearest neighbor, generalized inverted indexes (GIN), and generalized search-tree (GiST), only to be followed by space partitioned GiST and block range indexes (BRIN). Now the only question is which do I use? All the while there was this other camp using for something that felt cool but outside my world: GIS. GIS, geographical information systems, I thought was something only civil engineers used. Then GPS came along, then the iPhone and location based devices came along and suddenly I wanted to find out the nearest path to my Peets, or manage geographical region for my grocery delivery service. PostGIS had been there all along building up this powerful feature set, sadly to this day I still mostly marvel from the sideline at this whole other feature set I long to take advantage of... one day... one day. A little over 5 years ago I fell in love with your fastly improving analytical capabilities. No you weren't an MPP system yet, but here came window functions and CTEs, then I almost understood recursive CTEs (still working on that one). I can iterate over data in a recurs