Only time I had memory issues with PostgreSQL when I used too much data in arrays and json's
… Only time I had memory issues with PostgreSQL when I used too much data in arrays and json's. Those are memory only variables. For example, I'd return a huge json to client and I'd run out of memory on PostgreSQL. I started streaming row by row and problem solved.
"2. Push all your application logic into Postgres functions and procedures"
Why are functions and procedures (an abstraction layer at db layer) considered harmful to performance when the same abstraction layer will be required at the application layer (introducing out of process overhead and possibly network traffic)? I don't agree with this advice. (Or I don't understand it.)
Fwiw the specific case which motivated that section in the post was a set of recursive functions we used to denormalise an irregular graph structure (so not suitable for CTE) into a single blob of JSON to be sent to another data store. 99% of the time there were no issues with this but at times of heavier load and on complex subgraphs, those recursive call stacks contributed to severe replication lag on the replicas they were running on.
Moving the traversal logic into the application and just sending SQL queries to Postgres (we don't use an ORM) eliminated the lag. RTT between the application and the db was a few ms and this wasn't user-facing logic anyway, so extra latency wasn't an issue in this case.
Probably the fundamental problem here was a sub-optimal schema, but sometimes you're just working with what you've got. Plus a commenter on Reddit pointed out that if we used pure SQL fu…
I’m fully against using triggers to implement business logic, but find stored procedures can be great for encapsulating some elements of application/business logic. I’ve got several applications that access the same databases, and putting logic in the database avoids needing to duplicate it in clients.
Most comments about debugability are nonsense. It’s just different, with some pros and cons. One simple example - if you have a bug in production, you’re not going to attach a debugger to your production application. But you can absolutely open a readonly connection to the database and start running queries, invoking functions, etc. It helps if you can architect your functions to distinguish pure readonly functions from those with side effects, but you can still debug even if that’s not the case.