I've been thinking about how some database design decisions seem perfectly fine when a project starts, but create unexpected problems once the application grows and the data volume increases.

Things like choosing the wrong primary keys, over-normalizing tables, adding too many indexes, using triggers everywhere, or not planning for future changes.

For those who've worked on databases in production, what's one design decision you would approach differently if you could start over?

Would be interesting to hear some real experiences and the lessons you learned.

I'm getting really tired of burying findings that make people look bad. Last week I had a report showing a regional managers stores were almost 40% out of stock on a new launch, and it matched the Eileen shelf photos we pulled for the same week. When I flagged it, my director told me to soften the wording and call it 'mixed performance.' I get internal politics, but whats the actual point of tracking field data if leadership only wants the version that confirms their decisions? Anyone else been told to edit reality for a dashboard?

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.

For the past 9 months, I have been learning about databases purely about them. I started off focusing on database administration concept, then switched over to database internals. I began by understanding how hard disks and RAM actually work, and since then I tried to built my own page manager, buffer pool manager, and heap manager.

I want to keep learning, so I would love to get some answers on a few things from indrustry experts:

- Is it worth learning database internals right now, especially in today's AI era?

- Are there actual jobs for this kind of role?

- What is the typical salary range for remote jobs in this niche?

- What should I focus on learning next to get really good at this?

Thanks in advance for any advice!

Hi everyone,

I'm experiencing a strange issue with a SQL Server data-loading process and would appreciate some help troubleshooting it.

I have a SQL script that performs a full refresh of a staging table using TRUNCATE TABLE followed by an INSERT INTO ... SELECT from a remote SQL Server through a Linked Server (OPENQUERY).

The process was initially working perfectly when executed directly from SSMS, even when loading the entire dataset without batching.

However, recently, the process started failing when executed through SQL Server Agent.

What I've observed:

• Running the full TRUNCATE + INSERT operation manually in SSMS works successfully.

• Running the same process through SQL Server Agent results in an error.

• I initially suspected a row-volume limitation because the process seemed to struggle with larger loads.

• I introduced batch processing using TOP 1000 and key-based pagination, but I'm not convinced this is the actual solution.

• The SQL Agent job executes under a Windows domain service account.

Error message from SQL Server Agent:

text

Executed as user: [REDACTED].

Shared Memory Provider:
No process is on the other end of the pipe.

[SQLSTATE 08S01] (Error 233)

Communication link failure
[SQLSTATE 08S01] (Error 233)

Environment:

• SQL Server with SQL Server Agent.

• Linked Server connecting to a remote SQL Server instance.

• Data retrieved using OPENQUERY.

• Destination is a local staging database.

• Source table contains more than 60 columns.

Questions:

1. What could cause a query to execute successfully in SSMS but fail with Error 233 when executed through SQL Server Agent?

2. Could this be related to the SQL Agent service account, linked-server security mapping, or OLE DB provider configuration?

3. Could a large data transfer cause the SQL Server connection to terminate unexpectedly?

4. Are there specific SQL Server logs or diagnostic settings I should investigate?

5. Is batching a reasonable workaround, or should I focus on identifying the underlying connection issue?

I'd particularly appreciate insights from anyone who has encountered SQLSTATE 08S01 / Error 233 when working with SQL Server Agent and Linked Servers.

Thanks!

I was going through a checklist for evaluating managed Postgres and one section was about getting out of the DB again.

The questions are whether you can move data out with standard Postgres tooling and logical replication, or whether a full export requires that you reach out to support, and whether any provider-specific extensions or formats make leaving harder. The suggested test is restoring an export into a separate Postgres environment and running your app against it before you sign anything.

I’ve never seen anyone do that, and it would prolly not have occurred to me either. Everyone tests whether the thing works, but I'm not sure people make a point of testing whether they can leave.

Has anyone actually run an exit test during an evaluation? And for those who’ve migrated off a managed provider, how easy/bad was it?

I've been spending some time investigating slow MySQL queries in production recently. Thought I'd put together some notes on the approach I followed and a few things I found useful along the way.

My general approach is to get as much as possible out of the existing database before considering additional replicas, sharding or other infrastructure changes.

A few things I usually look at:

• Identifying expensive queries: Query frequency, average latency and rows read. A moderately slow query executed hundreds of times can be more expensive than an occasional slow one.

• EXPLAIN ANALYZE: Understanding actual execution plans, table scans, index lookups, rows and loops.

• Joins: Looking at nested-loop joins and how the number of rows entering a join affects subsequent lookups.

• Indexes: Choosing indexes based on actual query patterns rather than adding them blindly.

• Application code: Tracing expensive queries back to their source and checking for things like N+1 queries.

• Verification: Comparing database metrics and application latency after deploying a fix.

I also cover a few examples and explain how I approach reading execution plans.

Put together a write-up with the process and some SQL examples here: https://koul1sh.github.io/posts/getting-more-out-of-your-mysql-instance/

Would be interested in hearing how others approach database performance investigations, particularly in production environments. Anything you'd do differently?

I used AI to explore approaches, discuss tradeoffs, and work through implementation. It helped me finish the project much faster. But it also reinforced something for me: trusting generated code without understanding the codebase is a bad habit. Being able to question a suggestion, follow the implementation, and explain why a decision makes sense still matters.

Velis supports TTL expiration, eviction policies, pub-sub, a binary TCP protocol, an async client, and an interactive terminal UI. I spent a lot of time trying to keep the code design simple, clean, and easy to follow.

