As someone that cares greatly about the Postgres ecosystem there aren't nearly enough books out there, so really excited to see this one come to life. Instead of targeting DBAs the focus on app devs is very refreshing so that people can start to leverage more of the awesome in Postgres. I haven't yet read the book myself but absolutely will.
If you're curious to read some of Dimitri's writing ahead of time his blog is also a great resource - http://tapoueh.org/
I think it's to a degree a function of significant volume of high quality material available in the open. It's hard to sell a book when you can go to Bruce Momjian's site https://momjian.us and get access to a ton of information and that is just one resource.
Agreed there are a number of pieces out there in the open, but not many in a consolidated form or that flow from one step to the next. Perhaps it is because there are a number that blog about all things Postgres, but a really common question I hear from people is what book should I pick up if I want to become an expert at Postgres. And while some books are out there, there hasn't been a perfect answer to date, hoping this one helps :)
I've been wondering for a while about the practicalities of using postgres as a replacement or near-replacement for a traditional backend - does anyone have any opinions on how far it's wise to go with this (postgres-only? authentication? direct connections from the client?) and/or how feasible that kind of thing is?
Derek Sivers wrote in 2015 about merging more of the business logic into postgres, including generating JSON in postgres [0]. I've personally used this approach and found it quite powerful. There may be downsides to that approach if somebody else is going to be maintaining the project and doesn't understand postgres too well.
There's also PostgREST[1], which exposes a REST API to a postgres database and has been discussed on HN before [2,3]
This depends a lot on the usecase. You have to consider things beyond the simple serving of requests, things like rate-limiting, caching, logging, event tracking, for example.
About a year ago I designed a system for a client that was just Postgres with a Go http frontend. Go was used to handle http requests and responses, to translate the API from http to postgres functions/views, and serve the response straight from postgres. Even authentication and access control was handled using postgres's role system and RLS was looked into (and found to be viable for when needed). Postgres was a good choice because the project involved mainly a lot of data wrangling. Of course, I could do it this way because it was a completely internal system with a fixed number of users, and the burden of maintenance was minimal.
Main reasons for not going this direction, IMO, would be:
1. Developer proficiency, while building and maintaining. Far more people know Python, Ruby, JavaScript etc. than SQL. Far more people know how to think in imperative programming than to think in data models. Of course, one can write Postgres-hosted imperative programs as functions (in languages ranging from plpgsql to javascript), but at that point using the same language in a node or django app is much easier.
2. Unsuitability. Some things are just not suited to be run in a database. Realtime multiplayer games, multiple-source data compositing APIs, view rendering etc. come to mind. The truth remains, that general purpose programming languages (or special purpose, where the purpose is serving, languages) and environments have more possibilities than an environment that grew around dealing with data.
I haven't put anything into production yet but I've been playing with PostGraphQL (https://github.com/postgraphql/postgraphql). It automatically creates a GraphQL endpoint based on your schema using Node. You define your application logic as functions in your schema. It also can handle authentication for you. I'd definitely recommend checking it out.
Not sure that I would do authentication directly in PG (although it has many different methods [0]), I think it's still doable. You could move whole oAuth to PG through PL/SQL (maybe there is an extension for it?).
I would also encourage to introduce a proxy in between (e.g. pgbouncer [1]) to help you to handle connections and deal with the authentication.
We're using PG as "the brain" of our service since 2014 and it never failed us. The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote. However once you have functional logic, you can write fairly lightweight application layer around it and change it as often as you want.
You can also scale this setup almost indefinitely thanks to extension like citus [2] and still keep your application layer fairly thin.
> I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache.
I figured. If you isolate the data well enough, it could work. I'm always paranoid when it comes to DB.
> Isn't there the explain command and the \timing option for the command-line client, psql?
Yes, there is. This however doesn't help you with triggers and UDF, which is how you usually create the logic in the DB.
Hmm. Firefox 56.0 running on Ubuntu 16.04 and it's working fine minus the hijacked scroll. Note to all front end developers: STOP HIJACKING SCROLL! It's bad UX. No exceptions.
@tapoueh what level is this targeted at? It looks like it starts from a relatively basic level and covers pg usage fairly broadly. Would you recommend it to advanced users, or does it not go too deep into advanced usage?
The book definitely go into advanced usage. If you're already fluent in LATERAL joins, GROUPING SETS, Arrays, and JSON techniques, you might get bored to some degree.
Every single chapter has some beginner, then intermediate, then advanced topics covered, so I think everybody should have some of the right content, really. I tried and paid attention!
Best not to sneak an email collection scheme in on people even if it really is only for your newsletter.
Most people have more than enough spam in their inbox already, if someone wants to get updates it's very easy for them to just click a button that says "Click here to get updates".
Even if it isn't nefarious, it's often unwanted. I usually turn around if I have to provide my email to see some sort of whitepaper or sample because it means I will have to later unsubscribe from whatever email list I was put on and hope they don't share the list with others (which happens, often).
If only there was an email service that let you create multiple email addresses for free! Or better yet one that let you create an email address that only exists for 10 minutes. A 10 minute mail service if you will.
The background image in the e-mail collection form can be opened in a new tab. It's full-size, so you can see the ToC without buying to book or signing up.
I wish there was a discount code for the Dead Tree Edition. I have a lot of trouble focusing on reading PDFs on my computer versus having a physical book in front of me. And I don't have a tablet, unfortunately.
I have a curiosity: Some new SQL technologies, such as NuoDB, or MemSQL, chose to implement horizontal scaling by separating the data storage and transaction processing machines. I find this awesome because the data storage can scale independently from the heavy lifting of maintaining transactions and running big queries.
Additional machines can be added or removed from processing queries without having to scale the storage, and not have to worry about the complexities of data sharding, and data rebalancing.
Does anyone have experience with this kind of paradigm? Can PostgreSQL do something similar?
MemSQL doesn't quite do that. It has 2 tiers, aggregators and leaves. Leaf nodes store the data but they also do computation of local node data while aggregators split up the original query and assemble results with final ordering and filtering.
PostgreSQL can do something similar by using FDW (foreign data wrappers) to child databases, which can be other postgres nodes or different datastores entirely. It's still relatively naive with access but possible, and I'm not aware of any polished product that will do it all for you. The closest is systems like Citus, PipelineDB, TimescaleDB that work as extensions but the nodes do both compute and storage.
However there are now several SQL execution engines that you can use like Apache Spark (SQL), Apache Drill, Presto, Dremio, and others that will run SQL queries and joins over several different data sources, so you can scale each layer independently. It works especially well for data lake/warehouse needs where you can run an elastically scaling group of execution nodes against files in a cloud storage bucket. Not as fast as a focused system but cheap and effective.
Postgres doesn't have anything built in today, though a single node will comfortably scale to ... let's say somewhere between 100GB and 10TB depending on your use-case. Beyond that you can do read scaling with replicas, implement something with Foreign Data Wrappers, or hand-roll a sharding solution and stay on vanilla.
Alternatives to the DIY approach include open-source things like Postgres-BDR, CitusData, TimeScaleDB or closed-source single-vendor things Amazon Aurora, and RedShift.
Looks like a pretty rote implementation of Authority[0], but with a more modern UI. I wish we used PostgreSQL at my day job, this looks pretty interesting! I particularly like the idea of including the Docker container at the highest price point.
Edit: Looks like email sign ups are through ConvertKit, which is Nathan's company. Definitely Authority!
"The Full Edition comes with the eletronic book package and an archive with a PostgreSQL dump file ready to be restored in your own instance, plus all the 265 queries as separate files for you to run and edit!"
I’m going have to get that one though. I can’t seem to focus/finish when reading educational books on my kindle or in PDF form.
I like the “turn thousands of lines of code into queries”... I can’t count the number of times I’ve seen application code doing something a query could do either to appease an ORM or because a backend engineer wasn’t fluent in intermediate SQL.
Yeah... i'm totally guilty of abusing the 'python can do it' hammer instead of leveraging the DB. Just last week i looked at an old snippet of code that was essentially a 'query data, load in python, process, shove back in postgres' workflow. I was like, AHA i know better. spent 15 mins figuring out how do accomplish the same thing in the database. Now that shitty code is gone, replaced with 1 SQL statement that runs in seconds, compared to tens of minutes!
I had a rails/ar based report that I "fixed" at my last job that when it ran it generated 1100 reads (not even N+1, a lot of other crazy aggregates, etc).
It was complicated, but I got it into one query, subquery and a partition.
Went from 45s of app time to process to a <1s query!
True and it's unclear how usable cheaper options will be without those files. It's pretty much a de facto standard that source code going along a technical book is available for free. This is too bad because I would have purchased it instantly otherwise.
The book contains references for every data source it uses, all of them are available as Open Data. So you can load all the data, and copy/paste all the SQL queries with just the Book Edition.
The Full Edition isn't so much about the code contained in the book (SQL queries) as it is about having the very database used in the book handy to replay the queries.
I understand that the Full Edition doesn't make the same value for everyone, and it's fine. That's why it's not the only option. Given how many are sold compared to the other options, I'm confident it still is a solid option though.
Yep, the only version that's attractive enough (enterprise) is about 3/4 of the minimum wage here. I can't buy it, and it's a hard sell to my bosses, especially when the company doesn't even have more than 10 employees.
Thanks! That's awesome of you, but when I try to use it (as "hackernews") on the Enterprise Edition, the payment form's telling me it is invalid. What's up?
That's a good idea, unfortunately the providers don't know each other and don't make it as easy as it sounds. Given interest into that, I will see about making something happen, if possible. Thanks!
I bought the dead tree edition. The FAQ page says that I should send author an email with the receipt to get the pdf and mobi version; however, I can't find the author's email address anywhere. Any clues?
I didn't see any reference to PL/pgSQL in the table of contents. Does your book go into common strategies with stored procedures (e.g. When to LOOP to build a temp table vs. using table joins)?
It gives you plenty SQL techniques to avoid ever having to LOOP in a PL/pgSQL function, and show how to use PL/SQL rather than PL/pgSQL each time it is possible, though.
Looks interesting, wish I could "preview" before buying. Thankfully before venturing out on my own in the web development world, I was a member on a small business analytics team were we would all regularly write 3+ page SQL queries for reports. Sometimes they's take days to run. It was awesome and horrifying all at once but a good experience nonetheless and has allowed me to do what I do today.
I agree, I was disappointed not to find this on Amazon, would have bought if there, found another book with the same name though!
I do ALL my reading on iPad Kindle app, thousands of books, I can’t be arsed to fill out forms to buy a book also other book or pdf readers I’ve tried have always been crap compared to kindle app.
I spent years in all sorts of ORMs and have sworn off them in favor of pure-SQL patterns like yesql (for Clojure) and massive-js (for Node.js). For me, the boilerplate of writing SQL queries is trivial compared to the productivity and performance inefficiencies of trying to understand and work around the magic of ORMs. Writing pure queries also encourages good practices like writing field lists in your SELECT statements and using more of Postgres' built-in functions.
The book covers some theory when it's needed and comes with lots of practical examples, all using real world data sets to make them easier to understand and relate to.
Using an ORM and how to best integrate “raw SQL” in your code is also discussed, and data modelling gets its own chapter too. I think you will like it!
Is it aimed at developers from ground 0, or someone with 10+ years of experience?
I've been working with both raw SQL and ORMs for a pretty long time. I'm definitely not at the DBA-tier and really complex queries scare the shit out of me but I haven't ever hit a complete roadblock when it came to modeling and querying data in a reasonably efficient way.
With that said, an ideal book for me would be one that just showed 100+ real world data modeling examples and how to query it in a bunch of real ways. Ideally an expert's thought process would be explained every step of the way. It'd happily pay $100+ for that just because I'm sure using it as a reference guide would save me enough hours in dev time to make it worth it.
I have used PostgreSQL for 12 years. Early on I tried a few ORMs and I found them to be just something else to learn. If I had any issues, I had to figure out how it was all working on the SQL back end anyway.
My recommendation is to just directly learn SQL instead.
IMO an ORM is not a substitute for knowing SQL, much less studying the actual database.
To me, an ORM is a tool that allows someone that knows SQL to write statements they would write by hand using business-level vocabulary.
Also, you have to be savvy enough to know when you are hitting the max expressiveness of the ORM and need some powerful SQL you better write by hand. Active Record certainly provides API for that, and it is an API you are supposed to leverage when the circumstance arises (Rails team over here).
Mastering PostgreSQL in Application Development is meant to target any development environment, yet it does target some ecosystems: Python, PHP, Java and Go receive a special treatment, and I must confess Python is covered way more than the other choices.
The payment forms were in HTTPS from the beginning, but the webpages (hosted at GitHub Pages). Now it's all HTTPS with a proper domain certificate, thanks to CloudFlare. Enjoy!
If you're curious to read some of Dimitri's writing ahead of time his blog is also a great resource - http://tapoueh.org/