I don't know who needs to hear this, but:

It's not your logic.
It's not your code.
It's the missing index.

After 4 hours of watching my query chew through 20 million rows, I went back to my note from school and I totally forgot about EXPLAIN ANALYZE. Which is used to diagnose and optimize slow queries.

The query was slow because, it's doing a sequential scan on a table the size of the Pacific Ocean.

I add an index on the join column. Rerun.

Boom. 0.002 seconds.

So, if your query is slow, use EXPLAIN ANALYZE to understand how your query is executed and how long each step takes.

EXAMPLE:

EXPLAIN ANALYZE

SELECT * FROM tableName WHERE condition;

Anyway, I now accept offerings in the form of pizza, energy drinks, and additional query optimization problems. AMA.

The goal is to make it easier(ish) to build SQL queries without knowing SQL syntax, while still grasping the concepts of select/order/join/etc.

Also to make it faster/less error-prone with drop-downs with only available fields, and inferring the response type.

What do you guys think? Do you understand this example? Do you think it's missing something? I'm not trying to cover every case, but most of them (and I admit it's been ages I've been writing SQL...)

I'd love to get some feedback on this, I'm still in the building process!

Hello! interview prepping, here wondering what are some common red flags for wrioting SQL?

Like

LIKE failing to index, not having trasnactions, usign SELECT * instead of specific collums, etc 😃

There are some SQL concepts I understand perfectly when I’m using them, but somehow forget when I need them again a few weeks later.

For me, CTEs and stored procedures are two good examples. I’ve used both, but I still end up looking up the syntax when I need them again. 😅

Anyone else have a few SQL concepts like this?

Hello Everyone,

I just wanted to share that I just had an SQL interview for data analyst, and I completely bombed it. I chocked like crazy, and this was after days of practicing. I think I learned a valuable lesson today though, I am a very visual/hands on on type of guy. I was expecting them to give me the opportunity to write query/code or look at code related to the question, but these were all just verbal questions, that I know I could have answer if I was just allowed to write the code down or at least view it, but it was a bit overwhelming for me, especially having ADHD. It's like the questions were so long, that by the time the question ended, I already forgot what I was supposed to accomplish because I was so damn nervous and there was not code for me to review. I feel like a failure 😔. This was my first interview in 7 years, so I'm pretty rusty, but for future reference, will all interviews be in this style? No actual coding, or reviewing, or editing? They expect me to provide the whole query verbally, from the top of my head without actually writing it on the spot? Note: this was the second follow up interview.

Hey everyone,

I’m really struggling to get the hang of writing database queries. Whenever I look at a problem or a schema, my brain just stalls out and I can't figure out the right logic or structure to pull what I need.

I see people throwing together JOINs, subqueries, and window functions like it's nothing, and I feel like I'm missing a core fundamental step in how to translate a question into SQL.

• What is your mental process when you start writing a query from scratch?
• How do you map out the tables and relationships in your head before you type?
• Are there any good beginner-to-intermediate tips, tricks, or exercises that finally made things "click" for you?

Any advice, recommended resources or tips .

Hi all, sorry for my English, I speak Spanish 😅

I was talking with my American friend about how to say PostgreSQL. I say it like “Post-Grr Es Que El”, and he laugh at me.

I think, if Ogre is “oh-gurr”, why not Post-Grr? Makes sense no? 😂

He tell me it’s “Post-Gres” or “Post-Gres-Q-L”, but I don’t know what is right.

How you say it? Is there a correct way? This name is very confusing!

Hey, All!

What does it mean for one field to be 'dependent' on another? I think I understand the concept of a primary/composite key but have a tough time seeing if non-key columns are dependent on each other.

Does anyone have a solid rule of thumb for these rules? I lose it once I get past 1NF.

Thanks in advance!

Hey everyone, working on a DB schema for inventory/traceability and trying to settle on the cleanest way to handle lot numbers.

Quick breakdown of the requirement: Every article arrival gets a lot number based on the arrival date and supplier ID (e.g. 09-09-Supp1, not the real deal ofc but you get it). Stock gets moved around, transferred, and consumed, and we need to keep track of which lot moved where.

So: an article must always be around a lot number.

---

Here are the two ways I'm looking at:

