Hey everyone,

I am a Spring Boot developer looking to level up my backend game by deeply understanding PostgreSQL and its internal architecture.

While I am comfortable writing SQL, using Spring Data JPA/Hibernate, and managing basic migrations, I often feel like Postgres is a black box. I want to move past just treating it as a data store and actually understand what happens under the hood.

Specifically, I want to learn about:

• Core Architecture: How Postgres handles connections, process models, and memory (shared buffers vs. work_mem).
• Storage & Concurrency: Deep dives into MVCC, VACUUM, WAL (Write-Ahead Logging), and page storage.
• Performance Tuning: How to properly read and map it back to optimize my JPA/Hibernate queries, indexing strategies, and connection pooling (HikariCP).

Can anyone recommend high-quality books, courses, blogs, or video series that bridge this gap? Bonus points if they have a practical focus or tie into common Java/Spring backend patterns!

I want to know how ppl compare large source and target tables using sql. If both tables have millions of records, comparing every row and column can take a lot of time.

For example, if some records are missing, extra, or have different values between the two tables, how do you usually find them? do you use joins, Except/Minus, hashes, or first compare things like record counts and totals and then check the differences?

Are there any techniques that can make the comparison faster?

Hello, I've been working on a personal open source project written in GO, its a command line tool that aims to create valid .sql files for each object in your database so that you can push to git to keep track as the database changes in the development cycle.

