# indexes Published articles for indexes. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Eloquent Performance and Database Design: Evidence Before Eager Loading DevFeed: [Eloquent Performance and Database Design: Evidence Before Eager Loading]() Original publisher: [Read original article]() Author: Freek Van der Herten (freek@spatie.be) Published: 2026-09-11T12:30:27Z Content type: tutorial Language: en Sources: [freek.dev - all blogposts]() Topics: [Eloquent ORM](), [Database](), [Laravel](), [PHP]() Tags: [database](), [eager-loading](), [eloquent](), [indexes](), [laravel](), [optimization](), [performance](), [php](), [query]() ### AI overview A practical deep dive into improving Eloquent performance and database design for a growing team dashboard. It covers detecting N+1 queries, choosing aggregates and indexes, inspecting query plans, pagination, chunking, and transaction boundaries. ### Source excerpt A deep dive into Eloquent performance, from detecting N+1 queries to choosing aggregates, indexes, query plans, pagination, chunking, and transaction boundaries for a growing team dashboard. Read more ## Soft deletes affect database constraints, indexes, and application queries DevFeed: [Soft deletes affect database constraints, indexes, and application queries]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-08-20T00:00:00Z Content type: opinion Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [data-processing](), [Database](), [MySQL]() Tags: [data-modelling](), [database-design](), [foreign-keys](), [indexes](), [indexing](), [mysql](), [orm](), [postgresql](), [predicate](), [schema](), [schema-design](), [soft-delete]() ### AI overview The article explains how soft deletion affects database design beyond adding a deleted_at column. It discusses duplicate-key failures, foreign-key behavior, queries that may return deleted rows, and index inefficiency, noting that PostgreSQL partial indexes help while MySQL requires a workaround. ### Source excerpt A deleted_at column turns every future query into a conditional one, and the cost is not the extra predicate. Unique constraints stop working because the deleted row still occupies the key, foreign keys start pointing at rows the application considers gone, and any query written by someone who does not know about the column silently returns deleted data. Soft delete is a data lifecycle decision, and treating it as a boolean column is what makes it expensive. ## 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. ## Why your index is not being used, and why the planner is usually right DevFeed: [Why your index is not being used, and why the planner is usually right]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-08-16T00:00:00Z Content type: tutorial Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [Query (disambiguation)](), [MySQL](), [bug]() Tags: [bug](), [cardinality](), [database-performance](), [explain](), [function](), [indexes](), [indexing](), [mysql](), [postgresql](), [predicate](), [query](), [query-planner]() ### AI overview This tutorial explains why database indexes may not be used even when they exist. It focuses on inaccurate cardinality estimates caused by stale statistics, predicates that prevent index matching, implicit casts, and cases where sequential scans are the cheaper choice. ### Source excerpt An unused index is almost never a planner bug. It is usually a predicate the planner cannot match to the index, such as a function or an implicit cast applied to the column, or a cardinality estimate that is wrong because statistics are stale. When the estimate is right and the planner still refuses, it is often correct: past a few percent of the table, random access through an index costs more than reading the table sequentially. The diagnostic that matters is the gap between estimated and actual rows in EXPLAIN ANALYZE. ## Table-Per-Tenant vs Shared Table: The Multi-Tenancy Tradeoff in Postgres DevFeed: [Table-Per-Tenant vs Shared Table: The Multi-Tenancy Tradeoff in Postgres]() Original publisher: [Read original article]() Published: 2026-04-15T00:00:00Z Content type: tutorial Language: en Sources: [Gaurav Sarma's Blog]() Topics: [Multi-tenancy](), [Database](), [Software as a service]() Tags: [customers](), [database](), [indexes](), [isolation](), [migration](), [multi-tenancy](), [orders](), [postgres](), [saas](), [table]() ### AI overview This tutorial compares table-per-tenant and shared-table designs for multi-tenant SaaS applications in Postgres. It explains how tenant counts, data size, schema changes, scale, query planning, migrations, monitoring, and data-deletion requirements affect the choice. ### Source excerpt You are building a SaaS product. Every customer has their own orders, invoices, and documents... ## Waiting for PostgreSQL 19 - Introduce the REPACK command DevFeed: [Waiting for PostgreSQL 19 - Introduce the REPACK command]() Original publisher: [Read original article]() Author: depesz Published: 2026-03-19T18:07:59Z Content type: article Language: en Sources: [select \* from depesz;]() Topics: [PostgreSQL](), [postgresql clusters]() Tags: [analyze](), [bloat](), [command](), [indexes](), [locking](), [maintenance](), [pg-repack](), [pg19](), [planner](), [postgresql](), [repack](), [uncategorized](), [vacuum](), [waiting]() ### AI overview The article examines PostgreSQL 19's proposed built-in REPACK command, which combines functionality associated with VACUUM FULL and CLUSTER. It demonstrates reclaiming space and reordering a table, discusses locking because concurrent operation is not yet supported, and introduces the pg_stat_progress_repack progress view and available command forms. ### Source excerpt On 10th of March 2026, Álvaro Herrera committed patch: Introduce the REPACK command REPACK absorbs the functionality of VACUUM FULL and CLUSTER in a single command. Because this functionality is completely different from regular VACUUM, having it separate from VACUUM makes it easier for users to understand; as for CLUSTER, the term is heavily ... Continue reading "Waiting for PostgreSQL 19 - Introduce the REPACK command" ## 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... ## When High Correlation Makes PostgreSQL BRIN Indexes Slower DevFeed: [When High Correlation Makes PostgreSQL BRIN Indexes Slower]() Original publisher: [Read original article]() Author: Haki Benita Published: 2023-07-26T21:00:00Z Content type: article Language: en Sources: [Haki Benita]() Topics: [PostgreSQL](), [Database]() Tags: [article](), [articles](), [indexes](), [performance](), [postgresql](), [query](), [sql]() ### AI overview This article explains how PostgreSQL block range indexes (BRIN) work and why high correlation can still produce significantly slower execution under certain reproducible conditions. It describes lossy index behavior and presents a recent PostgreSQL feature as a possible remedy. ### Source excerpt Choosing to use a block range index (BRIN) to query a field with high correlation is a no-brainer for the optimizer. However, under some easily reproducible circumstances, a BRIN index can result in significantly slower execution even when the indexed field has very high correlation. In this article I describe how using a BRIN index in presumably "ideal circumstances" can result in degraded performance, and suggest a recent new feature of PostgreSQL as a remedy. ## Indexing by Custom Field in Craft CMS DevFeed: [Indexing by Custom Field in Craft CMS]() Original publisher: [Read original article]() Author: andrew@nystudio107.com (Andrew Welch) Published: 2023-06-28T17:13:00Z Content type: tutorial Language: en Sources: [nystudio107 | Articles on modern web development.]() Topics: [Content Management System](), [Database](), [migration]() Tags: [article](), [cms](), [content](), [craft](), [custom](), [custom-fields](), [database](), [fields](), [how-to](), [indexes](), [indexing](), [insights](), [learn](), [make](), [migration](), [performance]() ### AI overview A tutorial on adding database indexes for custom fields in Craft CMS through a content migration. It explains that Craft CMS does not automatically index custom fields, so queries filtering on those fields may not scale well as datasets grow. ### Source excerpt Learn how to make a content migration to add database indexes for custom fields in Craft CMS ## Finding and Reclaiming Unused PostgreSQL Index Space DevFeed: [Finding and Reclaiming Unused PostgreSQL Index Space]() Original publisher: [Read original article]() Author: Haki Benita Published: 2021-01-31T22:00:00Z Content type: article Language: en Sources: [Haki Benita]() Topics: [PostgreSQL](), [database monitoring](), [Databases]() Tags: [articles](), [database-monitoring](), [databases](), [django](), [indexes](), [orm](), [postgresql](), [query](), [statistics](), [storage]() ### AI overview The article explains how to identify potentially unused PostgreSQL indexes, assess whether they can safely be removed, and reset statistics counters before rechecking usage. It reports freeing more than 70GB of space overall, including about 20GB from unused indexed values, without dropping indexes or deleting data. ### Source excerpt In this article I describe the process we took to identify potential free space, and one surprising find that helped up clear up ~10GB of unused indexed values! ## Re-Introducing Hash Indexes in PostgreSQL DevFeed: [Re-Introducing Hash Indexes in PostgreSQL]() Original publisher: [Read original article]() Author: Haki Benita Published: 2021-01-10T22:00:00Z Content type: tutorial Language: en Sources: [Haki Benita]() Topics: [PostgreSQL](), [hash](), [Data structures](), [Databases]() Tags: [articles](), [data-structure](), [database](), [hash](), [index](), [indexes](), [performance](), [postgresql](), [sql]() ### AI overview This tutorial explains how PostgreSQL hash indexes work, including hash functions, buckets, tuple pointers, and collisions. It presents hash indexes as an option that can outperform B-Tree indexes under some circumstances. ### Source excerpt There is a type of index you are probably not using, and may have never even heard of. It is wildly unpopular, and until a few PostgreSQL versions ago it was highly discouraged and borderline unusable, but under some circumstances it can out-perform even a B-Tree index. ## Neo4j storage internals DevFeed: [Neo4j storage internals]() Original publisher: [Read original article]() Published: 2020-08-09T00:00:00Z Content type: tutorial Language: en Sources: [Gaurav Sarma's Blog]() Topics: [Neo4j](), [graph-database](), [Graphs](), [Databases]() Tags: [graph-database](), [indexes](), [internals](), [storage]() ### AI overview This article examines Neo4j storage internals and compares graph database relationship traversal with the join-based approach used by MySQL. It explains Neo4j's index-free adjacency and fixed-record-size pointer scheme, while discussing storage and cluster-size comparisons with MongoDB and Cassandra. ### Source excerpt I was exploring Neo4j and came upon this [video](https://www. youtube... ## BKD trees, used in Elasticsearch DevFeed: [BKD trees, used in Elasticsearch]() Original publisher: [Read original article]() Published: 2020-05-30T00:00:00Z Content type: tutorial Language: en Sources: [Gaurav Sarma's Blog]() Topics: [elasticsearch](), [data](), [geospatial]() Tags: [binary-search](), [data](), [elasticsearch](), [geospatial](), [indexes](), [searching](), [trees]() ### AI overview This tutorial explains how BKD trees extend binary search tree ideas to support multidimensional search, including geospatial data, and describes their use in Elasticsearch. ### Source excerpt I had worked on Elasticsearch back in 2015, when it was more known for its text searching capabilities using inverted indexes. As I looked to pick it up again last year for another project, I saw that Elasticsearch had added core support for other data types from text like numbers, IP addresses, geospatial data types, etc... ## PostgresOpen 2018 - First look at talks DevFeed: [PostgresOpen 2018 - First look at talks]() Original publisher: [Read original article]() Author: Map Published: 2018-06-27T20:55:56Z Content type: opinion Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Databases](), [debug](), [data]() Tags: [conference](), [databases](), [debugging](), [indexes](), [postgres](), [postgresql](), [talks]() ### AI overview The article previews selected talks for PostgresOpen 2018, highlighting sessions on debugging the PostgreSQL planner, indexes, extension APIs, and connection pooling. It also mentions the conference dates, location, and early-bird tickets. ### Source excerpt PostgresOpen is just a few months away and our list of talks is now live and available on the PostgresOpen website. This year selecting the talks was the hardest yet not only due to the number of talk submissions, but also the across the board high quality of submissions. There is hopefully something for everyone among the talks, at least if you like Postgres that is. If you're thinking about joining us I'd love to see you there and buy you a beer or coffee. The conference is September 5-7 in downtown San Francisco, and early bird tickets are open for just another few weeks. If you want to save some money on tickets grab it and the room now before things jump. But, if you're curious for a sampling of a few of the talks I thought I'd break down my top five I'm personally most excited about: Debugging the Postgres planner Okay, this one immediately caught my atttention. Melanie will start with the basics of an explain plan to progress down into an actual bug within the Postgres planner, how to can debug it in Postgres, and then write a patch for a fix herself. This talk is well beyond my depth as I'll likely never contribute code to the Postgres planner, but seems extremely entertaining and likely to highlight both performance profiling as well as useful debugging tips. Cleaning out Crocodiles teeth with PostgreSQL indexes I saw Louise give a super practical talk on undertanding explain this year in PgDay Paris. It was both valuable for application developers that aren't Postgres experts as well as surfaced knowledge for those that thought they already understood explain. I'm excited to hear her take on indexes, but maybe even more excited for the storytelling that will come along with this talk. A talk that can be put to a story always becomes a bit easier to follow the journey than simply the technical facts. How PostgreSQL extension APIs are changing the face of relational databases I've said it before personally that Postgres is becoming more of a data platform th ## Visualizing a column's space overhead using pg\_hexedit DevFeed: [Visualizing a column's space overhead using pg\_hexedit]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2018-05-18T23:11:00Z Content type: tutorial Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Optimization](), [Command-line interface](), [structure]() Tags: [command-line](), [index](), [indexes](), [internals](), [nbtree](), [optimization](), [overhead](), [pageinspect](), [pg-hexedit](), [postgresql](), [schema](), [storage]() ### AI overview The article describes a new pg_hexedit capability for annotating the space used by individual columns within PostgreSQL tuples in tables and B-Tree indexes. It explains how tuple metadata enables the feature and discusses how column ordering and types can reduce on-disk storage, particularly for large fact tables and machine-generated event data. ### Source excerpt pg_hexedit recently gained the ability to annotate the space taken up by each individual column/attribute within each individual tuple. This works with tables, and with B-Tree indexes. I had to come up with a way of passing the pg_hexedit frontend utility the relevant pg_attribute metadata to make this work. This metadata describes the "shape" of individual tuples in a relation (backend code uses a closely related structure called a "tuple descriptor"). My approach works seamlessly in simple cases, but can still be used when manually running the pg_hexedit command line tool. pg_attribute system catalog table with column annotations/tags This new capability could be applied to optimizing the data layout of a table that is expected to eventually have a massive number of rows. Carefully choosing the order and type of each column can reduce the total on-disk footprint of a table by an appreciable amount, especially when the final table ends up with several 1 byte columns that get packed together. I am aware of several PostgreSQL users that found it worthwhile to have a highly optimized tuple layout, going so far as to use their own custom dataypes. Alignment-aware micro-optimization of a Postgres client application's schema won't help much in most cases, but it can help noticeably with things like fact tables, or tables that contain machine-generated event data. Developing a sense of proportion around storage overhead should now be easier, and more intuitive. ## PostgreSQL Data Types: Ranges DevFeed: [PostgreSQL Data Types: Ranges]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-04-18T11:41:12Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [data type](), [SQL]() Tags: [data-type](), [extension](), [function](), [indexes](), [postgresql](), [range](), [sql](), [transformation](), [types](), [window]() ### AI overview This tutorial introduces PostgreSQL range types, using daterange values to represent exchange-rate validity periods. It demonstrates loading and transforming exchange-rate data, then enforcing non-overlapping periods with exclusion constraints, GiST indexes, and the btree_gist extension. ### Source excerpt Continuing our series of PostgreSQL Data Types today we're going to introduce the PostgreSQL ranges data type. Range types are a unique feature of PostgreSQL, managing two dimensions of data in a single column, and allowing advanced processing. The main example is the daterange data type, which stores as a single value a lower and an upper bound of the range as a single value. This allows PostgreSQL to implement a concurrent safe check against overlapping ranges, as we're going to see in this article. ## pg\_hexedit now supports GiST, GIN, and hash indexes DevFeed: [pg\_hexedit now supports GiST, GIN, and hash indexes]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2017-12-15T20:29:00Z Content type: release Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [hash](), [Sequences]() Tags: [experimental](), [hash](), [index](), [indexes](), [internals](), [nbtree](), [pg-hexedit](), [postgresql](), [sequences]() ### AI overview An update to the experimental pg_hexedit toolkit adds annotations for GiST, GIN, and hash indexes, as well as sequences, when inspecting raw PostgreSQL relation files. The author plans to add BRIN and SP-GiST support later. ### Source excerpt I've added several enhancements to pg_hexedit, the experimental hex editor toolkit that allows you to open up raw PostgreSQL relation files with useful tags and annotations about the state and purpose of each field. The tool now supports annotations for GiST, GIN, and hash indexes, as well as sequences. GIN "posting tree" leaf page. Compressed TIDs are in orange. It wasn't very time consuming to add these enhancements, because most index access methods share the same basic approach to page layout. I plan to add support for the two remaining index access methods (BRIN and SP-GiST) early in the new year. My hope is that this will spur interest in the internals of PostgreSQL index access methods (PDF link), and how they deal with index tuples and space management (PDF link). Hat tip to Pat Shaughnessy, who just today wrote a great blog post on the internals of GiST. The fact that he has done such a thorough job of explaining how GiST works to a wider audience is encouraging. ## 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 ## PostgreSQL Index bloat under a microscope DevFeed: [PostgreSQL Index bloat under a microscope]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2017-07-19T05:43:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Database]() Tags: [index](), [indexes](), [internals](), [nbtree](), [pageinspect](), [postgresql](), [query](), [wiki]() ### AI overview This technical article presents a PostgreSQL query that summarizes the keyspace of a target B-Tree index. Using pageinspect and a pgbench database, it examines index levels, page utilization, indexed value ranges, and block pointers, then interprets the resulting structure and relationships between the index and table. ### Source excerpt I've posted a snippet query to the PostgreSQL Wiki that "summarizes the keyspace" of a target B-Tree index. This means that it displays which range of indexed values belong on each page, starting from the root. It requires pageinspect. The query recursively performs a breadth-first search. Along the way, it also displays information about the space utilization of each page, and the number of distinct key values that actually exist on the page, allowing you to get a sense of how densely filled each page is relative to what might be expected. The query is available from: https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index If I use the query against the largest index that results from initializing a pgbench database at scale factor 10 (pgbench_accounts_pkey), the query takes about 3 seconds to execute on my laptop, and returns the following: level | l_item | blkno | btpo_flags | type | live_items | dead_items | avg_item_size | page_size | free_size | distinct_real_item_keys | highkey | distinct_block_pointers -------+--------+-------+------------+------+------------+------------+---------------+-----------+-----------+-------------------------+---------+------------------------- 2 | 1 | 290 | 2 | r | 10 | 0 | 15 | 8192 | 7956 | 10 | | 10 1 | 1 | 3 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 103945 | 284 1 | 2 | 289 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 207889 | 284 1 | 3 | 575 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 311833 | 284 1 | 4 | 860 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 415777 | 284 1 | 5 | 1145 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 519721 | 284 1 | 6 | 1430 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 623665 | 284 1 | 7 | 1715 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 727609 | 284 1 | 8 | 2000 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 831553 | 284 1 | 9 | 2285 | 0 | i | 285 | 0 | 15 | 8192 | 2456 | 284 | 935497 | 284 1 | 10 | 2570 | 0 | i | 177 | 0 | 15 | 8192 | 4616 | 177 | | 177 0 | 1 | 1 | ## amcheck: Verify the logical consistency of PostgreSQL B-Tree indexes DevFeed: [amcheck: Verify the logical consistency of PostgreSQL B-Tree indexes]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2016-05-10T18:50:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Database](), [bug](), [systems]() Tags: [amcheck](), [bug](), [consistency](), [database](), [indexes](), [internals](), [locking](), [postgresql](), [vacuum](), [verify]() ### AI overview The article introduces amcheck, a PostgreSQL extension for verifying the logical consistency of B-Tree indexes. It is intended to detect production index corruption with low overhead and generally non-blocking locks, while acknowledging that verification is not fully comprehensive. ### Source excerpt I've created a project page on Github for amcheck, a tool for verifying the logical consistency of PostgreSQL B-Tree indexes: https://github.com/petergeoghegan/amcheck The tool is primarily useful for detecting index corruption in production database systems. It can do this with low overhead; most verification requires only a non-disruptive lock on the index as it is verified. The strength of the lock taken on an index as it is verified matches that of simple SELECT statements (unless the highest level of verification is requested). The locking involved will generally not block concurrent reads or writes, and will not prevent VACUUM from running concurrently. amcheck is proposed as a contrib extension for PostgreSQL 9.7. This externally maintained version of the extension exists to support earlier versions of PostgreSQL (PostgreSQL 9.4+), and to make the tool available to those that need it sooner. While the level of verification is not totally comprehensive (in particular, there is no verification of indexes against underlying tables), the tool is still likely to detect many subtle problems in practice. amcheck verifies that certain invariants that must hold in the structure of B-Tree indexes actually do, in fact, hold. It's fairly exhaustive. One example of a problem that the tool can detect is inconsistency arising from the recent PostgreSQL 9.5 abbreviated keys glibc issue, where the new-to-9.5 abbreviated keys performance optimization could lead to structurally inconsistent indexes due to a bug in some glibc versions. This issue created a need to get amcheck into the hands of users sooner rather than later. It's not ideal that the tool is maintained externally, since there are complex locking protocols involved; the implementation must make sure that there cannot be false positives to be of much practical use, and so the tool ought to be considered whenever there is a question about these locking protocols. Unfortunately, we ran out of time to get amcheck into ## Avoid naming a constraint directly when using ON CONFLICT DO UPDATE DevFeed: [Avoid naming a constraint directly when using ON CONFLICT DO UPDATE]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2015-10-02T18:36:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [implementation](), [syntax](), [ordering]() Tags: [concurrently](), [implementation](), [indexes](), [postgresql](), [syntax]() ### AI overview The article explains PostgreSQL 9.5's UPSERT syntax and recommends using unique index inference with ON CONFLICT DO UPDATE instead of naming a constraint directly. It describes how inference selects arbiter indexes and handles variations such as column ordering, partial-index predicates, and multiple equivalent unique indexes. ### Source excerpt PostgreSQL 9.5 will have support for a feature that is popularly known as "UPSERT" - the ability to either insert or update a row according to whether an existing row with the same key exists. If such a row already exists, the implementation should update it. If not, a new row should be inserted. This is supported by way of a new high level syntax (a clause that extends the INSERT statement) that more or less relieves the application developer from having to give any thought to race conditions. This common operation for client applications is set to become far simpler and far less error-prone than legacy ad-hoc approaches to UPSERT involving subtransactions. When we worked on UPSERT, many edge-cases were carefully considered. A technique called "unique index inference" allows DML statement authors to be very explicit about what condition they want to take the alternative (UPDATE or NOTHING) path on. That alternative path can only be taken in the event of a would-be duplicate violation in an "arbiter" unique index (for the DO NOTHING variant, a would-be exclusion violation is also a possible reason to take the alternative NOTHING path). The ability to write UPSERT statements explicitly and safely while also having lots of flexibility is an important differentiator for PostgreSQL's UPSERT in my view. As the 9.5 INSERT documentation explains, the inference syntax contains one or more column_name_index (columns) and/or expression_index expressions (expressions), and perhaps an optional index_predicate (for partial unique indexes, which are technically not constraints at all). This is internally used to figure out which of any available unique indexes ought to be considered as an arbiter of taking the alternative path. If none can be found, the optimizer raises an error. The inference syntax is very flexible, and very tolerant of variations in column ordering, whether or not a partial unique index predicate is satisfied, and several other things. It can infer multiple un ## Abbreviated keys for numeric to accelerate numeric sorts DevFeed: [Abbreviated keys for numeric to accelerate numeric sorts]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2015-04-04T16:19:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Sorting](), [Development]() Tags: [abbreviation](), [benchmarks](), [c](), [cardinality](), [commit](), [count](), [fast](), [improvements](), [indexes](), [internals](), [maintenance](), [postgresql](), [sorting](), [table]() ### AI overview The article discusses PostgreSQL numeric abbreviated keys, a committed patch intended to accelerate numeric sorting. It reports 6x-7x improvements for representative in-memory queries and a 12x improvement in a PostgreSQL 9.5 text-column CREATE INDEX benchmark compared with PostgreSQL 9.4. ### Source excerpt Andrew Gierth's numeric abbreviated keys patch was committed recently. This commit added abbreviation/sortsupport for the numeric type (the PostgreSQL type which allows practically arbitrary precision, typically recommended for representing monetary values). The encoding scheme that Andrew came up with is rather clever - it has an excellent tendency to concentrate entropy from the original values into the generated abbreviated keys in real world cases. As far as accelerating sorts goes, numeric abbreviation is at least as effective as the original text abbreviation scheme. I easily saw improvements of 6x-7x with representative queries that did not spill to disk (i.e. that used quicksort). In essence, the patch makes sorting numeric values almost as cheap as sorting simple integers, since that is often all that is actually required during sorting proper (the abbreviated keys compare as integers, except that the comparison is inverted to comport with how abbreviation builds abbreviated values from numerics as tuples are copied into local memory ahead of sorting - see the patch for exact details). Separately, over lunch at pgConf.US in New York, Corey Huinker complained about a slow, routine data warehousing CREATE INDEX operation that took far too long. The indexes in question were built on a single text column. I suggested that Corey check out how PostgreSQL 9.5 performs, where this operation is accelerated by text abbreviation, often very effectively. Corey chose an organic set of data that could be taken as a reasonable proxy for how PostgreSQL behaves when he performs these routine index builds. In all cases maintenance_work_mem was set to 64MB, meaning that an external tapesort is always required - those details were consistent. This was a table with 18 million rows. Apparently, on PostgreSQL 9.4, without abbreviation, the CREATE INDEX took 10 minutes and 19 seconds in total. On PostgreSQL 9.5, with identical settings, it took only 51.3 seconds - a 12x improvemen ## Migrating Sakila from MySQL to PostgreSQL DevFeed: [Migrating Sakila from MySQL to PostgreSQL]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2013-11-12T10:37:00Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [MySQL](), [Database](), [data]() Tags: [array](), [conference](), [conversion](), [database](), [enum](), [indexes](), [migrate](), [mysql](), [parallel](), [postgresql](), [stored-procedures](), [types]() ### AI overview This tutorial demonstrates migrating the Sakila example database from MySQL to PostgreSQL with pgloader. It covers schema discovery, data type casting, transformations, default values, parallel index building, and limitations involving application queries, triggers, stored procedures, and views. ### Source excerpt As presented at the PostgreSQL Conference Europe the new version of pgloader is now able to fully migrate a MySQL database, including discovering the schema, casting data types, transforming data and default values. Sakila is the traditional MySQL example database, in this article we're going to fully migrate it over to PostgreSQL. ## hstore vs. JSON - Which to use in Postgres DevFeed: [hstore vs. JSON - Which to use in Postgres]() Original publisher: [Read original article]() Author: Map Published: 2013-07-03T20:55:56Z Content type: comparison Language: en Sources: [Craig Kerstiens]() Topics: [JSON](), [Database](), [data](), [Structured-data]() Tags: [data](), [database](), [indexes](), [json](), [performance](), [postgres](), [postgresql](), [query](), [sql]() ### AI overview A comparison of PostgreSQL hstore and JSON for flexible data storage. It explains that hstore supports key-value data, indexing, and operators but stores only text and cannot nest objects, while JSON supports nested objects and non-text values. The article recommends JSON for storing existing JSON and hstore for flexible data models where performant querying is important. ### Source excerpt If you're deciding what to put in Postgres and what not to, consider that Postgres can be a perfectly good schema-less database. Of course as soon as people realized this then the common comes a question, is hstore or JSON better. Which do I use and in what cases. Well first, if you're not familiar check out some previous material on them: hstore on PostgresGuide hstore in Postgres docs hstore with Django JSON datatype JavaScript support in Postgres If you're already up to date with both of them, but still wondering which to use lets dig in. hstore hstore is a key value store directly within your database. Its been a common favorite of mine and has been for some time. hstore gives you flexibility when working with your schema, as you don't have to define models ahead of time. Though its two big limitations are that 1. it only deals with text and 2. its not a full document store meaning you can't nest objects. Though major benefits of hstore include the ability to index on it, robust support for various operators, and of course the obvious of flexibility with your data. Some of the basic operators available include: Return the value from columnfoo for key bar: foo->'bar' Does the specified column foo contain a key bar: foo?'bar' Does the specified column foo contain a value of baz for key bar: foo@>'bar->baz' Perhaps one of the best parts of hstore is that you can index on it. In particular Postgres gin and gist indexes allow you to index all keys and values within an hstore. A talk by Christophe Pettus of PgExperts actually highlights some performance details of hstore with indexes. To give away the big punchline in several cases hstore with gin/gist beats mongodb in performance. json JSON in contrast to hstore is a full document datatype. In addition to nesting objects you have support for more than just text (read numbers). As you insert JSON into Postgres it will automatically ensure its valid JSON and error if its well not. JSON gets a lot better come Postgres 9 [Next page]()