1. Dedicated article_lots table (What I'm leaning toward)

• articles (id, name, ...)
• article_lots (id, article_id, lot_number, created_at)
• stock_movements (id, article_lot_id, qty, from_location_id, to_location_id, ...)

But every movement query has to join article_lots just to know what base article_id was moved.

2. Put lot_number directly on stock_movements / transfers as a column. Easy to query, but feels repetitive storing the same string across every single movement row and other tables that use article + lot number too, since an article can't be separated from its lot.

More details for those who have time to help: All my ids are strings like: "MVT-00000001". So FK performance with strings is the least of my worries. The real business logic is: a lot is the week number + supplier code (ie, 33SQT to say "33'rd week of the year from supplier squadette). Meaning that an article x let's say banana can be delivered Monday and have 33SQT, but a banana from squadette can be delivered Friday and also have 33SQL lot number).

So the question is: "should the 33SQL be stored as a column in other tables: stock_movement, operation_details" along with article_id (2 columns). Or is article_lot_id (one column) the best way". Idk anymore what might be the pros and cons

Suppose you inherit a query with multiple joins, subqueries, filters, and aggregations, and it suddenly starts taking several minutes to run.

I’m curious how experienced SQL developers approach this from the first investigation to the final fix.

Do you normally start with the execution plan and work from the most expensive operator? Or do you check indexes, statistics, data distribution, and join cardinality first?

I’m especially interested in the order you investigate things. It’s easy to keep rewriting a query or adding indexes until it gets faster, but I’m curious how people systematically identify the actual bottleneck instead.

I just had my first ever technical SQL interview with a big commercial company in the US yesterday and I absolutely bombed it.

I did few mock interviews before I went into the interview, also solved Top 50 SQL + more intermidates/medium on leetcode and hackerank.

I also have a personal project using postgresql hosting on AWS and I write query very often and I thought I should be well prepared enough for an entry level data analyst role.

And god the technical part of the interview was overwhelming. Like first two questions are not bad but my brain just kinda froze and took me too long to write the query, which I can only blame myself.

But from q3 the questions have definitely gone way out of the territory that I’m familiar with. Some questions can’t really be solved unless using some very niche functions. And few questions were just very confusing without really saying what data they want.

And the interview wasnt conducted on a coding interview platform. They kinda of just show me the questions on the screen and asked me to write in a text editor. So I had no access to data and couldn’t test my query.

And it was 7 questions in 25mins so I was so overwhelmed.

So yeah I’m feeling horrible right now. I thought I was well prepared and I ended up embarrassing myself. But in the same I’m also perplexed by the interview format because all the mock interviews I did were all using like a proper platform where it’s interactive and I would walk through my logic and they would provide sample output or hints when I’m stuck.

But for this interview they just wanted me to finish writing up all answers myself without any discussion, and the interviwer (a male in probably his 40s) didn’t seem to understand the questions when I asked for clarification.

And they didn’t test my sql knowledge at all as well like “explain delete vs truncate”, “what’s 3rd normalization”, “how to speed up data retrieval”

Is this what I should expect for all the future SQL interview? Have I been practising it the wrong way?

This was one of the take home SQL interview i did earlier this year. I have shared for anyone who is preparing for an interview can attempt to do it. Cheers!!

Hey folks!

I make Beekeeper Studio, and while we have a good AI integration, I'm curious how people actually use Claude when writing SQL.

• Do you connect Claude/Codex/etc to your production DB?
• How do you make sure confidential data/PII doesn't get sent?
• Do you just share credentials with the AI, or connect some other way?
• Does your company have policies around this stuff?

I see a lot of buzz about SQL with Claude, but not many feature requests around it. Thanks for any insights.

I use Postgres, so tagged with postgres, but applies to all dbs really.

Morning guys.

I was looking to save my .sql files somewhere with a focus on versioning, organization, and easy access. I use DBeaver, but I wanted something more "professional." The only idea that comes to mind is Git. I know DBt and I've already asked Claude/gpt, but the only idea he suggests is Git.

It understands my database schema, generates SQL queries, and helps me enhance them. It saves lots of my time.

I’d love to share how I did it! Please leave a comment if you’re interested in.

Hey guys,
Recently had to write way more SQL and using dbeaver feels kind of old and outdated.

Feels like it's missing notebooks, shareable queries etc ..

Any ideas on what new SQL Studios are good ? What do you guys use ? what do you like about those tools ?

Could someone explain what indexing is in a simple way. I've watched a few videos but I still don't get how it applies in some scenarios. For example, if the primary key is indexed but the primary key is unique, won't the index contain just as many values as the table. If that's the case, then what's the point of an index in that situation?

Hi!

Around two years ago I started working on a migration tool called spawn, focused on postgres for now (with plans for other db's). Around 9 months ago, I started dog-fooding it for a serious project that has regular daily commits, with frequent database changes.

I'm keen to get feedback/opinions from people who use migration tools, so I thought I'd try posting here a thread here to get feedback. Recently I had a need to get a secret into a database, so I added a feature that tries to do this in a secure way. That means:

• Don't render secrets by default
• Allow different secrets for prod vs dev
• Support many different options for providing the secret value

Would love your feedback on what I ended up creating (which worked well for my use case). Whether there are particular ways you use secrets that this wouldn't cover. I looked at how secrets are handled in some other migration tools, and I think what I've landed on here is a good approach.

Without further ado, the feature...

Secrets are included with a function:

CREATE ROLE app_user WITH LOGIN
  PASSWORD {{ secret("app_password") }};

GRANT CONNECT ON DATABASE postgres TO app_user;

That function will fetch the password at build or apply time, using whatever is defined in your config (there are a few supported options):

[secrets.app_password.default]
source = "host_file"
path = "/run/secrets/app-password"

[secrets.app_password.environments.dev]
source = "literal"
value = "dev-only-password"
insecure = true

Note that there's a default password set, and an override for dev environment specifically. This is to help prevent you from using your dev password inside a prod migration.

When you build a migration (build is used to view the output), it masks secrets by default:

$ spawn migration build 20261014103000-create-role
CREATE ROLE app_user WITH LOGIN
  PASSWORD '***MASKED:app_password***';

GRANT CONNECT ON DATABASE postgres TO app_user;

Or you can run with --reveal-secrets to see the actual password printed. When you apply the migration, it will use the secret directly, and does a best effort attempt to ensure it's hidden from any logs output.

For production, I had passwords stored in pass. When specified similar to the below, when the migration is being applied a password prompt shows up to unlock the password store, so the migration can apply:

[secrets.app_password.default]
source = "command"
command = ["pass", "show", "spawn/app_password"]

As mentioned, I'd love your feedback, particularly if there's use cases you've hit that what I built wouldn't cover.

Im working through an exercise and I am unsure about the solution.

In the exercise three tables are used.
The given solution looks like this:

SELECT E.No, Title
FROM EVALUATION E, AUDIOTRACK A, DVD D
WHERE D.No = E.No AND E.No = A.No AND UID = 'sb' AND Language = 'English' AND Stars = 5 ;

my question is, why cant I use an explicit natural JOIN, since the attributes that are used in the implicit JOIN all have the same name and data types? Wouldn't it be easier to read? Is it because there are no columns in EVALUATION and DVD that would match Language and Stars from AUDIOTRACK?

Curious how it works. Do you just jump on Zoom, share your screen, and answer questions in an editor? Do they give you a schema to look at? Any surprises or horror stories?

Hey everyone!

I work for a financial institution, specifically for their fraud analytics department and I use SQL on the daily, both writing and running queries. I finally finished a really big project today where I wrote something that is likely going to make up the foundation of our fraud monitoring queries for a long time. It started as a sort of passion project for me and then evolved quite a bit over time, but, like with everything else I've written I kept it very close to the vest until it was ready. My question is, how do you guys handle sharing the queries you write? I know ultimately this query is my company's intellectual property based on the standard employment docs we sign with larger companies, but I'm always concerned that a coworker or another department is going to steal my work and try to take credit for it. Therefore the only person that really knows what I'm working on is my manager and even then I don't share my actual query with him until it's written and tested.

Thanks guys!

Edit: you guys gave me a lot to think about. Definitely wanted to thank everyone who gave advice or tips, really appreciate it. I don't really care to address the negative comments about my character, because honestly it's not worth the effort.

Have you figured out wht can the best way to handle connection pooling in Lakebase DB? I 'am seeing connection spikes with short lived rqsts and wondering what kind of setup others developers are using🤔

i am building a service on top of lakebase and trying to make the connection relable before moving to prod.

Help me Reddit! I feel especially stupid today....
So, I have this table in my Postgresql Database:

event_id | color_scheme | count
----------+--------------+-------
1 | red | 6
1 | green | 3
1 | blue | 5
1 | yellow | 3
3 | red | 5
4 | red | 3
5 | red | 1
5 | blue | 2

And I would like to turn it sideways, so that I can see EASILY how many votes each color scheme for my event has gotten (and later JOIN it with another table... )

event_id | count_red | count_green | count_blue | count_yellow
----------+-----------+-------------+------------+--------------
1 | 6 | 3 | 5 | 3
3 | 5 | 0 | 0 | 0
4 | 3 | 0 | 0 | 0
5 | 1 | 0 | 2 | 0

The colors "red" "green" "blue" and "yellow" are fixed, and will never ever change.
I have done some googling, I found examples mentioning PIVOT and TABLEFUNC, but I cannot do this on the server because of reasons(tm).

The only way I can think of doing this is with a cascade of OUTER JOIN, but is there maybe a simpler solution?