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.
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
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
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.
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
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
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!