30 ms·
PostgreSQL 9.5: UPSERT, Row Level Security, and Big Data
- reactor 11y agoCongrats to everyone involved, indeed an amazing opensource project that displays true integrity and discipline.
- petergeoghegan 11y agoThanks
- overcast 11y agoInteresting, I wasn't aware PostgreSQL didn't have UPSERT until now. MySQL has INSERT on DUPLICATE functionality that is similar.
- avidal 11y agoYep. Been a long requested feature. One of the reasons why the post states: "This feature also removes the last significant barrier to migrating legacy MySQL applications to PostgreSQL."
- overcast 11y agoRoger that.
- avar 11y agoWhat do they mean by "legacy" in this context? INSERT ... ODKU is not a legacy feature of MySQL, it's a currently supported first-class feature of the database, nor is MySQL itself a "legacy" database.
- brlewis 11y agoI think they're referring to mysql. The sentence would have worked just as well without the word "legacy". I say this as someone who prefers PostgreSQL.
- X-Istence 11y agoNo, they are referring to an application that is being moved from MySQL to PostgreSQL. The application is "legacy" in that it is an older version and the new version is "current". Due to the English language however there can be some debate as to what they meant. In this case "legacy" is most likely meant to describe the "MySQL application" not "MySQL" itself.
- brlewis 11y agoYou're wrong about which meaning is more likely. A meaning that adds something to a sentence is a more likely meaning than one that adds nothing to a sentence. If you take "legacy" to mean "being migrated from" then the sentence becomes This feature also removes the last significant barrier to migrating being-migrated-from MySQL applications to PostgreSQL. It's more likely that if "being migrated from" was the intended meaning, they would have simply left the word out.
- elbear 11y agoYour comment assumes the author of the release notes has perfect command of the English language and they thought through in detail what the word "legacy" would mean in this context.
- brlewis 11y agoNot at all. First, my comment says "more likely" so it isn't assuming anything. Second, if we change "more likely" to "definitely", the assumption is merely that the sentence in question is written with the same command of the English language as the rest of the announcement, i.e. no egregiously redundant words.
- 11y ago
- scidev 11y agoLegacy applications, not legacy MySQL feature.
- greenleafjacob 11y agoIf you are switching from MySQL to Postgres, then it's legacy by definition rather than intrinsic properties of MySQL.
- desdiv 11y agoIronically the first two google results for "UPSERT" is from wiki.postgresql.org.
- overcast 11y agoShows how long it's been in the works, and requested.
- spamizbad 11y agoI'm very excited about CUBE and ROLLUP -- I am just about to start a project that barely requires an OLAP shim. Now it looks like I can just do it all with just database features. Yay for fewer dependencies!
- paulsmith 11y agoI'm not familiar with those statements, can you provide an example?
- jsmeaton 11y agoThey're useful for providing summaries like row totals and column totals. Rather than just get an aggregated count for the total GROUP, you can also get aggregated counts for each unique combination of columns within the GROUP. col1 col2 count ---- ---- ----- a b 10 a null 5 null b 5 There's more to it than that obviously, but you can read about them here: http://www.postgresql.org/docs/devel/static/queries-table-expressions.html http://www.postgresql.org/docs/devel/static/queries-table-ex... (7.2.4. GROUPING SETS, CUBE, and ROLLUP)
- jeltz 11y agoThere are plenty of nice minor improvements in the release notes. One of my favorites is "Allow array_agg() and ARRAY() to take arrays as inputs (Ali Akbar, Tom Lane)", it will come in handy when writing ad hoc queries to understand stored data. Right now I have to build a string which I use as input to array_agg().
- Twisell 11y agoI'm so pleased to see that I was not the only freak out here doing that!
- wingsonfire 11y agoI am looking forward for Row Level Security, Though cell level security will be even more awesome to have.. Difficult to achieve in SQL. Only Apache Accumulo in NoSQL space has it.. But once we have it make sure no one has access to SSN column and we will be protected to one degree in data breaches.
- noselasd 11y agoWouldn't you be able to do this in Postgres now ? "RLS implements true per-row and per-column data access control"
- brlewis 11y agoWhat can you do with row-level security that you can't do by setting permissions on an updatable view?
- jeffdavis 11y agoNormal views aren't designed for security. There are a number of ways that they can "leak" information that is supposed to be hidden. The reason is that the optimizer reorders operations. So, a tricky person can write the query in a way that, for example, throws a divide-by-zero error if someone's account balance is within a certain range, even if they don't have permission to see the balance. Then they can run a few queries to determine the exact balance. RLS builds on top of something called a "security barrier view" which prevents certain kinds of optimizations that could cause this problem. It also offers a nicer interface that's easier to manage.
- Sanddancer 11y agoI may be wrong in the level of separation that pgsql provides in such situations, but it appears that a materialized view offers another level of isolation that would make such leaks more difficult to handle.
- elchief 11y agoFor one, you don't need a view. And you'd probably need (in 9.4) insert, update, and delete triggers for anything beyond trivial row security.
- systems 11y agohow does postgresql upsert compare to ms sql's merge statement i want to look deeper into this, but didnt have the time but from the little i read, seems ms sql merge is more powerful
- manigandham 11y agoLots of databases have MERGE but it's different from the typical UPDATE OR INSERT logic in terms of use cases, table requirements and concurrency control. Here's a great post from Postgres team showing why they didn't just implement merge themselves: http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com http://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGY...
- jeltz 11y agoThe new PostgreSQL syntax is more convenient to use in the UPSERT use case while the MERGE syntax is more convenient to use when doing complicated operations on many rows of data (for example when merging one table into another, with a non-tricial merge logic). The reason PostgreSQL went with this syntax is that the goal was to create a good UPSERT and getting the concurrency considerations right with MERGE is hard (I am not sure of the current status, but when MERGE was new in MS SQL it was unusable for UPSERT) and even when you have done that it would still be cumbersome to use for UPSERT. EDIT: The huge difference is that PostgreSQL's UPSERT always requires a unique constraint (or PK) to work, while MERGE does not. PostgreSQL relies on the unique constraint to implement the UPSERT logic.
- dsp1234 11y agoI am not sure of the current status, but when MERGE was new in MS SQL it was unusable for UPSERT I've used MERGE as an UPSERT using MATCHED/NOT MATCHED and SERIALIZABLE/HOLDLOCK since it was introduced in mssql 2008. It was one of the first features I upgraded my code to use, and it worked out of the box with no issues.
- jeltz 11y agoSee this blog post for what I am talking about: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... If PostgreSQL had gone the same route as MS SQL I would have expected a similar set of bugs. I suspect all of this have been fixed by now, but I do not follow MS SQL.
- sandGorgon 11y agoanybody know how quickly does RDS upgrade to newer versions of Postgres. I'm really, really keen to use 9.5 jsonb with its insert/update changes.
- fuhrysteve 11y agoThey have a policy not to say anything or make any promises. That said, using history as a guide, it seems to take them about 2.5 months after a major release to add support.
- andor436 11y agoWhile you wait, check out http://stackoverflow.com/a/23500670/229006 http://stackoverflow.com/a/23500670/229006 My plan is to rely on these functions for now, and switch to the native implementations once 9.5 is production ready on RDS.
- sandGorgon 11y agothis is so cool... thanks!!
- deleted 11y ago[deleted]
- Jweb_Guru 11y agoThat isn't true, according to their own documentation: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Upgrading.html#USER_UpgradeDBInstance.PostgreSQL http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_U...
- cjauvin 11y agoI wonder how much time it will take to appear in the Ubuntu apt repo? Should it be already there (I don't see it yet)? Edit: I meant apt.postgresql.org of course, not the official Ubuntu repo..
- jeltz 11y agoNo idea, but the PostgreSQL community distributes official Debian and Ubuntu packages at apt.postgresql.org. They should already have 9.5, or if not have it very soon.
- lugus35 11y agoWith a REST layer like https://github.com/begriffs/postgrest https://github.com/begriffs/postgrest you don't need any extra application server layer to serve your data, securely (RLS). Bye bye Java EE ?
- scardine 11y agoPostgrest has a very clever design. I want to see other tools going this route.
- saosebastiao 11y agoIt would be kinda cool to find a way to stuff a fast HTTP server into Postgres and run it all directly from the Postgres process serving the request.
- dc2447 11y agoThere is an nginx module for this.
- leeoniya 11y agorelevant?: https://en.wikipedia.org/wiki/Jamie_Zawinski#Zawinski.27s_law_of_software_envelopment https://en.wikipedia.org/wiki/Jamie_Zawinski#Zawinski.27s_la...
- nickpeterson 11y agoPeople seem to dislike this because they want separate components in order to 'scale out', but honestly, having the option for most trivial applications would be extremely nice. Even more so when you consider Postgres supports routines written in non-sql programming languages.
- bdcravens 11y agoI looked at Postgrest, and it was a little more opinionated re URLs and associations than I would have preferred.
- gcb0 11y agosearch in 5% the time it would take to search a btree? anyone can see that with actual data?
- gcb0 11y agohttp://pythonsweetness.tumblr.com/post/119568339102/block-range-brin-indexes-in-postgresql-95 http://pythonsweetness.tumblr.com/post/119568339102/block-ra... the very first example points to BRIN indexes resulting in smaller index than btree but with much longer search time... so i guess the 5% time figure was very use-case specific?
- tmaly 11y agothis is excellent news. I really enjoy using postgresql in one of my current projects. I look forward to using upsert and the new indexes
- desmondrd 11y agoUpsert is something I expect so commonly in modern databases nowadays. Happy to see it here with Postgres.
- jayess 11y agoCan anyone suggest a good "getting started" tutorial for PostgreSQL/debian/php? I've been using Mysql for years and would like to give Postgres a try.
- hardwaresofton 11y agohttp://www.postgresql.org/docs/9.5/static/tutorial-start.html http://www.postgresql.org/docs/9.5/static/tutorial-start.htm... What particularly are you looking for in a "getting started" tutorial? Honestly, you should just plunge in, on some side project (or a mirror of whatever projects you've used MySQL on) and just compare. This is a lot easier to say than do/live by, but I think you shouldn't invest in one tool choice when you haven't given the others a fair shake (once you have enough time to step back and think about your decision).
- deleted 11y ago[deleted]
- olefoo 11y agoInstall it, and then build something. A few things you will want to look at that are different: 1. data types are much richer and more useful than in mysql 2. transactional DDL means migrations are atomic. 3. schemas are what mysql refers to as databases. Remember to set `search_path`. 4. roles and grants are somewhat more expressive and work differently than in mysql, but not that differently for the simpler use cases 5. database functions ( aka stored procedures ) are awesome as are extension languages.
- tracker1 11y agoOn point 5... love PLv8, which imho makes working with the newer JSON data types really nice.
- tommoor 11y agoI might get some hate, but I also think upsert was one of the best features that MongoDB offered that PG didn't, so this is a big win from that perspective too.
- tracker1 11y agoReally nice to see the direction things are moving in... I do feel that the replication/failover story needs a lot of work, but there's been some progress towards getting it in the box. Even digging into any kind of HA strategy is cumbersome to say the least (short of a 5-6 figure support contract). It's one of the things that generally stops me from considering PostgreSQL for a lot of projects. As a side note, I really like how RethinkDB's administrative interface is and their failover usage. It would be great to see something similar reach an integration point for PostgreSQL. I also think that PLv8 should probably make it in the box in the next release or two. With the addition of JSON and Binary JSON data options, having a procedural interface that leverages JS in the box would be a huge win IMHO. Though I know some would be adamantly opposed to this idea.
- roeme 11y ago> I do feel that the replication/failover story needs a lot of work, but there's been some progress towards getting it in the box. Even digging into any kind of HA strategy is cumbersome to say the least (short of a 5-6 figure support contract) Eeeh...a simple HA solution can be developed in about a week (I was able to do so on 9.3, and so far, it held it's ground). Also, now with 9.5's pg_rewind you can easily switch back and forth between nodes (http://www.postgresql.org/docs/9.5/static/app-pgrewind.html http://www.postgresql.org/docs/9.5/static/app-pgrewind.html), simplifying things a great deal. Can't imagine that's 5-6 figures. I agree that you don't get a Plug&Play-Solution out of the box, but from anecdotal evidence they often don't quite work as advertised anyway (remember 1995? And I'm sure your friendly DBA has some stories to share as well).
- elchief 11y agoYa I'd love to see PLV8 (with as many ES6 features as possible) as a stock language
- yashap 11y ago> Even digging into any kind of HA strategy is cumbersome to say the least (short of a 5-6 figure support contract). It's one of the things that generally stops me from considering PostgreSQL for a lot of projects. If you're going to be hosting your db on something like AWS EC2 anyways, then just buy a db product like AWS RDS, and pay for the HA option. Ends up around the same price as if you'd set up everything yourself (assuming you were going to host on AWS anyways, and not going with a low cost option), and is very easy.
- gionn 11y agoBye bye mongodb.
- mmaunder 11y agoUnless I'm mistaken MySQL has had this for almost a decade with "ON DUPLICATE KEY UPDATE". I'm seeing a lot more about PSQL here and in the news. I've always found it to be unfriendly and slow. Why the new attention? Is there really something about PSQL that makes it better than MySQL these days? It used to be transactions, but InnoDB made that moot years ago. We do over 20,000 queries per second on one of our production mysql DB's and I'm not sure I'd trust anything else with that: http://i.imgur.com/sLZzXhS.png http://i.imgur.com/sLZzXhS.png Just curious if I'm missing out on some new awesomeness that PostgreSQL has or if it's just marketing.
- davidw 11y agoMysql always seemed to be fast like a bike going downhill with no brakes. Postgres has always taken a more 'solid' approach. One instance that made my jaw drop when I realized it: in the past (has this been fixed?), DDL (alter table, create table, etc...) were not transactional in Mysql. You could get 50% through a series of them, and find your database 100% fucked up. That said, over the years Mysql has been improving too, for sure.
- colanderman 11y agoIf all you care about is QPS, by all means, stick with MySQL. People like myself use Postgres because it has a much richer feature set. See http://stackoverflow.com/a/5023936/270610 http://stackoverflow.com/a/5023936/270610 for some examples. Personally I find MySQL beyond frustrating due to its lack of… well almost all of those. Recursive CTEs in particular, but arrays and rich indexing are pretty core too. Postgres's query optimizer is far more advanced too. MySQL doesn't even optimize across views, which discourages good coding practices. The documentation is fantastic. Complete and well-written, covers the nuances of every command, expression, and type. MySQL's doesn't hold a candle to it. Don't know what you mean about "unfriendly". Help is built into the command-line tool, and like I said, the documentation is fantastic. Maybe MySQL is a little more "hand-holdy", but I don't care for such things so I wouldn't know.
- saosebastiao 11y agoWhenever there is a version update, I can't help but be grateful for the documentation ethic of Postgres. For the vast majority of my projects I have to wade through unaffiliated and incomplete blog tutorials that may or may not be relevant to the version I'm trying to use. With anything related to Postgres, I may read about something on a blog post, but I always know that I can count on the Postgres documentation if I need supplemental information, or sometimes I'll skip the post and go straight to the official docs. The PostgreSQL project, in my mind, sets the standard globally for software documentation. I should add that Postgres was the first database I ever used, and I literally learned pretty much everything I know about Postgres, SQL, as well as Relational and Set Logic from the official docs. And that was with no background in software development and an undergraduate business degree with Excel being my most technologically advanced toolset. That is a documentation success story.
- jrapdx3 11y agoRemarkable similar to my own history with Postgresql, which I started using in ~1998 at the time of their first public release. Postgresql documentation has indeed been the exemplar for all software, open source or not. It's been the SQL textbook I've relied on. With the steady addition of features, it's gotten much more complex, and there will come a time when using just the documentation won't be enough to learn how to use Postgresql to full advantage. With release of 9.5 we might be there now. Perhaps the logical extension of the documentation is some form of coursework to enable users to learn the DB systematically. I haven't looked into it, this might already be offered.
- AlisdairO 11y ago(self plug) for coursework on the read-only SQL side, you might want to give http://pgexercises.com http://pgexercises.com a try. I have ambitions to expand it to include arrays and json, but alas haven't found the time so far...
- Alex3917 11y agoThis is awesome, thanks for making it!
- elchief 11y agoRegarding Row Security, yes you can use it with a web application and still use connection pooling. From web server, connect to db as one user then SET ROLE to the database user. This gives you Column Security and easier auditing as well. See http://stackoverflow.com/questions/2998597/switch-role-after-connecting-to-database http://stackoverflow.com/questions/2998597/switch-role-after...
- jimktrains2 11y agoThe thing is that each application user now needs a corresponding DB user to use RLS. While this isn't a huge problem, it's different than how most (if not 99%?) of applications work.
- elchief 11y agoYes, but it's not harder to have many db users vs many app users. There's even an extension to sync pg users with ldap
- jimktrains2 11y agoI'm not saying it's _hard_, it's just _different_ and would be a challenging migration for established apps as I don't know of any framework's authentication system that works like that. Also, why would you sync pg users in ldap? pg can auth against ldap.
- jimktrains2 11y ago> Also, why would you sync pg users in ldap? pg can auth against ldap. I figured out why: To pull roles/groups into the database from ldap.
- anarazel 11y agoNo, RLS does not necessarily require separate database users. Using database users is one relatively obvious way to use the feature, but you can very well do something like 'SELECT myapp_set_current_user(...)' or something, and use a variable securely set therein for the row restrictions.
- dandigangi 11y agoI swear... I will redesign and develop Postgre's site for free.
- jimktrains2 11y agoSend a message to pgsql-www
- spacemanmatt 11y agoI'm curious, what do you think needs to be redesigned?
- dandigangi 11y agoI wish I could say it's circa Web 2.0 but it's still stuck even farther back than that. I mean it works which is great but I loathe spending time on it because it's such a poor experience.
- ankimal 11y agohttp://www.postgresql.org/docs/9.5/static/brin-intro.html http://www.postgresql.org/docs/9.5/static/brin-intro.html If like me you were looking for what BRIN index is all about.
- avita1 11y agoOut of curiosity, has anyone managed to find something more detailed about the guts? It sounds like it's basically a BTree that stops branching at a certain threshold, but I'm almost certainly wrong.
- anarazel 11y agoNo, that's not really it, although you could see it as a very degenerate form of a btree. Basically it's using clustering inherent to the data - say a mostly increasing timestamp, autoincrement id, model number ... - to build a coarse map of the contents. E.g. saying "pages from 0 to 16 have the date range 2011-11 to 2011-12" and "pages from 16 to 48 have the date range 2012-01-01 to 2012-01-13". With such range maps (where obviously several overlapping ranges can exist) you can build a small index over large amounts of data. Obviously single row accesses in a fully cached workload are going to be faster if done via a btree rather than such range maps, even if there's perfect clustering. But the price for having such an index is much lower, allowing you to have many more indexes. Additionally it can even be more efficient to access via BRIN if you access more than one row, due to fewer pages needing to be touched.
- jakobegger 11y agoThere was a talk about index internals at pgconf.eu that also covered the new BRIN indexes. Slides are here: http://hlinnaka.iki.fi/presentations/Index-internals-Vienna2015.pdf http://hlinnaka.iki.fi/presentations/Index-internals-Vienna2...
- avita1 11y agoOut of curiosity, has anyone managed to find something more detailed about the guts? It sounds like it's basically a BTree that stops branching at a certain threshold, but I'm almost certainly wrong.
- btilly 11y agoYay! I like the changes. But they are doing absolutely nothing about my biggest beef with PostgreSQL. Which is that there is absolutely no way to lock in good query plans. It always reserves the right to switch plans on you, and sometimes gives much, much, much worse ones. No other database does this to me. Even MySQL's stupid optimizer can be reliably channeled into specific query plans with the right use of temporary tables and indexes. This is a problem because improvements don't matter if the query plan is "good enough". But they will care if you screw up. PostgreSQL usually does well, but sometimes screws up spectacularly. The example that I have been struggling the most often with in the last few months is a logging table that I create summaries from. Normally I only query minutes to hours, but I set it up as a series of SQL statements so I first put the range in a table, and then have happened BETWEEN range_start AND range_end. PostgreSQL really, Really, REALLY wants to decide that the index on the timestamp is a bad idea, and wants to instead do a full table scan. Every time it does, summarization goes from under a second to taking hours. Hopefully the new BRIN indexes will be understood by the optimizer in a way that makes it happier to use the index. But I'm not optimistic. And if I lean on it harder, I'm sure from past experience that I'll find something else that breaks.
- keslerm 11y agoYou can push it in favor of certain options by disabling the one you don't want, such as sequential scan. SET enable_seqscan = OFF; We use these options a lot on tables that result in odd query plans to get them doing the best option..
- btilly 11y agoThat's a random sledgehammer, but that is how I have been solving the problem. I've set enable_seqscan, enable_nestloop and enable_material to false and it is working at the moment. At first I only turned off enable_seqscan, but then I turned off the other two after we switched database hosts and the query went belly up. What scares me is that this is unreliable, and according to the documentation, the optimizer is free to choose to ignore everything that I say whenever it wants. The fact that it already HAS done that to me does not provide me comfort.
- omarforgotpwd 11y agoWhat an absolutely fantastic project. The recent releases have all been very exciting.
- jhealy 11y agopglogical (http://2ndquadrant.com/en-us/resources/pglogical/ http://2ndquadrant.com/en-us/resources/pglogical/) claims to allow cross version upgrades from 9.4 to 9.5 with minimal downtime, but the documentation seems fairly light-on. Has anyone come across a guide to using it for upgrades?
- deleted 11y ago[deleted]
- ropiku 11y agoGreat that Heroku sponsored upsert and have support for 9.5 right now (in beta): https://blog.heroku.com/archives/2016/1/7/postgres-95-now-available-on-heroku https://blog.heroku.com/archives/2016/1/7/postgres-95-now-av...?
- chbrown 11y agoEvery time there's a minor version update I have to remind myself the sequence of upgrade incantations. It's pretty simple, but here's a gist that might help anyone upgrading from 9.4 to 9.5 with Homebrew: https://gist.github.com/chbrown/647a54dc3e1c2e8c7395 https://gist.github.com/chbrown/647a54dc3e1c2e8c7395