Tatiyants

What helped me a lot getting away from being limited by ORM capabilites while on the job:

- Get the raw SQL of some slow or memory intensive queries the ORM produces and try to optimize them by hand. Try different approaches to get the same result, measure and understand them by using 'explain' and visualizers like http://tatiyants.com/pev/

This works great together with libraries like sqlalchemy where the ORM is optional and build upon a abstraction of SQL that you can use directly. This way you can use the ORM for the 80% where it works just fine and hand write the rest in Python without having to deal with raw, fairly inflexible SQL in application code.

- Try moving workload from the application to the database. In the past I often ended up doing ORM queries to get a large numbers of objects and then further process and even join them in Python. In Most of these cases doing it in the database is way more efficient and lets you get away with a slow language, synchronous requests running on small servers for a surprisingly long time.

- Do business intelligence type queries for reports and monitoring. Through doing this I discovered a lot of database features that I didn't encountered commonly in web application development but that nonetheless came in handy several times for it. Also since you often need to combine data in ways that it wasn't necessarily originally designed for, you really need to start thinking about how your data is structured and how to get it in and…

My previous job(s) were primarily MSSQL and I was excited to get to use PG in my current position. In general I agree with the premise of the article - for just about everything PG is better. For me, the only places where MSSQL has an edge is:

- Management Studio is superior to any standard query manager ive found for PG. I currently use what is in intellij (which is similar to their datagrip product) which I find perfectly acceptable and in some ways superior to Management Studio, but PGAdmin isn't in the same ballpark.

- MSSQL visual query explainer is great - ive found some alternatives (http://tatiyants.com/pev/#/plans) and in general ive gotten much better at reading and understanding the text output of PG's explain, but I do miss MSSQL in this regard.

- MSSQL CTE's not being optimization fences meant that I got to use them in more places. But I can do more things with CTE's in PG (such as using an ORDER BY) - this is relatively minor but annoying - readability with CTE's vs subqueries is much improved.

- Snapshot and transaction logs with MSSQL is much better than any alternative ive found with PG, but tbf I haven't looked into it very far. When it comes up though it would be really nice to be able to restore to point in time easily with PG.

- I do occassionally come across missing functions in PG, but thats getting more and more rare.

- Cloud offerings - they exist for PG and are great for what they offer, but many extensions are not available or not fully…

> what is the distinction between "system-level" and "application-level"?

I think it mostly comes back to scope or encapsulation. I expect my host to provide facilities external to my program (databases, sendmail, a webserver), and I expect to have control over the software libraries internal to my program.

Perhaps you're also asking why APT or Yum, which are great at managing system-level package availability and versioning, couldn't be adapted for local use by applications. I'm not sure there's a good answer for that. Maybe it's just portability? If BlubLang runs on 9 platforms, then BlubPM needs to run on those same 9 platforms. It's probably easier to get there if BlubPM is written in BlubLang.

> What happened in the past 10-15 years that changed the world so much that whereas before we ran our applications on top of the system, now we seem to want to run them in individual sandboxes, often inside of other sandboxes inside of other sandboxes?

VMs? I once heard a terrible analogy to the effect that we used to treat servers like pets: we gave them cute names and we nursed them back to health when they got sick. Now we treat them like livestock: if one gets sick, you shoot it and get a new one. There's a hilarious send-up of hand-maintained systems in the "DevOps is Ruining My Craft" article at http://tatiyants.com/devops-is-ruining-my-craft/

The more you isolate the applications from the host, the easier it is to redeploy them.

Other tips:

- Change the field type from JSON to JSONB (better storage and the rest) https://www.postgresql.org/docs/13/datatype-json.html

-Learn the json in-build functions and see if one of them can replace one you made ad-hoc

- Seriously, replace json with normal tables for the most common stuff. That alone will speed up things massively. Maybe keep the old json around in case, but remove when it become old(?)

- Use views. Views allow to abstract over your databaee and allow to change internals

- If a big things is searching and that searching is nkind of complex/flexible, add FTS with proper indexing to your json then use it as first filter layer:

SELECT .. FROM table WHERE id IN (SELECT .. FROM search_table WHERE FTS_query) AND ...other filters

This speeeeeedupppp beautifully! (I get sub-second queries!)

- If your query do heavy calculations and your query planner show it, consider move them into a trigger and write the solved result into a table, and query that table instead. I need to loans calculations that requiere sub-second answers and this is how I solve it.

And for your query planner investigation, this handy tool is great:

https://tatiyants.com/pev/#/plans

That page yields a 504 Gateway Time-out right now. Cached at http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R0E1cJ:tatiyants.com/postgres-query-plan-visualization/+&cd=2&hl=en&ct=clnk&gl=us

The demo at http://tatiyants.com/pev/#/plans works.

Unfortunately it has some glitches.

Hints for using it:

* In psql use \o plan.txt to redirect the output of explain to a file. It will be a long output because you must use EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) and copying it from the terminal won't be fun.

* Remove the first two lines (header) and the last two (footer) leaving only the json data. Remove all the + characters at the end of every line. Suggestion to the author: the tool should handle that.

That said, it works. It really is much clearer than the output of explain in the terminal and as a result I'm googling how to speed up sort now. Thanks.

Good addition! Here are some more:

https://ferd.ca

https://blog.filippo.io

https://lethain.com

http://movingpackets.net

https://www.integralist.co.uk

https://peter.bourgon.org/blog

https://engineering.linkedin.com/blog

https://begriffs.com

https://mijailovic.net

http://tatiyants.com

http://blog.pentestbegins.com (seems down right now?)

https://kalzumeus.com/archive

http://blog.stephenwolfram.com

https://paragonie.com/blog

https://www.filfre.net

https://brandur.org/articles

http://archagon.net

https://eklitzke.org

I guess I could go on forever…