r/SQL • • 20h ago

PostgreSQL PostgreSQL For personal budgeting and finance?

13 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 • • 23h 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
10 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 • • 10h ago

BigQuery BigQuery Query Optimization: Myths vs. What Actually Saves Money

Thumbnail
alvin.ai
8 Upvotes

r/SQL • • 18h ago

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

Thumbnail
reddit.com
3 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 • • 1h ago

SQLite Learning SQL with Python?

Post image
0 Upvotes

This ๐Ÿ‘‰interactive SQLite demonstration helps student understand relational databases.

The sqlite3 extension of ๐ฆ๐ž๐ฆ๐จ๐ซ๐ฒ_๐ ๐ซ๐š๐ฉ๐ก makes database content visible alongside Python's data structures. The demo shows how to:

  • Create tables with primary and foreign keys.
  • Model relationships between students, courses, and teachers.
  • Insert data and observe how the database changes.
  • Combine multiple tables using SQL JOIN operations.
  • Use parameterized queries.

The final query answers a seemingly simple question: Which teachers teach Alice, and for which courses? Answering this question involves joining five tables!

With ๐ฆ๐ž๐ฆ๐จ๐ซ๐ฒ_๐ ๐ซ๐š๐ฉ๐ก, students can step through the Python program and inspect the database content at every step, directly in their browser, without installing anything.

See ๐Ÿ‘‰other ๐ฆ๐ž๐ฆ๐จ๐ซ๐ฒ_๐ ๐ซ๐š๐ฉ๐ก visualizations.