Aurelien Grosdidier’s Post

Recently, I decided to revise my SQL skills, and instead of just practicing queries in isolation, I created a small Java + PostgreSQL expense tracking project to make the learning hands-on and real. I am using command line for PostgreSQL instead of GUI. Here are some features I implemented in the project: ✔ Insert new expense records ✔ Fetch all expenses ✔ Fetch expenses for a specific date ✔ Fetch recently added expenses ✔ Find lowest and highest expense ✔ Sort expenses by date ✔ Sort expenses by amount ✔ Count total expenses ✔ Delete expenses by date ✔ Delete all expenses I’m happy with the progress, and I’ll continue exploring joins, and advanced queries. Github link : https://lnkd.in/dCD6gJPg #java #sql #jdbc #postgresql #psql #database #revision #learningbydoing #backenddevelopment

MySQL CREATE TABLE Command - MySQL Tutorial 09 🚀 ► https://lnkd.in/gd87NfjF ► Master the basics of creating tables in MySQL with the CREATE TABLE command. This tutorial covers syntax, examples, and key concepts such as data types, primary keys, and foreign keys. Perfect for beginners and those looking to organize data efficiently in MySQL. MySQL Tutorials Playlist:  ► https://lnkd.in/g33rsg2m #MySQL #SQL #Database #LearnSQL #MySQLTutorial #Coding #Programming #DataScience #TechEd #DatabaseManagement

MySQL CREATE TABLE Command - MySQL Tutorial 09 🚀 ► https://lnkd.in/g3RPTD4Z ► Master the basics of creating tables in MySQL with the CREATE TABLE command. This tutorial covers syntax, examples, and key concepts such as data types, primary keys, and foreign keys. Perfect for beginners and those looking to organize data efficiently in MySQL. MySQL Tutorials Playlist:  ► https://lnkd.in/gVGsZMsG #MySQL #SQL #Database #LearnSQL #MySQLTutorial #Coding #Programming #DataScience #TechEd #DatabaseManagement

🔔 Welcome to Day 19 of the MySQL Community Advent Calendar! 🔔 Today’s focus is on deploying on OCI using the starter kit. We’ll set up the MySQL REST Service, enabling access to your data without needing to write SQL. You’ll learn to grant necessary privileges, create a database and table, and access records using curl. Plus, we’ll explore using a Python SDK to interact with your data seamlessly. This approach is ideal for quick prototypes and microservices! Join us in enhancing your MySQL HeatWave experience. #MySQLCommunity https://lnkd.in/e2nEKS3r

Online index creation in Laravel Creating indexes on large tables can block reads and writes. Laravel lets you create indexes online, so your application keeps running during index creation. >> PostgreSQL uses CONCURRENTLY >> SQL Server uses WITH (ONLINE = ON) This is useful for production databases with live traffic. #TechCommunity #Coding #Laravel #LaravelDeveloper #LaravelNews #Technology

sqlit is a user-friendly TUI (Terminal User Interface) tool for interacting with SQL databases directly from your terminal. It’s written in Python and supports multiple database types, including SQLite, PostgreSQL, MySQL, SQL Server, Turso, DuckDB, CockroachDB, Snowflake, Oracle, and more. https://lnkd.in/gh5pwGxc

DAY 4 (SENIOR DEVELOPER SERIES) - Database Optimization If your database is slow, your app is slow. Period. Most “performance problems” aren’t magic — they’re self-inflicted: • Missing or incorrect indexes • Inefficient query patterns (SELECT *, unnecessary joins) • N+1 query problems hiding in your ORM • Poor pagination and unbounded queries Before you think about: ❌ Adding more servers ❌ Increasing instance size ❌ “Scaling” prematurely Do this instead: ✔️ Analyze slow queries ✔️ Add the right indexes ✔️ Reduce query count ✔️ Let SQL do what it’s good at You don’t scale a slow query. You fix it. #SQL #DatabasePerformance #BackendEngineering #SoftwareArchitecture #SystemDesign Python SQL Database™ Israel Ikechukwu Nwangwu Anne T Griffin

While working with the Spring Boot we often rely on Spring Data JPA and repository methods without writing explicit SQL queries. However, to build efficient and reliable applications, it's important to understand what happens behind the scenes - especially how SQL joins work at the database level. A strong foundation in SQL helps not only when optimizing JPA queries, but also when working directly with databases like PostgreSQL. Understanding joins deeply makes debugging, performance tuning, and data modeling much easier. Continuous learning at the fundamentals always pays off. #SpringBoot #SpringDataJPA #SQL #PostgreSQL #BackendDevelopment #Java #Microservices #Learning #SoftwareEngineering

I had a `users` table with 5 million rows. I added an index to the `email` column, but my search queries were still taking over 500ms. 📉 🤦♂️ **I faced this:** I couldn't understand why the database was ignoring my index. I realized my query was `WHERE LOWER(email) = 'user@example.com'`, but my index was just on `email`. 💡 **Then I realized this (Functional Indexes):** A standard index is useless if you transform the column in your `WHERE` clause. This is a nuance many mid-level developers LACK. **The Fix:** I created a **Functional Index** (or Expression Index) in Postgres: `CREATE INDEX idx_user_email_lower ON users (LOWER(email));` **The Realization:** Your database indexes must match your code’s logic exactly. If you use `LOWER()`, `UPPER()`, or extract a `DATE()`, you need an index that does the same. My query time dropped from 500ms to 2ms. What’s your favorite "hidden gem" index type in PostgreSQL? #PostgreSQL #SQL #DatabaseOptimization #Python #BackendTips

🚀 Native Query vs JPQL vs JPA Specification (Short & Clear) 🔹 Native Query Definition: Raw SQL written directly in the code. Example: SELECT * FROM product WHERE price > 1000 Disadvantage: ❌ Database dependent (MySQL ≠ PostgreSQL), harder to maintain 🔹 JPQL (@Query) Definition: Object-oriented query using entity fields. Example: SELECT p FROM Product p WHERE p.price > :price Disadvantage: ❌ Becomes messy with dynamic filters (too many conditions) 🔹 JPA Specification ✅ Definition: Criteria-based, dynamic and reusable query building. Example: Add predicates only when filters exist (search, min/max price). Disadvantage: ❌ Slight learning curve, more verbose 👉 I chose Specifications for clean, DB-independent, and dynamic filtering. #SpringBoot #JPA #Specification #Java #BackendDevelopment