No offence, but a full stack's job is not to write good SQL, it's to write enough SQL to get what is needed, then the development DBA's job is to make it fast and efficient. You don't need to be a great car mechanic to drive to the office and back.
I have never worked somewhere that has a dedicated database person. The SQL goes in the application code so it is the developers job to commit stuff which is performant and safe.
There is no point having developers write sql at all if someone else has to come in and redo it after.
I have a team of 4 people doing part time exactly this: improving performance of queries from various apps. My team does not know the business logic (many apps, not enough people), the developers don't have the knowledge to build good SQL code for databases with tables of hundreds of GB each. If you write small web apps it is not a problem, but if you have hundreds of servers with that size of databases, development DBAs are a must.
You assume a large company with lots of specialised roles. But lot's of full-stack developers work in small companies where they are the only DBAs. I for example have never worked at a company that employed DBAs.
At most places I've worked, there are no DBAs, and the devs are responsible for optimising all the SQL.
On the occasion that there have been DBAs at the company I worked for, they always refused to help with any SQL, on the grounds that all SQL is "application level", and insisting that they were only responsible for configuring / deploying / monitoring the DB infrastructure.
The configuring/deploying/monitoring the DB infrastructure DBAs are production DBAs. The ones tuning query performance are developer DBAs. If you put the large queries in stored procedures instead of the application, they can be easily touched and improved by DBAs.
No need of DBAs if your database is up to a few GB, you cannot live without DBAs if you exceed 100GB. I have several hundred SQL servers with databases exceeding 1 TB, on average several hundreds of GB each. This is where performance tuning is essential.
