Postgres is our favorite database system. We’ve used Postgres with over 20 projects now and we still get amazed by how many hidden and great features Postgres has. No wonder, Postgres is world’s most advanced open source database! We’ll talk about some of the most powerful but rather lesser used features and aspects of Postgres in this post.4
Features of PostgreSQL:
- JSONB: This is an extension (now comes built in with every Postgres installation) which allows you to store JSON documents in a database table and query it natively. The performance is better than most documents based databases (looking at you, MongoDB). You can even put validations in the form of triggers when inserting/updating a JSONB column in a table. JSONB is so good that we’re planning to dedicate a blog post just for it. Stay tuned to learn more about this awesome extension.
Read Another Blog: How PostgreSQL is transforming the database landscape for good
- count() over() AKA Window functions: Sometimes when you’re paginating, you have to run 2 queries, one for the first n records, and another for the total count. It’s possible to get both in one query using OVER(). This is an example of much more extensive window functions which can used to aggregate results in a better way. (Read More)
- PostGIS: PostGIS is the goto extension for our GiS based applications. It’s battle tested and can perform almost every operation on spatial data. It supports both geography and geometry based calculations. We’ve built apps like BottomsUp which gives you all the pubs and bars which are near to you in a specified radius. Building location aware apps is now more simpler thanks to PostGIS!
- Foreign Data Wrappers: Postgres is also great at getting along with other database systems like Redis, MySQL, Oracle etc. Once setup, FDWs allows to query the externally connected database right from Postgres like a normal table. One can even JOIN Postgres tables and the connected database tables (or keys in Redis). This is similar to db_link which allows to JOIN tables from two different database is Postgres. We recently used FDWs in one of our projects to query Redis datastore right from our Postgres database. We used Redis to store things like view counts, session data etc.
- Using Vim in psql: Last but one of the most handy features of psql is editing in Vim. Vim is our favorite editor and pairing it with psql is just like icing on the cake. Using \e command in psql and setting the EDITOR shell environment variable, you can use Vim to edit large SQL queries and run them in psql.
Given how feature rich is Postgres, this looks like just a tip of an iceberg. There are, of course, many other features in Postgres which we haven’t covered yet. As Postgres community grows more strong, expect more such features which will make building complex and large applications quick and fast.