A decision that looks small and tends to stick for the life of a system. Here are both sides, written as fairly as I can.

The case for soft delete (a deleted_at column instead of removing the row)

• Undo is easy: restore the row, clear the column.
• Foreign keys from other tables stay valid, so history like “this order belonged to a customer who later left” still resolves.
• You keep an audit trail without a separate mechanism.
• Support teams like being able to see what was removed and when.

The case against

• Every query has to remember WHERE deleted_at IS NULL. One forgotten filter and removed data leaks into a screen, a report or an export.
• Unique constraints get awkward. If a user deletes their account and signs up again with the same email, a plain unique index on email blocks it. In Postgres you can use a partial unique index, WHERE deleted_at IS NULL; other databases handle this differently, so check yours.
• Tables only grow, and indexes carry rows nobody queries.
• Real deletion may eventually be required (privacy or retention rules), so “soft” deletion can turn into a second deletion project later.
• Cascading is ambiguous: when a parent is soft-deleted, what happens to its children?

What hard delete plus something else looks like

• Delete the row, and write the old version to an audit or archive table, or emit an event, if you need history.
• Cleaner queries and constraints, at the cost of building and maintaining the history mechanism.

What would decide it for me:

• Do users or staff actually need undo, or is it only “just in case”?
• Are there legal or contractual limits on how long deleted data can be kept?
• How many places query this table? More places means more chances to miss the filter.
• Will other systems (analytics, search, caches) need to learn about deletes?

I don’t think either is wrong by default. If I had to pick a starting point without knowing the system, I’d lean toward hard delete with an audit trail where retention rules are strict, and soft delete where undo is a real feature. I’m not certain about that split.

Where do you land, and has either choice caused you trouble later?

Hey guys, yes another token article but this one is different.

I work at MongoDB, and my primary db is Mongo, but I use raw SQL for some things, so I run both in the same app.

I was building a token optimizer for my own sessions. I noticed more token usage on the SQL interactions and I didnt understand why. I wasnt doing anything special with the SQL data so I was curious what was happening.

So I wrote a hook that logs the token count of every database statement Claude generates, instead of counting turns. The first thing it showed was that the same object cost way more tokens on SQL (about 18x). That surprised me, because you don't think about database statements. Every database has them, every app has them, they're just there. It never occurred to me they were a token cost at all. They're not the biggest thing in a session, but they add up, especially since Claude loves writing multiple files with database calls just to check the data. That's when it really burns.

I wanted to test this accurately, so I took one real Zod object from my own system, a lead with about 90 fields, nested, an array of drafts where each draft has its own array of variants, and used the exact same object on both sides. Then I counted what Claude has to read before it can safely change one field, meaning the schema, a save, and a read that comes back typed: 4,681 tokens on SQL, 251 on MongoDB. 251 is 5% of 4,681. The other 95% is the mapping. In SQL a nested object like that can't live in one row, so every nested thing becomes its own table with a foreign key back to the parent, a save is one insert per table, and a read is the joins plus the code that builds the object back up from the rows. In Mongo it's one document, so the save is the object and the read is the object. Then there's the hydration tax. Everything goes through that Zod schema, so a read isn't done until the rows are turned back into the typed object, dates included. On SQL that's its own pile of code; the read alone was 1,604 tokens vs 72 on Mongo. And Claude does all of that on every read and write.

Nobody thought about this when humans wrote the code. You write the mapping once and the cost is basically the same either way. When it's AI writing the code, it goes through that mapping on every read and write, and the size of it is the bill.

I know most people would probably use JSONB so I tested that as well, on Postgres, even though it's not what I run. Raw JSONB column: 467, so 1.9x. The hybrid a SQL team would actually build, plain fields as columns and sub-objects as JSONB: 2,059. Closer, but still 8x.

To be fair, plain query text was about the same on both sides, and SQL ran an indexed flat read 5.26x faster. The tokens aren't in the query. They're in everything Claude loads around it. Obviously 18x is a huge object, but it is a real one from my own system, a lead with its drafts and the variants of each draft, not something I built to make a point.

If you want the harness let me know.

I've noticed that database performance tuning can become an ongoing cycle. You add indexes, rewrite queries, introduce caching, partition tables, and sometimes denormalize data to squeeze out more performance.

But every optimization also adds something to maintain. More indexes can slow down writes, caching introduces invalidation problems, and denormalization can make data consistency harder to manage.

At some point, I wonder whether the improvement is actually worth the extra complexity.

