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

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

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?

Hello I'm a newbie college student studying databases and I'm currently making a database for a small inventory system that's solely focused on monitoring stocks and its shelf life to avoid running out of stock and pull outs for my project. The store I'm basing it off on sells seasoning and other powdered flavoring (beverages, etc.), they also explained that they get bulk products from the supplier and then they repack them into smaller quantities for selling. What would be the proper entities and attributes for this system? I've tried searching other inventory systems but i don't know how the repacking would be translated to the database/ERD

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.

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?

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

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

Hello!

My primary experience is with Airtable and my new company will not purchase it going forward as it is to expensive (84K for 2 years). I need to see what alternatives there are with the constraints I have. Biggest constraint is that the software package has to be self contained and I cannot use outside plugins. I have immediate access to Sharepoint (with powerapps), Access, Excel, and PostgreSQL but I dont think any of them fit my needs, but would love more input from people a lot more knowledgeable in these software's. So this is my use case: I am wanting to build a relational database. Each 'sheet' has parent information that I can connect together in a form. Sheet A, has entires like cat, dog, Sheet B has Colors like blue, red, and I can have sheet C that can have inputs from a form that users can connect the data. I can add pictures and other info. I can then have a dashboard that can create graphs from the data or search bars that I can search for 'Dog" and it shows me all entires related to "Dog'. I also need this software to be able to handle formulas and tie in the different sheets. I can Pull in Dog+Blue=BlueBerry and output into Sheet D. So the biggest things are: Able to connect the data, have data input through forms, able to create dashboards that can show the data through graphs, have KanBan style dashboards where I can show Status of certain fields. Airtable was the perfect software for my needs, so I need a Airtable equivalent but I just dont have any experience with other softwares to dig into them.

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

I need a real project to test something database-related. Nothing production critical, just a hobby/fun project with some actual usage.

If you have one and are okay with me testing on it, let me know 👀

I'm in the early stages of designing a database for an organization that does environmental remediation and research. (Small org, so I'm basically the data person.) One of the major types of data we have is the results of tests for a range of soil contaminants, so in my schema draft, the sampling points within work sites are linked to tables of associated measurements. (Currently there's one table per sampling point but I'm open to other designs.)

The problem is that which contaminants are tested for mostly can't be known in advance for future projects and can change within a project if new funding appears or questions arise. There are hundreds if not more of substances that could reasonably be tested for (although ~20-30 are core), so listing all possibilities doesn't seem practical. And the contaminants data is key to the organization's work and is probably the thing that will be queried the most. How would you handle a situation like this?

So I am in my final year and I want to do something spicy, which is a minidb, what and what do you guys recommend me to know so it can be a success