---
title: "This Week's PostgreSQL News (#200)"
publication: "Postgres Weekly"
issue: 200
published_at: "2017-03-29"
canonical_url: "https://postgresweekly.com/issues/200"
---

Postgres Weekly 200   . 

[Read this on the Web](http://postgresweekly.com/issues/200) 

[Postgres Weekly](http://postgresweekly.com) Issue 200 — March 29, 2017      

**It's issue 200!** To celebrate, we've dug through our 4 years of archives and found some popular posts that are still interesting today. Enjoy them in the second half of this issue - here's to 200 more :-) Thanks for your support. 

[Why Does A JSON Element Return Text When It's Not Text?](http://dba.stackexchange.com/questions/168029/why-does-a-json-element-return-text-when-its-not-text?utm_source=postgresweekly&utm_medium=email)JSON and JSONB are really powerful when working with Postgres, but it doesn’t mean they’re not without their quirks. Here we look at why text is typically the return value. Database Administrators Stack Exchange [How We Implement Disaster Recovery and High Availability with Postgres on Citus Cloud](https://www.citusdata.com/blog/2017/03/23/a-look-into-disaster-recovery-and-high-availability-and-how-they-work-with-postgres-on-citus-cloud/?utm_source=postgresweekly&utm_medium=email)Disaster recovery and high availability are key in running and managing your database, whether on your own or as-a-service disaster [can strike](https://about.gitlab.com/2017/02/01/gitlab-dot-com-database-incident/) and you need to be prepared. Here’s a primer on what each is and how Citus Cloud implements both. Citus Data [Postgres-XL: MPP and Horizontally Scalable PostgreSQL](https://2ndquadrant.com/en/resources/postgres-xl/)Postgres-XL is a Massively Parallel Database built on top of, and very closely compatible with PostgreSQL 9.5. It's flexible enough to handle simultaneous OLAP & OLTP workloads and is designed to be Horizontally Scalable. 2ndQuadrant PostgreSQL Products#sponsored[![\](https://copm.s3.amazonaws.com/c2d3cb64.jpg)](https://2ndquadrant.com/en/resources/postgres-xl/) [Tgres: A Go and PostgreSQL-Powered Time Series Data Integrator](https://grisha.org/blog/2017/03/22/tgres-0-dot-10-dot-0b-time-series-with-go-and-postgresql/?utm_source=postgresweekly&utm_medium=email)A Go program which receives time series data via Graphite, Statsd, or even a Web pixel, stores it in PostgreSQL, and provides Graphite-like access to the data. Gregory Trubetskoy#tools [pgDevOps: A Web Console for Managing Postgres Servers](https://bitbucket.org/openscg/pgdevops?utm_source=postgresweekly&utm_medium=email)If you need a turnkey admin console for managing Postgres, PgDevOps may help. It’s a multi-user and multi-server Postgres web console for devops that features integration with pgAdmin4. Dvir Volk#tools [PipelineDB 0.9.7: Delta Streams & Towards a Postgres Extension](https://www.pipelinedb.com/blog/pipelinedb-0-9-7-delta-streams-and-towards-a-postgresql-extension?utm_source=postgresweekly&utm_medium=email)PipelineDB allows you to turn Postgres into a streaming database, but perhaps most exciting is it’s moving towards becoming an _extension_ in future. Derek Nelson [Postgres vs MySQL - Which Should You Pick As Your Next Analytical Database?](http://www.silota.com/docs/getting-started/postgres-vs-mysql-database-analytics.html?utm_source=postgresweekly&utm_medium=email)While the debate may be open for which OLTP database you want to use, Postgres has a pretty clear advantage over MySQL when it comes to analytics. It’s no surprise there’s a number of forks to make it even better as an analytical database with these features. Ganesh Swami [Faster Operations with the JSONB Data Type in PostgreSQL](https://compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/?utm_source=cooper&utm_medium=newsletter&utm_content=postgresql)Guest writer Lucero Del Alba takes a look at how to get better performance out of JSONB data types in Postgres. Compose#sponsored 

Gold from the archives

[Using PostgreSQL Arrays The Right Way (2014)](https://blog.heapanalytics.com/dont-iterate-over-a-postgres-array-with-a-loop/?utm_source=postgresweekly&utm_medium=email)Heap is clearly a power user of [Postgres](https://www.youtube.com/watch?v=NVl9_6J1G60) had some fantastic Postgres blog posts in 2014. You can also still enjoy [Speeding Up Postgres with Partial Indexes](https://blog.heapanalytics.com/speeding-up-postgresql-queries-with-partial-indexes/?utm_source=postgresweekly&utm_medium=email) and [Creating Postgres Arrays Without A Quadratic Blowup](https://blog.heapanalytics.com/creating-postgresql-arrays-without-a-quadratic-blowup/?utm_source=postgresweekly&utm_medium=email). Heap Blog [Writing Postgres Extensions - The Basics (2015)](http://big-elephants.com/2015-10/writing-postgres-extensions-part-i/?utm_source=postgresweekly&utm_medium=email)Postgres extensions can be extremely powerful from [geospatial](http://postgis.net/) to [sharding](https://www.citusdata.com/) and here’s a great guide to getting started writing your own. Big Elephants [Finding the Nearest Big City (2013)](http://tapoueh.org/blog/2013/05/02-nearest-big-city.html?utm_source=postgresweekly&utm_medium=email)Postgres has 7 different index types and continues to grow. Here’s a real world example of using KNN (K-Nearest Neighbor) indexes for finding the nearest big city or nearest big ‘X’ given some set of conditions. Dimitri Fontaine [Window Functions in Action (2013)](http://blog.endpoint.com/2013/06/window-functions-in-action.html?utm_source=postgresweekly&utm_medium=email)Window functions are in the intermediate to power user territory, but key to reporting with Postgres. This post, is still a classic of not only using window functions but also combining them with common table expressions, arrays, and composite types. Joshua Tolley [Trees and Paths using WITH RECURSIVE in PostgreSQL (2014)](http://blog.databasepatterns.com/2014/02/trees-paths-recursive-cte-postgresql.html?utm_source=postgresweekly&utm_medium=email)CTEs can be incredibly powerful and expressive, not just for [readability](http://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-not-using/) within Postgres, allowing you to deliver recursive queries without plpgsql or another pl language. Neil McGuigan [UUID vs BIGSERIAL for Primary Keys (2015)](http://thebuild.com/blog/2015/10/08/uuid-vs-bigserial-for-primary-keys/?utm_source=postgresweekly&utm_medium=email) The Build     

Postgres Weekly is curated by [Craig Kerstiens](https://twitter.com/craigkerstiens) [Read this issue on the Web](http://postgresweekly.com/issues/200) [See our archives](http://postgresweekly.com/issues) [Change your email address](*|UPDATE_PROFILE|*)  [Stop getting this newsletter](*|UNSUB|*)  

Published by [Cooperpress](https://cooperpress.com/)
Fairfield Enterprise Centre, Lincoln Way, Louth, LN11 0LS, UK