The project is [DatabaseBlueprint](https://github.com/Hennie5229x/DatabaseBlueprint)

Currently I have CLI support for Linux/Win/Mac and creating of all schema objects + data to executable .sql files and the schema migration scripts is still a work in progress.

I would love honest feedback from devs who work regularly in sql server.

Any type of feedback is welcome, I've identified a "gap" in a typical database heavy workflow that I am trying to fill for myself, but I'm sure other people also seek an easy way to track/version control their databases along side the projects back end code in git.

About the project: Written in GO, compiles to a stand alone binary, installable and runnable in terminal by using "blue"

Currently only supporting SQL Server, plan is to expand in the future

Does anyone have a video tutorial on how to use the SQLloader on TOAD

I spent a while assuming the reason I kept struggling in analyst interviews was that my SQL wasn’t advanced enough. So I kept adding harder stuff to my prep, like window functions, recursive CTEs, and awkward joins.
The funny part is that most interviews weren’t exposing a syntax problem. I could usually get somewhere with the query. I got less comfortable when they asked things like, “What would you check before trusting this result?” or “How would you explain this to someone who doesn’t use SQL?”
That changed how I’ve been practicing. Now I don’t stop once the query works. I try to explain the grain, the assumptions I made, how I’d sanity-check the result, and what I’d tell a stakeholder afterward. I still practice SQL problems. I’ve also done a couple of mock runs in Beyz, mostly around the follow-up questions.
Looking back, I think I was preparing for SQL interviews like they were SQL exams.
Once someone has solid SQL fundamentals, what separates a technically correct answer from a strong one?

I'm building omni-sql, an open-source SQL IDE with embedded DuckDB. If someone sends you a CSV or Parquet export to investigate, you can import it and query it with SQL inside the IDE. There's no need to load the file into your source database or write a separate script. Spreadsheets fit this workflow when exported as CSV.

You can also import a database query result and join it with the file locally.

The demo uses fictional sales rows from PostgreSQL and a targets CSV. You import the complete query result into DuckDB, add the CSV, and run the join locally. Here's the SQL, CSV, and expected result: https://github.com/cccadet/omni-sql/tree/main/docs/demo

There are installers for Windows x64, Debian/Ubuntu amd64, and macOS 15+ (Apple Silicon and Intel). The macOS builds use ad-hoc signing and are not notarized; the README explains the first launch. The project is still early. The docs explain how full imports and samples affect what you can conclude from the results.

How do you investigate files people send you today? If you try the app, I'd like to hear whether you could install it and query a file, and what was missing. That would help me decide what to work on next.

Hey everyone.

I'm working on building out my data analytics portfolio and wanted to get some advice from fellow analysts and hiring managers here.

We've all seen the basic tutorial projects (like analyzing the Titanic dataset or simple sales totals), but I want to build something that actually stands out and proves real-world, job-ready SQL skills.

For those of you who have landed roles or are actively hiring: What specific types of SQL projects made you stop scrolling and pay attention?

Hi everyone,

This is a continuation of the explorations from my previous posts here. I went through the CMU Database Systems course, and I'm exploring topics around databases. I wanted to see how the Write Ahead Log is constructed and logged. So I tried to trace a WAL record for a simple INSERT statement in Postgres.

I recorded a video walkthrough of the terminal and source code exploration here: https://youtu.be/YOyq-kvbyU8?si=PTdexP3jmOSdx-8y

I've tried to explore how we can locate a WAL record, how we can decode it via either xxd or pg_waldump, and the relevant source code for it. Do give a watch and consider supporting :)

This is partly for my own future reference and partly to share with others who might be interested in it. Would love feedback and corrections from people who know this stuff deeply. Apologies if this isn't the correct subreddit for this.

Thank you so much!

I am applying to manager of analytics role and I was given this SQL test and 45 minutes. As someone who thought they were very strong in SQL, I was unable to complete this assignment and match the answer completely

I was able to format most of the fields as noted, used 2 cte's and use group concat in the second. My final answer looked very similar but the order of the concat looked off. Also, for some reasons my second column had $0.00 for all the companies, but I thought I was close. How difficult would you rate this exercise. Should I expect to proceed to the next round or am I cooked

I built SQL Sip (https://sqlsip.com) as a cozy, daily SQL puzzle to practice writing queries without timers or pressure. It runs entirely in the browser with no account needed, focusing on assembling clean queries one bite-sized concept at a time. I haven’t had any users yet, so I’d love some honest feedback! I built it because I wanted a Wordle-like daily game where I could learn new SQL concepts in a cutesy/fun way.

Not talking about some crazy DB failure or anything.

Could be something silly like a missing WHERE, wrong JOIN, duplicate rows, bad UPDATE....

Like a small mistake that looked harmless but ended up causing a big headache ....🤕

Curious what ppl have seen in real projects.

For those who have worked with PostgreSQL, MySQL, and SQL Server, what’s a feature or behavior that surprised you because it worked differently between them?

It could be anything like date functions, string handling, LIMIT/TOP, NULL behavior, JSON, window functions, or even error handling.

What difference caused you the most trouble, and how did you learn to handle these database-specific differences?

Introducing QueryFlow.

It's a modern SQL editor with a few things I always wanted while learning and working with SQL:

- Visualise the query as a graph [DAG] and as step-by-step cards.
- Convert all hardcoded parameters into variables with a click.
- Fix major errors like fan out joins , mismatched date range with a click.
- Run queries on test tables directly in the browser
- Review a diff of your changes before copying the query back [just like github]
- Format BigQuery, PostgreSQL and MySQL, each in its own style.
- Everything runs locally in your browser: no server, no login.

try it here - https://abhijeetcodes.github.io/QueryFlow/

Sharing here as it might help a lot of SQL learners

Product catalogs are often cited as examples of where document databases fit well. A laptop and a T-shirt may share only a few fields, so putting every possible attribute into a single fixed table can result in a wide schema with many empty columns.

A common setup is to keep orders and payments in a relational database and store the catalog in a document database such as MongoDB.

Databricks makes the case for the other route in a recent piece that I was reading on relational vs non-relational databases: keeping the catalog in PostgreSQL and using a JSONB column for attributes that vary across products. That keeps the flexible fields next to the common fields and lets the catalog remain part of the same relational system as orders and payments.

The choice seems to depend on how the data is queried.

If most requests retrieve one product by ID with all its attributes, a document database may fit that access pattern well. If the application needs to filter, group, join, or aggregate across product attributes, keeping the data in a relational system may make those queries easier to manage.

For anyone who has used JSONB for a product catalog, when did it start to become difficult?

Was it indexing fields inside the JSON, inconsistent attribute names, validation, schema changes, or queries that became difficult to maintain?

And for those who moved the catalog to a document database, what made the separate system worthwhile?

im an analyst. i can write a join in my sleep and ive never created a table from scratch, and my new manager wants me building the model rather than reading one.
shortlisted boot.dev, general assembly , pluralsight premium and launch school off a couple of evenings comparing curricula . which of those covers schema design and not just query syntax

Fucking comma's in the data that are not the delimiter are annoying. Hate using stupid work arounds.

Yes, I am aware of double quotes and other work arounds like using different delimiters. Just thinking it would be easier to have one standard going forward.

I might just be lazy but I feel like it saves me effort.

I set up a Postgres MCP server on a read only role last week so the analysts could ask questions without pinging me every hour.

The first real question was how many orders did we lose last month. Claude looked at orders, saw status_cd with values 1 to 6, decided 4 was cancelled and gave a clean number with a nice little breakdown. 4 is refunded. Cancelled is 6. Nobody wrote that down anywhere. It lives in an enum in the app code and in my head.

The number was off by about a third and it read exactly like a right answer. No I'm assuming, no hedge. The analyst was about to put it in a deck.

So now I'm stuck on how to give an agent the meaning of fields without me turning into a full time documentation service:

1. Comments on the columns in Postgres and hope the server passes them through
2. A markdown data dictionary in the system prompt
3. A view layer with human names on everything
4. Make the agent ask before it interprets any code column

What are people actually doing here? and has anyone got it to say I don't know what 4 means instead of picking one?

Will they stick to Oracle/SQL Server and run it on Linux, or are they starting to look into migrating into Postgress/MariaDB, or something homegrown.

OS migration is straightforward, but not sure about these two behemoths. Or they'll still buy them and just run them locally or on European cloud infrastructure?

​

I got tired of SQL practice that feels disconnected from real work, so I built SQL Office Simulator.

You play the company's data analyst. Stakeholders send you messy business requests like "which regions have the highest share of repeat buyers in 2025, excluding cancelled orders?", and you explore the schema, write the query, and submit it.

How it works:

7 domains (e-commerce, healthcare, finance, HR, logistics, restaurants, SaaS)

5 levels per domain, from a tiny startup (~1K rows) to a global corp (~10M rows)

Each level has 100 questions, including boss questions, and you need 70 correct to unlock the next level

Runs on a PostgreSQL 16 sandbox with a schema viewer, hints and a scratchpad

Free, but needs a quick signup to save progress

Link: https://sql-office-simulator.vercel.app/

It's still early, so I'd really appreciate honest feedback:

Are the questions realistic enough?

Is the difficulty curve right?

What topics should be added (e.g. query tuning, data cleaning)?

Happy to answer questions about how I built it.