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!
