Postgres hidden gems
Postgres has a rich set of features, even when working everyday with it you may not discover all it has to offer. In hopes of learning some new features that I didn’t know about myself as well as seeing what small gems people found joy in I tweeted out to see what people came back from. The response was impressive, and rather than have it lost into ether of twitter I’m capturing some of the responses here along with some resources many of the features.
- @listrophy -
$ brew postgresql-update database- Though personally I prefer Postgres.app ;)
- @pat_shaugnessy - ltree
- Pat has a great post that walks through ltree
- @billyfung - citext
- A really handy datatype for case insensitive text
- @eeeebbbbrrrr - date math with intervals
- I couldn’t agree more on this one, working with time in Postgres is the easiest time I’ve every had
- @DataMiller - The jsonb datatype and lateral joins
- I’d argue it’s hard to claim now JSONB is a hidden gem, but lateral joins are certain a great one
- @ideasasylum - row_number() over(partition http://orders.site_id order by orders.created_at)
- Window functions are definitely a handy feature was my hidden (to me) discovery this week
- @franckverrot - Index access method, and custom FDWs
- @jonjensen0 - Set-returning functions and custom aggregate functions can be very helpful.
- @ascherbaum - psql -x
- Psql is indeed awesome and can be well tuned
- @Abstr_ct - The fact that the docs are fantastic and all hidden gems are actually readily available. Oh, and pl/brainfuck obviously
- @Halpin_IO - Subnetting and network operations
- @jkatz05 - Replication slots, both physical and logical. They’ve made setting up replication infinitely easier. And range types. Because they’re awesome.
- @petereisentraut - Unicode table borders
- @_avichalp Notify/listen
- @simonw - The fact that GIN indices can make LIKE queries run fast even if the % isn’t just at the end of the string
- @javisantana - it has a statistics system to plan queries that can be used by the user when accuracy does not matter, for example, use “explain select * from table” to replace count() or use “_postgis_selectivity” to know how many points fall into a bbox.
- @l_avrot - The fact that we can use vim editor in psql
- @mashd - Logical decoding for change data capture.
- @4thdoctor_scarf - the MVCC. If I had a penny per each time I’ve explained how really works, I’ll be a millionaire now :)
- @docteur_klein - \timing in psql
- @thibaut_barrere - From times to times I find foreign data wrapper with CSV files very helpful (& easy to setup with Ruby’s Sequel library) https://gist.github.com/thbar/0093ee54c5a61aa5a0c5a4737fc3bd45
- @steve_touw - Foreign data wrappers
- @roimartinez_gis - Clearly aggregate functions make live very simple :) .
- @whalesalad - select where datetime > yesterday and other natural language time queries.
- @pwramsey - At the hacker level: hooks. So many cool hooks, and finding them, a bit of an easter egg hunt.
- @pwramsey - At the user level: the quality and breadth of tsearch still feels radically under appreciated; same for ranges.
- @TomCiopp - PostGIS / PgRouting
- @peterbe - psql -l
- @jbrancha - In psql, setting ‘\x auto’ so that wide table results get displayed vertically!
- @westermanndanie -
\watch
Well that was quite the list. And I’m sure we’ve only scratched the surface. Have something not on the list that you feel like classifies as a hidden gem? Lets hear about it
If you’re looking for a deeper resource on Postgres I recommend the book The Art of PostgreSQL. It is by a personal friend that has aimed to create the definitive guide to Postgres, from a developer perspective. If you use code CRAIG15 you’ll receive 15% off as well.