Modern SQL in PostgreSQL

http://www.slideshare.net/MarkusWinand/modern-sql

by lelf • 11 years ago

446 128 11 years ago

leoh• 11 years ago

Interesting. I don't think django implements a lot of this in their ORM.

Django's ORM is very limited, it doesn't even expose GROUP BY. I don't think there will ever be support for more powerful constructs like these.

lastofus2• 11 years ago

You are right in that the Django ORM will always be relativly simple, but it does have some GROUP BY support: https://docs.djangoproject.com/en/1.7/topics/db/aggregation/...

collyw4• 11 years ago

Group by is kind of implicit depending on the model you start with.

Model_a.objects.all().select_related(extra_tables).aggregate(arg_params)

will group by the Model_a table.

qooleot2• 11 years ago

I work on several Django apps, and also write advanced SQL in Postgres (CTEs, plv8 functions, materialized views, etc.). One thing the ORM allows though is writing a custom sql query and its pretty easy to 'cast' the sql results into a Django model.

With that said, its still sometimes a struggle to justify if almost all the queries are highly custom, and the app doesn't require Django Admin. Certainly there are advantages, but the minimalism of Flask and focusing on the REST interface for integration and building micro services is more appealing in some cases.

IndianAstronaut2• 11 years ago

If one wants to use these Postgres features, Flask without an ORM is the way to go. Django is too constraining, especially with its primary key limiting.

fein• 11 years ago

Same goes with Doctrine.

You can write crazy complex hydrators, or use simple result set mapping for populating custom query results into objects.

Nearly all of my complex queries for some analytics software I wrote lives in sql statements that aren't inlined in the EntityRepository of a specific entity. ORM's are nice, but doing complex stuff in DQL (doctrine) or the other query builders is more hassle than its worth.

jfb• 11 years ago

SQLAlchemy supports many of these advanced concepts.

mjrpes4• 11 years ago

One thing that would be nice is if SQL provided first class support for sub records.

So instead of "SELECT name, (SELECT GROUP_CONCAT(CONCAT_WS(',', post_id, post) SEPARATOR ';') FROM posts p WHERE p.user_id = u.user_id) AS 'posts' FROM users u WHERE u.user_id = 1",

you could do "SELECT name, (SELECT post_id, post FROM posts p WHERE p.user_id = u.user_id) AS 'posts' FROM users u WHERE u.user_id = 1".

and the query result would be { name : 'Todd', posts : [ { post_id : 1, post : 'My Comment' } ] }.

Obviously this is a simple example and could have been rewritten as a query on the posts table, inner joined on the user table, and duplicating the user's name in the result. But it becomes much nicer to have as queries get more complex.

A query that supports sub records would gives you flexibility to structure data like a JSON object and simplify the server end of REST apis.

qooleot2• 11 years ago

If you're just trying to get JSON out (for a simple query to REST api, or in a node.js environment), have you considered converting the record/recordset to json in the subquery?

The json functions in 9.3+ are pretty handy for that sort of thing. Andrew (core developer who wrote most of that functionality) and I decided to keep the api pretty lightweight, as its easy to also add your own functions to suit your needs.

mjrpes4• 11 years ago

JSON functions do look pretty helpful and go a long way, even if not first class. As you can tell from syntax I'm stuck in the MySQL/MariaDB world, so not up with everything in PostgreSQL. This and the features in the slideshare are an eye opener to me. Thanks!

zapov3• 11 years ago

Postgres/Oracle supports such constructs.

And Revenj has been using it for years: https://github.com/ngs-doo/revenj

And yes, Revenj now comes with an offline compiler ;)

ddorian434• 11 years ago

how can you do that with postgres ? a simple example / documentation link ?

exogen3• 11 years ago

With Postgres:

    SELECT u.name, json_agg(p) AS posts
        FROM users u, posts p
        WHERE p.user_id = u.user_id
        GROUP BY u.user_id;

mintplant• 11 years ago

Thank you so much for this! I spent a good chunk of today trying to work out how to do this, and had given up concluding that it wasn't possible.

danneu2• 11 years ago

What if you only want the id and markup of each post?

Postgres 9.4 gave us json_build_object:

    SELECT 
      u.name, 
      array_agg(
        json_build_object(
          'id', p.id,
          'markup', p.markup
        )
      ) posts
    FROM users u, posts p
    WHERE p.user_id = u.user_id
    GROUP BY u.user_id;

rosser7• 11 years ago

