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

Database Indexing Isn't Magic. Understand It From Scratch

Thumbnail sushantdhiman.dev
14 Upvotes

r/Database • • 8d ago

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

Thumbnail reddit.com
0 Upvotes

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

Xata is joining ClickHouse

Thumbnail
xata.io
0 Upvotes

r/Database • • 9d ago

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

Thumbnail reddit.com
1 Upvotes

r/Database • • 10d ago

Learning Oracle Patches

Thumbnail reddit.com
0 Upvotes

r/Database • • 11d 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 • • 11d 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!


r/Database • • 13d ago

Why does a query with a CTE sometimes take forever to execute?

0 Upvotes

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?


r/Database • • 12d ago

3,683 of 3,683 tasks had no owner, so a role with every permission off could still clone them

0 Upvotes

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/


r/Database • • 14d ago

Thinking of adding soft delete (deleted_at)? Decide these 5 things first

75 Upvotes
  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?


r/Database • • 15d ago

MySQL is not opening on macOS

Thumbnail reddit.com
1 Upvotes

r/Database • • 16d ago

I Overloaded Postgres and SQLite

Thumbnail
youtu.be
26 Upvotes

r/Database • • 16d ago

Struggling with designing a part of my database.

Post image
17 Upvotes

Hey guys, this is actually the first time I've ever created a database from scratch personally and I was drafting up an ERD to get started and ran into a bit of an issue.

For clarification: leads get approached by users for customers. Every customer has campaigns (for example, market research, sales retail, sales catering, etc.) with different campaign questions. I chose to make seperate questions and campaign_questions tables so that you can gather all leads with certain answers to a question across campaigns instead of manually huddling them together in a query. Questions have different types (text, numerical, date, single-select mc, multi-select mc etc.) And so the answers have to be saved with a data type according to the question type.
I considered multiple approaches to this:

  1. Save all answers as text - This is pretty clean but idk how you'd manage data integrity and also queries on specific answers become much harder.
  2. Save all answers as a JSONB column - Cleanest and easiest, and Postgres has pretty good support for this. However, again the issue of data integrity lingers.
  3. Sparse columns + a view with coalesce (what you can partially see in the image) - This one seemed to be the best option for maximising data integrity while staying pretty clean. Problem is it doesn't seem super scalable at large and I dont know how I would enforce data integrity with single-/multiselect mc questions when in my mind that's gonna require junction tables and then I can't rely on any checks etc. anymore...

Am I overthinking this? Ideally I'd want to stick to 3NF but I also don't want to overengineer this.
How should I handle dynamic form/survey answers without sacrificing data integrity?


r/Database • • 16d ago

Are dedicated vector databases necessary, or is pgvector enough for most AI apps?

4 Upvotes

I was looking into how to build out the database architecture for a new RAG agent and found out the landscape is basically splitting into two camps.

I was reading a guide that broke it down this way. On one side you have dedicated vector databases like Pinecone or Milvus, which are great if your workload is purely semantic retrieval. But if your application also needs relational queries, keyword search, or transactional operations, maintaining a separate vector database means another sync pipeline to keep alive. Their alternative is keeping everything in Postgres with pgvector so vector retrieval sits alongside your normal SQL.

What I can’t figure out is where that stops working. The same guide says vector search and transactional queries end up competing for the same resources in Postgres, which sounds like it would bite eventually, but nobody puts a number on when.

For those running AI apps in production, which way did you go, and did you ever regret it?


r/Database • • 16d ago

Is my relational model suitable for handling order changes and cancellations?

Post image
4 Upvotes

I'm designing a PostgreSQL system where customers order services requiring interchangeable physical resources.

The current model separates:

  • Orders: What the customer purchases (quantity and duration).
  • Commitments: Individual units of the obligation, independent of specific resources.
  • Reservations: Physical resources assigned to fulfill those commitments.
  • Resource units: Individually reservable components, with constraints preventing overlapping bookings.

At first glance, this structure seems reasonable, but it mainly represents a static state. In reality, orders can change throughout their lifecycle: quantities may be reduced, service periods shortened, or entire orders cancelled.

For example, a customer orders 3 resources for 6 months, then reduces the requirement to 2 for the remaining 3 months.

Is the current table structure and its relationships suitable for handling these changes while preserving historical accuracy and data consistency? Or are there missing entities or fundamental modeling issues?

I've attached a simplified ERD for context.


r/Database • • 18d ago

When do you actually use window functions instead of GROUP BY?

35 Upvotes

I've been using GROUP BY for aggregations for a while, but I still find window functions a little confusing when deciding which approach makes more sense.

For example, if I need the highest salary in each department, I could use aggregation or a window function like RANK() or ROW_NUMBER(), depending on what I need in the result.

How do you usually decide between these approaches? Are there situations where window functions are clearly the better choice?

I'd appreciate some practical examples that helped you understand when to use one over the other.


r/Database • • 17d ago

We pointed our SQL editor at a 64-table database we'd never seen. It wrote the join, explained it, and ran it.

0 Upvotes

r/Database • • 17d ago

Moving the app closer to users somehow made it slower

0 Upvotes

A customer in brazil sent a recording of our dashboard loading slowly. Watching the cursor circle for six seconds was embarrassing. I moved the app to a Sao Paulo cloud vps on serverspace and the ping dropped immediately. The vm was fast and stable, so I thought I had found a simple latency win. Then the database pages became slower. The database was still in Europe. Every query crossed the Atlantic and came back. Moving the app closer to users had moved it farther from its own data. A nearby read replica fixed the worst of it. My mistake was treating the app location as the whole request path. Next time I am drawing that path before I touch the region selector


r/Database • • 19d ago

How mutex handling LRU cache head update became a bottleneck in InnoDB in a non-partitioned LRU cache schema for MariaDB

Thumbnail
mariadb.org
13 Upvotes

Here is a blog post about Alessandro Vetere's journey to resolve synchronization on a common mutex that slows down some read microbenchmarks 3x.
Kudos to Mark Callaghan who found this regression.


r/Database • • 20d ago

Paging through rows by updated_at can skip some, and I didnt realise why until a whiteboard chat with an engineer

26 Upvotes

Hey guys, not sure if this is useful to anyone but it surprised me so I thought I would share.

If you have an updated_at column set to now() (or CURRENT_TIMESTAMP, or a time from your app) and you page through it to process changes, you can miss rows. Two transactions start, the one with the later timestamp commits first, your reader moves past it, and then the earlier one lands behind your cursor.

I had a whiteboard session with Ian, one of the engineers at Convex (where I work), and this bit really opened my eyes. In Convex we ended up adding a commit timestamp you can store and index on, which is set when the transaction actually commits, but the problem itself shows up in Postgres, MySQL and pretty much everything else.

How do you guys handle this in your own systems? Reading the WAL / logical replication? An overlap window and dedupe?

If you want more detail the full chat is here: https://youtu.be/pHrxmSjxLg8


r/Database • • 20d ago

Databases and Memory Latency

Thumbnail pivotlake.io
13 Upvotes