Hacker News Reader

Comment by einhverfr | Hacker News Reader

My two favorite features are not so high on the PR docs though.

The first is SECURITY BARRIER and LEAKPROOF which gives us an ability to rethink how to multi-tenant applications. This is a game changer and will get even better in future versions I am sure.

The second is NO INHERIT constraints, which I will certainly be making good use of. It is also a complete game changer when it comes to table inheritance and partitioning, and my main use will be things like CHECK (false) NOINHERIT to ensure that a table in fact never has rows of its own.

There is an amazing amount of good stuff going on around Postgres right now. Postgres-XC was recently released, and more. It is an amazing data modelling platform and ORDBMS.

View on HN

Could you talk about them further and explain why they're your favorite new features?

Regarding the inheritance features, he's been writing an extensive series on the subject recently:

http://ledgersmbdev.blogspot.com/2012/08/intro-to-postgresql...

(though he should provide forward/backward links in the posts -- as it stands you need to find the other parts in the archive links on the right)

Managing the forward/backward links in a series this long while it is still coming out is a pain. They will be added as the series draws to a close next week though.

The basic thing is this:

One area where Oracle is ahead is in multi-tenant applications. They have an approach where you can build filters on data based on specific criteria. You can do this on PostgreSQL too but the way you have to do it before this either involves add-ons or actual partitions of data, or tweaks to prevent building functions altogether.

Security barriers are important in catching up to Oracle in this regard and they allow us to build more integrated multi-tenant applications with greater access to the db by the tenants.

NO INHERIT constraints open up a fairly large area of PostgreSQL for use or misuse, because you can now partition a primary key between parent and child declaratively. My own use will be to prevent inserts into the parent table directly. Table inheritance is a really neat feature if you use it in non-traditional ways. It is not particularly useful for its advertised use (set/subset stuff outside of partitioning). However what it allows you to do is to compose your tables out of smaller re-usable pieces each of which can have complex derivations of data attached. For example, if you want full text search on comments on a lot of your tables, you can create a consistent, centralized interface for this using table inheritance.

>The first is SECURITY BARRIER and LEAKPROOF which gives us an ability to rethink how to multi-tenant applications

It doesn't really let us re-think it does it? It just closes a hole in how you would have typically done it anyways. Is there any way to solve the problem of having to create a new connection as company_X_user for every single request?

I think a number of people might find such functionality useful -- every once in a while I run across people with tens or hundreds of thousands of schemas, because a program written to query against a particular schema is more convincing and easy to audit than row-based multi-tenancy.

The problem is that there are performance and tooling issues when you have that many database objects, so those people can end up sad, even though the model was one they basically were happy with.

So I think adding database features for lucid multi-tenant work while reducing the risk of cross-tenant leakage is a movement in a good direction.

> Is there any way to solve the problem of having to create a new connection as company_X_user for every single request?

Yes you can accomplish that with Veil, but I agree, it would be nice if it was built in, but an add-on is ok too.

Reply on news.ycombinator.com