One of my favorite idioms lately for doing bulk updates without incurring lock contention is to chain CTEs, like so:

  with candidate_rows as (
     select id
       from table
      where conditions
      limit 1000
        for update nowait
  ), update_rows as (
     update table
        set column = value
       from candidate_rows
      where candidate_rows.id = table.id
  returning table.id
  )
  select count(1) from update_rows;

...and loop on issuing that query until the "count(1)" returns zero some for number of iterations (three works pretty well).

Want to add a column to your "orders" table and populate it without blocking concurrent writes for as long as it will take to rewrite a multi-million row table? Want to re-hash user passwords using something stronger than MD5, but not prevent users from ... you know, logging in for the duration?

CTEs are all that and the bag of chips.

lastofus2• 11 years ago

Do you have any resources for learning cool advanced SQL like this? I've used CTEs in the past, just from reading about them in the documentation, but I feel like I'm only scratching the surface of what's possible out there.

Vivtek• 11 years ago

Second that request. What can I read to go beyond my SELECT * from t order by c-level knowledge?

ddebernardy• 11 years ago

http://www.postgresql.org/docs/

Just read it. Seriously. Literally cover to cover, or nearly so. (Maybe simply scan the C-related sections if you're not into that, but do take note they exist. Similarly for some pl/ sections.) It might take you a few evenings, but you won't regret it. The Postgres docs is one of the best out there...

mahmud• 11 years ago

The author of the slides, Markus Winand, wrote the book "SQL Performance Explained".

rosser7• 11 years ago

Other than The Fine Manual, linked by a sibling comment, not really. Everything I've learned, I've just picked up along the way.

EDIT: When I wanted to learn about CTEs, for example, I conveniently had a large, ugly materialized view that needed refactoring, and they happened to fit the need perfectly. The previous version was, in places, five or six layers of subqueries deep, many of them repeated several times as they were reused. It hurt to read. Rewriting it to use CTEs made it about eight times faster, and the SQL script was a third the size of the original.

kibibu• 11 years ago

re-hash user passwords using something stronger than MD5

Isn't this only possible if you do it on user login - unless you are also cracking user passwords... Actually has anybody cracked their own MD5 user passwords to upgrade them?

rosser7• 11 years ago

You might be right. It was a while ago that we did that, and I may have conflated it with another bulk update of our "users" table.

Either way, I've used this idiom at least half a dozen times in production, all without any downtime or user-visible effect, and we have millions of orders, SKUs and users.

bradleyjg2• 11 years ago

You can sort of upgrade hashes in place at the cost of extra book keeping.

Take the old hash, and use it as the input to a new stronger hash. Mark some column to indicate you've done this. Then next time the user logs in, you calculate the old hash and then the new hash from that. Once you validate the password use it to calculate a new new hash, put that in the field and clear the update column.

jeffdavis2• 11 years ago

"Want to add a column to your "orders" table and populate it without blocking concurrent writes for as long as it will take to rewrite a multi-million row table?"

PostgreSQL ALTER TABLE ... ADD COLUMN is an O(1) operation, and requires no data rewrite (as long as you are OK with NULLs).

You can even add an attribute to a composite type, and existing tables using that type will see the extra attribute. Again, O(1), no data rewrite.

rosser7• 11 years ago

Yeah, it's not about adding the column; I know that's instantaneous. I'm talking about the UPDATE you'll have to issue to populate the column once it's been added — unless you're okay with it staying NULL (or doing some sort of "lazy loading" is viable in your specific scenario). If you try to do that for the entire table in one go, you're contending with concurrent writes, incurring a lot of IO at once, and as much as doubling the disk size of your table.

This way, you're hitting a limited number of rows per iteration, significantly softening the IO impact (granted, at the expense of wall-clock time); the NOWAIT fails fast on rows that have write locks out against them when you try to grab them; and (if the column you're populating is indexed, obviating HOT updates) leaving a radically smaller number of dead tuples — particularly if you up-tune autovacuum on the table in question while doing this (though that can mitigate some of the reduction in disk IO).

ddorian434• 11 years ago

Really? I had the impression that if you added a column to a composite-type, the rows that have that composite-type as a column will be rewriten (tested in 9.3 couple of months ago)?

platz• 11 years ago

Is this really different from using a transaction with separate read (using an update lock) and update statements, and looping over that in the same way from the application until the count is 0?

rosser7• 11 years ago

Yes. That incurs many of the consequences of doing them all in one query (particularly lock contention and dead tuple bloat), and takes longer — making those consequences worse than doing it in a single query.

xvirk• 11 years ago

is it even possible ?

dtech• 11 years ago

