Na'aman Hirschfeld’s Post

I optimized a query that was taking 45 seconds down to 200ms. Here's exactly what I did: After 9 years with Java + Spring Boot + various databases, here are the database lessons that actually matter: 1. Indexes are not magic, they're a trade-off   Every index speeds up reads but slows down writes   Index what you query, not everything 2. N+1 queries will kill your app   Use JOIN FETCH in JPQL or @EntityGraph   Always check the SQL Hibernate is generating 3. Pagination is non-negotiable   findAll() on a 10M row table is a career-limiting move   Spring Data's Pageable is your best friend 4. Connection pools matter more than you think   HikariCP defaults are a starting point, not gospel   Monitor your pool metrics in production 5. Read replicas for read-heavy workloads   Your reporting queries shouldn't compete with your transactional queries 6. EXPLAIN ANALYZE before any optimization   Never guess. Always measure. Most performance problems I've seen weren't code problems. They were database design problems. What's your worst database horror story? #Java #SpringBoot #Database #Performance #SQL #BackendDevelopment

🚀 Built my first CRUD API using FastAPI + MySQL and deployed it on Render! 🌐 Live URL: https://lnkd.in/gHKJaCXx Today I created a REST API with full CRUD operations using Python (FastAPI) and MySQL as the database, and deployed it using Render. What I built: ✔ GET → Read data from MySQL   ✔ POST → Insert data into MySQL   ✔ PUT → Update existing records   ✔ DELETE → Remove records from MySQL  Deployment: 🌐 Hosted on Render This project helped me understand how backend systems work in real-world applications—from API design to database integration and deployment. Key learnings: - REST API design principles   - CRUD operations with MySQL   - FastAPI backend development   - Deploying applications on Render  It’s a simple project, but it reflects real-world backend architecture. Next step: add authentication and improve security. #Python #FastAPI #MySQL #CRUD #BackendDevelopment #Render #Deployment #RESTAPI

🔍 Since I have plenty of time, I was building a tool that indexes every hardcoded constant in your Java bytecode and config files — and diffs them across versions. Ever had to answer "where is this SQL string used?" or "what changed between our last two releases?" across a large multi-module codebase? That's the problem I set out to solve. Constant Tracker parses JVM class files, classifies constants by semantic type (SQL, URL, logging, file path, error message, annotation), and indexes everything into Solr for full-text search. The version diff feature is my favourite part: upload two finalized JARs and see exactly which constants were added, removed, or changed — per class, with full usage context. Tech: Java 25 · Spring Boot 3 WebFlux · Solr 10 · Redis · Postgres · React 19 · Docker Compose 3-command quickstart with pre-seeded demo data included. 🔗 GitHub: https://lnkd.in/dzqxKGHP #java #springboot #solr #postgres #redis #react #githubcopilot

Most developers use connection pooling. Without it, every database query does this: → Open TCP connection (20ms) → Authenticate (10ms) → Run query → Close connection 500 requests per second means 500 new connections per second. Your database collapses before your code even runs. Connection pooling fixes this with one idea. Keep a small set of connections open. Reuse them. App needs a query: → Grab connection from pool → Run query → Return to pool No handshake. No auth. Just the query. That 30 to 50ms overhead vanishes. The real numbers: 10 to 20 pooled connections can handle thousands of requests per second. The mistake most people make: Bigger pool is not always better. Too few connections: requests queue up. Too many connections: database runs out of memory. Sweet spot for most apps: 10 to 20 connections per instance. Formula: (core count x 2) + 1 Tools worth knowing: → HikariCP (Java) → PgBouncer (PostgreSQL) → SQLAlchemy pool (Python) → Prisma pool (Node.js) Always monitor these three: → Pool wait time → Active vs idle connections → Connection timeout rate These will warn you before your users feel it. The biggest performance wins in production are rarely clever algorithms. They are boring infrastructure configs nobody touched. What is the biggest win you have found from a config change? 👇 #SoftwareEngineering #BackendEngineering #SystemDesign #DatabaseEngineering #Java #Python #SpringBoot #PostgreSQL #BuildInPublic #Engineering

I’ve been playing around with a technology I stumbled upon in a YouTube video called sqlc. People who have worked with me know that I have opinions about ORMs and how I would prefer to just use SQL the way it was intended. By using sqlc, I can do just that, and it will generate the boilerplate code that interfaces with the database, while also providing a beautiful type-safe interface for the input and output types for various queries. The best part for me has been the ability to make sure that my sql queries are syntactically correct before testing, as well as ensuring that I don’t forget to add new columns to my input and output types. Since it’s entirely based on the query itself, that part is taken care of! And it reads your schema files, so you don’t even need a live database for it to work. It has official support for Go (my preference of the list), Kotlin, Typescript, and Python. If you don’t mind working with raw SQL, and especially if you prefer it, give it a shot. Shoutout Dreams of Code for the YouTube video that got me started down that rabbit hole.

