What's Coming in PostgreSQL 9.5 | Hacker News Reader

What's Coming in PostgreSQL 9.5

compose.io

287 pointsthomcrowe62 comments

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.

Example:

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.

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.

Meh, you can already do this with a query AST. For every request, grab the user's data permissions represented in the same query AST and then AND them together. Compile your query AST into whatever search technology (I've seen it done with ES, Postgres, MySQL, Mongo, Solr, and Rethinkdb in the last couple years). If you're being super fancy you can even use your query ast to match on a document stream in real-time by compiling to some actual programming language and checking things on whatever document stream.

The difference is that postgres can enforce this for arbitrary queries.

This doesn't matter in the typical webapp where all accesses to the DB happen through the same database user id, but when actually using the user system of the DB, it allows for fine grained access control to a common data set.

The closest you have without explicit RLS support is to create a view for each user. RLS generates per-user views on demand under a common name.

I use schemas[0] for this. Could someone explain what advantage is gained from RLS in comparison, either to views or schemas?

[0] http://www.postgresql.org/docs/current/static/ddl-schemas.ht...

I am very familiar with RLS, but not schemas. Could you provide an example of how you would use schemas? If not too much trouble, could you also use my salesperson example from my other comments?

BRIN (Block Range) Indices look really interesting.

Instead of storing the whole B-Tree (and spending time updating it) just store summary of ranges (pages).

This for example, would be great for a time series database that is write heavy but not read as often.

I found these benchmarks here explaining the differences:

http://www.depesz.com/2014/11/22/waiting-for-9-5-brin-block-...

---

Creating 650MB tables:

   * btree: 626.859 ms.
   * brin: 208.754 ms

(3x speedup)

Updating 30% of values:

   * btree: 8398.461 ms.
   * brin: 1398.711 ms.

(4x speedup)

Extra bonus:

   * size of btree index: 28MB
   * size of brin: 64kb

Search (for a range):

  $ select count(*) from table where id between 600000::int8 and 650000::int8;
    * btree between: 9.574 ms
    * brin between: 21.090 ms

---

Yep, this is a big deal, for me at least. It basically makes it almost cost-free to add indexes to tables that are write heavy.

I've always felt like Bitmap Indexes were a killer feature, and never understood why they weren't used more in databases. If you have a low cardinality column (anything suitable for an enum), your indexes become incredibly fast and cheap.

There was an effort to build bitmap indexes for Postgres a few years ago. There are details in the mailing list archives. It looks like it was almost completed! It's high on my list of things to tackle if I ever get time to start contributing, but maybe someone else will get to it first.

Is that different than the bitmap index access method, which IIRC landed in 8.1 or so, and is used to combine results of multiple index scans?

Yes, totally different thing. :-) A great book on query plan stuff is here:

http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm...

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.

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?

Next would be to further improve the clustering, to remove the last reason people continue to use mysql.

How would you like to do the initial setup? I'm not perfectly happy with how things are, but I don't see a non SQL interface being better. An easier way to start postgres without network, process a file and shut down, would be goid IMO.

Some pieces already exist, like the pg* commands. Perhaps they could be improved to handle the remaining configuration tasks. The config files themselves could probably use a good redesign as well.

What do other databases do? I haven't used others in a while, but at the time of choosing pg I remember it being more fiddly.

> Some pieces already exist, like the pg* commands

I don't think those really help? You need to start a server for that and be allowed to connect. If you have that you can just as well feed a file to psql to do all the setup at once.

> The config files themselves could probably use a good redesign as well.

Hm. I've dealt with postgresql.conf files for a decade now, so maybe I just don't see the problem with the format itself. I think we should make more parameters auto-tuned, but that's something different to the file format itself.

If you're talking about pg_hba.conf: Wholewheartedly agreed. That's the one thing I remember being terminally confused about back when I started using postgres.

Here is my script to create a test instance in about 5 seconds (albeit without any tables define yet, just space for them, with a running server on that space, and an account to create and use the tables in the DB):

(puts tablespace in folder under current directory)

<code>

#!/bin/sh -x

# create and run an empty database

# note: fails miserably if there are spaces in directory names

PGDATA=`pwd`/pgdata

export PGDATA

# make it if not there

mkdir -p $PGDATA

# clean it out if anything there

