If you're curious to read some of Dimitri's writing ahead of time his blog is also a great resource - http://tapoueh.org/
This is exactly the type of book I've been wanting for a long time.
There's also PostgREST, which exposes a REST API to a postgres database and has been discussed on HN before [2,3]
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:
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 would also encourage to introduce a proxy in between (e.g. pgbouncer ) 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  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.
db.exec('set role to $1', req.remote_user);
> The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote.
Isn't there the explain command (https://www.postgresql.org/docs/current/static/using-explain...) and the \timing option for the command-line client, psql (https://www.postgresql.org/docs/10/static/app-psql.html)?
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.
EDIT, I tried adding https to check (cert is wrong too, however) and it looks better. Be aware the link posted (for some reason) is HTTP only.
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!
It's a beautiful service.
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?
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.
Alternatives to the DIY approach include open-source things like Postgres-BDR, CitusData, TimeScaleDB or closed-source single-vendor things Amazon Aurora, and RedShift.
Edit: Looks like email sign ups are through ConvertKit, which is Nathan's company. Definitely Authority!
A professionnally printed paperback of 332 pages, with the same content.
eletronic is also a typo.
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.
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!
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.
Please consider a Fully Dead Edition. It would give access to all the digital content, but include a paper version of the book. Thanks!
dim [at] tapoueh.org per his github: https://github.com/dimitri
Enjoy your day.
Edit: revised @ to [at] to reflect format in author's FAQ update.
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.
For example SQLALchemy in Python and ActiveRecord in Rails.
Is the book a ton of SQL examples or general theory?
Most of my day to day database activities as a web developer are creating good indexes, data modeling and setting up queries with an ORM.
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!
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.
My recommendation is to just directly learn SQL instead.
Does anyone have any stats on what percentage of developers use an ORM?
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).
I want to buy the book but not sure if I should enter my card details on a non https site?
It's called "Mastering PostgreSQL" but the site reads like it's trying to convince beginner programmers that SQL iteself is useful.
Even the case made here: http://tapoueh.org/blog/2017/11/mastering-postgresql-in-appl...
seems like it would only be convincing to someone who has never had to write much SQL and also has not already had to do a pattern like that in an API call.
The title is inspired to a great deal by an idea of how to best learn new things, which is very nicely covered in the following Ted Talk: https://www.ted.com/talks/sal_khan_let_s_teach_for_mastery_n...