Re:Running 10M PostgreSQL Indexes in Production
by bruce_one on Tuesday June 13, 2017 @05:16AM
(#14543674)
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?)
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?)