
The Art of PostgreSQL - _JamesA_
https://theartofpostgresql.com/
======
jfbaro
I am going to buy the book for two reasons.One for its content and Two to
somehow thank Dimitri Fontain for all his work on Postgres. He is one of the
main Postgresql contributors.

~~~
ioltas
Dimitri is a major contributor of Postgres for a very long time, still he is
no committer:
[https://wiki.postgresql.org/wiki/Committers](https://wiki.postgresql.org/wiki/Committers)

Saying that, the man is really cool and his book is very good, so that's a
must-have for developers in my opinion.

~~~
hardwaresofton
For those fact-checking this:

[https://twitter.com/tapoueh](https://twitter.com/tapoueh)

> PostgreSQL Major Contributor (CREATE EXTENSION, Event Triggers), pgloader,
> el-get

This is a _very_ bold lie to put on your Twitter, I'm going to go ahead and
assume he's not one and the postgres wiki needs to be updated

[EDIT] - I stand corrected, everyone (but me) is right -- He is a
_contributor_ , not a _committer_ to the Postgres project, and considered at
the very least a major/notable contributor. No one stated that he was a
committer but the comment above clarifying that he's _not_ one is correct.

~~~
btown
Contributor != committer. Great projects need both people who are sticklers
for process and code quality, and people who can focus on push the boundaries
of what is possible while knowing that others have their backs on process. It
appears the author is on the boundary pushing side, and that doesn’t diminish
his contribution in the slightest.

------
mmckelvy
I'm a web developer (React / Node.js) and I bought the first edition of this
book (it was called Mastering PostgreSQL in Application Development then). I
can say that it really changed the way I approach building apps. I definitely
lean on the database _a lot_ more now. No more "models", ORMs / query
builders, and crunching data in app code. Just build your schema, write plain
sql queries, and you're off to the races. As a bonus, I'm also much more
comfortable with Postgres' built in command line tool, psql.

The scope of the book is pretty broad. You'll cover psql setup, a little
relational theory, some nuts and bolts about the inner workings of Postgres,
indexing strategy, data types, aggregates, window functions, common table
expressions, etc. Given the scope, it's difficult to cover everything in
depth, so I'd treat this book as more of an introduction to what's possible.

Highly recommended.

~~~
majewsky
> No more "models", ORMs / query builders, and crunching data in app code.
> Just build your schema, write plain sql queries, and you're off to the
> races.

I tend to do it the same way, but how do you deal with the lack of a query
builder when you truly need to build a query on the fly? For example, what do
you do when implementing a REST endpoint like GET /things where the user can
give any number of 10 query parameters to filter the result?

~~~
mmckelvy
For cases like this, I use pl/pgsql functions and dynamic queries. Dynamic
queries take extra care and aren't the most pleasant things to write, but they
can handle the GET /things with 10 parameters endpoints quite well.

------
war1025
Looked at the website and didn't really get much of a feel for what was
actually in the book.

I will say though, having a good knowledge of SQL (we use Postgres where I
work) can help you do some really neat things.

There are probably three or four large new features I've added to our system
in the past couple years that have boiled down to "Pre-process the input and
then ask Postgres for the answer"

Databases are very powerful. They are even more powerful when you know how to
use them right.

~~~
pupdogg
I couldn’t agree more with your last comment. A lot of folks strive for
optimizations in their application language of choice...relying on standard
SQL queries (aka joins and unions that fit most use cases but not specific
ones). ORMs have also come a long way...but...if you know your SQL, you can be
lethal!

~~~
trevyn
Could you give an example of a “lethal” query?

~~~
war1025
Don't know that its necessarily "lethal", but one thing I've learned over the
years is to let the database do the calculations for you.

For example, we've had numerous places over the years where we would pull
hundreds or thousands of records out of the database, and then process them on
the server to get just a handful of result values. Moving the calculations
down to SQL saves a ton of bandwidth and also lets the query optimizer make
good choices for you.

Learning how to analyze queries and write the proper indexes to make things
run fast is also a good skill.

In Postgres, learning how to use the builtin json functions is very helpful
too.

Also learning about aggregate functions and things like that.

Really I've found that most of my learning happens when I come across
something that people are saying is too slow, and then I read up and think
about the problem and usually learn a new trick or two to make everything work
a little bit happier.

Also even just the very basic things like properly setting up foreign keys,
constraints, etc. can get you a long way. For a long time my coworkers were
reluctant to do anything that operated on more than one table at a time. And
if they did it was all using ORM magic, which is useful in a way, but also
pretty handicapped as far as efficiency.

------
radicalriddler
Can anyone speak to the quality of this book. Been looking to learn PostgreSQL
and get a better understanding of SQL.

~~~
Scarbutt
I skimmed through the first edition, the book is heavily biased towards
telling you to do everything you can(business logic) in the DB versus doing
stuff in application code. Whether that's good or bad, it depends, but the
book philosophy is to cram as much as application code you can into the DB,
citing performance and less code to write as reasons.

------
samwestdev
I wish they published at least the book index

~~~
fero14041
It is available in the free chapter, with foreword etc.

~~~
slezyr
... which needlessly(to a user) behind email block.

------
aldoushuxley001
Anybody know if this book touches on the more recent features added into
Postgresql for Full-Text Search? Hard to tell what's in the contents. Seems
like a great resource though.

~~~
hardwaresofton
Postgres's documentation on FTS is actually really excellent, have you looked
at it?

[https://www.postgresql.org/docs/current/textsearch.html](https://www.postgresql.org/docs/current/textsearch.html)

------
einpoklum
Don't use PostgreSQL or MySQL/MariaDB for analytic work - they're super-slow
at that. Try systems like MonetDB, ClickHouse (not a full DBMS) etc:
[https://en.wikipedia.org/wiki/List_of_column-
oriented_DBMSes](https://en.wikipedia.org/wiki/List_of_column-oriented_DBMSes)
Column stores are where it's at.

(Not relevant if you need to process transactions.)

~~~
stingraycharles
As someone who works for a high perf timeseries database (QuasarDB), the first
thing I tell potential clients is to solve the problem with PostgreSQL.

For people who are still exploring their problem domain, using something as
flexible as Postgres is a blessing. You’ll be surprised how far you can get
nowadays in terms of performance with it.

Only once you properly understand your problem domain, and more precisely the
limitations of PostgreSQL that you are running into, you can start figuring
out your actual requirements and look for an appropriate database that goes
with it.

------
vldr
So I need to subscribe to a mailinglist in order to get the index/free
chapter? No thanks.

