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.html3. HSTORE datatype for storing non-relational data as key value pair -More details http://www.postgresql.org/docs/9.1/static/hstore.html4. 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 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, Ashnik DBA
  Singapore

  
  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

1

0

• Tweet 0
• Share 0
• +1 0
• LinkedIn 11