291–300 of 464 posts
"The fastest way to access to a row in a database is by its primary key. If you have better ways to identify records, sequential IDs may make the most significant column in tables a meaningless value. Please pick a globally unique natural primary key (e.g. a username) where possible." Has anyone had a problem due to surrogate keys?
Here's a fun bug I had a few years ago - Had a postgres database which was using pgbouncer for connection pooling. The most senior developer (24yo or so) we had on the project was using Go to connect to the database to write some simple reports, but each report took hours to run, and often had to sleep for 30+ minutes. So, after a while, pgbouncer would kill their connection, and their report would die. No other appl…
Found the real bug, and it wasn't in the library.
I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…
I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it. IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database. Your goal was to accomplish as much as possible in a single…
I did this (actually for longer), but it always washes away.
One challenge for me is I don't see an incentive to learn complex SQL setups unless absolutely necessary. Since I tend to work at startups, development velocity is way more important than optimized codebases. I'd rather write the "dumb" solution that nearly every engineer can grok than the "elegant" solution that goes above people's heads.
That aggregate function, yes, I know I can do it 100% in SQL. However, it's much easier for me to selectively pull parts into memory and modify them with language tools I work with everyday. When the junior needs to modify that function, they can do it with tools they're most familiar with.
Here's a fun bug I had a few years ago - Had a postgres database which was using pgbouncer for connection pooling. The most senior developer (24yo or so) we had on the project was using Go to connect to the database to write some simple reports, but each report took hours to run, and often had to sleep for 30+ minutes. So, after a while, pgbouncer would kill their connection, and their report would die. No other appl…
That aside, I wonder why you need to keep the connection alive for > 30 mins, while usually sql con is short lived. Why can't you just close and reopen them, is it temporary table?
Earlier quoted context omitted.
Many interests are pulling developers' attention in several different areas all the time. Database, security, accessibility, performance, infrastructure, tooling and productivity, business concerns, workflow processes (agile), language concerns, new things All of these like to say "if only the developer could do $MY_AREA better, they'd be better developers and we'd have better software". Each of them wants to pile on…
10, 11 year timeline to what? You should be constantly learning everything about the whole stack so that you can actually build functional, reliable, manageable and maintainable systems. I expect a competent developer to be able to build a modern multi-page web application, with a HTML/JS front end, relational database back end, appropriately configured certificates and DNS/CNAME/URL, build basic uptime and applicati…
What does all that have to do with OP's assertion that expecting application developers to understand that nuances of DB's listed in the article is noble, but unrealistic? A "competent developer" could fulfill your requirements and still not understand the implications of time drift or how to scale horizontally or other deep topics. Applications developers are the hub to many spokes, but expecting them to have deep knowledge across all technologies is unrealistic just as it is to expect a DBA to have a deep understanding of how a certain application framework works.
Earlier quoted context omitted.
> The application language was a pass through later between the client and the database. This style of doing things resulted in spaghetti style unmanageable databases, filled with an unknowable number of triggers and procedures, all written in PL/SQL (which is much, much worse than either Java or PHP). The reason why ORMs started to become popular is that you can write your application without filling your DB with ar…
But now you're filling your application with arcane and inscrutable logic, with an extra layer of abstraction via the ORM to make it even less scrutable. I think one should view a SQL DB like a microservice. Instead of REST endpoints (or gRPC or whatever), create stored procedures. These define a strong contract with your DB, the capabilities that it provides to your app(s). Now you know what the query and insert pat…
1) Version Control - I guess a lot of the stored procedures were being put straight into the DB without recording a history of the changes. These days you could easily do this using DB migrations I guess.
2) Testing - is unit testing a thing for Stored Procedures? I guess again, you might be able to do this from code as well programatically adding a stored procedure, running a bunch of tests and removing it again.
I do wonder - what do people generally do in practice for overcoming these objections? Does anyone have any other objections around using stored procedures?
(The 80/20 rule applies below, some developers do care) Developers... just don't care. They want to spin up an ORM, point it at a URI, and forget about it. I've fought this for over a decade now as a DBA, SRE, DevOps, and architect. Most of the developers don't want to deal with anything infrastructure-wise; they want to spend all the time they can just focusing on the problem they're writing software to solve. Obser…
Stubborn people like me keep doing the right thing, but the fact is that there are kudos and recognition for implementing a new feature. There is nothing for keeping servers from crashing, and only a little for reducing server count if you wait for things to get bad first.
The problem with doing things right the first time is nobody appreciates how hard it was. And you will sometimes get questioned about your loyalty and your competence to do the job.
I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…
SQL feels to me like bash or regular expressions. You can do amazing things if you do it full time. But if you do it only a few times per month or year you quickly forget all the subtleties and it gets hard to understand even the stuff you wrote half a year ago. I guess in the end things have become so complex that as a dev you can’t be good at everything. I often wish there were dedicated database guys but if you ha…
I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply technical person but one day he confessed to me that he didn't really grok SQL and that he'd rather work with a "real" procedural programming language to just store and retrieve data. Part of it may be due to the fact SQL isn't really a programming language b…
People struggling with sql because they usually don't know easier way to query that they resulting to use more complex query. For example `select where id in` and `case when` are both powerful query that many don't know or under utilized.
With `select where in` for example, we can do object mapping / join on application level instead db level.