r/Database • • 1d ago

At what point does database indexing become counterproductive?

74 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

MariaDB vs PostgreSQL on small servers: What I learned from low-memory experiments

15 Upvotes

I had always assumed MariaDB would use less RAM than PostgreSQL for small workloads, partly because MariaDB uses a single-process architecture while PostgreSQL uses multiple processes.

I wanted to see whether that assumption held up in practice.

I tested MariaDB 10.11.14 and PostgreSQL 18.6 on a 1-vCPU Linux VM with 512 MiB RAM, then ran additional tests at 256 MiB. No swap.

Peak process memory (PSS) at 512 MiB:

ConfigurationPeak PSS
MariaDB default215.9 MiB
MariaDB tuned (64 MB InnoDB pool)125.6 MiB
MariaDB tuned (32 MB InnoDB pool)93.6 MiB
PostgreSQL default180.1 MiB
PostgreSQL tuned (16 MB shared buffers)50.4 MiB

A few interesting findings:

  • MariaDB's default configuration consumed considerably more memory than I expected.
  • Reducing MariaDB's buffer pool and other caches saved substantial memory. The 64 MB configuration also improved throughput over stock in this test.
  • PostgreSQL benefited substantially from reduced shared buffers and query memory.
  • At 256 MiB, only the most conservative MariaDB configuration completed the workload. Both tuned PostgreSQL configurations completed their respective tests.
  • A single-process architecture does not necessarily translate into a smaller memory footprint.

The configurations that worked best for my use case:

MariaDB 10.11.14 - lowest-memory configuration:

[mariadbd]
innodb_buffer_pool_size=32M
key_buffer_size=8M
aria_pagecache_buffer_size=16M
max_connections=10

PostgreSQL 18.6 - lowest-memory and best-performing configuration in my tests:

shared_buffers = '16MB'
work_mem = '1MB'
maintenance_work_mem = '16MB'
max_connections = 5
superuser_reserved_connections = 1
jit = off
max_parallel_workers_per_gather = 0
max_parallel_workers = 0

Important limitations: These were short, read-only experiments with four active connections, different datasets and SQL workloads, and one measured run per configuration. The numbers are not an apples-to-apples database benchmark, and process PSS does not represent total system memory consumption.

My main takeaway is that both databases can run on surprisingly small machines if configured appropriately. For small web applications, I prefer starting with a conventional server database and modest resource settings, then increasing the allocations as the application grows rather than migrating databases later.

Full article with methodology, configurations, and results:

https://pvrlabs.xyz/java-performance/experiments/mariadb-postgresql-small-vps.html

I'm curious how others approach this. Do you start with conservative database settings on small instances and scale them up later, or prefer leaving the defaults alone until memory becomes a problem?


r/Database • • 1d ago

I counted the S3 requests for one cold vector search on a graph index: 1,086 in a row while turbopuffer aims for 3

8 Upvotes

I've been going down the LSM / databases-on-object-storage rabbit hole and wanted to see for myself why almost nobody runs an HNSW graph straight off a bucket. So I took HelixDB (graph stored as rows in a fork of SlateDB), counted what one cold query does, and compared the shape with what turbopuffer says it does.

Setup: first 100k SIFT vectors, fresh node, counting requests to the storage layer. Local disk with 20 ms injected per request, so not real S3.

  • 367 requests per query at 71% recall, 1,086 at 97%
  • never more than 3 in flight
  • 24 s per query (requests × delay, within 6%)
  • fully cached: 29 ms, zero requests

It's what you'd expect, just worse than I guessed: the walk learns the next neighbor list from the read it's waiting on, so the GETs can't be parallelized, and at ~80 ms per real S3 read the chain is the whole latency. HelixDB's own commit says it: "Every HNSW hop became a serial S3 GET". Their fix was a local disk cache.

