I found the first edition to be quite useful and practical. If you've been around the block once or twice, it can be difficult to find references for material that will extend what you know, give you some additional perspectives on things you already do, and provide a look in how another developer goes about every day tasks to be productive. The first edition did that for me. Good stuff.
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.
I purchased the book last night and have spent maybe an hour or two looking through it.
My assessment is that if you're a beginner to databases and database theory, you'll probably get a lot out of the book.
Having taken several courses on database design in college and having been working with Postgres almost daily for the better part of a decade now, not a terrible lot of new info.
Still, for $30 (The basic edition), probably not a bad investment. Just don't expect it to blow your mind or anything.
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.
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!
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.
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.
> 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.
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.
If that was not clear, the root comment mentioned that Dimitri was a committer, and it got edited afterwards. Regarding the book, I have a copy of the first edition close by (with a dedication from the author), and for application developers I think that it provides good insight about various aspects.
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.
Postgres supports json types and some other NoSQL type features these days. I wouldn't pick anything else over Postgres if I could help it, but lets not get toooo snarky about it.
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.
> 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?
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.
> No more "models", ORMs / query builders, and crunching data in app code
I think you've overstepping there. I definitely think it's great if not essential to understand the underpinnings of your RDBMS and how to actually use it, but an ORM is a really useful tool. It keeps a lot of crap from reaching your database (incorrectly cast data, malicious input, etc) and hands you back a format that is easy and logical to work with, as well as managing schema changes for you without destroying your whole database.
If your ORM won't let you do your number-crunching in-database, it's a shitty ORM. You were right to throw it straight into the ocean... But mine does.
Again, I'm not in any way suggesting you shouldn't know SQL —it's great to be able to understand what your ORM is doing, allowing you to improve it— so this book sounds essential for us Postgres lifers... But don't insist on walking everywhere because your car broke down. Buy a better car.
I'm not sure an ORM adds much in the way of casting data or preventing malicious input. Both of those features are readily available in Postgres itself. As far as formatting, the usual drivers (e.g. node-postgres) return rows as simple arrays of objects with keys corresponding to column names. That format seems easy enough to work with.
RE: number-crunching, I was referring to the fact that leaning more heavily on Postgres allows you to skip the number crunching altogether and just output the end results (e.g. using aggregates, window functions, CTEs, lateral joins, etc.).
> I'm not sure an ORM adds much in the way of casting data or preventing malicious input.
My ORM is constantly converting things backwards and forwards using in-database functions. The biggest change here is casting internal datetimes to the right timezone.
This and input mitigation is all possible to build into your queries, but a) you have to remember to do it every time and b) you have to do it. I've got better things to do than debug my crappy SQL (at least that's what I tell myself).
> using aggregates, window functions, CTEs, lateral joins, etc.
Yes, that's what I'm talking about too. A good ORM lets you use those in-database features from the ORM, building queries that use the features of your RBMDS.
I think we have very different experiences of what an ORM can do. They're not equal.
For some background, I've been using Django for a decade. It hasn't always been as good as it is today, but doing the sorts of things we're talking about here is stupid-simple. Plus you get to stay within a Model-based environment so you can encase this data in business logic, or get raw data, or use that query inside another aggregate.
Here's some Django (me playing around in shell_plus on real models with real data, start is a datetime) and the SQL it's running.
today = datetime.date.today()
Booking.objects.filter(start__date=today).count()
SELECT COUNT(*) AS "__count"
FROM "bookings_booking"
WHERE ("bookings_booking"."start" AT TIME ZONE 'Europe/London')::date = '2019-08-22'::date
And if you want to do something a little fruitier like pulling back the average booking.cost for bookings over the next week, it's pretty simple if you understand how things like `.values()` transforms a query.
Booking.objects.filter(
start__date__range=(today, today + timedelta(days=7))
).annotate(
date=Trunc('start', 'day', output_field=models.DateField())
).values(
'date'
).annotate(
avg_cost=Avg('cost')
)
SELECT DATE_TRUNC('day', "bookings_booking"."start") AS "date",
AVG("bookings_booking"."cost") AS "avg_cost"
FROM "bookings_booking"
WHERE ("bookings_booking"."start" AT TIME ZONE 'Europe/London')::date BETWEEN '2019-08-22'::date AND '2019-08-29'::date
GROUP BY DATE_TRUNC('day', "bookings_booking"."start")
It's not perfect, but it's not far off. There's also enough tooling around the ORM to detect bad queries and fix them.
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.
44 comments
[ 2.2 ms ] story [ 55.9 ms ] threadMy assessment is that if you're a beginner to databases and database theory, you'll probably get a lot out of the book.
Having taken several courses on database design in college and having been working with Postgres almost daily for the better part of a decade now, not a terrible lot of new info.
Still, for $30 (The basic edition), probably not a bad investment. Just don't expect it to blow your mind or anything.
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.
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.
I think they need a more meaty sample chapter than the one they have (first chapter). Also the formatting in the PDF could use some work.
Reads pretty decently on a phone, so there's that, and the line numbers in code blocks seem a nice touch.
Saying that, the man is really cool and his book is very good, so that's a must-have for developers in my opinion.
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.
https://www.postgresql.org/community/contributors/
By the way, I used to think Citus was bought out by IBM. By no, they are part of MicroSoft.
https://www.postgresql.org/docs/current/textsearch.html
Pepperidge Farm remembers.
More: https://knowyourmeme.com/memes/pepperidge-farm-remembers
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.
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?
https://www.elastic.co/guide/en/elasticsearch/reference/curr...
I think you've overstepping there. I definitely think it's great if not essential to understand the underpinnings of your RDBMS and how to actually use it, but an ORM is a really useful tool. It keeps a lot of crap from reaching your database (incorrectly cast data, malicious input, etc) and hands you back a format that is easy and logical to work with, as well as managing schema changes for you without destroying your whole database.
If your ORM won't let you do your number-crunching in-database, it's a shitty ORM. You were right to throw it straight into the ocean... But mine does.
Again, I'm not in any way suggesting you shouldn't know SQL —it's great to be able to understand what your ORM is doing, allowing you to improve it— so this book sounds essential for us Postgres lifers... But don't insist on walking everywhere because your car broke down. Buy a better car.
RE: number-crunching, I was referring to the fact that leaning more heavily on Postgres allows you to skip the number crunching altogether and just output the end results (e.g. using aggregates, window functions, CTEs, lateral joins, etc.).
My ORM is constantly converting things backwards and forwards using in-database functions. The biggest change here is casting internal datetimes to the right timezone.
This and input mitigation is all possible to build into your queries, but a) you have to remember to do it every time and b) you have to do it. I've got better things to do than debug my crappy SQL (at least that's what I tell myself).
> using aggregates, window functions, CTEs, lateral joins, etc.
Yes, that's what I'm talking about too. A good ORM lets you use those in-database features from the ORM, building queries that use the features of your RBMDS.
I think we have very different experiences of what an ORM can do. They're not equal.
For some background, I've been using Django for a decade. It hasn't always been as good as it is today, but doing the sorts of things we're talking about here is stupid-simple. Plus you get to stay within a Model-based environment so you can encase this data in business logic, or get raw data, or use that query inside another aggregate.
Here's some Django (me playing around in shell_plus on real models with real data, start is a datetime) and the SQL it's running.
And if you want to do something a little fruitier like pulling back the average booking.cost for bookings over the next week, it's pretty simple if you understand how things like `.values()` transforms a query. It's not perfect, but it's not far off. There's also enough tooling around the ORM to detect bad queries and fix them.(Not relevant if you need to process transactions.)
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.