{"id":672,"date":"2012-03-26T09:55:29","date_gmt":"2012-03-26T16:55:29","guid":{"rendered":"https:\/\/redfindevelop.wpengine.com\/blog\/devblog\/?p=672"},"modified":"2020-10-05T13:12:26","modified_gmt":"2020-10-05T20:12:26","slug":"json_in_postgres","status":"publish","type":"post","link":"https:\/\/www.redfin.com\/news\/json_in_postgres\/","title":{"rendered":"JSON in Postgres"},"content":{"rendered":"<p><a href=\"https:\/\/redfin.com\/blog\/devblog\/wp-content\/uploads\/sites\/3\/2012\/03\/happy-elephant-011.jpg\"><img fetchpriority=\"high\" decoding=\"async\" class=\"alignright size-full wp-image-675\" style=\"margin-left: 20px;margin-right: 20px\" src=\"https:\/\/redfin.com\/blog\/devblog\/wp-content\/uploads\/sites\/3\/2012\/03\/happy-elephant-011.jpg\" alt=\"\" width=\"308\" height=\"450\" \/><\/a><\/p>\n<p><span>Since Dec 2011 we\u2019ve been storing a small portion of our data as JSON in Postgres. \u00a0This blog post gives a quick overview of why we decided to do this, how it works, and what we\u2019ve learned so far.<\/span><\/p>\n<p><strong>Why JSON in Postgres?<\/strong><\/p>\n<p>Redfin\u2019s basic stack includes a Java-based web app on top of a relational database. \u00a0We started with MySQL and <a href=\"https:\/\/redfin.com\/blog\/devblog\/2007\/11\/elephant_versus_dolphin_which_is_faster_which_is_smarter.html\">switched to Postgres<\/a> a few years ago.<\/p>\n<p>Overall we\u2019re happy with a relational DB, but it does have its pain points. \u00a0Perhaps the biggest issue is the difficulty involved in changing schema. \u00a0To change the shape of a table Postgres must take a table lock, which means that nobody can read or write to the table while the schema change is happening. \u00a0Some schema changes take little time to process (e.g. if the table is small or you\u2019re adding a nullable column). \u00a0But some schema changes can take a while to process, which translates to a lot of downtime. Various other people have written about this same problem before. To give just one example, here\u2019s what the folks at FriendFeed had to say about it: <a href=\"http:\/\/backchannel.org\/blog\/friendfeed-schemaless-mysql\">http:\/\/backchannel.org\/blog\/friendfeed-schemaless-mysql<\/a><\/p>\n<p>We had a situation where we wanted to store information about the various service providers in our new <a href=\"https:\/\/redfin.com\/blog\/2012\/02\/redfin_brings_transparency_to_title_inspection_mortgage_staging.html\">Open Book<\/a> directory. \u00a0We expected to iterate on the data model, and we wanted this to be easy. \u00a0So we decided to follow a similar approach to the one used by the folks at FriendFeed: to store data in our relational DB but in a semi-structured fashion.<\/p>\n<p>Aside: why not use a NoSQL store? \u00a0We considered this. \u00a0The main reason we decided against NoSQL (for now) is that it would require a lot more to deploy, monitor, and maintain. \u00a0Things that already work with our Postgres setup (backups, etc) would need to be setup anew. \u00a0Also, for this particular use-case we didn\u2019t need the scale that NoSQL offers, and we were hesitant about some of the trade-offs that often come with NoSQL (limitations in transactional support, queries you can pose, etc.).<\/p>\n<p>Postgres offers a few ways to manage semi-structured data:<\/p>\n<ul>\n<li>XML: <a href=\"http:\/\/www.postgresql.org\/docs\/9.1\/static\/datatype-xml.html\">http:\/\/www.postgresql.org\/docs\/9.1\/static\/datatype-xml.html<\/a><\/li>\n<li>Hashmap: <a href=\"http:\/\/www.postgresql.org\/docs\/9.1\/static\/hstore.html\">http:\/\/www.postgresql.org\/docs\/9.1\/static\/hstore.html<\/a><\/li>\n<li>Array: <a href=\"http:\/\/www.postgresql.org\/docs\/9.1\/static\/arrays.html\">http:\/\/www.postgresql.org\/docs\/9.1\/static\/arrays.html<\/a><\/li>\n<\/ul>\n<p>We were hoping to use JSON because it\u2019s more flexible than hashmaps or arrays, and because it\u2019s familiar &#8211; we use it all the time. \u00a0Unfortunately, Postgres doesn\u2019t (yet) offer built-in support for JSON. \u00a0It appears that one guy, Joey Addams, worked on adding JSON support to Postgres as a Google summer-of-code project back in 2010, but he didn\u2019t quite finish at the time. \u00a0Looks like he may finish soon: \u00a0<a href=\"http:\/\/stackoverflow.com\/questions\/4995945\/optimize-escape-json-in-postgresql-9-0\/5448248#5448248\">http:\/\/stackoverflow.com\/questions\/4995945\/optimize-escape-json-in-postgresql-9-0\/5448248#5448248<\/a>.<\/p>\n<p>In the meantime we developed our own strategy to store data as JSON in Postgres, to define indexes on facts inside the JSON, and to query it.<\/p>\n<p><strong>How does it Work?<\/strong><\/p>\n<p>Let\u2019s take a hypothetical example &#8211; you want to store a bunch of products (televisions, shoes&#8230;). \u00a0They will have some common facts (price) and also some facts that are unique to each type of product (televisions have a resolution, shoes have a size). \u00a0You suspect that you\u2019ll want to change the schema frequently, to add\/modify both the common facts and the custom facts.<\/p>\n<pre>create table products (\n   id    bigserial primary key,\n   json  text\n)\n\ninsert into products(json) values\n   ('{\"type\":\"television\", \"price\": 899.99, \"resolution\":\"1080p\"}'),\n   ('{\"type\":\"shoe\", \"price\": 74.99, \"size\":10.5}')\n;<\/pre>\n<p>As far as Postgres is concerned, the json column is a plain-old text column.<\/p>\n<p>Clearly you can fetch a product by ID:<\/p>\n<pre>select * from products where id=1;<\/pre>\n<p>But how do you query for products based on facts inside the JSON field? \u00a0Here\u2019s how we do it:<\/p>\n<pre>select * from products\n   where\n   jsonGet(json, '$.type') = 'television'\n   and jsonGet(json, '$.resolution') = '1080p';<\/pre>\n<p>What\u2019s going on here? \u00a0We\u2019re making use of a special function: jsonGet. \u00a0This is not a built-in function; it\u2019s one we added to Postgres, a custom function.\u00a0You can write custom Postgres functions in one of several <a href=\"http:\/\/www.postgresql.org\/docs\/9.1\/static\/xplang.html\">procedural languages<\/a>. We opted for Perl. Our function uses <a href=\"http:\/\/goessner.net\/articles\/JsonPath\/\">jsonpath<\/a>, which is like XML path &#8211; it\u2019s a simple language for identifying a portion of a JSON document.<\/p>\n<p>What about performance? \u00a0If we do nothing special, these queries will be very inefficient &#8211; Postgres will be forced to do a table scan and evaluate the jsonGet function on each record. \u00a0The way to avoid table scans is to add the appropriate indexes. \u00a0Here\u2019s how to add an index for all product prices:<\/p>\n<pre>create index products_price on products(jsonGet(json,'$.price'));<\/pre>\n<p>This is a functional index. \u00a0Whenever you create or update a record in the products table, Postgres will run the jsonGet function to extract the price fact from JSON and store just that fact inside the index. \u00a0If, later, you query by price using the same jsonGet function, Postgres will use the index and avoid the table scan. \u00a0In other words, you&#8217;re trading off some performance at create\/update-time in order to achieve better performance at query-time.<\/p>\n<p>As another example, here\u2019s how to add an index for TV resolutions:<\/p>\n<pre>create index products_television_resolutions\n   on products(jsonGet(json,'$.resolution'))\n   where jsonGet(json,'$.type') = 'television';<\/pre>\n<p>This is a partial index. \u00a0Postgres will only store records in this index for products of type &#8216;television&#8217;. \u00a0Similarly, Postgres will use this index for queries that include both of the jsonGet filters &#8211; the one on type and the one on resolution.<\/p>\n<p>So far I described how it works at the DB-level. \u00a0There\u2019s more to say about how this works in our Java layer, but in the interest of brevity I\u2019ll leave that discussion out of this blog post (if you\u2019re interested, leave a note in the comments below).<\/p>\n<p><strong>So&#8230;does it Work? \u00a0Do we like it?<\/strong><\/p>\n<p>Yes, it does work! \u00a0We\u2019ve been using this technique since December to track information about service providers in our Open Book directory. \u00a0The quantity of data is still modest (in the low 10\u2019s of thousands of records) but we haven\u2019t seen any major gotchas.<\/p>\n<p>Is this a silver bullet? \u00a0No, I wouldn\u2019t say that. \u00a0This strategy has pluses and minuses.<\/p>\n<p>The biggest benefit is that schema changes are generally easier. \u00a0Changing the shape of our JSON data doesn\u2019t feel like a big scary thing. \u00a0We follow the <a href=\"http:\/\/en.wikipedia.org\/wiki\/Don't_repeat_yourself\">DRY principal<\/a> &#8211; instead of defining the data model both in SQL and in Java, we define it just once, in Java. \u00a0It\u2019s very easy to change the Java code to add a new field. \u00a0It\u2019s also quick to change the Java code to modify or remove a field, but those changes are definitely trickier &#8211; our app code now needs to be smart enough to deal with records of various shapes, both old and new.<\/p>\n<p>The JSON format gives us the freedom to collapse one-to-many relationships into a single record (denormalize). \u00a0This isn\u2019t always a good thing to do, but where appropriate this strategy allows us to skip a join and achieve better performance.<\/p>\n<p>Finally, this solution required less work than deploying a new unfamiliar NoSQL solution, and allowed us to maintain full transactional support.<\/p>\n<p>The minuses are:<\/p>\n<p>1. There\u2019s still more infrastructure. \u00a0We had to deploy plperl, json, and jsonpath libraries to our DB servers. \u00a0It took some tinkering to figure this out (most recently, we ran into issues with plperl on <a href=\"http:\/\/stackoverflow.com\/questions\/8674995\/has-anyone-gotten-plperl-to-work-with-postgres-9-1-on-windows\">Postgres 9.1 on Windows<\/a>).<\/p>\n<p>2. This is an unfamiliar technique. \u00a0Again, it\u2019s not as radically different as a full-on transition to NoSQL, but it still takes more ramp-up time for other developers to figure out how to query using jsonpath.<\/p>\n<p>3. This strategy doesn\u2019t jive too well with our Java layer. \u00a0I didn\u2019t talk much about our Java layer, but the short story is that we use <a href=\"http:\/\/en.wikipedia.org\/wiki\/Hibernate_(Java)\">hibernate<\/a>. \u00a0I can\u2019t say we love everything about hibernate, but it\u2019s our standard, and it does make it easy to work with tables and relationships. \u00a0For example, if you have a customers table and a real_estate_agents table, then hibernate maps these records to Java objects and gives you a simple way to walk the relationship from one object to another: customer.getRealEstateAgent(). \u00a0This just doesn\u2019t work when you have rich data stored as JSON inside fat columns &#8211; hibernate doesn\u2019t understand the make-up of these JSON columns. \u00a0So, you, the developer, are forced to crack open the JSON, find the ID of the related object, and query for it separately. \u00a0It\u2019s all doable, but it leads to longer code.<\/p>\n<p><strong>What do you think?<\/strong><\/p>\n<p>Do you use a relational database? Have you struggled with schema changes? Have you tried storing semi-structued data in a relational DB? Did it work out? Or did you choose to switch to a non-relational store? We\u2019re interested to know &#8211; please leave your thoughts in the comments below.<\/p>\n<p><strong>Source for jsonGet<\/strong><\/p>\n<p>In case it\u2019s useful, here\u2019s the source code of our jsonGet function:<\/p>\n<pre>create or replace function jsonGet(text,text) returns text as\n$$\n\tmy ($json_text,$json_path_text) = @_;\n\n\tuse JSON;\n\t$json = JSON-&gt;new-&gt;allow_nonref;\n\t$json_hash = $json-&gt;decode($json_text);\n\n\tuse JSONPath;\n\tmy $jpath = JSONPath-&gt;new();\n\tmy $result_array = $jpath-&gt;run($json_hash, $json_path_text);\n\n\t# If there is a single item in the result array,\n\t# just format that item and return it\n\t# Otherwise, format the whole array\n\n\t$length = @{$result_array};\n\n\tmy $formatted_result;\n\tif ($length == 1) {\n\t\t$formatted_result = $json-&gt;encode($result_array-&gt;[0]);\n\t\t# strip quotes\n\t\t$formatted_result =~ s\/^\"(.*)\"$\/$1\/;\n\t} else {\n\t\t$formatted_result = $json-&gt;encode($result_array);\n\t}\n\treturn $formatted_result;\n$$\nlanguage plperlu immutable;<\/pre>\n<p><strong>Kudos<\/strong><\/p>\n<p>Kudos to <a href=\"https:\/\/github.com\/masukomi\/jsonpath-perl\">masukomi<\/a> for his jsonPath implementation!<\/p>\n<p>Also kudos to <a href=\"http:\/\/benscheirman.com\/2010\/06\/installing-postgresql-for-rails-on-mac-os-x\">Ben Scheirman<\/a> for the Happy Elephant photo \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Since Dec 2011 we\u2019ve been storing a small portion of our data as JSON in Postgres. \u00a0This blog post gives a quick overview of why we decided to do this, how it works, and what we\u2019ve learned so far. Why JSON in Postgres? Redfin\u2019s basic stack includes a Java-based web app on top of a [&hellip;]<\/p>\n","protected":false},"author":13478,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[57],"tags":[],"dashboard":[],"coauthors":[],"class_list":["post-672","post","type-post","status-publish","format-standard","hentry","category-company-news"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v24.7 (Yoast SEO v27.6) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>JSON in Postgres - Redfin Real Estate News<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.redfin.com\/news\/json_in_postgres\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"JSON in Postgres\" \/>\n<meta property=\"og:description\" content=\"Since Dec 2011 we\u2019ve been storing a small portion of our data as JSON in Postgres. \u00a0This blog post gives a quick overview of why we decided to do this, how it works, and what we\u2019ve learned so far. Why JSON in Postgres? Redfin\u2019s basic stack includes a Java-based web app on top of a [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.redfin.com\/news\/json_in_postgres\/\" \/>\n<meta property=\"og:site_name\" content=\"Redfin Real Estate News\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/redfin\" \/>\n<meta property=\"article:published_time\" content=\"2012-03-26T16:55:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-05T20:12:26+00:00\" \/>\n<meta name=\"author\" content=\"Shahaf Abileah\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@redfin\" \/>\n<meta name=\"twitter:site\" content=\"@redfin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Shahaf Abileah\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/\"},\"author\":{\"name\":\"Shahaf Abileah\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#\\\/schema\\\/person\\\/603fb52f159bd91f911df1e786fee6ba\"},\"headline\":\"JSON in Postgres\",\"datePublished\":\"2012-03-26T16:55:29+00:00\",\"dateModified\":\"2020-10-05T20:12:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/\"},\"wordCount\":1472,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/redfin.com\\\/blog\\\/devblog\\\/wp-content\\\/uploads\\\/sites\\\/3\\\/2012\\\/03\\\/happy-elephant-011.jpg\",\"articleSection\":[\"Company News\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/#respond\"]}],\"copyrightYear\":\"2012\",\"copyrightHolder\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#organization\"}},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/\",\"url\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/\",\"name\":\"JSON in Postgres - Redfin Real Estate News\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/redfin.com\\\/blog\\\/devblog\\\/wp-content\\\/uploads\\\/sites\\\/3\\\/2012\\\/03\\\/happy-elephant-011.jpg\",\"datePublished\":\"2012-03-26T16:55:29+00:00\",\"dateModified\":\"2020-10-05T20:12:26+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/#primaryimage\",\"url\":\"https:\\\/\\\/redfin.com\\\/blog\\\/devblog\\\/wp-content\\\/uploads\\\/sites\\\/3\\\/2012\\\/03\\\/happy-elephant-011.jpg\",\"contentUrl\":\"https:\\\/\\\/redfin.com\\\/blog\\\/devblog\\\/wp-content\\\/uploads\\\/sites\\\/3\\\/2012\\\/03\\\/happy-elephant-011.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/json_in_postgres\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.redfin.com/news\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"JSON in Postgres\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#website\",\"url\":\"https:\\\/\\\/www.redfin.com/news\\\/\",\"name\":\"Redfin Real Estate News\",\"description\":\"The latest real estate news and research from technology-powered residential real estate company, Redfin.\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.redfin.com/news\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#organization\",\"name\":\"Redfin\",\"url\":\"https:\\\/\\\/www.redfin.com/news\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.redfin.com\\\/news\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/Redfin-News-Logo.png\",\"contentUrl\":\"https:\\\/\\\/www.redfin.com\\\/news\\\/wp-content\\\/uploads\\\/2020\\\/10\\\/Redfin-News-Logo.png\",\"width\":1100,\"height\":235,\"caption\":\"Redfin\"},\"image\":{\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/redfin\",\"https:\\\/\\\/x.com\\\/redfin\",\"https:\\\/\\\/www.instagram.com\\\/redfinrealestate\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/redfin\",\"https:\\\/\\\/www.pinterest.com\\\/redfin\\\/\",\"https:\\\/\\\/en.wikipedia.org\\\/wiki\\\/Redfin\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/#\\\/schema\\\/person\\\/603fb52f159bd91f911df1e786fee6ba\",\"name\":\"Shahaf Abileah\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.redfin.com/news\\\/wp-content\\\/uploads\\\/2025\\\/06\\\/Redfin-2025-Logo-B-150x150.jpgb98d1f204d143aab6d4240c534a1657e\",\"url\":\"https:\\\/\\\/www.redfin.com/news\\\/wp-content\\\/uploads\\\/2025\\\/06\\\/Redfin-2025-Logo-B-150x150.jpg\",\"contentUrl\":\"https:\\\/\\\/www.redfin.com/news\\\/wp-content\\\/uploads\\\/2025\\\/06\\\/Redfin-2025-Logo-B-150x150.jpg\",\"caption\":\"Shahaf Abileah\"},\"url\":\"https:\\\/\\\/www.redfin.com/news\\\/author\\\/shahaf-abileahredfin-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"JSON in Postgres - Redfin Real Estate News","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.redfin.com\/news\/json_in_postgres\/","og_locale":"en_US","og_type":"article","og_title":"JSON in Postgres","og_description":"Since Dec 2011 we\u2019ve been storing a small portion of our data as JSON in Postgres. \u00a0This blog post gives a quick overview of why we decided to do this, how it works, and what we\u2019ve learned so far. Why JSON in Postgres? Redfin\u2019s basic stack includes a Java-based web app on top of a [&hellip;]","og_url":"https:\/\/www.redfin.com\/news\/json_in_postgres\/","og_site_name":"Redfin Real Estate News","article_publisher":"https:\/\/www.facebook.com\/redfin","article_published_time":"2012-03-26T16:55:29+00:00","article_modified_time":"2020-10-05T20:12:26+00:00","author":"Shahaf Abileah","twitter_card":"summary_large_image","twitter_creator":"@redfin","twitter_site":"@redfin","twitter_misc":{"Written by":"Shahaf Abileah","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/#article","isPartOf":{"@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/"},"author":{"name":"Shahaf Abileah","@id":"https:\/\/www.redfin.com\/news\/#\/schema\/person\/603fb52f159bd91f911df1e786fee6ba"},"headline":"JSON in Postgres","datePublished":"2012-03-26T16:55:29+00:00","dateModified":"2020-10-05T20:12:26+00:00","mainEntityOfPage":{"@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/"},"wordCount":1472,"commentCount":0,"publisher":{"@id":"https:\/\/www.redfin.com\/news\/#organization"},"image":{"@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/#primaryimage"},"thumbnailUrl":"https:\/\/redfin.com\/blog\/devblog\/wp-content\/uploads\/sites\/3\/2012\/03\/happy-elephant-011.jpg","articleSection":["Company News"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.redfin.com\/news\/json_in_postgres\/#respond"]}],"copyrightYear":"2012","copyrightHolder":{"@id":"https:\/\/www.redfin.com\/news\/#organization"}},{"@type":"WebPage","@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/","url":"https:\/\/www.redfin.com\/news\/json_in_postgres\/","name":"JSON in Postgres - Redfin Real Estate News","isPartOf":{"@id":"https:\/\/www.redfin.com\/news\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/#primaryimage"},"image":{"@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/#primaryimage"},"thumbnailUrl":"https:\/\/redfin.com\/blog\/devblog\/wp-content\/uploads\/sites\/3\/2012\/03\/happy-elephant-011.jpg","datePublished":"2012-03-26T16:55:29+00:00","dateModified":"2020-10-05T20:12:26+00:00","breadcrumb":{"@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.redfin.com\/news\/json_in_postgres\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/#primaryimage","url":"https:\/\/redfin.com\/blog\/devblog\/wp-content\/uploads\/sites\/3\/2012\/03\/happy-elephant-011.jpg","contentUrl":"https:\/\/redfin.com\/blog\/devblog\/wp-content\/uploads\/sites\/3\/2012\/03\/happy-elephant-011.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.redfin.com\/news\/json_in_postgres\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.redfin.com\/news\/"},{"@type":"ListItem","position":2,"name":"JSON in Postgres"}]},{"@type":"WebSite","@id":"https:\/\/www.redfin.com\/news\/#website","url":"https:\/\/www.redfin.com\/news\/","name":"Redfin Real Estate News","description":"The latest real estate news and research from technology-powered residential real estate company, Redfin.","publisher":{"@id":"https:\/\/www.redfin.com\/news\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.redfin.com\/news\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.redfin.com\/news\/#organization","name":"Redfin","url":"https:\/\/www.redfin.com\/news\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.redfin.com\/news\/#\/schema\/logo\/image\/","url":"https:\/\/www.redfin.com\/news\/wp-content\/uploads\/2020\/10\/Redfin-News-Logo.png","contentUrl":"https:\/\/www.redfin.com\/news\/wp-content\/uploads\/2020\/10\/Redfin-News-Logo.png","width":1100,"height":235,"caption":"Redfin"},"image":{"@id":"https:\/\/www.redfin.com\/news\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/redfin","https:\/\/x.com\/redfin","https:\/\/www.instagram.com\/redfinrealestate\/","https:\/\/www.linkedin.com\/company\/redfin","https:\/\/www.pinterest.com\/redfin\/","https:\/\/en.wikipedia.org\/wiki\/Redfin"]},{"@type":"Person","@id":"https:\/\/www.redfin.com\/news\/#\/schema\/person\/603fb52f159bd91f911df1e786fee6ba","name":"Shahaf Abileah","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.redfin.com\/news\/wp-content\/uploads\/2025\/06\/Redfin-2025-Logo-B-150x150.jpgb98d1f204d143aab6d4240c534a1657e","url":"https:\/\/www.redfin.com\/news\/wp-content\/uploads\/2025\/06\/Redfin-2025-Logo-B-150x150.jpg","contentUrl":"https:\/\/www.redfin.com\/news\/wp-content\/uploads\/2025\/06\/Redfin-2025-Logo-B-150x150.jpg","caption":"Shahaf Abileah"},"url":"https:\/\/www.redfin.com\/news\/author\/shahaf-abileahredfin-com\/"}]}},"_links":{"self":[{"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/posts\/672","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/users\/13478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/comments?post=672"}],"version-history":[{"count":0,"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/posts\/672\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/media?parent=672"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/categories?post=672"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/tags?post=672"},{"taxonomy":"dashboard","embeddable":true,"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/dashboard?post=672"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.redfin.com\/news\/wp-json\/wp\/v2\/coauthors?post=672"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}