# indexing Published articles for indexing. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## When JSONB columns create schema, consistency, and performance problems DevFeed: [When JSONB columns create schema, consistency, and performance problems]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-09-01T00:00:00Z Content type: article Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [data](), [Structured-data](), [JSON]() Tags: [data-modelling](), [indexing](), [jsonb](), [migration](), [outage](), [postgres](), [postgresql](), [query](), [schema](), [schema-design](), [toast]() ### AI overview The article explains why using JSONB to avoid recurring migrations can create hidden schema and data-consistency problems. It discusses runtime failures from inconsistent keys and types, difficulty identifying dependencies across consumers, and the storage and update costs of large PostgreSQL JSONB documents. ### Source excerpt JSONB is a good fit for genuinely open-ended data and a poor one for schema you did not want to commit to yet. Without a schema there is no NOT NULL, no type, no foreign key and no way to know which keys are load bearing, so every read becomes a parse and a cast that can fail at runtime. Large documents are stored out of line and compressed, which means reading one key can require fetching and decompressing the whole document, and updating one key rewrites all of it. ## Foreign keys are not overhead, and the thing you measured was a missing index DevFeed: [Foreign keys are not overhead, and the thing you measured was a missing index]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-08-30T00:00:00Z Content type: opinion Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [Database](), [integrity]() Tags: [database](), [database-performance](), [foreign-keys](), [indexing](), [integrity](), [overhead](), [postgres](), [postgresql](), [referential-integrity](), [schema-design]() ### AI overview The article explains that the measured cost of a slow parent delete is usually caused by a missing index on the child table's foreign-key column, not by foreign-key checking itself. It argues that dropping the constraint merely moves referential-integrity enforcement into application code and can allow orphaned records. ### Source excerpt A foreign key check is an index lookup against a primary key that is almost certainly already in cache, which is close to the cheapest thing a database does. The expensive case people measure is a parent delete with no index on the child's referencing column, which forces a full scan of the child table per deleted row, and that is a missing index rather than a cost of the constraint. Dropping the key does not remove the work, it moves the integrity guarantee into application code where nothing reports when it stops holding. ## 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. ## 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. ## Shazam finds songs by voting on time offsets, not by comparing audio DevFeed: [Shazam finds songs by voting on time offsets, not by comparing audio]() Original publisher: [Read original article]() Author: hello@ankit-rana.com Published: 2026-03-16T00:00:00Z Content type: tutorial Language: en Sources: [Ankit Rana | Mechanical Sympathy]() Topics: [Algorithm](), [hash](), [Databases]() Tags: [algorithm](), [algorithms](), [audio-fingerprinting](), [databases](), [hash](), [indexing](), [lookup](), [music-recognition](), [query](), [system-design]() ### AI overview The article explains how Shazam recognizes songs from short, noisy recordings. Instead of comparing audio similarity, it extracts spectrogram peaks, combines nearby peaks into hashes, and uses an inverted index to find tracks whose hash matches share a common time offset. The production system beyond the public 2003 paper is noted as unavailable. ### Source excerpt Shazam does not compare audio. It reduces each track to spectrogram peaks, pairs nearby peaks into ~32-bit hashes, and looks those up in an inverted index. A match is declared when many hashes from the sample agree on a single time offset into one track. The offset histogram is the whole trick: noise scatters offsets randomly, a real match stacks them into a spike. ## How Cursor Indexes Codebases Fast DevFeed: [How Cursor Indexes Codebases Fast]() Original publisher: [Read original article]() Author: Engineer's Codex Published: 2025-05-10T16:28:09Z Content type: article Language: en Sources: [Engineer's Codex]() Topics: [cursor](), [Software Engineering](), [Code](), [hash](), [Embeddings](), [structure](), [file](), [implementation]() Tags: [code](), [cursor](), [embeddings](), [hash](), [index](), [indexing](), [software-engineering]() ### AI overview This article explains how Cursor indexes codebases using Merkle trees. It describes local code chunking, hash-tree construction and synchronization with Cursor's server, followed by embedding generation through OpenAI's embedding API or a custom embedding model. ### Source excerpt Merkle Trees in the real world ## 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 ## PostgreSQL Data Types: JSON DevFeed: [PostgreSQL Data Types: JSON]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-04-30T07:49:33Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [JSON](), [PostgreSQL](), [data type](), [SQL]() Tags: [compatibility](), [data-type](), [functions](), [indexing](), [json](), [jsonb](), [postgresql](), [sql](), [types]() ### AI overview This tutorial introduces PostgreSQL's JSON and JSONB data types. It explains that JSON stores validated text, while JSONB uses a processed binary representation with operators, functions, indexing, and searching capabilities. The article recommends JSONB for most use cases and demonstrates differences through SQL queries. ### Source excerpt Continuing our series of PostgreSQL Data Types today we're going to introduce the PostgreSQL JSON type. PostgreSQL has built-in support for JSON with a great range of processing functions and operators, and complete indexing support. The documentation covers all the details in the chapters entitled JSON Types and JSON Functions and Operators. ## PostgreSQL Data Types: XML DevFeed: [PostgreSQL Data Types: XML]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-04-23T16:18:48Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [XML](), [data type](), [SQL](), [Databases]() Tags: [data-type](), [indexing](), [postgresql](), [sql](), [sql-database](), [sql-xml](), [stored-procedures](), [transformation](), [xml]() ### AI overview This article introduces PostgreSQL's XML data type and its SQL/XML support for storing and manipulating XML in a database. It also discusses PL/XSLT for XML processing and notes that PostgreSQL's XML processing and indexing capabilities are limited. ### Source excerpt Continuing our series of PostgreSQL Data Types today we're going to introduce the PostgreSQL XML type. The SQL standard includes a SQL/XML which introduces the predefined data type XML together with constructors, several routines, functions, and XML-to-SQL data type mappings to support manipulation and storage of XML in a SQL database, as per the Wikipedia page. ## PostgreSQL Data Types: Arrays DevFeed: [PostgreSQL Data Types: Arrays]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-04-20T12:47:25Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Databases]() Tags: [array-agg](), [arrays](), [data-type](), [dataset](), [indexing](), [postgresql](), [query](), [sql]() ### AI overview This tutorial introduces PostgreSQL array data types, explaining when arrays can replace lookup tables and how to work with array elements in SQL. It demonstrates importing geolocated tweets, extracting hashtag arrays, aggregating matches, and using GIN indexing to query array contents. ### Source excerpt Continuing our series of PostgreSQL Data Types today we're going to introduce the PostgreSQL array data types. Arrays can be used to denormalize data and avoid lookup tables. A good rule of thumb for using them that way is that you mostly use the array as a whole, even if you might at times search for elements in the array. Heavier processing is going to be more complex than a lookup table. ## PostgreSQL Data Types: Network Addresses DevFeed: [PostgreSQL Data Types: Network Addresses]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-04-16T10:32:53Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Network](), [function]() Tags: [functions](), [indexing](), [ipv4](), [ipv6](), [logs](), [network](), [postgresql]() ### AI overview This tutorial explains PostgreSQL network address types, including cidr, inet, and macaddr. It demonstrates loading Apache log data and using set_masklen() to analyze addresses under arbitrary CIDR network masks. ### Source excerpt Continuing our series of PostgreSQL Data Types today we're going to introduce network address types. PostgreSQL includes support for both cidr, inet, and macaddr data types. Again, those types are bundled with indexing support and advanced functions and operator support. ## Preventing Google from Indexing Staging Sites DevFeed: [Preventing Google from Indexing Staging Sites]() Original publisher: [Read original article]() Author: andrew@nystudio107.com (Andrew Welch) Published: 2017-02-02T09:00:00Z Content type: tutorial Language: en Sources: [nystudio107 | Articles on modern web development.]() Topics: [Search engine optimization (SEO)](), [Crawler](), [Content Management System](), [Web Development]() Tags: [config](), [diluting](), [environments](), [google](), [indexing](), [insights](), [multi-environment](), [prevent](), [robots-txt](), [seo](), [seomatic](), [sites](), [staging](), [value]() ### AI overview This tutorial explains how to prevent Google and other search engines from indexing staging sites. It presents robots.txt and SEOmatic as an alternative to password protection, allowing external performance and SEO testing while avoiding duplicate-content concerns. ### Source excerpt SEOmatic and a multi-environment config can prevent Google from indexing your staging sites, and diluting your SEO value ## PostgreSQL 9.4 - Looking up (with JSONB and logical decoding) DevFeed: [PostgreSQL 9.4 - Looking up (with JSONB and logical decoding)]() Original publisher: [Read original article]() Author: Map Published: 2014-03-24T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [JSON](), [Decoding](), [Database](), [Replication]() Tags: [decoding](), [indexing](), [jsonb](), [logical](), [postgres](), [postgresql]() ### AI overview This article examines PostgreSQL 9.4 features, focusing on JSONB and logical decoding. It explains how JSONB provides binary JSON document storage and how GIN indexes can automatically index document keys and values. The supplied text begins discussing logical decoding but is incomplete. ### Source excerpt Just a few weeks back I wrote a article discussing many of the things that were likely to miss making the 9.4 PostgreSQL release. Since that post a few weeks ago the landscape has already changed, and much more for the positive. The lesson here, is never count Postgres out. As Bruce discussed in a recent interview, Postgres is slow and steady, but much like the turtle can win the race. So onto the actual features: JSONB JSON has existed for a while in Postgres. Though the JSON that exists today simply validates that your text is valid JSON, then goes on to store it in a text field. This is fine, but not overly performant. If you do need some flexibility of your schema and performance without much effort then hstore may already work for you today, you can of course read more on this in an old post comparing hstore to json. But let's assume you do want JSON and a full document store, which is perfectly reasonable. Your option today is still best with the JSON datatype. And if you're retrieving full documents this is fine, however if you're searching/filtering on values within those documents then you need to take advantage of some functional indexing. You can do this some of the built-in operators or with full JS in Postgres. This is a little more work, but also very possible to get good performance. Finally, onto the perfect world, where JSON isn't just text in your database. For some time there's been a discussion around hstore and its future progress and of course the future of JSON in Postgres. These two worlds have finally heavily converged for PostgreSQL 9.4 giving you the best of both worlds. With what was known as hstore2, by The Russians under the covers, and collective efforts on JSONB (Binary representation of JSON) which included all the JSON interfaces you'd expect. We now have full document storage and awesome performance with little effort. Digging in a little further, why does it matter that its a binary representation? Well under the covers building o ## PostgreSQL FOSDEM 2014 DevFeed: [PostgreSQL FOSDEM 2014]() Original publisher: [Read original article]() Published: 2014-01-30T23:00:00Z Content type: article Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Extension](), [FOSDEM]() Tags: [advanced](), [auto-completion](), [contributors](), [developers](), [extensions](), [fosdem](), [geolocation](), [indexing](), [postgresql](), [practical](), [text](), [users]() ### AI overview A talk about advanced uses of PostgreSQL extensions, including practical solutions for geolocation and text indexing for auto-completion. ### Source excerpt This talk is about Advanced Uses Cases for PostgreSQL Extensions. It shows how to use extensions to solve practical problems such as geolocation or text indexing for auto-completion. ## Where to start with developer content DevFeed: [Where to start with developer content]() Original publisher: [Read original article]() Author: Map Published: 2014-01-21T20:55:56Z Content type: opinion Language: en Sources: [Craig Kerstiens]() Topics: [Publishing](), [Reddit](), [X (Twitter)]() Tags: [developer](), [developers](), [engagement](), [hacker-news](), [indexing](), [marketing](), [publishing](), [reddit](), [traffic](), [twitter]() ### AI overview The article discusses how developers and companies can distribute content beyond Hacker News. It compares Hacker News, other news sites, Twitter, and Google Plus, arguing that these channels differ in reach, shelf life, traffic quality, engagement, and indexing speed. ### Source excerpt Getting the word out Hacker News When it comes to marketing, specifically to developers, the most common question is how do I get on Hacker News? The second most commong is, well in addition to there what matters. This fully depends on your audience, and if you really only care about the former versus the broader issue of creating a sustainable model for circulating your content then just read these links then move on. If you want a full model for getting your content out there then keep reading. The best time to post to hacker news How to get on the front page of hacker news More than just Hacker News First off don't get me wrong, HN can provide a great surge of short term traffic. There's a few problems I have with this though. To begin with it gives you one shot to get your message perfect, if you have the wrong message, miss your call to action, or forget an affiliate link then you get your 15k viewers well you get no second shot at it. Though more interestingly and a bit of gut feel, I've noticed that traffic contains more bounes and lower engagement. And then theres the issue that its definitely not a science to getting on there.... Other news sites Of course theres other news sites, ones of relevance include reddit, dzone, monacle. I've found each of these can be similar in some ways to hacker news. Yet, they have a bit longer of a shelf life, giving me a slightly higher propensity to give them some attention. At the same time they also have a smaller reach. Twitter Twitter is yet another method that can work quite well. Observations of it include lower traffic than something like a hacker news, and equal to better engagement overall. Perhaps the most interesting piece I find is that you'll often get more intelligent and also positive discussion on twitter vs. HN. There is one huge fail I find with twitter that nearly every company commits. The author of the post or company twitter handle posts it, then 10 people proceed to retweet it within 10 minutes. The pro ## Postgres Indexing - A collection of indexing tips DevFeed: [Postgres Indexing - A collection of indexing tips]() Original publisher: [Read original article]() Author: Map Published: 2013-05-30T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Databases](), [SQL](), [Heroku Postgres](), [JOIN]() Tags: [heroku](), [heroku-postgres](), [indexes](), [indexing](), [information-schema](), [join](), [performance](), [postgres](), [postgres-performance](), [postgresql](), [query](), [schema](), [sql](), [stat](), [tips]() ### AI overview A collection of PostgreSQL indexing tips covering unused-index analysis, indexing costs, and the trade-offs between composite and separate indexes. It includes SQL examples and Heroku tooling for examining index usage. ### Source excerpt Even from intial reviews of my previous post on expression based indexes I received a lot of questions and feedback around many different parts of indexing in Postgres. Here's a mixed collection of valuable tips and guides around much of that. Unused Indexes In an earlier tweet I joked about some SQL that would generate the SQL to add an index to every column: # SELECT 'CREATE INDEX idx_' || table_name || '_' || column_name || ' ON ' || table_name || ' ("' || column_name || '");' FROM information_schema.columns; ?column? --------------------------------------------------------------------- CREATE INDEX idx_pg_proc_proname ON pg_proc ("proname"); CREATE INDEX idx_pg_proc_pronamespace ON pg_proc ("pronamespace"); CREATE INDEX idx_pg_proc_proowner ON pg_proc ("proowner"); The reasoning behind this is guessing whether an index will be helpful can be a bit hard within Postgres. So the easy solution is to add indexes to everything, then just observe if they're being used. Of course you want to add it to all tables/columns because you never know if core of Postgres may be missing some needed ones As included with the pg-extras plugin for Heroku you can run a query to show you all unused indexes. On Heroku simply install the plugin the run heroku pg:unused_indexes to show the size and number of times an index scan has been used. On a non Heroku Postgres database you can run: # SELECT schemaname || '.' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC; table | index | index_size | index_scans ---------------------+--------------------------------------------+------------+------------- public.grade_levels | index_placement_attempt ## Postgres Indexes - Expression/Functional Indexing DevFeed: [Postgres Indexes - Expression/Functional Indexing]() Original publisher: [Read original article]() Author: Map Published: 2013-05-29T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Query (disambiguation)](), [Code](), [data]() Tags: [code](), [count](), [function](), [functions](), [indexes](), [indexing](), [javascript](), [json](), [postgres](), [postgresql](), [reporting](), [sql](), [table]() ### AI overview A tutorial on PostgreSQL expression and functional indexes. It demonstrates indexing date expressions for reporting, using custom PLV8 functions to index values in JSON fields, and combining expressions with built-in functions for case-insensitive lookups. ### Source excerpt Postgres is rich with options for indexing. First you've got a variety of types, and beyond that you can do a variety of things with each of these such as create unique indexes, use conditions to index only a portion of your data, or create indexes based on complex expressions or functions. In cases where you commonly use various PostgreSQL functions in your application or reporting you can get some great gains from this. Let's take a look at a really simple case. Given a basic user table: # \dt users Table "public.users" Column | Type | Modifiers ------------+-----------------------------+----------- id | integer | not null email | character varying(255) | created_at | timestamp without time zone | You may commonly want to run a report against it showing your signups by date. Let's say you do this by running the query: SELECT count(*), date_trunc('day', created_at) FROM users GROUP BY 2; If you're commonly using date_trunc('day', created_at) for grouping, filtering, or projecting it out you can get some great gains by creating an index on this: # CREATE INDEX idx_user_created ON users(date_trunc('day', created_at)); Of course you can go beyond the built in functions of Postgres and use more complicated functions you create yourself. For example if you have JSON stored within PostgreSQL, have PLV8 enabled, and want to create a Javascript function to parse and return the text for a given key: # CREATE OR REPLACE FUNCTION get_text(key text, data json) RETURNS text $$ return data[key]; $$ LANGUAGE plv8 IMMUTABLE STRICT; Of note in the above function is IMMUTABLE and STRICT. Immutable specifies that the function given the same inputs will return the same result. Strict means that if you send in NULL values you'll get a null result. Given some example data inside your JSON field: { "name": "Craig Kerstiens", "location": "San Francisco", "numbers": [ { "type": "work", "number": "123.456.7890" }, { "type": "home", "number": "987.654.3210" } ] } If you wanted to return just ## Prefix GiST index now in 8.1 DevFeed: [Prefix GiST index now in 8.1]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2009-02-09T23:00:00Z Content type: release Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Extension](), [networking](), [Query (disambiguation)]() Tags: [extension](), [indexing](), [postgresql](), [query](), [routing]() ### AI overview The prefix extension adds support for prefix matching through PostgreSQL GiST indexes. Version 8.1 includes support code for a module version shipped by some distributions, including Red Hat Enterprise Linux 5.2. A user reported query times of 3-4 milliseconds over more than 200,000 rows, including complex fallback and carrier-priority queries. ### Source excerpt The prefix project is about matching a literal against prefixes in your table, the typical example being a telecom routing table. Thanks to the excellent work around generic indexes in PostgreSQL with GiST, indexing prefix matches is easy to support in an external module. Which is what the prefix extension is all about. Maybe you didn't come across this project before, so here's the typical query you want to run to benefit from the special indexing, where the @> operator is read contains or is a prefix of: