25 Features your Developers are missing when not using Postgres!
Sameer Kumar I Senior Solution Architect, Ashnik
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 –
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
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 –
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.
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:
- 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.
- Would PostgreSQL leak your data? No! But YOU might!
- Integrating Docker Trusted Registry with Google Chat
- Chaos Engineering with Docker EE