Today, it is still heavily used in mid-sized data warehouses and “data marts”, meaning databases in the one to ten terabyte range.
.. There are some drawbacks to BRIN indexes, though. They can’t be used to enforce uniqueness or support keys. BRIN works much better on values that are incrementally increasing with each row inserted, like timestamps and incrementing integers. Most of all, BRIN indexes handle updates and deletes on the underlying table inefficiently, so they’re best used with append-only tables.
Postgres The Bits You Haven’t Found, Heroku’s Peter van Hardenberg at Waza 2013
ABOUT PVH: Peter (PVH) is a scientist. By day and by night, he runs Heroku Postgres, the largest and most reliable Postgres service in the world. But he will happily share his knowledge on any subject, from oceanography to coffee roasting. @pvh
Modern SQL in PostgreSQL [and other databases]
“SQL has gone out of fashion lately—partly due to the NoSQL movement, but mostly because SQL is often still used like 20 years ago. As a matter of fact, the SQL standard continued to evolve during the past decades resulting in the current release of 2011. In this session, we will go through the most important additions since the widely known SQL-92, explain how they work and how PostgreSQL supports and extends them. We will cover common table expressions and window functions in detail and have a very short look at the temporal features of SQL:2011 and the related features of PostgreSQL.”
ZomboDB: Making Postgres and Elasticsearch work together like it’s 2015
ZomboDB is a Postgres extension that enables efficient full-text searching via the use of indexes
backed by Elasticsearch. In order to achieve this, ZomboDB implements Postgres’ Access Method API.In practical terms, a ZomboDB index doesn’t appear to Postgres any different than a standard btree index might. As such, standard SQL commands for mutating are data fully supported:
INSERT,UPDATE,DELETE,COPY, andVACUUM.