
Mastering PostgreSQL in Application Development - thibaut_barrere
http://masteringpostgresql.com
======
craigkerstiens
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/](http://tapoueh.org/)

~~~
qaq
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](https://momjian.us) and get access
to a ton of information and that is just one resource.

~~~
craigkerstiens
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 :)

------
jonstaab
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?

~~~
doh
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.

[0] [https://www.postgresql.org/docs/current/static/auth-
methods....](https://www.postgresql.org/docs/current/static/auth-methods.html)

[1] [https://pgbouncer.github.io](https://pgbouncer.github.io)

[2] [https://www.citusdata.com](https://www.citusdata.com)

~~~
combatentropy
> Not sure that I would do authentication directly in PG

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);
    

Apache 2.4's form-based authentication makes this attractive.

> 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...](https://www.postgresql.org/docs/current/static/using-
explain.html)) and the \timing option for the command-line client, psql
([https://www.postgresql.org/docs/10/static/app-
psql.html](https://www.postgresql.org/docs/10/static/app-psql.html))?

~~~
doh
> 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.

------
_joel
I don't know how much cross-browser testing you do, but the site looks
completely broken in Firefox, renders fine on Chrome.

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.

~~~
ethagnawl
I'm also seeing broken/missing CSS on Debian/FF 56.0.2.

~~~
_joel
Yep, that would tally, I'm on Ubuntu 17.10

------
mrlinx
Launched today, and already has the best sellers package.

~~~
swyx
you must be new to internet marketing

------
aidos
@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?

~~~
tapoueh
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!

------
bluegene
Why do you need my email to see the table of contents?

~~~
tapoueh
So that you can receive updates if I make some. You don't have to, though,
read [http://tapoueh.org/blog/2017/11/mastering-postgresql-in-
appl...](http://tapoueh.org/blog/2017/11/mastering-postgresql-in-application-
development-launches/) where you will find the full Table of Contents, or just
buy the book ;-)

~~~
alpos
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".

~~~
spacemanmatt
Email for updates being a foot in the door for further communications is pro
forma and legally regulated. I think you're unnecessarily suspicious.

~~~
Xorlev
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).

~~~
jakobegger
Whenever someone asks for an email, I just type lalelu@yopmail.com, and then I
go to [http://yopmail.com/lalelu](http://yopmail.com/lalelu) and click the
activation link.

It's a beautiful service.

------
kidfiji
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.

~~~
meritt
FWD15 gets 15% off, which basically negated the price of shipping for me.

~~~
kidfiji
Thank you kind sir. You dfinitely made me move forward with a purchase.

------
Nican
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?

~~~
manigandham
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.

------
pc86
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!

[0] [http://nathanbarry.com/authority/](http://nathanbarry.com/authority/)

------
WalterGR
Just FYI, in the "The Dead Tree Edition" section, "professionally" is spelled
wrong:

 _A professionnally printed paperback of 332 pages, with the same content._

~~~
tapoueh
Thanks, fixed now!

~~~
reducesuffering
"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!"

eletronic is also a typo.

~~~
tapoueh
Fixed now. I really should have had my reviewer work on the website too, not
just the book itself. Thanks guys!

------
odammit
The “dead tree edition.” Good play, sirs.

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.

~~~
brootstrap
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!

~~~
odammit
It feels good!

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!

------
adamnemecek
Look interesting but I'm not crazy about the fact that to get the source +
data, I have to get the $90 version.

~~~
rhizome31
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.

~~~
tapoueh
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.

------
tapoueh
I've setup an Offer Code for the next 48h guys. Feel free to use it!
[http://masteringpostgresql.com/#packages](http://masteringpostgresql.com/#packages)

~~~
throwanem
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?

~~~
tapoueh
Went to fast and fat-fingered it. Works now!

~~~
throwanem
Great, thanks again!

------
tomca32
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?

~~~
davidkuhta
disappointed /u/tomca32... easy as: SELECT email FROM authors;...

dim [at] tapoueh.org per his github:
[https://github.com/dimitri](https://github.com/dimitri)

Enjoy your day.

Edit: revised @ to [at] to reflect format in author's FAQ update.

------
djklanac
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)?

~~~
tapoueh
No, I don't address that point in this book.

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.

~~~
djklanac
Sweet. I'll give your book a read then. Gaining a more solid SQL foundation to
avoid LOOP in the first place is what I need.

------
esaym
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.

~~~
pvh
I see in the FAQ that Tapoueh promises to refund anyone who isn't satisfied
for any reason.

------
kozikow
It would be nice if I could buy it on Amazon.

~~~
garyclarke27
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.

------
nickjj
I like the idea of it targeting developers but aren't most developers going to
end up using an ORM in the end?

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.

~~~
tapoueh
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!

~~~
nickjj
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.

~~~
tapoueh
You know what? Just buy the book and if you don't like it, tell me. I'll
refund you. My guess is that you will like it ;-)

------
virtuexru
This book seems like an amazing resource however I definitely cannot afford
the price tag at $179 unfortunately. :(

~~~
jimbokun
Keep scrolling. :) The cheapest version, at $39, is way way down at the
bottom.

------
pletnes
Does it cover development in some particular language? E.g python+sql?

~~~
tapoueh
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.

------
Dowwie
Thanks for putting the time and effort into this, Dimitri

------
systems
why do i have to give my email, to even get a look at the table of content?

not saluted

------
sumedh
tapoueh why are you not using https on your site?

I want to buy the book but not sure if I should enter my card details on a non
https site?

~~~
tapoueh
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!

~~~
sumedh
Chrome still complains that the payment form is using non secure elements on
the page but anyway I bought the book.

------
alpos
Not really feeling sold on this.

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...](http://tapoueh.org/blog/2017/11/mastering-postgresql-
in-application-development-launches/) 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.

~~~
tapoueh
Yes the book targets more the SQL beginners, which are in my experience
developers of all levels, from beginners to experts.

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...](https://www.ted.com/talks/sal_khan_let_s_teach_for_mastery_not_test_scores)