MySQL (and thus MariaDB I presume) and SQLite seem to be pretty poor in supporting these "new" features.

SQLite seems logical because it needs to be kept lean for embedding purposes, but do people know why MySQL is lagging behind so much?

weaksauce• 11 years ago

Oracle influence? Postgres is the leader now in mindshare and features for the opensource offerings. If you like MySQL you could check out mariadb

barrkel4• 11 years ago

My experience with PostgreSQL and MySQL is that PostgreSQL supports lots of features, but performance needs a very careful eye - often a neat feature isn't as useful as it seems because optimization isn't good enough. Whereas MySQL tends to be very good at what it can do, and makes it easy to do silly things, like turn off referential integrity in a slow query to boost performance.

It almost feels like a worse-is-better story. As a programmer, PostgreSQL is much better to work with; more tools, better EXPLAIN, more features, more types, more of almost everything. But to use in the heat of battle, it's less clear-cut. PostgreSQL's replication story is complicated. MySQL master-master replication is fairly easy to set up, and if you use a master as a hot failover, it all mostly just works; when the primary site comes back up, it resyncs with the failover. PostgreSQL has a lot of different replication stories - without a strong central narrative, it's hard to gain confidence.

noehp• 11 years ago

This doesn't match my experience at all. MySQL performance is highly variable, and it's incredibly easy to write simple queries that have truly abysmal performance.

Postgres on the other hand, has always tended towards making things work well (a step that mysql often skips), and then work quickly thereafter.

A lot of MySQL acolytes say Postgres is slow because, unlike MySQL, it doesn't ship with unsafe defaults. MySQL doesn't just allow you to do dumb things, it starts off with many of those settings as the defaults.

To me, the real problem is that people likebarrkel exist. He doesn't know what he's on about, but he likes MySQL. Most of wheat he wrote is flatly false, but he said it confidently. And he's employed someplace that probably uses MySQL.

MySQL got adoption for two reasons:

1) it used to be easier to install; and

2) it has unsafe defaults that mean if an idiot runs a benchmark, it wins.

That's it. That's how it won market. After that, it was network effects, and nothing else. MySQL is a turd. It requires substantial expertise to use MySQL because it is such an awful and dangerous tool. It slows you down as you get better. But most of the people who use it don't know any better, or (like barrkel) they spew nonsense that is the opposite of reality. So it wins.

Network effects suck.

moe• 11 years ago

it all mostly just works

Yes, that sums up MySQL pretty well.

And then one day, one of your masters segfaults for no discernible reason. And when you restart it, the replication process (or even the InnoDB recovery) fails to resume with a generic error message that you can't find any useful information about in the mess that MySQL calls "documentation".

That's when you realise that "it all mostly just works" is really not what you want from your database.

ako• 11 years ago

I did some tests with Postgres recently to test query optimization. Was pretty impressed, especially where it applied the correct index on an inline view (2nd example): * http://mxblog.eu/link/post/sql-is-about-what-data-you-want-n... * http://mxblog.eu/link/post/impact-of-inline-views-on-query-p...

lgunsch• 11 years ago

I've never been with a company that didn't have serious problems with MySQL replication. At least once a month master-master replication breaks horribly and must be manually fixed; often by having to delete and remove data. MySQL replication half-works at best.

threeseed2• 11 years ago

but do people know why MySQL is lagging behind so much

Because for 99% of the MySQL user base there isn't a need for these features.

Those using MySQL at scale are using them as dumb key-value stores with horizontally sharding. Those who aren't typically are using them with an ORM and so they aren't dealing with the database at the SQL layer.

Everyone else who is manually writing SQL generally was on or moved to Oracle, Teradata, SQL Server, PostgreSQL etc anyway.

tkyjonathan2• 11 years ago

This whole statement is 99% wrong

jeffdavis2• 11 years ago

It seems to be a philosophical difference. A lot of people seem to think that the database should only support primitive operations, and the rest should be left to application code.

I am not one of those people -- I think a good database system (like postgres) can make many things dramatically simpler.

gaius• 11 years ago

In the 90s the MySQL guys believed that transactions and referential integrity, should be done in the application...

ecopoesis3• 11 years ago

And yet, despite all these cool features, Postgres still doesn't support upsert or merge, which even MySQL manages to get right.

rosser7• 11 years ago

What's really funny about this comment is that, while all of the features discussed in TFA are fully standards-compliant SQL, MySQL implements none of them, and its "upsert" isn't.