My colleague Laura Trotta released a new ES|QL plugin for JetBrains IDEs! If you're writing raw ES|QL queries in your code, this plugin gives you: 🔹 Autocomplete for commands, functions, and field names 🔹 Real-time syntax checking with clear error messages 🔹 Inline documentation on hover 🔹 The ability to run queries and see results directly in your IDE 🔹 Connection to your Elasticsearch instance for index and field discovery The plugin is it's built on the ES|QL ANTLR grammar, so it's fast and predictable. While the blog post focuses on IntelliJ IDEA with Java and Kotlin examples, this works in Rider (.NET) too. It's still experimental, so give it a spin and share your feedback on the GitHub repo! 📦 JetBrains Marketplace: https://lnkd.in/ddBRgPum 👉 https://lnkd.in/dnDu58BQ If you are a .NET developer, also make sure to check out the LINQ to ES|QL provider if you prefer a "EntityFramework style" DSL: 👉 https://lnkd.in/dgFzs6CD #Elasticsearch #ESQL #JetBrains #IntelliJ #Rider #DotNet #Java #Kotlin #DeveloperTools #Elastic

Day 15. My query was slow. Not because of bad code. Because of a missing index. I had this: @Query("SELECT u FROM User u WHERE u.email = :email") User findByEmail(@Param("email") String email); Looks fine. It’s not. Here’s what was actually happening: → 1 million users in the database → Every search = full table scan → Response time: 3–4 seconds per request That’s not slow code. That’s a missing index. Your code looks fine. Your database is doing all the work. The fix is simple: Tell the database where to look. @Entity @Table(name = "users", indexes = {   @Index(name = "idx_user_email", columnList = "email") }) public class User {   private String email; }   What actually changes: → Speed — full table scan becomes instant lookup → Scalability — fine at 1K rows, critical at 1M → Cost — fewer resources, faster queries The hard truth: → ORMs don’t add indexes for you → You won’t notice this in development → You WILL notice it in production Writing queries is easy. Knowing why they’re slow is what makes you a backend developer. Are you adding indexes to your tables? 👇 Drop it below #SpringBoot #Java #MySQL #BackendDevelopment #JavaDeveloper

🚀 Mastering Persistence in Spring Data JPA: persist() vs. merge() vs. save() Ever wondered which method to use when saving data in Java? Choosing the wrong one can lead to unnecessary SQL queries or even dreaded EntityExistsException errors. Here is the breakdown of the "Big Three": 🔹 1. persist() – The "New Only" Approach What it does: Takes a brand-new (transient) entity and makes it managed. It schedules an INSERT. Best for: Creating new records when you are sure they don't exist yet. Watch out: It will throw an exception if the entity is already detached or has an ID that exists in the DB. 🔹 2. merge() – The "Reconnector" What it does: Takes a detached entity (one that was loaded in a different session) and copies its state onto a new managed version. Best for: Updating existing records that were passed through different layers of your app (e.g., from a REST controller). Watch out: It creates a copy. You must use the returned object for further changes! 🔹 3. save() – The Spring Data Way What it does: A smart wrapper provided by Spring Data JPA. It checks if the entity is "new." If yes, it calls persist(); if not, it calls merge(). Best for: Most standard repository patterns. It’s the "safe bet" for 90% of use cases. Watch out: Because it checks state first, it might trigger an extra SELECT query to decide whether to insert or update. 💡 Pro Tip: If you are building high-performance systems with massive inserts, using persist() directly via the EntityManager can sometimes be more efficient than the generic save() method. Check out the infographic below for a quick visual cheat sheet! 📊 #Java #SpringBoot #JPA #Hibernate #SoftwareEngineering #BackendDevelopment

Implementing Exception Handling Technique in MS SQL Server... Yes, it is possible to implement Exception Handling Technique in MS SQL Server as well. Normally this technique is used in other traditional languages Like Python, Java, C# etc. So MS SQL Server is also not an exception here. The same try , catch, statements are used to only thing is that we use Begin try...End Try and Begin Catch....End catch statements. In case some exception is raised immediately the control is passed to catch block(Begin Catch...End Catch); otherwise normal execution will happen. See the code snippet below:- BEGIN TRY   DECLARE @a INT = 10   DECLARE @B INT = 0   DECLARE @RESULT INT   -- This will generate divide by zero error   SET @RESULT = @A / @B   PRINT 'Result = ' + CAST(@RESULT AS VARCHAR) END TRY BEGIN CATCH   PRINT 'Error Occurred!'   PRINT 'Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR)   PRINT 'Error Message : ' + ERROR_MESSAGE()   PRINT 'Error Line : ' + CAST(ERROR_LINE() AS VARCHAR) END CATCH ERROR_NUMBER() - Shows the error No. ERROR_MESSAGE() - Shows the Error Message - cause of Exception ERROR_LINE() - Show the Line of execution where the exception was raised. As MS SQL Server is one of my favorite subject, so thought to share a LinkedIn Post for the viewers. #Microsoft #LinkedIn

