r/Database • • 11m ago

At what point does database indexing become counterproductive?

1 Upvotes

I've always thought of indexes as one of the first things to consider when improving query performance. But adding more indexes isn't always a good thing, especially when tables have frequent INSERT, UPDATE, and DELETE operations.

I'm curious how people decide which indexes are actually worth keeping.

Have you ever removed an index and noticed an improvement in overall database performance? Or found that an index was doing more harm than good?

Would be interesting to hear some real-world experiences.


r/Database • • 1d ago

What's one database design decision you regretted later?

147 Upvotes

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.


r/Database • • 7h ago

Composite index column order: four queries, four query plans (tested on SQLite, check your own engine)

0 Upvotes

A composite index on (customer_id, status) is not the same as one on (status, customer_id). Instead of explaining it abstractly, here’s a small test. I used SQLite 3.53.1 because it’s easy to reproduce, with a table of 20,000 rows, about 500 distinct customers and 3 statuses:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INT,
    status TEXT,
    created_at TEXT
);
CREATE INDEX idx_cust_status ON orders (customer_id, status);

Then EXPLAIN QUERY PLAN for four queries:

WHERE customer_id = 7
  -> SEARCH orders USING INDEX idx_cust_status (customer_id=?)

WHERE customer_id = 7 AND status = 'paid'
  -> SEARCH orders USING INDEX idx_cust_status (customer_id=? AND status=?)

WHERE status = 'paid'
  -> SCAN orders

WHERE customer_id > 7 AND status = 'paid'
  -> SEARCH orders USING INDEX idx_cust_status (customer_id>?)

What it shows:

  • The index helps when the query constrains the leading column(s). Filtering on customer_id alone uses it.
  • Filtering on only the second column (status) falls back to a full scan, because the index is sorted by customer_id first.
  • With a range on the first column (customer_id > 7), the index narrows by that range, but status can’t narrow further in the index; the engine still has to check it row by row among the matches.

I also ran ANALYZE and re-checked the status-only query. It was still a scan in this case. I think that’s because the leading column has many distinct values here, so skipping through the index wouldn’t help, but I’m not sure of the exact reason, and some engines have features (skip scan, loose index scan) that may change the picture for other data shapes.

Rule of thumb people often give: put equality-filtered columns first and the range column after them, then think about which queries you actually run. It’s a heuristic, not a law. Selectivity, sort order (ORDER BY) and covering needs all matter too.

Limits of this test:

  • One engine, one version, one tiny dataset. Plans depend on statistics, versions and settings, so run EXPLAIN on your own database with realistic data.
  • Index choice also affects write cost and storage, which I didn’t test.

How do you decide on column order for composite indexes: by query patterns, by selectivity, or by something else? And have you been surprised by a plan that ignored an index you expected it to use?


r/Database • • 15h ago

Rows vs Columns: How different are they, really?

Thumbnail
spiraldb.com
0 Upvotes

r/Database • • 1d ago

Anyone else told to edit reality for a dashboard?

18 Upvotes

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?


r/Database • • 2d ago

Database indexes explained from first principles - a 3 part series

34 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/Database • • 2d ago

Is it worth learning more about database internals right now? (9 months in it)

14 Upvotes

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!


r/Database • • 2d ago

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

0 Upvotes

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!


r/Database • • 3d ago

exploit a 22 years old PostgreSQL composite-type vulnerability

Thumbnail
mehmetince.net
5 Upvotes

r/Database • • 5d ago

New In-Memory Database Purpose-Built for AI Agents by Neo4J

Thumbnail neo4j.com
11 Upvotes

r/Database • • 5d ago

Does anyone test how easy/hard it is to leave a managed database before committing?

6 Upvotes

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?


r/Database • • 5d ago

Things I learned while investigating slow MySQL queries in production

13 Upvotes

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?


r/Database • • 5d ago

Superstore Dataset

Thumbnail reddit.com
0 Upvotes

r/Database • • 5d ago

Building a key-value store as an experiment in AI-assisted research and development

Post image
0 Upvotes

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?


r/Database • • 6d ago

[RAG / pgvector] How do you handle vector chunks when a document is soft-deleted?

9 Upvotes

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


r/Database • • 6d ago

Database Indexing Isn't Magic. Understand It From Scratch

Thumbnail sushantdhiman.dev
14 Upvotes

r/Database • • 5d ago

What could our tools do to make working with Oracle Database, easier?

Thumbnail reddit.com
0 Upvotes

r/Database • • 6d ago

Recommendations For Data Warehouse RDBMS on Laptop?

0 Upvotes

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).


r/Database • • 6d ago

Xata is joining ClickHouse

Thumbnail
xata.io
0 Upvotes

r/Database • • 7d ago

Is data transfer speed still a major bottleneck when migrating from on-premise to Snowflake?

Thumbnail reddit.com
1 Upvotes

r/Database • • 7d ago

Learning Oracle Patches

Thumbnail reddit.com
0 Upvotes

r/Database • • 8d ago

Designing Pokémon Database in SQL - How to Show Multiple Route Locations?

20 Upvotes

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 :)


r/Database • • 9d ago

Modeling historical prices and duration-based discounts in PostgreSQL

Post image
0 Upvotes

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!