EDIT: And before one slags PostgreSQL too hard for not currently supporting upsert, one might peruse the relevant pg wiki page [1] to better understand where development stands and why we don't yet have it. (Hint: it's actually kinda complicated, assuming you want it done, you know, "right".)

[1] https://wiki.postgresql.org/wiki/UPSERT

ecopoesis3• 11 years ago

"Get right" probably isn't the right term. Mostly I'm just annoyed that I can't choose an open source DB that has upsert (or merge if you want to do the standards version) and CTEs. I generally have uses for both of these in every project I work on.

tveita2• 11 years ago

Both MySQL and SQLite's implementations work satisfactorily, so I tend to think that PostgreSQL's reluctance is the perfect being the enemy of good.

It's a pattern that shows up often when you're mirroring data from a third-party, so it's a shame that the programmer has to do conflict handling for an operation that the database could easily do atomically.

stream_fusion• 11 years ago

Everyone who needs 'upsert' is forced to go and read those discussions (and probably a few more articles as well), and then implement their own version of the same thing many times over.

The fact that it's complicated is precisely the reason this ought to be solved for the general case.

Otherwise, Postgresql is still an awesome product.

dbenhur• 11 years ago

which even MySQL manages to get right.

hahahaha.

If you think MySQL gets this right, you haven't thought about it much.

http://www.pgcon.org/2014/schedule/events/661.en.html https://wiki.postgresql.org/wiki/UPSERT

tveita2• 11 years ago

None of those links say that MySQL's ON DUPLICATE KEY UPDATE doesn't work as intended.

There's a single caveat: "If more than one unique index is matched, only the first is updated. It is not recommended to use this statement on tables with more than one unique index."

That isn't a problem for most users since the use cases for upsert tends to be simple by nature, with a primary key that you want to set to the latest values whether or not it exists.

nroose• 11 years ago

I was interested in NoSQL. Went to a Mongo pres. Both examples would have been easier and faster in SQL. Even the SQL I used 20 years ago. I asked for an example that would show performance advantage. I got a tired vague statement about the vague performance advantage. Seems like snake oil to me.

cgh• 11 years ago

Unfortunately you were downvoted, but it wasn't so long ago on HN that every second story was NoSQL this, NoSQL that. There were even "SQL is dead"/"relational DBs are dead" posts, just ridiculous. So it's nice to see stories like this.

I've been writing a lot of recursive queries for Postgresql lately using CTEs. Quite cool though a little mindbending at times.

michaelchisari• 11 years ago

I remember those days. I was never sold on NoSQL, but if anything can be said, it lit a fire under SQL and competition in the space was ultimately a good thing.

Glad I didn't go all in on NoSQL, though.

crdoconnor2• 11 years ago

HN is very fad-oriented.

juliangregorian4• 11 years ago

I'm not about to go to bat for Mongo in an SQL thread (there's plenty of problems with that platform that are real), but I rather enjoy their query syntax, it's very AST-like, even through the aggregation pipeline.

I don't believe it's probably much faster even in the best case (and I'm sure an experienced SQL expert wouldn't find it any "easier"), but on a grammatical level I do find it a fresh take on query structure, and writing queries and map-reduce jobs in coffeescript was extremely satisfying because of how terse, elegant, and pseudocode-like it turned out.

vidarh• 11 years ago

"NoSQL" is generally a misnomer. It's not SQL that is/was the problem, but that there are a lot of cases where specific common properties of RDBMS' are limiting. The NoSQL moniker is a result of the fact that most of these RDBMS's uses SQL as the query language, and most of the "new" database engines does/did not.

Since then, a lot of the RDBMS's have adopted features that have reduced the gap. E.g. Postgres' rapidly improving support for indexed JSON data means that for cases where you have genuine reasons to have data you don't want a schema for, you can just operate on JSON (and best of all, you get to mix and match).

For some of the NoSQL databases that puts them in a pickle because they're not distinguishing themselves enough to have a clear value proposition any more.

But it is not the lack of SQL that has been the real value proposition.

m_mueller2• 11 years ago

Our current usecase for NoSQL is that CouchDB can be replicated to mobile devices - I'm not aware of Postgres being able to do this. So I think there still are cases where it can be useful.

mason55• 11 years ago

I love Postgres but its support for sharding, multi-master, and most forms of scaling that aren't just "buy a bigger box" is still way behind most of the NoSQL solutions.

Lots of use cases don't need that kinds of scalability but if you do then Postgres can be more difficult to work with.

lkrubner• 11 years ago

Both examples would have been easier and faster in SQL.