𝗔 𝗹𝗼𝘁 𝗼𝗳 𝗦𝗽𝗿𝗶𝗻𝗴 𝗱𝗲𝘃𝗲𝗹𝗼𝗽𝗲𝗿𝘀 𝘀𝘁𝗶𝗹𝗹 𝘁𝗵𝗶𝗻𝗸 𝘄𝗿𝗶𝘁𝗶𝗻𝗴 𝗾𝘂𝗲𝗿𝗶𝗲𝘀 𝗺𝗲𝗮𝗻𝘀 𝗼𝗻𝗹𝘆:  • JPA method names  • @Query But if your queries are getting more complex, 𝗷𝗢𝗢𝗤 𝗶𝘀 𝗮𝗹𝘀𝗼 𝗮𝗻 𝗼𝗽𝘁𝗶𝗼𝗻 — and honestly, more developers should know about it. In Spring, you can implement database queries in multiple ways: 𝗷𝗢𝗢𝗤 Great when your application is 𝗦𝗤𝗟-𝗵𝗲𝗮𝘃𝘆. 𝙒𝙝𝙮 𝙥𝙚𝙤𝙥𝙡𝙚 𝙡𝙞𝙠𝙚 𝙞𝙩:  • type-safe SQL  • better for complex joins  • cleaner for reporting / analytics queries  • stays close to real SQL 𝘽𝙚𝙨𝙩 𝙛𝙤𝙧:  • advanced filtering  • multi-table joins  • database-driven applications 𝗝𝗗𝗕𝗖 The most direct way to talk to the database. 𝙒𝙝𝙮 𝙥𝙚𝙤𝙥𝙡𝙚 𝙪𝙨𝙚 𝙞𝙩:  • full control  • lightweight  • no ORM magic 𝙏𝙧𝙖𝙙𝙚𝙤𝙛𝙛:  • more boilerplate  • manual mapping  • less convenient as the project grows @𝗤𝘂𝗲𝗿𝘆 A very common middle ground in Spring. 𝙂𝙤𝙤𝙙 𝙛𝙤𝙧:  • custom repository queries  • when derived methods are not enough  • medium-complexity use cases 𝙏𝙧𝙖𝙙𝙚𝙤𝙛𝙛:  • query strings can get harder to maintain  • not ideal when SQL becomes a big part of the app 𝗝𝗣𝗔 𝗱𝗲𝗿𝗶𝘃𝗲𝗱 𝗺𝗲𝘁𝗵𝗼𝗱𝘀 The fastest option for simple queries. 𝙂𝙤𝙤𝙙 𝙛𝙤𝙧:  • CRUD  • small filters  • rapid development 𝙏𝙧𝙖𝙙𝙚𝙤𝙛𝙛:  • method names can become ugly very fast  • not made for complex query logic 𝗧𝗵𝗲 𝗿𝗲𝗮𝗹 𝗽𝗼𝗶𝗻𝘁: 𝗦𝗽𝗿𝗶𝗻𝗴 𝗱𝗼𝗲𝘀 𝗻𝗼𝘁 𝗳𝗼𝗿𝗰𝗲 𝘆𝗼𝘂 𝘁𝗼 𝘄𝗿𝗶𝘁𝗲 𝗲𝘃𝗲𝗿𝘆 𝗾𝘂𝗲𝗿𝘆 𝘁𝗵𝗲 𝘀𝗮𝗺𝗲 𝘄𝗮𝘆. 𝘈𝘯𝘥 𝘵𝘩𝘢𝘵’𝘴 𝘴𝘰𝘮𝘦𝘵𝘩𝘪𝘯𝘨 𝘢 𝘭𝘰𝘵 𝘰𝘧 𝘥𝘦𝘷𝘦𝘭𝘰𝘱𝘦𝘳𝘴 𝘰𝘷𝘦𝘳𝘭𝘰𝘰𝘬.  • simple query → derived method  • custom query → @Query  • raw control → JDBC  • serious SQL work → jOOQ Knowing Spring is not just knowing JPA. It’s knowing 𝘄𝗵𝗶𝗰𝗵 𝗱𝗮𝘁𝗮 𝗮𝗰𝗰𝗲𝘀𝘀 𝗮𝗽𝗽𝗿𝗼𝗮𝗰𝗵 𝗳𝗶𝘁𝘀 𝘁𝗵𝗲 𝗽𝗿𝗼𝗯𝗹𝗲𝗺 𝗯𝗲𝘀𝘁. 𝗛𝗮𝘃𝗲 𝘆𝗼𝘂 𝗲𝘃𝗲𝗿 𝘂𝘀𝗲𝗱 𝗷𝗢𝗢𝗤 𝗶𝗻 𝗮 𝗦𝗽𝗿𝗶𝗻𝗴 𝗽𝗿𝗼𝗷𝗲𝗰𝘁? #Java #SpringBoot #SpringFramework #JOOQ #JPA #JDBC #SQL #BackendDevelopment #SoftwareEngineering #Programming #Database #Tech