No Comments

25 Features your Developers are missing when not using Postgres!

Sameer Kumar I Senior Solution Architect, Ashnik
Singapore, 5 Jun 2014
Sameer_Ashnik

by , , No Comments

5-Jun-2014

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!

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

  • Sameer Kumar is the Database Solution Architect working with Ashnik. Sameer and his team works towards providing enterprise level solutions based on open source and cloud technologies. He has an experience in working on various roles ranging from development to performance tuning and from installation and migration to support and services roles.

More From Sameer Kumar I Senior Solution Architect, Ashnik :
5-Jun-2014