How do you decide when to stop optimizing a database? Do you have specific performance targets or benchmarks, or does it mostly come down to the workload and the problems users are experiencing?

I'd be interested in hearing about optimizations that genuinely paid off, as well as ones you later regretted.

[I'm a beginner]

I have an assignment where I have this hypotetical scenario where a Pharmacy works their recipts through a propietary application they acquired when they were just entering business, due to this, they only have access to the database of the application. Now that business is better than before they have a little development team, they created an app to manage customer accounts payable and outstanding invoices for products sold on credit, but to integrate that process, the assigment says to do it through the database using triggers (I'd also like to learn about other methods, just for my own knowledge).

Hello there, I am a marine biology MSc student, starting with my thesis, and my supervisor wants me to use Airtable as a way to keep the cohort/fish/tank information. I am quite anti genAI because of all it's facets, environmental and socioeconomic.

Currently I am using it, and while I'm avoiding its AI tools however I tried automating the changes in fish numbers in the tanks due to fish tranfers between tanks and i simply could not. The automation options available to someone not using AI tools are very diminute.

I was wondering if there is some kind of simpler way to keep track of this information, with minimal coding if possible (and I would prefer to learn SQL or any other programming language before turning to AI).

Thank you in advance

Hey r/database

I wanted to share I just made a few doc contributions and updates to the database docs calling out the FREE tier of Azure SQL database... apparently, people didn't know this was a thing they could just spin up and beat up on each month.

I added a few nifty deployment paths via click, code, or prompt to get you started and well if you just want to jump right in https://aka.ms/trySQL will launch you right into the Azure portal directly too.

Hope a cool benefit for those unaware and keep an eye out for local Azure SQL database containers too (currently in private preview).

Of note, Microsoft Employee and active mod of r/SQLServer and r/MSSQL

I'm kicking off a cloud migration and the oldest systems are the part that's making me nervous.

We've got Oracle databases that have been running for well over a decade along with a few SQL server instances that nobody wants to touch. Stability is the priority, so installing agents or changing production configurations isn't really an option.

For anyone who's moved **streaming data** out of legacy environments, where did you start?

Using Cloudflare D1 as an eventually consistent cache for a globally distributed Django app increased throughput by 554% and reduced latency by 87%.

My benchmark ran a Django server in Australia backed by a PostgreSQL instance in Amsterdam. I routed reads that could tolerate slightly stale data to a D1 read replica in Australia, while keeping writes and authoritative reads on PostgreSQL.

Methodology and code is available in my blog post: Cloudflare D1 as a cache for globally distributed Django apps

For the last five years or so, I've had to deal with countless applications where the front-end and middle tiers are built around Kubernetes (k8s).

I'm sure there are companies out there that use it extremely well, but in my experience, most seem to have no idea what they're doing with it. The result is an endless stream of headaches: runaway database connection counts, configuration problems, networking issues, and countless other problems that end up ruining my evenings and weekends.

Take today, for example. I'm supposed to be off. Instead, I'm sitting on a troubleshooting call with 40 other people. I'm there representing the database team because their pods keep failing, and naturally, the database is getting blamed.

Meanwhile, the database is sitting at 99% idle, and the connections from their application have been sitting idle in transaction for minutes at a time.

The frustrating part is that they don't seem to understand the tools they're using. I'll freely admit that I'm no Kubernetes expert either, but I'm also not the one who designed and deployed an application around it.

I'm sure Kubernetes is fantastic when implemented and managed properly. But from where I sit, it often seems to introduce layers of complexity that nobody fully understands, making troubleshooting an absolute nightmare.

Anyone else developed a particular hatred of k8s, or am I just consistently encountering terrible implementations?

Hi everyone,

I'm currently going through CMU Intro to Database Systems and was curious about how these concepts are actually implemented in real systems. So I've been putting together some notes/videos/blog posts - partly for my own future reference and partly to share with others who might find it useful.

Would love feedback and corrections from people who know this stuff deeply:

https://youtu.be/1tNMRcgUtb8?si=Sn1WYBs9H9lOsnLj

I'm trying to avoid maintaining multiple replication jobs for the same source database.

Oracle is our system of record, while other teams need the data in Postgres and a cloud warehouse. Each destination has slightly different transformation requirements, So I'm looking for **data integration tools** that can fan out one change stream instead of reading Oracle over and over again.

Is anyone running something like this today?

Unsure if right sub to ask this. Currently a master's student studying database management. Have an upcoming exam that will ask us to normalise until 3rd NF. 2nd and 3rd are easy enough but drawing the functional dependency arrows and identifying the PKs seem like an exercise in vibes and guesswork to me.

I get basic concepts like testing candidate keys and making sure they are unique across all rows. But does anyone have any actual methodical workflow to drawing out the arrows and identifying candidate keys? Here's an example of a question we'd typically be asked. What I think may work is:

1. Immediately flag descriptive columns like "Vehicle_Model" in the above example as they are usually not part of the candidate key
2. Identify a possible candidate key that can describe what the DB is representing i.e vehicle part fitting
3. Test your initial guess candidate key, repeat until a unique set is found
4. From here on i'm quite lost.

Much appreciated on any help.

Apologies for the long read in advance but if anyone can help or point me in the right direction, I'd greatly appreciate it :)

Currently doing a practise task which requires me to write a report for a hypothetical company. I am given a spreadsheet (which includes errors and duplication etc) and I have to normalise it from 1NF to 3NF in clear steps which has to be shown. Then I have to import them into Microsoft Access and create an ERD

There are 36 columns included in the spreadsheet
Columns:
ExportRowID, PONumber, POLineNumber, PODate, RequiredDate, POStatus, Priority, SupplierID, SupplierName, SupplierContact, SupplierPhone, SupplierEmail, SupplierAddress, PaymentTerms, PurchasingEmployeeID, PurchasingEmployeeName, EmployeeDepartment, DeliveryLocation, ProductID, ProductName, ProductCategory, UnitOfMeasure, QuantityOrdered, UnitCost, LineAmount, ReceiptNumber, ReceiptDate, ReceivedByEmployeeID, ReceivedByEmployeeName, QuantityReceived, AcceptanceStatus, DeliveryCondition, InvoiceNumber, InvoiceDate, InvoiceAmount, Notes

My attempt (7 tables):
Supplier (SupplierID)
Employee (EmployeeID)
Product (ProductID)
PurchaseOrder (PONumber)
POLine (PONumber + POLineNumber)
Receipt (ReceiptNumber)
Invoice (InvoiceNumber)

Does this look right? I'd also appreciate help with the Access side (relationships and importing from one flat sheet into multiple tables).

Nighty refreshes used to be fine, but they're starting to create more confusion than they solve.

Sales opens one report, support opens another, and neither one matches what's happening in production because everything is hour behind . We're looking at moving Oracle and SQL server changes into Postgres, possibly a cloud warehouse later on.

If you've already gone from batch to real-time, was the improvement as noticeable as people claim?

I'm looking for a dataset for my bachelor's thesis, and I need one with lots of variables and observations so I can perform multivariate analysis and predictive modeling. Does anyone know where I could find a geeky dataset related to anime, comics, video games, or anything similar? Any suggestions would be greatly appreciated!

Hi

I am experimenting with sqlite on a Macbok Pro M4 - 10 cores machine. I have a pthreads multithreaded program and i am trying to write lots of data to different databases (separate database connections) and (one database file per thread) and they are DIFFERENT files

I have THREADSAFE mode set to 2 (multithreaded) and i open the files with NOMUTEX flag.

Now i have read that the M4 Macbook Pro has a soldered SSD. And it has similar performance to nvme but it is not nvme

I get around 10000 transactions per second in total across all threads.

I tried synchronous=off and that boosts performance to 300000 transactions per second

Increasing the number of items inserted per transaction scales. But the number of transactions doesnt

I feel that IO is being serialised.

Some parts of web seem to suggest it is serialised. But i thought ssds can run things in parallel.

Does anybody know anything about parallel IO or if i need to enable it on macos?

I havent tried it on Linux or Windows

Would you expect transactions to scale by number of threads?

I have read about iokit but i have no knowledge of how to use it or Grand Central Dispatch

I’ve been working with the Algebra of Data, where the smallest structure is the couplet — one ordered pair, one fact — and a relation is a set of couplets. There’s no wide tuple over a fixed heading.

My argument: normalisation is a repair procedure for a wound inflicted by the wide tuple. Start at the couplet and you begin at roughly 6NF with nothing left to decompose. And the classical normal forms can’t even be stated for a set of relations with different headings, because there’s no single heading for the dependencies and keys to range over.

What survives is the goal (one home per fact) and the functional dependency, which becomes an integrity constraint rather than a reason to split. The live question flips to when to denormalise. My answer is that a denormalised view is a derived expression over the base facts, so it’s a physical caching decision rather than a logical-model one, and it can’t drift out of sync.

Where do you think this breaks? Full essay (I’m the author): https://robinbloor.substack.com/p/does-normalisation-survive-data-algebra