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: 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 than 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 think I missed any features, please help me update this list, below. Thanks in advance!

– Sameer Kumar I Senior Solution Architect, Ashnik

Read other articles in the Newsletter:

Capture and Consolidate: Emerging priorities for Enterprises – Sachin Dabir

Guest Article: It’s Not Your Database That’s Causing Hair Loss! – Marc Linster, Senior VP, Products & Services – EDB

Building and sustaining a Partner Ecosystem – Kaustubh Patwardhan

---

• Would PostgreSQL leak your data? No! But YOU might!
• Integrating Docker Trusted Registry with Google Chat
• Chaos Engineering with Docker EE