I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions.
Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011.
I like bit of photography, cycling, poetry and I just love gadgets!!
Watch Ashnik’s Video Blog showcasing ‘Busting that slow running SQL’ Watch the recording of first ever Postgres Hangout and see how I took viewers through finding the slow running queries. The event is hosted by Ashnik. Follow @Ashnikbiz on Twitter to get latest updates of next Hangout.
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.
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
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
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
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
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
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!