Hacker News Reader

Comment by lalwanivikas | Hacker News Reader

Hey Michael, not directly related to your post, but I wanted to know how a noob(like myself) can be good at Postgres?

I am a frontend developer who wants to start making(and deploying) full stack apps. I have heard that Postgres is one of the most reliable relational databases out there and I would love to use it in my apps.

Any suggestions on how to get started with Postgres? Or relational dbs in general? Books/courses/making projects/anything else?

Thanks in advance!

View on HN

I'm not Michael, and I'm not sure if this is the answer you want to hear, but if you're a frontend developer the database is likely going to be the least of your worries when you start. Making and deploying the backend is going to take much more of your time than doing Postgres well.

There's a lot of blog posts on HN about bending databases like Postgres to pretty insane workloads. Heap's workload is a nice example of that. they're interesting blog posts but don't let them distract you. Most databases really do work pretty well by default, if you just use them the simplest way possible. Most apps don't have insane workloads, and especially when you start out they won't.

If you just make a table for every "thing" in your system and link them together with foreign keys you're pretty much set. Most popular frameworks (Rails, Django, etc) do this for you without even touching SQL.

This approach is fine for nearly every case. I bet even Heap started like that. Famously, Twitter started like that as well, and the only reason that got them in trouble (remember the failwhale?) was because they were growing faster than nearly any platform ever before. Most sites don't 10x their user count every half year, and if you do, lucky you. Scaling problems are luxury problems.

Half of HN is jealous of people who have scaling problems. I sure am. The other half, well, they won't have scaling problems because they're not shipping fast enough. Their backends would scale though. They read all the books on Postgres and doing it right from the start!

This is exactly right. Install Postgres, add some indices on the columns you're querying, and you're probably good for the next ten years in that respect. Try to focus on making a product people want and making them aware of it instead. No company[1] has ever died because their tech stack wasn't good enough.

[1]: Approximately.

I ran a web agency in Silicon Valley and I encountered numerous clients who's budgets were significantly hampered by having to maintain or scale poorly designed tech stacks. MediaZone was the biggest. They chose to use an in-house, proprietary ASP.net framework for their web platform. They were live streaming major sports programming back in 06/07 like Wimbledon. They had tons of customers, but their development run rate and slow speed significantly contributed to their parent company to shutting them down. It's true that most companies never get to their scale (~200 employees), but if you want to get that big, you have to plan a text stack early on that can accommodate your aspirations.

Well, how far in advance are you planning? It's easy to over-optimize when you're starting out and you end up all tangled in an overly-complex system that is over-engineered for your needs.

Everything in moderation. You can often run a single master PostgreSQL system into the millions of transactions a second without having to resort to sharding and other complexities.

As someone who uses Django more or less full time, I don't think relying on the ORM without knowing any SQL is especially good advice.

Django's ORM is very easy to use, but it is not especially powerful, and when you want to do advanced queries (multiple join conditions for example) it often isn't possible. When someone asks me for some data from the database, usually its easier for me to go directly to the database than use the Django ORM.

Use the ORM to start with, but its most definitely a good idea to learn proper SQL .

ORM is very much like hypermonadic supertemplate C+++++ type theory, you can only play with it if you know what you're doing, but don't want all that boilerplate (cognitive or in-code unit tests) in your head.

So, I think starting with PHP and raw SQL is a good thing for motivated learners.

And if you need motivation, try building a WordPress site with themes and plugins first. And maybe an Android app. And then something cute like a LED blinker with a raspberry.

I learnt a lot of Postgres-ing while working on a project that was a SPA front-end, and then a fairly basic (unto itself) Node application using [Massive.js](https://massive-js.readthedocs.io/en/v2/) which exposes Postgres, its SQL, and its features rather than hiding them.

Then something that I felt taught me a lot was endeavouring to use only a single SQL query per HTTP request. Which meant some silly things like learning CTEs in much more depth (including stuff like doing updates and selects via multiple cte's but within a "single" query); and using more aggregation and window functions to create the JSON responses from the DB itself as much as possible. (I used some PLPGSQL as well, although I tried not to just reach for it every time, hence why I mention it in brackets rather than directly :-p )

It wasn't perfect, but it was a fun thing to do and I felt like I learnt a lot more about Postgres (in particular, but SQL/DBs on the whole) as I progressed.

(The reason I mention this is that Massive.js is really nice for Postgres, and as a frontend developer, using Node.js might be reasonably approachable?)

It's nice to see a node app using PG and not just using Mongo by default!

Craig Kerstiens has a phenomenal set of blog posts about a few different Postgres topics to get you thinking: http://www.craigkerstiens.com/content/

It's really worth checking out all his stuff - his explanations are concise and easy to follow: http://www.craigkerstiens.com/categories/postgresql/

(self plug) for learning Postgres-flavoured SQL, take a look at https://pgexercises.com.

Books-wise, Learning SQL is a great start. http://shop.oreilly.com/product/9780596007270.do

The postgres docs themselves are also pretty fantastic.

Maybe try https://postgresweekly.com for good articles and tips

The O'Reilly books are a good reference.

For theory I really enjoyed reading E.F. Codd's papers on the relational model. He was a good writer!

I am also writing a book you might be interested in https://agentultra.com/postgres-book.html where I focus on treating PostgreSQL as an application server. It covers data design, server programming, extensions, and integration with external processes and data stores.

It clicked for me after reading Joe Celko books: https://www.amazon.com/Joe-Celko/e/B000ARBFVQ

Any specific ones you can point out?

One simple way is to just have an idea of what you can do with it. Doesn't mean you have to do all of it, but having an idea of the capabilities is a good place to start.

Outside of that, there's a lot that you'll just pick up through usage. Get comfortable with basic SQL and then everything else in Postgres is a bonus.

I attempted to write a high level capability overview for that exact purpose. :-)

http://brightball.com/articles/why-should-you-learn-postgres...

I know Postgres really well because my job is mostly scaling Postgres. I got a good feeling of the tools of available by reading the Postgres manual. Then I got to learn when each tool is effective and the tradeoffs between each of them while working.

Reply on news.ycombinator.com