I see it as a project for learning, testing, experimenting, or just having fun with database internals. It could also be a starting point for thesis work or experiments comparing different approaches.

I’d welcome feedback on the implementation and design, but also your opinions on AI-assisted development. Where has AI helped you research a problem or make better decisions, and where has it made understanding the work harder?

Hi everyone,

I'm building an ingestion pipeline for a RAG system. My tech stack relies on PostgreSQL for relational data and pgvectore for storing text chunks and performing similarity searches.

I'm facing a design doubt regarding how to handle vector chunks when the parent document undergoes a "soft-delete" (e.g., is_deleted = true in the relational DB).

I've considered two approaches:

1. Hard-delete the vector chunks: Physically delete all associated chunks from the vector store. If the document is ever restored (which is a rare case i suppose), the only cost is re-generating the embeddings via the API/model.

2. Soft-delete the chunks as well: Keep the chunks in the vector store and rely on metadata filtering during the hybrid/vector query. My concern here is the risk of hallucinations or performance degradation (as well as being careful to put "where" clauses everywhere basically)

How do you handle this specific issue in production? What are in your opinions the best practices here?

Sorry I'm pretty new to databases, vectore stores ect, so if I missed information please tell me and ask me.

Thanks

I am creating a data warehouse for my personal infotainment using Python. My original plan was to use SQLite since it is embedded in Python and super easy to use. My design got off to a great start, but SQLite did not scale when loading data. Having a lot of experience in enterprise use of SQL Server, I installed SQL Server Express and converted the fact tables to column store. In running large volume loads, it became clear that MS had neutered the Express edition. A quick google search reveals this to be true (1 CPU, 5 GB RAM), which aligns with what I observed while monitoring. Wondering what would be a better choice for a non-operational DW on a personal machine (I use both a MBP with M5, 24 GB RAM and a min-PC with an AMD Risen 7 and 64 GB RAM).

Hello! I'm taking my first database design course in grad school and our final project is to make our own databook on any topic we want. I chose to make a database of all the Pokémon, moves, trainers, products, and locations in Pokémon Ultra Sun for the Nintendo 3DS.

I have two separate tables for Pokémon: one as a species, and one as an individual. I have a separate ROUTE entity that is a child of ISLAND, and was planning on putting ROUTE_ID as an attribute in the SPECIES entity. But many Pokemon can be found on many different routes, and vice versa, (for example, Magikarp can be found on Route 7, 8, 9, 13, 14, and 15 as well as other locations, and more than Magikarp can be found on those Routes), so I'll definitely need a bridge table. I'll probably label it ENCOUNTERS.

What would be the best way to utilize this bridge table without making data entry super time consuming? For example, since Magikarp can be found on six different routes, would I need to have six different instances of Magikarp in the data itself - each with a different ROUTE_ID?

I'm an absolute total beginner at SQL, and our class uses phpMyAdmin which comes with its own quirks and obstacles in what commands are allowed, but I really appreciate any and all insight or advice here! Thanks :)

I'm experimenting with a database model for contracts and their associated fulfillment obligations.

Some services offer duration-based discounts: the longer a customer commits, the lower the monthly price. Every product has a base price, but only some have these discounts.

The challenge is that base prices and discount tiers can change independently over time. I'd like to use an append-only approach, preserving the exact pricing terms that applied when a contract was signed.

I've put together a simplified ER diagram, but I'm unsure about the relationships.

Would foreign keys referencing immutable price and discount versions be sufficient? Does a composite key make sense here, or would storing price snapshots on contract items be more appropriate?

I'm also curious how others would ensure that signed contractual terms remain immutable while still allowing later amendments.

Any thoughts on the relational design would be appreciated!

I've noticed that some SQL queries using WITH clauses (CTEs) take significantly longer to execute than expected, especially when multiple CTEs are chained together.

I understand that a CTE is mainly used to organize complex queries and make them easier to read, but I'm curious about what happens behind the scenes.

Does the database execute each CTE separately, or does it optimize everything into a single execution plan? Can CTE materialization, repeated references, or joins between multiple CTEs cause performance issues?

I'd be interested to hear about real cases where replacing a CTE with a subquery or temporary table actually improved query performance.

Which database are you using, and have you run into this issue?

A security definer function checked "has the permission, or is the creator, or is the assignee" and raised if that was false. The tasks were seeded automatically, all 3,683 of them with owner and assignee NULL.

false or null or null is null, not false. IF only runs its branch on true, so the raise was skipped and the function carried on. The same condition in an RLS policy would deny, because a policy treats null as no. Same expression, closed in one place and open in the other.

Screenshot is a small rebuild on Postgres 17. The fix was coalesce(..., false) around the whole condition. Writing it as "if allowed then do the work, else raise" fails closed too, i think that reads better anyway.

Most founders building on Supabase with Lovable or Bolt won’t know to do that search. So I made a free checker for non-technical founders that runs a Supabase project through 46 checks for mistakes like this one: https://madsalt.tech/tools/supabase-security-check/

1.  Unique constraints.  A deleted user still "uses" their username. Use a partial unique index (`where deleted_at is null`).

2.  Every query must filter deleted rows.  Miss one, and deleted data shows up in a report or API response.

3.  Foreign keys.  What happens to orders that belong to a soft-deleted customer?

4.  Real deletion.  Privacy laws may require you to actually erase data, so soft delete can't be the only option.

5.  Restoring.  What if the username was taken again while the old account was deleted?

Do you use soft delete, or did you move to archive tables instead?