PGSQL Phriday #004 Postgresql And Software Development

Perhaps a good discussion and drink are all that you really need. 

This Blog post is for the PGSQL Phriday blogging event. Henrietta Dombrovskaya asks about Software Development and PostgreSQL. This is a significant topic, I work with developers frequently and how they use the database(usually telling them it’s time to move away from an ORM). I think for this month I’ll take the topic from that perspective. 

Did you ever have (frustrating) interactions with application developers? Did they end with some truce?

I rarely have non-frustrating interactions with developers, when I start working on a DB project. The first thing I do is have a good discussion with the DB. These are scripts I’m running to do this but it usually goes something like this.

Pat: Hey DB, Let’s check your query and resource utilization and see how you are doing, 

DB: I keep getting these different queries hitting me, none are in functions or procedures, none are optimized and they keep asking for 10 tables in every join?!?!!?! Help! 

Pat: Sound like you have an ORM problem. 

DB: What’s an ORM? Why is it hurting me so much? Please help! 

Pat: An ORM is a way for developers to build something fast, cheap, and easy. They use it to build applications quickly and then rarely if ever find ways to remove those ORMs and make a better application.

DB: Can we charge them with DB abuse? 

Pat: Unfortunately it’s not that easy, but don’t worry I’ll start working with them and we’ll give you some medicine for the short term and long term we will work on removing the bad pieces. 

DB: <Takes some new index pills, and starts feeling better> Thanks! I look forward to being healthy in the future! 

I just realized this would be a perfect “Tell me you’re a DBA without telling me you’re DBA” 

As a data person, whether with PostgreSQL or any other RDBMS we feel a certain sense to “defend” the database. It’s a system that cannot defend itself in most cases. This isn’t wrong to defend the database but you have to be careful how you present this.

We too often assume developers are doing this because they just don’t care about the DB. I find that’s almost never the case. They want to write an optimized, scalable, and efficient system as well they just don’t usually understand how to do that. Most application developers that went through school barely got 1-2 semesters of “How to write SQL”. Most of that time was how to work with 3rd normal form and nothing else. They also don’t think like databases. In Procedural logic in which Application Developers live everything is “IF this happens then do X else do Y”. It bases the ideas on one item going through some sort of logic. Databases think in sets, they think in large chunks of data, and they format based on rows and columns and not just one item. So most application developers at first apply the same thing they did in code to the DB and you end up in a bad situation. 

The key here is not to come into these situations with guns blazing trying to tell the developers how many things they have done wrong to the database. You need to walk into situations like this and focus on what short-term items you can do for the database to give it some time so that you can educate and start discussions with your application developers. 

Does it end in a truce? 

For me, a “truce” means it’s a competition and someone has to win. I don’t see any need for competition. No one has to “win”. They have to create a system that works for the company in a way that works for the company. If the Database is hit a little harder than I would like but it makes better financial and cultural sense for the company then both the application developers and DBA/Data people are winning. That’s what you really want. You don’t want a Truce, you want a working system that makes the company money. 

Thank you, Hettie, for bringing up a great topic! Hopefully, all my readers can think about how they approach working with application developers in the future. Perhaps like in the picture at the beginning they will sit down and have a drink and a good discussion about Databases. 

Advertisement