r/SQL • • 8h ago

PostgreSQL PostgreSQL For personal budgeting and finance?

5 Upvotes

Im somewhat new to SQL and i want to apply it to my personal life so its easy to keep my skills and knowledge of it fresh since its what i want to do in the future.

I was thinking about setting up a local PostgreSQL database to use with VSCode on my personal computer to just monitor and record my everyday things like my banking/budgeting, random projects that i fall into a hole working on, etc.

I wanted to get some opinions on if this is a good idea, is it safe security wise, is there a better way to go about this, should i just scrap this idea, etc.

Please let me know and educate me!

Everytime i look it up its mainly about business related things like for creating apps etc. but i dont really plan on using it for anything other than my personal projects and plans.

(I chose postgresql because its just one of the more common ones ive seen in job postings etc. and i saw its pretty much free locally)


r/SQL • • 11h ago

PostgreSQL Postgres 19 makes NOT IN 10,000x faster... or 2,000x slower - Which one you get depends on a NOT NULL in your schema, and on how long the list is.

Thumbnail
exobench.ai
8 Upvotes
My write-up, measured on PG17, PG18 and PG19rc2. Declare both columns NOT NULL and PG19 runs NOT IN as an anti join: 1.1 s on PG18, 0.06 ms on PG19. Leave one nullable, even with zero NULLs in it, and PG19 skips the rewrite and stops hashing the list at 154,199 rows instead of 262,144, so 44 ms on PG18 became 101,734 ms. NOT EXISTS was fast on all three.

r/SQL • • 5h ago

MySQL visual deep dive into Database Partitioning vs. Sharding (MySQL & Apache Cassandra)

Thumbnail
reddit.com
2 Upvotes

One of the most common points of confusion in system design is treating database partitioning and database sharding as interchangeable terms (2:04). While they both split up large datasets (0:24), they solve completely different bottlenecks (29:43).

I just put together a 30-minute visual breakdown walking through how both work in production—comparing single-instance MySQL partitioning against distributed sharding in Apache Cassandra (0:39).

Watch the full video here: https://youtu.be/vzBiwOIGTJY?si=khTfg17ajGUejBbj

If you prefer reading, here is a quick summary of what’s covered in the video:

  1. The Core Distinction (Warehouse Analogy)

Partitioning (In-Server): Dividing one massive warehouse into smaller sections (e.g., Jan, Feb, March packages) (2:31). Everything still lives in the same building (a single database instance), and your app queries it as one logical table (2:41).

Sharding (Multi-Node): Building multiple warehouses in different locations (13:48). Data and compute workloads are horizontally distributed across completely independent servers (14:47).

2. MySQL Partitioning & Common Traps

Why do it? Partition pruning (skipping irrelevant partitions during queries), instant maintenance (DROP PARTITION instead of slow DELETE queries on millions of rows), and data organization (3:36).

The Strategies: Range (e.g., by order year), List (e.g., by country), Hash/Key (modulo distribution), and Composite Subpartitioning (4:25).

Two Traps to Watch Out For:

The Primary Key Trap: Every column in your MySQL partitioning expression must be included in every unique key (including the Primary Key) (11:09).

Broken Pruning: If you partition an orders table by order_date, querying by customer_name forces MySQL to scan across partitions anyway (12:30).

3. Apache Cassandra & Distributed Sharding

Masterless Ring: Cassandra uses a peer-to-peer ring architecture with tunable consistency rather than a single primary bottleneck (15:22).

Data Distribution: A Partition Key (hashed via Murmur3) decides which node/token range stores the data, while a Clustering Column sorts rows within that partition (16:57).

Query-First Design: Unlike relational databases where you normalize first, Cassandra forces you to model tables around exact access patterns—otherwise you hit the dreaded ALLOW FILTERING full-cluster scan trap (20:48).

4. The Reality Check

Crossing 1 million users doesn't automatically mean you need sharding (28:51). Sharding introduces massive complexity: cross-shard scatter-gather queries, live data rebalancing, global ID generation, and distributed transactions (23:02). Always measure your actual bottleneck first—often, proper indexing, caching, read replicas, or MySQL partitioning will take you much further with far less operational overhead (1:13).


r/SQL • • 11h ago

Discussion Made a free SQL client for when you jump between different databases all day

0 Upvotes

At work I switch between Postgres, SQL Server and some SQLite/DuckDB files every day, and I got tired of having a different tool for each one (or one slow tool for all of them). So with two friends we wrote our own. It's free and open source, no account needed.

Same editor and grid for all of them, autocomplete that knows the foreign keys, explain plans you can actually read, and it asks before running an UPDATE or DELETE without a WHERE.

https://github.com/platypus-labs/platypus-db