/bin/rm -rf $PGDATA/*

# set up the database directory layout

initdb

# start the server process

nohup postgres 2>&1 > postgres.log &

sleep 2

# create an empty DB/schema

createdb edrs_test_db

# create a user and password ("demo") to use for connections

psql -d edrs_test_db -c "create user guest password 'guest'"

ps auxww | grep '[p]ostgres'

echo run tail -f postgres.log to monitor database

# vi: nu ai ts=4 sw=4

# * EOF *

</code>

"edrs" is the name of an app - sub in something more applicable. I'm running this on OXS, but should work on Linux as well.

protip: indent with 4 spaces to get code formatting

Why `nohup postgres 2>&1 > postgres.log &` instead of `pg_ctl start`? You could also use `pg_ctl -w start` to wait for proper server startup instead of `sleep 2`

Why? Ignorance. I'm used to running postmaster from years past (when not starting from an /etc script). Postmaster is now just called postgres, and it works for a disposable test instance setup.

I'll look into that, though, as it sounds like the right thing vs the sleep hack.

... and I put the 2>&1 in the wrong place :-(

(before the file redirection instead of after)

I'd tend to think most pg'ers would want to use their everyday pg tools to setup their db's. For most, that's going to be command line tools, which sounds like what you are not having fantastic success using.

If embedding here-documents inside scripts isn't to your liking, perhaps you should either separate out the seed scripts and cat them in, or have your app-server take on more responsibility for seeding. Systems like Liquibase used by Dropwizard are an example of a more formalized way of performing initial seed, an example of something that might be more what you are looking for if shell scripts are proving difficult to operate.

It works, it's just clumsy.

Scripting is clumsy compared to what? Clicking radio buttons and "next" for 5 minutes???

Another alternative is to make an empty (or test setup) database instance in the "PGDATA" directory, and archive that (while the DB is shut down) for redeployment on another server instance. Unlike Oracle, everything is in one place, as ordinary files and subdirectories.

No, here-documents should not be required. The security settings should be a lot more intuitive, etc.

Fair enough on the security settings. That's something I have to squint at in many system, as I don't deal with it much, other than to grant full access to an app level account. A cheat sheet on common use cases would help.

> 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?

Yes, and it works for most server software, not just PostgreSQL. Use a configuration management system, like Salt, Puppet, or Chef.

"Now you have two problems."

That these tools exist doesn't mean pg couldn't be streamlined. Also, we're moving to containers and may drop some of the extra tools.

do you use docker containers for pg? One thing you could do is use a separate data volume and clone it for every new spinup.

There's createdb from memory (?), but for other bits and pieces, it might be worth spending a bit of time on a thin wrapper script e.g. "create-pg-user foo" that just wraps the annoying parts, but using your environment specific bits and pieces if needed, or using a C/Python/Ruby/Java/etc api. At a bit of a different level maybe some Ansible playbooks that hide away the psql level stuff, so they can be reused.

I fully agree though and in thinking about it, a range of CLI level tools would be nice. I guess then the team would have to play catch up with any syntax/api changes for those tools too, so its adding that annoying extra bit (albeit fairly small probably). But I guess its the annoying extra bit getting down in one place, and not hand-crafted by everyone.

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.

You already could do that with postgres hstore, I think, but postgres hstore is limited to a flat list of key/values, not nested data structures like json.

I've been wishing for a while though that Rails ActiveRecord would support the atomic partial update operations inside hstore that postgres already does.

(http://www.postgresql.org/docs/9.0/static/hstore.html)

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.

I thought you could do that now by making a view and setting security on the view. Oracle allows for that sort of thing.

True, I have seen Microsoft SQL systems like that as well.

No, partial updates of materialized views it seems like.

nope. but since 9.4 you can at least update them without an exclusive lock on them.

Cool stuff for multi-tenant DBs! Aside from the obvious row level security, tenant ID makes a nice BRIN key for some tables, I suspect.

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?

Initially I had the same thought, as it's much less hassle. Unfortunately, other b-tree indexes in the table(s) then won't benefit from this optimization and continue growing. Also, the technique of dropping old partitions at once doesn't work anymore. So no replacement for partitions.

For a log table or anything immutable and write heavy, definitely.

That being said it might be difficult to know when you won't get any benefit out of it unless you have control or knowledge of how rows are laid out in the table space. For example, deleting some rows based off of a fairly random criteria may make Postgres insert into those spaces on subsequent writes (after a vacuum), which could "pollute" the block ranges with non-ordinal data and make the block ranges less targeted.

Upsert, oh well. CRUD becomes CRUDUM: Create, Read, Update, Delete, Uperst, Merge.

Instead of 4 orthogonal concepts we now have 6 overlapping. Because the majority voted for it. That's progress!

I'll take actual usefulness in practice any day over theoretical elegantness that has problems in practice.

And really, upserts aren't that hard to understand.

To do upserts correctly in a case of concurrent write access to the database is a real pain in the ass to get right and in the end always boils down to locking or retrying in loops with random sleep times interspersed in order to not conflict over and over again.

Having the ability to tell the database the data to insert together with a conflict resolution rule and then having the guarantee that either the record will be created or the conflict resolution will be applied is very handy.

No more looping, no more deadlocks, no more retrying the same insert multiple times.

Yes, you can do it manually, but it's painful.

Well, philosophically, CRUD is a lie. You only ever need two operations: READ and UPSERT.

Create with upsert and Delete by upserting "deleted = true" flag.

That "deleted" flag is extremely useful in data warehousing and OLAP applications. I wish every table had a "deleted" column and an "updated" column.

They do in my schemas :)

One extra tip, which I have found useful, is to make the deleted column a time data type (just like created and updated), but nullable. That way, your Boolean check just needs to change to an IS NULL check, but you get the additional 'when' information without using an extra column.

That is the normal pattern in Rails apps using the `acts_as_paranoid` or `permanent_records` gems (`deleted_at` to match `created_at` and `updated_at`). But I often also have `deleted_by_id` to capture Who, and I wonder if I shouldn't just have a separate `deletions` table with the who/when and other context, and then `deletion_id` on the record.

And then I wonder if I should track updates too. There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of.

The idea of a relational schema having some kind of temporal dimension letting you get at changes is something that's been on my mind a lot lately.

> There are auditing solutions to record all that, but the ones I know are (rightly) not really designed for building application logic on top of.

Yes, a big problem with table-level audits is that you lose all kinds of information about the other entities in the system. Sure, now you have an audit log of when a row was changed, but you don't really know anything about the state of all the other pieces of the database at that time, so you can't really usefully reconstruct what the entity looked like at the time it was modified.

In theory you could parse through the whole audit log to reconstruct the state of the DB but in practice it gets very complicated.

A better solution is to perform row-level snapshots with a compressed storage format, such as a column store. I maintain a database which takes monthly snapshots of data and supports an application that allows period vs. period comparisons of aggregates or even individual rows. In my case, I use snapshots, but more space efficient (at the cost of computation) would be to only store changed records, then dynamically determine which data to show based on the desired periods and sorted the row changes.

I think what mason had in mind, which I agree is a major pain point, is when a central table "owns" records in other tables, e.g. a `book` might have several rows in `pages`. I want to say "give me edition 3" and get not just the book at that point but all its pages too. Tracking changes to the book is not so hard, but reconstructing it with all its child records is a pain.

Yes exactly. Piecing together the state of all the foreign tables across the system at a specific point in time is difficult/painful.

This is one place where document stores really shine as you generally keep everything in a single place. When you update a document you don't have to worry about the values of all the foreign keys, you just save the current version which contains all your values.

I have dealt with this scenario before. The type of data is called "slowly changing dimension" or SCD. SCDs might never change, or change daily. It all depends on the business logic. To implement this tracking, I have seen row-level historical snapshots for auditing and dimension-level tables to track the less-important dimension changes. For health data, row-level snapshots with a key generated from the data might be most appropriate. For data that has less legal implications, a table that has "id", "dimension value", and "timestamp" may be enough. From that, using from advanced SQL, you can even produce dimension time intervals. An example where that would be useful is tracking the average time a support ticket spends in "Waiting on Customer" or a call spends waiting in queue to be answered.

> The idea of a relational schema having some kind of temporal dimension letting you get at changes is something that's been on my mind a lot lately.

You may want to check out Datomic. It uses an immutable, time-based model that covers deleted_at and many more scenarios (e.g., it's effortless to ask, "what was the state of this object last month?" without the need for looking at old backups.)

I'd be rocking Datomic everywhere if it were free software.

It's really too bad - if they offered paid support and otherwise-sane licensing, I'd be all over it.

You should take a look at event sourcing. Every action is stored as a separate event and periodically snapshotted. To see how something was modified, you can replay all the events forward from day 0 or from any snapshot.

Core team member Magnus gave an excellent presentation at PGConf this year about how to achieve that with PG triggers and schemas: http://www.pgconf.us/2015/event/60/

How do you manage referential integrity?

If you have 2 tables, A and B, where B references A Ideally I couldn't set deleted on row in A until all the rows in B that reference it have also been set to deleted.

If I wasn't using soft deletes, I'd just use a foreign key from B (a_id) to A (a_id), but with soft-deletion, that constraint doesn't get enforced.

Use Datomic, unfortunately.

This problem is pervasive, and it's not easy to solve.

An on update trigger, would be my first thought...

Try 6NF[0], not only do you get separate logging for when the row was/will be marked deleted, but you have a record of when the DB believed that the record was/will be deleted. Also true history and rewind capability.

[0] https://en.wikipedia.org/wiki/Sixth_normal_form

Right, thank you for reminding me about the term. I like to see databases that generally follow this strategy, while providing higher-speed access to current data through materialized views [1].

[1]: https://en.wikipedia.org/wiki/Materialized_view

So with the original 4 CRUD operations how would you instead suggest handling the "upsert" pattern in a way that maintains data integrity and performance without a specific operator?

Reply on news.ycombinator.com