6 ms·
The Art of PostgreSQL
- radicalriddler 7y agoCan anyone speak to the quality of this book. Been looking to learn PostgreSQL and get a better understanding of SQL.
- madhadron 7y agoThis is why testimonials and other social proof are important on sales pages. :)
- grzm 7y agoI 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.
- Scarbutt 7y agoI 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.
- war1025 7y agoI 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.
- war1025 7y agoLooked 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 7y agoI 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 7y agoCould you give an example of a “lethal” query?
- war1025 7y agoDon'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.
- jfbaro 7y agoI 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 7y agoDimitri 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 7y agoFor 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.
- qaq 7y agoBeing a contributor and having commit access are orthogonal.
- samprotas 7y agoFWIW he is listed here https://www.postgresql.org/community/contributors/ https://www.postgresql.org/community/contributors/
- teej 7y agoBeing a contributor and having commit permissions are technically different things. Dimitri is listed as a contributor on the appropriate wiki page https://www.postgresql.org/community/contributors/ https://www.postgresql.org/community/contributors/
- aldoushuxley001 7y agoAnybody 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 7y agoPostgres'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
- samwestdev 7y agoI wish they published at least the book index
- codesushi42 7y agoRemember the push for NoSQL on HN years ago? Pepperidge Farm remembers.
- i_feel_great 7y agoI don't remember Pepperidge Farm. What was it?
- iKevinShah 7y agoIts a meme. More: https://knowyourmeme.com/memes/pepperidge-farm-remembers https://knowyourmeme.com/memes/pepperidge-farm-remembers
- jayd16 7y agoPostgres 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.
- mmckelvy 7y agoI'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.
- originalvichy 7y agoThanks for the recommendation! Might buy this come payday.
- majewsky 7y ago> 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?
- spiderxxxx 7y agoYou do "or <parameter> IS NULL". It makes the query plan uncacheable or requiring a godly cache, but it's fine for searches.
- mmckelvy 7y agoFor 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.
- deleted 7y ago[deleted]
- einpoklum 7y agoDon'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 7y agoAs 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 7y agoSo I need to subscribe to a mailinglist in order to get the index/free chapter? No thanks.