That's easy, so long as we mean the whole entire project when we say "faster". When I worked at Timeout.com they were importing information about hotels from a large number of sources. For some insane reason, they were storing the data in MySql. Processing was 2 step:

1.) the initial import was done with PHP

2.) a later phase normalized all the data to the schema that we wanted, and this was written in Scala

The crazy thing was that, during the first phase, we simply pulled in the data and stored it in the form that the 3rd party was using. That meant that we had a separate schema for every 3rd party that we imported data from. I think we pulled data from 8 sources, so we had 8 different schemas. When they 3rd party changed their schema, we had to change ours. If we added a 9th source of information, then we would have to create a 9th schema in MySQL. We also checked the 3rd party schema at this phase, which struck me as silly because this did not mean that step 2 could be innocent of the schema, rather, both step 1 and step 2 would have to know the structure of those foreign schemas, but it was necessary because we were writing to a database that had a schema.

The system struck me as verbose and too complicated.

It's important to note that most of the work involved with step 1 could be skipped entirely if we used MongoDB. Simply import documents, and don't care about their schema. Dump all the data we get in MongoDB. Then we can move straight to step 2, which is taking all those foreign schemas and normalizing them to the schema that we wanted to use.

For ETL situations like, NoSQL document stores offer a huge convenience. Just grab data and dump it somewhere. Simplify the process. Your transformation phase is the only phase that should have to know about schemas, the import phase should be allowed to focus on the details of getting data and saving it.

electrum• 11 years ago

You can do that in SQL databases too: store the XML / JSON / whatever in a blob. There is no need to have a normalized import schema, especially since you are doing the transformation using an external application (Scala program).

headgasket3• 11 years ago

(accidentally posted this to the root of the story)

Postgresql 9.4 with jsonb sends mongo to the dustbin, IMHO. If you have to write it in js close to the data or if plpgsql is too steep of a learning curve, you can play with the experimental plv8. But you should really pick up plpgsql, it's "python" powerful, with the an awesome db (and has a python 2.x consistent API, sadly, but the doc is very good) There is a great sublime 2.0 package that makes the writing and debugging of functions in one file just awesome. Write an uncalled dumb function that has a lot of the API in it at the top of your file, and you'll get autocomplete on this part of the API. Specifically no not miss getting acquainted with json and hstore, specifically using json as a variable size argument passing and returning mechanism, it's just hilariously effective. cheers, and keep making this place(not only HN, our blue dot) better, F

dragonwriter• 11 years ago

It sounds like you are talking about plpython rather than plpgsql.

gamesbrainiac• 11 years ago

Anyone know where I can get the full video lecture for this? I'd really like to see it.

fatalmind3• 11 years ago

I've given this talk on Saturday in Moscow again and it was video taped there. Organizers told me they'll publish the English original as well as the Russian voice over version. I'm keen for the second one ;)

I keep you posted.

jiphex• 11 years ago

It was given at FOSDEM, the videos should be available "soon".

juliangregorian4• 11 years ago

I often find myself wishing that SQL had a more uniform syntax. So much of it resembles COBOL to me, with all the capital letters and "english-like" arbitrary word order. At the same time, relational algebra exists and is very elegant, in an APL-like way. Would be very interested if there was a project to expose such an api to SQL databases.

collyw4• 11 years ago

SQL is case insensitive. It is a bit quirky though.

juliangregorian4• 11 years ago

True, but very few people write it as anything but.

domoarevil• 11 years ago

Indeed, something people forget sometimes leading to collation problems in the (odd) instance when CS is set.

ris2• 11 years ago

SQLAlchemy (for python) tries to sanitize the syntax away from SQL with some level of success.

liprais• 11 years ago

According to my understanding ,Teradata has already supported these so-called "modern" features for several years.

headgasket3• 11 years ago

Postgresql 9.4 with jsonb sends mongo to the dustbin, IMHO. If you have to write it in js close to the data or if plpgsql is too steep of a learning curve, you can play with the experimental plv8.

But you should really pick up plpgsql, it's "python" powerful, with the an awesome db (and has a python 2.x consistent API, sadly, but the doc is very good) There is a great sublime 2.0 package that makes the writing and debugging of functions in one file just awesome. Write an uncalled dumb function that has a lot of the API in it at the top of your file, and you'll get autocomplete on this part of the API.

Specifically no not miss getting acquainted with json and hstore, specifically using json as a variable size argument passing and returning mechanism, it's just hilariously effective.

cheers, and keep making this place(not only HN, our blue dot) better, F

Modern SQL in PostgreSQL

Related Stories