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 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?
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.
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.
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.
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.
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.
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.
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.
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.
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.
> 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.
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.
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?
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.
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.
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.
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.
> 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 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.
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/
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.
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.
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].
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?
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.