Let me just take a moment to point out how important row-level security is as a concept. That features allows one to create essentially a secure analytics data model by tying security business logic into the values of a table. For every query, just join to that security table, and now the database is flexible and secured.<p>Example:<p>Tie a salesperson to an order placed, and use the order table as the primary fact table. For all reporting and visualizations that can be tied to orders, all one has to do is incorporate an association to that table. Worrying about which customers, products, time periods, etc. a salesperson can see are automatically handled by that association.<p>I am not saying that PostresSQL implements what I am describing, but this example can be expanded by creating an intermediate table with many-to-many associations. E.g., this table might have one row for each salesperson's access and one row for each salesperson under a supervisor. Once again, a centralized location for controlling access throughout the entire analytical data model. It is a very useful tool that I have relied upon extensively.
BRIN (Block Range) Indices look really interesting.<p>Instead of storing the whole B-Tree (and spending time updating it) just store summary of ranges (pages).<p>This for example, would be great for a time series database that is write heavy but not read as often.<p>I found these benchmarks here explaining the differences:<p><a href="http://www.depesz.com/2014/11/22/waiting-for-9-5-brin-block-range-indexes/" rel="nofollow">http://www.depesz.com/2014/11/22/waiting-for-9-5-brin-block-...</a><p>---<p>Creating 650MB tables:<p><pre><code> * btree: 626.859 ms.
* brin: 208.754 ms
</code></pre>
(3x speedup)<p>Updating 30% of values:<p><pre><code> * btree: 8398.461 ms.
* brin: 1398.711 ms.
</code></pre>
(4x speedup)<p>Extra bonus:<p><pre><code> * size of btree index: 28MB
* size of brin: 64kb
</code></pre>
Search (for a range):<p><pre><code> $ select count(*) from table where id between 600000::int8 and 650000::int8;
* btree between: 9.574 ms
* brin between: 21.090 ms
</code></pre>
---
Love me some postgres. If I could be so bold as to ask for a feature or two, it would be great if the initial setup were easier to script.<p>Perhaps this is outdated already but we have to resort to here-documents and other shenanigans to get the initial db and users created. Is there a better way to do this?<p>Next would be to further improve the clustering, to remove the last reason people continue to use mysql.
Nobody has mentioned jsonb partial updates yet. This is huge and goes further in superseding MongoDB use cases. Previously you would have add your own locking mechanisms (or use SELECT ... FOR UPDATE) so read/modify/update could be performed atomically. Now it will be built in.
Do you guys think row-level security will eventually replace the crazy logic we have to add to our systems normally to allow for this? I can think of many places this might help a bunch if combined with SET SESSION AUTHORIZATION command.
It sounds like BRIN could replace partitioning on some cases. Am I right? Assuming you have a huge log table, partitioned by week, would this be a better fit?
Upsert, oh well. CRUD becomes CRUDUM: Create, Read, Update, Delete, Uperst, Merge.<p>Instead of 4 orthogonal concepts we now have 6 overlapping. Because the majority voted for it. That's progress!