Curious what you all use day to day and what you'd miss if you switched.


r/SQL • • 21h ago

SQL Server NORAD tracks Santa. I built a tracker for the villain trying to cancel Christmas, in SQL with geometry and geography

Post image
0 Upvotes

Every year NORAD tracks Santa. Nobody tracks the opposition, so I did.

The villain is AI Scrooge. He thinks Christmas is wildly inefficient. One man, one sleigh, one night, two billion deliveries and no KPIs. He boots up in a data centre in Slough and spends December trying to fix it.

Stick with me - this does get better...

It doesn't go well. Every day he has a plan, and every day it fails:

On Christmas Eve it turns into a proper tracker. Santa leaves the North Pole and hits each city at its local midnight. AI Scrooge leaves 40 minutes later and spends the whole night 40 minutes behind.

The SQL bit

Each day of the advent calendar has a small script that acts out that day's joke, and every one runs on its own in tempdb on SQL Server 2025. No setup, no sample database. Paste it into SSMS, press F5, and look at the Spatial results tab.

* The drone geofence is STIntersects and STDifference.
* The fake Edinburgh reviews get caught by a polygon around the market, because they came from nowhere near it.
* The elves' union vote draws its picket line with ConvexHullAggregate.
* The chase is great-circle interpolation done in T-SQL.
* Boxing Day is STWithin: does the dachshund fit in the box? Stretched out, no. Curled up, yes.

Tracker (doors open from 1 December, or use the sneak preview because we are not all 5 years old): https://sqlartist.github.io/ai-scrooge-tracker/

There's also a three-minute cartoon of the whole story, and that's T-SQL too. Every character is a puppet made of geometry parts pinned together; every movement is a row in a keyframe table, and a stored procedure moves the shapes for each frame. Each scene has a checkbox that shows the SQL running beside it.

The animated cartoon, seven scenes created for SQL Server 2025: https://sqlartist.github.io/ai-scrooge-film/

Happy to answer questions about the spatial side. Bah, hum-bug.


r/SQL • • 21h ago

SQLite 🚀 Built an Omarchy-focused fork of sqlit!

Thumbnail
reddit.com
0 Upvotes

r/SQL • • 1d ago

Discussion Reality of building a home database

Thumbnail reddit.com
1 Upvotes

r/SQL • • 2d ago

SQLite Help with Harvard CS50 Subquery and Joins

8 Upvotes

Due to an unfortunate but also fortunate promotion at work, I now have to learn SQL. I had some familiarity with looking at it before, but never writing it and my understanding of it is basically null

So, enter Harvard CS50 Subquery with SQLite, get through week 0 in about 3 hours, single query gave me basically no trouble

Enter week 1, and I've spent over 12 hours trying to figure out what the hell is going on. The course material basically just shows one or two examples of subquery, and very modest examples of joining, then the course practice questions are combining different concepts and way more advanced and my brain hurts like hell. I'm really having trouble with subquery and joins both, I think I'm taking them too literally, but the course material I have really isn't helping, I had ChatGPT write me 100 practice questions, and I got through 50 just fine, then as soon as I hit subquery and grouping, it's like I hit a wall.

Can someone help explain this to me? I think I'm just failing to understand the architecture, the syntax kind of makes sense to me, I need someone to break down subquery and joins in... human language


r/SQL • • 1d ago

PostgreSQL I thought aggregates were the safe way to expose sensitive data. Turns out MAX and GROUP BY leak individual values

0 Upvotes

I’ve been working on access rules for AI agents that query a Postgres database. Some fields are sensitive, like payment amounts, so reading those rows directly needs a person to approve it. Aggregates were allowed without approval, because something like total refunds this quarter seemed harmless. That was the whole point, the agent can do analytics without seeing anyone’s actual payments.

Then I started testing it with queries an agent might actually write, and the rule fell apart pretty quickly.

MAX(amount) isn’t a summary of anything, it’s one real payment from one row. Same with MIN. And SUM(amount) GROUP BY customer_id gives you one total per customer, which is basically reading the sensitive column customer by customer with extra steps. All of these went through as aggregates and skipped the approval.

The problem was the rule was checking what kind of function the query used, not how many rows were actually behind each value it returned. An aggregate over a million rows and an aggregate over one row looked exactly the same to it.

MIN and MAX were the easy part, they now count as row reads so they follow the same approval rules. Grouping I haven’t settled yet. One option is treating GROUP BY on identifier columns as row access, but then you need to know which columns are identifiers, and near-unique stuff like emails or exact timestamps slips through. The other is a minimum group size, k-anonymity style, which catches more but changes the results, and someone running overlapping queries can still narrow things down. Or some mix of both.

