14 ms·
What's Coming in PostgreSQL 9.5
- lephyrius 11y agoNo, partial updates of materialized views it seems like.
- pilif 11y agonope. but since 9.4 you can at least update them without an exclusive lock on them.
- ExpiredLink 11y agoUpsert, 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!
- FooBarWidget 11y agoI'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.
- pilif 11y agoTo 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. See also http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...
- andrewl-hn 11y agoWell, philosophically, CRUD is a lie. You only ever need two operations: READ and UPSERT. Create with upsert and Delete by upserting "deleted = true" flag.
- bmh100 11y agoThat "deleted" flag is extremely useful in data warehousing and OLAP applications. I wish every table had a "deleted" column and an "updated" column.
- Todd 11y agoThey 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.
- pjungwir 11y agoThat 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.
- mason55 11y ago> 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.
- deleted 11y ago[deleted]
- bsg75 11y agoSo 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?
- rdtsc 11y agoBRIN (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-range-indexes/ 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 ---
- saosebastiao 11y agoYep, 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.
- pjungwir 11y agoThere 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.
- therealunreal 11y agoIt 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?
- endymi0n 11y agoInitially 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.
- saosebastiao 11y agoFor 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.
- bmh100 11y agoLet 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.
- emidln 11y agoMeh, 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.
- pgeorgi 11y agoThe 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.
- 11y ago
- jtwebman 11y agoDo 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.
- mixmastamyk 11y agoLove 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.
- anarazel 11y agoHow 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.
- mixmastamyk 11y agoSome 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.
- anarazel 11y ago> 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.
- Roboprog 11y agoHere 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.
- Roboprog 11y agoCool stuff for multi-tenant DBs! Aside from the obvious row level security, tenant ID makes a nice BRIN key for some tables, I suspect.
- bkeroack 11y agoNobody 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.
- jrochkind1 11y agoYou 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 http://www.postgresql.org/docs/9.0/static/hstore.html)