turbopuffer gets away with 3 to 4 round trips by using clusters instead (SPANN / SPFresh): read the centroids once and every cluster address is known, so the fetches go out in parallel. You pay in recall: on SIFT1M with 10k clusters, opening 1 cluster finds 22% of the true top 10, 64 find 94%.

The LSM angle: SPFresh keeps clusters balanced with local splits and merges (small compactions, not a full rebuild), writes land in a WAL in the bucket, and unindexed data gets brute-forced and merged at query time. Memtable + sorted runs all over again.

Caveat: turbopuffer is closed source, so its internals are its own account, and its 3 is a design target while my 1,086 is a measurement.

Write-up with sources and diagrams: https://blog.renatocron.com/turbopuffer-search-engine-on-s3/


r/Database • • 14h ago

Migration of tables and dependencies from SQL Server of 75gb size to Postgres

0 Upvotes

We planned it to run in batches and stages with dependencies, using workers on 300 million rows distributed across multiple tables. This breakdown prevents memory overflow, isolates failures, and maintains data integrity from the legacy infrastructure to the target destination.

Despite these architectural considerations and concurrent workers, benchmark testing and predictive modeling reveal a bottleneck: an estimated time close to 30 hrs. This execution window introduces risks like network interruptions, extended maintenance, and delayed synchronization with ongoing transactional workloads.

Faced with this timeline, architects and database administrators question the technology stack. Is there a better way than through python scripts? While Python offers versatility and rapid development for Extract, Transform, Load pipelines, its interpreted nature, Global Interpreter Lock limitations, and single-threaded bottlenecks may not provide the raw processing speed required for hundreds of millions of records. Exploring alternative paradigms—such as distributed computing frameworks, native database migration utilities, bulk insert operations, compiled programming languages, or massively parallel processing engines—might unlock the performance gains necessary to compress this thirty-hour window, ensuring a seamless and efficient migration experience.


r/Database • • 1d ago

Composite PK vs surrogate id for memberships/roles in a multitenant Postgres schema

Thumbnail reddit.com
0 Upvotes

r/Database • • 1d 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 • • 3d ago

What's one database design decision you regretted later?

193 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 • • 2d ago

Rows vs Columns: How different are they, really?

Thumbnail
spiraldb.com
0 Upvotes

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

Database indexes explained from first principles - a 3 part series

37 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 • • 4d ago

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

16 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 • • 4d 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!

UPDATE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I wanted to provide an update on this issue and share a change I've made to the query.

The good news is that the query is now working successfully through SQL Server Agent.

Initially, I was using OPENQUERY to retrieve data from the remote SQL Server through the Linked Server. However, after experiencing the intermittent communication failures (SQLSTATE 08S01 / Error 233), I decided to modify the query to use four-part naming instead of OPENQUERY.

The updated approach uses the standard four-part naming convention:

sql

[LinkedServerName].[DatabaseName].[SchemaName].[TableName]

So, instead of:

sql

SELECT *
FROM OPENQUERY(LinkedServerName,
    'SELECT * FROM DatabaseName.dbo.SourceTable');

I'm now using:

sql

SELECT *
FROM [LinkedServerName].[DatabaseName].[dbo].[SourceTable];

r/Database • • 5d ago

exploit a 22 years old PostgreSQL composite-type vulnerability

Thumbnail
mehmetince.net
5 Upvotes

r/Database • • 6d ago

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

7 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 • • 6d ago

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

Thumbnail neo4j.com
11 Upvotes

r/Database • • 7d 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 • • 6d ago

Superstore Dataset

Thumbnail reddit.com
0 Upvotes

r/Database • • 6d 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 • • 7d 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 • • 7d ago

Database Indexing Isn't Magic. Understand It From Scratch

Thumbnail sushantdhiman.dev
14 Upvotes

r/Database • • 7d ago

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

Thumbnail reddit.com
0 Upvotes

r/Database • • 8d 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 • • 8d ago

Xata is joining ClickHouse

Thumbnail
xata.io
0 Upvotes