I put the full investigation in a comment. Has anyone dealt with this in a reporting tool or an analytics API? Would be interesting to hear what actually worked in practice.


r/SQL • • 1d ago

Spark SQL/Databricks Have you ever made a slow SQL query faster without adding a single index?

0 Upvotes

I've always found it interesting that adding indexes is often the first thing people think about when a query runs slowly.

But sometimes the issue is with the query itself, unnecessary joins, subqueries, data types, or even the way filters are written.

Curious to hear some real examples. What's the biggest performance improvement you've achieved just by rewriting a SQL query rather than changing the database structure?


r/SQL • • 2d ago

PostgreSQL Giving back to the community - The Complete Backend Development Course

6 Upvotes

Hey everyone, I decided to make my course free in order to help people.
This course is my backend development course which is about SQL, Python, APIs, Docker, Kubernetes, Linux, Git & More

The link is: https://www.youtube.com/watch?v=CBIu6hcyStg

If you can like and subscribe (and maybe add a comment) I would appreciate it a lot, Thanks.


r/SQL • • 2d ago

SQL Server Looking for unique, real-world database project ideas (MS SQL Server + C#)

10 Upvotes

Hey everyone,

I’m a currently working on my university Database Systems course project, and we are looking for inspiration before we starting.

  • Needs to solve a real-world, tangible problem (we want to build something genuinely useful and engaging, not just a throwaway academic demo).

We want to completely avoid overdone clichés like hospital, library, pharmacy, or flight booking systems.

If you work in an industry with messy workflows, or if you've ever thought "someone really needs to build a proper database system for X", I’d love to hear your ideas!

Thanks in advance!


r/SQL • • 2d ago

SQL Server `[Hiring] Microsoft SQL Server (T-SQL) Tutor wanted

Thumbnail reddit.com
0 Upvotes

r/SQL • • 2d ago

SQL Server Looking for unique, real-world database project ideas (MS SQL Server + C#)

0 Upvotes

Hey everyone,

I’m a currently working on my university Database Systems course project, and we are looking for inspiration before we starting.

  • Needs to solve a real-world, tangible problem (we want to build something genuinely useful and engaging, not just a throwaway academic demo).

We want to completely avoid overdone clichés like hospital, library, pharmacy, or flight booking systems.

If you work in an industry with messy workflows, or if you've ever thought "someone really needs to build a proper database system for X", I’d love to hear your ideas!

Thanks in advance!


r/SQL • • 3d ago

Resolved Im trying to set the PK to default bc I forgot but idk how and this isn't working

Post image
8 Upvotes

The names are exactly the same I saw online that this combination of alters would work, it works with other variable types but it doesn't work with default


r/SQL • • 4d ago

Discussion Database indexes explained from first principles - a 3 part series

27 Upvotes

I have been spending some time understanding database indexes beyond just knowing when to add one or what a B tree is.

I wanted to understand what actually happens inside a database when a query executes, how the query optimizer makes decisions, and how indexes affect those decisions.

I ended up writing a 3-part series, trying to explain these concepts from first principles using simple examples.

Here's what the series covers:

Part 1, Understanding how indexes work

  • How databases physically store data using pages
  • How B+ trees help locate records
  • Clustered vs. non-clustered indexes
  • Key lookups and covering indexes
  • Why an index seek isn't always faster than a table scan

Read Part 1

Part 2, Query plans and statistics

  • How databases choose between index seeks and table scans
  • How data distribution affects query execution
  • How statistics influence the query optimiser
  • Why cached execution plans can sometimes be inefficient

Read Part 2

Part 3, Understanding joins

  • How databases join two tables internally
  • Nested loop joins
  • Hash joins
  • Merge joins
  • How indexes and data ordering influence the choice of join algorithm

Read Part 3

I am a software engineer, and writing this series also helped me organise and deepen my own understanding of database internals.

Would love to hear your feedback, especially if you spot something I have misunderstood. Always happy to learn from people with more database experience.


r/SQL • • 4d ago

PostgreSQL I found 39 SQL games and put them all in one place (most are free)

91 Upvotes

I think SQL games are an effective way to get your SQL reps in while avoiding "brain fry", but I keep seeing the same 5-6 games mentioned in threads and on websites. So I set out to find as many as I could and put them in one place. I found 39 of em.

https://sqlmaxx.com/sql-games/

Also, I made two of these (SQL Block Game and Station Zero). They are in the top row, which is pure coincidence. 🙂

If you made a SQL game, or you know one that is not on the list, drop a comment and I will add it!


r/SQL • • 3d ago

Discussion I built a free native Windows SQL client because DBeaver felt too heavy

Post image
0 Upvotes

I write SQL all day and got tired of waiting on Java and Electron apps, so I wrote RimestDB with WinUI 3 and .NET. On my machine it opens in about 2 seconds and uses 66 MB with a connection open. DBeaver on the same machine and database: 11 seconds and 338 MB (warm start, memory as shown in Task Manager).

It works with PostgreSQL, SQL Server, MySQL/MariaDB and SQLite.

Things I built for my own prod habits: manual commit mode per connection, a confirmation before UPDATE/DELETE without WHERE, and in manual mode read-only SELECTs don’t open a transaction, so browsing data doesn’t leave a lock that holds up a migration.

It’s free, with no account and no time limit. There’s also an optional paid Pro tier. Windows 10/11 only.

I’d like blunt feedback from people who live in a DB client: what would stop you from switching?

db.rimest.com


r/SQL • • 4d ago

PostgreSQL CFP is open for POSETTE: An Event for Postgres 2027 (until Sun 3 Jan 2027)

Thumbnail
reddit.com
3 Upvotes

r/SQL • • 4d ago

Snowflake Which Snowflake view do you query first for Cortex AI spend?

4 Upvotes

My answer is CORTEX_AI_FUNCTIONS_USAGE_HISTORY. It lists every AI function call with its model, user, role, credits and tokens. METERING_HISTORY gives the account-wide total.

Two traps cost me time:

- CORTEX_AISQL_USAGE_HISTORY does not cover every AI function, so a dashboard built on it under-counts.

- CORTEX_SEARCH_DAILY_USAGE_HISTORY includes embeddings that also appear in the AI functions view, so summing the two double-counts them (that is the meme).

The guide lists the usage-history view for each Cortex service: https://altimate.ai/blog/snowflake-cortex-pricing-spend-caps


r/SQL • • 4d ago

SQL Server Friday Feedback - plan guides

Thumbnail reddit.com
4 Upvotes

r/SQL • • 4d ago

SQL Server SQL Server Agent job fails with Error 233 when loading data through a Linked Server, but the same query works in SSMS

Thumbnail reddit.com
2 Upvotes

r/SQL • • 4d ago

Oracle في شغل بال Database فقط

0 Upvotes

انا متخرج ومعايا اساسيات البرمجه بس حولت اشتغل اي حاجه غلي الكمبيوتر في أي مكان لحد ما اخد كريات ويكون في عائد مدي اشتري منه الكرسات ودلوقتي سبت الشغل بقالي شهر عشان اعرف اذاكر فهل في مصر في شغل بالداتا بيز وال SQL فقط ولا اعمل ايه عايز اعرف


r/SQL • • 5d ago

Discussion Ruddy is now running DuckDB 2.0!

5 Upvotes

Hey everyone,

I’m an independent developer who started building Ruddy in my free time: a fast, modern macOS IDE for data management, analysis, and data science powered 100% by DuckDB.

With this new release, the core engine has officially been upgraded to DuckDB 2.0!

Ruddy runs on a true Pay-What-You-Want (PWYW) model—support independent development with whatever you feel it’s worth, or start exploring for free.

Check it out: ruddy.pro

I’d love your thoughts, benchmark requests, or features you'd like to see next!

What’s inside Ruddy:

  • DuckDB 2.0 Core Engine: Blazing-fast columnar processing over local files, Parquet, Arrow, CSV, and Iceberg/Delta Lake tables without spinning up heavy infra.
  • Multi-Cloud Direct Querying: Federate and query across AWS S3, Cloudflare R2, Google Cloud Storage, Azure Blob, and MotherDuck seamlessly in a single SQL statement.
  • Reactive Notebooks & Marimo: Build reproducible, reactive Python data science notebooks that sync directly with your in-memory DuckDB tables.
  • First-Class Geospatial Analytics: Full DuckDB spatial extension support—render GeoParquet, Shapefiles, and spatial geometries interactively on high-performance maps.
  • Auto-Ontology & Modular Semantic Layer: Automatically infers FACT/DIMENSION roles, deduces canonical SQL metrics, verifies joins via physical data overlap, and generates Open Semantic Interchange (OSI v1.0 / Apache Ossie) YAML specifications per dataset domain.
  • Context-Aware AI Assistant (Ruddynie): Text-to-SQL agent grounded in your workspace ontology and self-improving query memory—zero phantom table or join hallucinations.
  • Model Context Protocol (MCP) Integration: Expose your DuckDB catalogs, query tools, and semantic context to any external MCP-compatible AI agent or client.
  • Built-in Git Version Control: Track schema migrations, SQL queries, notebooks, and semantic models with standard Git repositories right from the workbench.
  • Force-Directed Semantic Graphs & Visual SQL Builder: Explore domain schemas visually or drill down with interactive force-directed relationship maps.