Tuesday, September 10, 2013

New features in Postgres 9.3 (just released)

New features in PostgreSQL 9.3

I'm particularly excited about materialized views - a very useful feature I've used in Oracle for years. It looks like Postgres MVs will only have a fraction of the Oracle functionality, but hey it's a start!

Updatable views: also very nice. This feature can be useful when you have some tables that you want to make "private" and only expose a view based on them. An example off the top of my head... Maybe you have an archive_purchase table and current_purchase table - kind of like partitions - probably current_purchase is lean and mean and archive_purchase is old, huge and fat. You have some page in your web app that lets the user view and edit purchases from any time, so you make the view all_purchase that UNIONs the two. Now you simply query and update all_purchase without having to worry about where the underlying row comes from.

...And many more nice-looking new features.

100x faster Postgres performance by changing 1 line

From Datadog: 100x faster Postgres performance by changing 1 line

TL;DR: To check in a large list of values, use ANY(VALUES(...)) instead of ANY(ARRAY[...])

If this is a consistent issue, hopefully it's something Postgres can better optimize in the query planner.