25 Features your Developers are missing when not using Postgres

I recently met a few prospects who were keen to know what Postgres can offer to their developers which they weren’t already getting while using the contemporary databases. So I thought of putting together a list.

Though the list refers to features of Postgres Plus Advanced Server, lot of these features are applicable for PostgreSQL too. I have found great resources and documentation for “how to use” references for most of these features and hence I am not going to repeat that here. Let me share the list and some quick reference/user guides for each of them.

1.       JSON Datatype for storing non-relational data in JSON document- More details http://www.postgresql.org/docs/9.3/static/datatype-json.html

2.       JSON Functions to convert relational data into JSON format- More details http://www.postgresql.org/docs/9.3/static/functions-json.html

3.       HSTORE datatype for storing non-relational data as key value pair- More details http://www.postgresql.org/docs/9.1/static/hstore.html

4.       Range Datatype- DATE Range and Integer Range for storing RANGE [minValue-maxValue] together instead of having to store minValue and maxValue in separate columns- For More details- http://www.postgresql.org/docs/9.2/static/rangetypes.html

5.       Boolean Datatype: Support for Boolean as datatype for columns- For more details- http://www.postgresql.org/docs/9.3/static/datatype-boolean.html

6.       Support for TIME datatype- As far as I remember TIME data-type was not present in DB2 (supported by Oracle)

7.       Infinite Cache for Application Performance- Exclusive to EnterpriseDB Postgres Plus

8.       Handling and Playing with Time Series Data is much more simpler and easier in PostgreSQL: http://no0p.github.io/postgresql/2014/05/08/timeseries-tips-pg.html

9.       Rich Foriegn Data Wrapper Support for querying form various different sources as foriegn table- More details here: http://wiki.postgresql.org/wiki/Foreign_data_wrappers

10.   UNLOGGED Tables: You can create unlogged tables which does not have WAL (redo-log equivalent) overhead for write operations.

11.   Serializable Snapshot Isolation: SSI provides features of Serializable transaction but without the performance impacts More details: http://wiki.postgresql.org/wiki/SSI and http://wiki.postgresql.org/wiki/Serializable

12.   NOT VALID Constraints: Add a check constraint/FK for new incoming data but ignore any inconsistency in existing data. This will make adding constraints faster. Later, you can clean-up the data and validate the constraint

13.   Filter Based Indexes: You can index a particular column based on some filter. This can be useful for indexing columns based on criteria e.g. index on employee_table(dept) where hasLeft=false

14.   Create Recursive Views using shorthand syntax - More details here: https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3#Recursive_View_Syntax

15.   Define sort memory for each query- Gives you flexibility to assign more resources for one query which takes huge resources. You can define work_mem at user, transaction, query/statement or at DB level.

16.   LATERAL JOIN: A LATERAL JOIN enables a subquery in the FROM part of a clause to reference columns from preceding items in the FROM list. More details: https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3#LATERAL_JOIN

17.   Define User defined Operators- Flexibility to define user defined operators makes it easy to define your own operators. e.g while migrating from SQL Server one can define a new Operator '+' with two string operands which would work similar to concatenation operator of SQL Server (+).

18.   Rich Language Support: Create user defined objects in Pl/pgSQL, PL/SQL, pl/perl, pl/python, pl/java etc. Your developers can choose a language of their choice for implementing the Stored Procedure

19.   IS NOT NULL queries can make use of indexes- I have seen RDBMS which boasts about its optimizer failing to do so

20.   GRANT/REVOKE access on all tables of a particular schema- More general thatn grant access on each table but at the same time more granular than "SELECT ANY TABLE" and similar privileges

21.   LIMIT and OFFSET features- Limit the number of rows in your output using LIMIT keyword. You can further set an OFFSET from where to start fetching using OFFSET

22.   Granular security and logging: Set User privileges and logging parameter per user/role

23.   Full Text Search Features- More details here- http://www.postgresql.org/docs/9.3/static/textsearch.html

24.   Use indexes for getting statistics on the fly- This feature can help you get a better plan when you are reading data from a table which has been loaded with bulk data recently

25.   COPY JDBC API: Use COPY API to do bulk load into table via JDBC. Check my earlier post on this- http://pgpen.blogspot.com/2013/05/using-copy-in-your-jdbc-code.html

Remember, I have not even added the awesome new features which we will see in PostgreSQL v9.4 [https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.4].

If you are developer who is not using PostgreSQL then now you know what you have missing all along. If you are a CIO/IT Manager or Product Manager then you know what great features your developers have been deprived off. What are you waiting for? 

If you are a happy postgreSQL user and you think I missed any features, please help me update this list. Thanks in advance!