Modern SQL in PostgreSQL
slideshare.net
slideshare.net
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;
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.
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...
Quoting from http://Use-The-Index-Luke.com/ (the main page):
Use The Index, Luke is the free web-edition of SQL Performance Explained. If you like this site, consider getting the book. Also have a look at the shop for other cool stuff that supports this site.
Still, its good to support Markus' effort and if you prefer having a PDF rather than going to the website, just buy it. It's like $15.
Use The Index, Luke is the free web-edition of SQL Performance Explained. If you like this site, consider getting the book. Also have a look at the shop for other cool stuff that supports this site.
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.
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?
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.
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.
If you do it that way you'll pay a performance cost on every password check forever. I suppose that trade-off might worth it in some cases.
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.
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).
Additionally, however, if you're bulk rewriting the table in one query, autovacuum has no (or very limited) opportunity to mark dead tuples as truly dead — especially if there's any lock contention going on, so other, older xids are waiting for your bulk update to complete — so in the worst case your table is half dead tuples, and twice the size it needs to be.
I've been writing a lot of recursive queries for Postgresql lately using CTEs. Quite cool though a little mindbending at times.
Glad I didn't go all in on NoSQL, though.
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.
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.
Lots of use cases don't need that kinds of scalability but if you do then Postgres can be more difficult to work with.
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.
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
SQLite seems logical because it needs to be kept lean for embedding purposes, but do people know why MySQL is lagging behind so much?
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.
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.
And the issues around "safety" stopped being a concern for most developers a decade ago when the ORM was invented. So this idea that you need "substantial expertise" to use it is simply ridiculous.
You're putting words in my mouth that I didn't say. I don't like MySQL. I prefer PostgreSQL. And I have had a few rough times optimizing some queries in PostgreSQL, whereas I've had fewer such bad times with MySQL, despite using it more often. It's anecdata. Take it for what it's worth.
Time sinks in MySQL have come more from its crappy defaults, from its bizarre error handling (or lack thereof) in bulk imports, and most recently, a regression caused by a null pointer in the warning routine.
If I were working on my own project, I'd probably go with PostgreSQL and figure out the replication story. But I'm not. I do use PostrgeSQL on my personal projects.
(If there was one feature I'd add to PostgreSQL, it would be some means of temporarily and selectively disabling referential integrity. Not deferring it, not removing and readding foreign keys, just disabling. The app I work on does regular 10k-1M+ row bulk inserts, usually into a new table every time (10s of thousands of tables), but sometimes appending to an already 100M+ row table. It would be nice to have referential integrity outside of the bulk inserts, but not pay the cost on bulk insert.)
set session_replication_role='replica';
SET CONSTRAINTS ALL DEFERRED
Then do your inserts, followed by whatever work needs to be done without referential integrity, in the same transaction.
Seriously? you're getting worked up about a database and you conclude that it would be better if certain people didn't exist?
I'd like to remind you that this is HN, not the Linux kernel dev list. For all its flaws, HN still is about civility. You just wished someone out of existence over a database, can you please stop that? Grow up!
It makes for an uncomfortable community.
He created that account to "go after" me with a lot of vitriol. It's a bit mysterious though. Why would someone work themselves up into such rage over a database? Normally, you'd explain this as teenage frustration or something. But he's clearly very unhappy, lashing out.
More mystifying than offensive, since it's impossible to take seriously. How can you deal with these people.
But from complete strangers who are hammering profanity and abuse into their keyboards as hard as they can, that is quite unnecessary and doesn't make for a nice community. If someone was speaking the things some people on here post to my face, I'd be incredibly offended and they wouldn't be the sort of person you'd want to work with, hang around with or even live next door to. But they don't seem to mind typing it????
These posts rear their head in C++ articles and anything to do with OSX it seems. Really disappointing.
I suppose the best way to deal with it is just detach from it for a while, use another forum, go outside, look at the birds or stroke a cat or something.
There's something very therapeutic about picking up a fluffy cat (I have 4 British Shorthairs, great for fussing) or simply watching sparrows and small birds go about their business in the dust or seed feeders. They continue working without worries, but work hard to survive still and seem happy about it (as far as a bird can be happy). I find it a contrast to us sat in yellow-lit offices with deadlines, stresses, possibly incompetent managers/colleagues and concerns about our existence/paying bills etc.
The features that Postgres already has can be solved in MySQL, but take some work. Sometimes they require me to use temporary tables and pre-calculated summary tables.
MySQL's speed is only variable by the queries that are run against it. MySQL is "simple enough" for developers to write queries for it and in 10%-20% of those cases, those queries could be a bit more optimal or the data model could use some more tweaking. In terms of getting things done, you can do a whole lot before needing someone like me to come along and tweak things.
A performance audit from someone like me every 6-9 months after your company's website has been in use for >3 years can be perfectly fine.
Also the main reason MySQL won out back in the 90s was because they had better documentation and answered questions on their forums faster.
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.
To be clear, our master/master replication is strictly read-only at one site and read-write at the other site, and never read-write simultaneously. We have yet to see issues in production under fairly hefty write load, and we've failed over numerous times, and back again. But it's only been 10 months or so.
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.
I am not one of those people -- I think a good database system (like postgres) can make many things dramatically simpler.
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.
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.
And Revenj has been using it for years: https://github.com/ngs-doo/revenj
And yes, Revenj now comes with an offline compiler ;)
SELECT p, array_agg(SELECT c FROM "MasterDetail"."Child_entity" c WHERE c."parentID" = p."ID") as c FROM "MasterDetail"."Parent_entity" p
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;select json_agg(sub) from (select u.username, (select array_agg(p) from posts p where u.id = p.user_id) posts from users u) sub;
SELECT
json_build_object(
u.username,
(SELECT json_agg(p) FROM posts p WHERE u.id = p.user_id)
)
FROM users u
[
{"chuck": null},
{"blair": [
{"id": 1, "markup": "hello"},
{"id": 4, "markup": "world"}
]},
{"serena": [{"id": 5, "markup": "testing"}]}
]
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;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
I keep you posted.
The GROUP BY still isn't exposed directly, but neither are joins or subqueries. The rules that make up what should be included in the group by are fairly elaborate. However, writing custom expressions will now allow you to craft some pretty cool SQL, and allow the sub-expression to specify whether it should contribute to GROUP BY or not.
Disclaimer: I did a lot of the work for query expressions in 1.8. The ORM team is expanding, and people are actively working on bringing more powerful features to the ORM.
And just query from that model as normal.
Model_a.objects.all().select_related(extra_tables).aggregate(arg_params)
will group by the Model_a table.
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.
I usually try to keep my queries within the ORM, as it means things like sorting and pagination become very easy, but its lacking in certain areas.
I wouldn't choose Flask for anything; it's too slow, even by the relatively poor standards set by Tornado and Django.
What are your picks for good and fast web frameworks which are also light weight?
My additional reasons for choosing Django over a minimalist framework:
There is a large selection of third party add ons for Django. I had a look around I couldn't find anything like reversion for any other framework (maybe it exists, but I didn't find it).
There are a standard set of components, so you get a default choice. These will work together and likely have some consistency in the way they get used. Choosing your own components for Flask (or whatever framework), they may well work together, or you may get problems.
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.
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".)
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.
Satisfactory for you perhaps, but it may not be in the general case.
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.
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
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.