Re:They don't even know the fundamentals
by protomyth on Monday November 01, 2021 @10:40AM
(#29067142)
Sadly, a good DBA would be able to give you information on these things, but the DBA like system admins is a fading "not needed" position. I know HN hates stored procedures, but having an application use stored procedures instead of on-the-fly queries allows a team to do the proper optimizations. Databases evolve with use and added records, and few teams devote resources to stay ahead of the changes.
Re:They don't even know the fundamentals
by bigbizisverywyz on Tuesday November 02, 2021 @08:26AM
(#29080205)
I personally love stored procedures, and Postgres, MSSQL, Sybase and Oracle all provide pretty good languages to implement them in.
In my experience the most reliable and long living projects treat the DB as its own separate product, whose interface to data mutation is via stored procs. The db can then be sanely designed with good data integrity rules (foreign keys, unique indexes and data column constraints).
Then external callers use the stored procs as their API, and then mostly don't have to worry about the internals. You can easily create a web based client, REST calls or a native GUI app that all work directly off the same db.
However they do have to worry about : -connection timeouts -query timeouts -deadlocks
But that's the same for all distributed systems.
On my current work database is riddled with indices over various status columns. Those indices are not selective and basically useless. Worse of all: it's not immediately obvious that those indices are bad, because inserts/updates are fast enough, but they slow system down as they accumulate and there's no easy way back.