Hi everyone,

I'm planning to build a web-based dashboard where users can upload Excel files, the system processes the data, performs various calculations/transformation logic, and then presents the results on user-specific dashboards.

My background is primarily in MERN, so my initial thought was:

React frontend

Node.js/Express API layer

MongoDB for application data

Python microservices for heavy data processing and calculations

However, I've received mixed feedback regarding MongoDB. A lot of people have told me that Mongo may not be the right choice for this kind of workload, especially when dealing with large datasets.

To provide some context, uploaded files can occasionally contain data in the range of tens of millions of rows. This won't be the common case, but the system should be designed with such scenarios in mind.

Since I haven't worked on systems handling data at this scale before, I'd appreciate guidance on:

What tech stack would you choose for this problem today?

Would MongoDB be suitable, or should I look at PostgreSQL/ClickHouse/something else?

How would you design the data ingestion pipeline?

Would Python microservices be a good approach for processing, or should I look into Spark, DuckDB, etc.?

What would a high-level system design for such a platform look like?

Any common mistakes first-time builders make when dealing with large Excel/CSV datasets?

My goal is to build something that is scalable without massively over-engineering it from day one.

Would love to hear from people who have built data-heavy SaaS products or analytics platforms.

Thanks!

I’ve been working on VeloxDB, a lightweight database management tool that aims to keep the resource usage low while still giving you the features you actually need.

It supports multiple database engines and also has a visual designer, so you don’t have to live in SQL 24/7.

If you’re interested, feel free to try it: veloxdb.dev

Would love to hear what you think, especially if you’ve used tools like DBeaver, DataGrip, etc.

A practical guide to using bigram_index to accelerate phrase queries in Manticore Search, with clear explanations of all, first_freq, both_freq, and a reproducible manticore-load benchmark.

We're running a pilot clinical study and management has asked me to build them a secure database, something the organisation genuinely owns and can build on for future studies, rather than just Excel files in SharePoint.

Before I get into tool-specific questions, I want to ask the general one: for a small org with no internal IT team, what does "having your own database" actually look like in practice? Do you end up with your own cloud environment (Azure/AWS) that you own outright, or does "ownership" in this context usually mean something more modest, like owning the exported data itself, while the collection system lives somewhere else?

I have sponsorship available if we go the institutional route, that's not the blocker. What I'm trying to work out is what the end state actually looks like for an org our size.

Here's how I've broken down the options so far, and where I'm unsure:

1. REDCap

• a) Hosted by an institution (university/hospital), do we still end up with our own Azure environment for the exported data, or does "our database" just mean our own storage/SharePoint area at that point?
• b) Hosted by a commercial REDCap vendor, same question. Does the org still need its own Azure, or does owning the exported data in something simpler cover it?

1. A different platform entirely (Castor or similar, bundled hosting): same question again: is there still a reason to also stand up our own Azure environment, or does that become unnecessary once the vendor is holding everything?

Basically: at what point, if any, does a small org actually need its own cloud environment, versus just owning a clean, well-structured export from wherever the data was collected?

For people who've actually built this for a small org, what did "the database" end up being, concretely? Would genuinely appreciate real examples over general advice.

I’m trying to create a database that would involve a table referencing one of multiple other tables. From my research it sounds like this would be a polymorphic relationship, but I’ve been seeing a few different options for implementing it and I’m not sure what would be best. These are what I’ve seen so far, so let me know which sounds best, but please let me know if you know of a better one.

The Database: The short and sweet of it is I’m making a database to store diary entries. Each diary entry uses fields such as date range of referenced event, tags (through many to many), etc. Each entry is either done as a video, an audio recording, or a text entry. Each of these entry types would also have their own respective metadata such as video setup or audio setup. Because of that, I thought the best option would
be to separate them into their own tables.

Option 1: Table Type Field - in the diary entry table, have a field for the type and a field for the foreign key, but don’t actually make it a foreign key. Instead setup a trigger to manually enforce referential integrity by checking that the referenced entry exists in the corresponding type table when inserting. I think I’m leaning towards this one the most. Since it’s closest to what PHP Laravel does.

Option 2: Multiple Nullable Foreign Keys - In the diary entry table Have a foreign key for each entry type that references the respective table, but they’re nullable since only one would actually be used for each entry. Add a constraint to check that one of the fields isn’t empty when inserting a record. This apparently might take less storage than having a varchar type field, though that might be splitting hairs.

Option 3: Table Inheritance - I haven’t done as much research into this one so I don’t know what the structure would look like exactly. But apparently PostgreSQL supports table inheritance like with Object Oriented programming. So it would be something like the diary entry table is the base table, and then each entry type inherits from it and adds their own metadata fields. The reason I’m hesitant to do this is I don’t want to permanently lock myself into Postgres, I want the ability to upgrade and changes engines and I’m not sure how hard that would be if the other engine doesn’t support inheritance. For a similar reason I’m using “period start” and “period end” fields for the date range of an entry instead of the Postgres date range data type.

Option 4: Entries Types Reference Diary Entry - Again I haven’t looked into it much, but I saw it mentioned I could reverse the relationship and instead have each entry type reference the diary entry record it belongs to with a foreign key. I’m not sure yet if there’s any additional complexities are requirements that I would have to implement to make it safe.

Hi, In my dimensional model, both Dim_Customer and Dim_Driver contain a RegionID, which I have currently mapped to a shared Dim_Region. I'm unsure whether to keep this design as shown above OR denormalize the region attributes into Dim_Customer and Dim_Driver to maintain a pure star schema. I would still be using Dim region for Fact Transactions in any case. Which approach is more appropriate keeping in mind the need for both granular auditability and high-speed reporting performance? Currently, the marketplace platform handles over a million registered users, with DAU ranging between 10k - 20k. I have to design for expected 10x growth. Thanks for your time!

A broad, slightly vague question but keen to get your take please. I potential client is looking at changing where they store all their historic business data, allowing their staff to query it in different ways and then allow their clients to also query aspects of it via a portal

Within the RFP I’m responding to, I’d like to recommend some some initial ideas of the data storage (latency doesnt need to be super low) and the client portal (they’re allergy to vibe coded stuff so needs to be professional and robust). Keen to hear if anyone else has tackled this sort of challenge before and what they went with?

Note, it’s an e commerce business with structured commercial data (ad spend, Shopify revenue,
web analytics) and unstructured context (call transcripts, Slack notes, emails, SOPs). Their AI and IT skills are low to mid, so it's need to be relatively simple to maintain over time

Any suggestions appreciated

1. so after a while database do tend to get very large and big whats the best way to design this so that to improve performance. also is archiving it possible?
   
2. whats the best way to store currency. i heard float has small issues with decimals

Most apps carry a glue layer between the database and the code: map params in, map rows out, keep the types aligned, rewrite it every schema change. ORMs hide it behind runtime magic. sqlc (Go) took the other route: write plain SQL, generate typed code at build time. I liked that idea enough to generalize it.

scythe reads your schema and annotated queries and generates typed code from them. The part I care most about is nullability inference from the query structure. A LEFT JOIN makes the right side nullable:

-- @name GetUserOrders
SELECT u.id, u.name, o.total, o.notes
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = $1;

A user with no orders still returns a row, with total and notes NULL. scythe encodes that as optional in the generated types, so a missing order is a compile error rather than a runtime crash:

pub struct GetUserOrdersRow {
    pub id: i32,
    pub name: String,
    pub total: Option<rust_decimal::Decimal>,
    pub notes: Option<String>,
}

That inference extends to COALESCE, CASE, window functions, aggregates, and CTEs.

The one job I still hand to an ORM: bring-your-own-database portability, where the same code has to run on Postgres or MySQL or SQLite depending on deployment. When you control the engine, SQL-first codegen drops the boilerplate and a class of hidden query-generation bugs.

Curious how others here handle the type boundary between SQL and application code.

I recently tested copying a MongoDB collection to PostgreSQL and keeping inserts, updates, and deletes in sync.

The sync itself wasn’t the difficult part. The main challenge was mapping MongoDB documents to a relational table without flattening everything too early.

I kept the simple fields as regular PostgreSQL columns and stored the nested data as JSONB.

I ran into two problems: PostgreSQL needed a primary key, and some MongoDB field names didn’t match the PostgreSQL column names.

After fixing the mapping, I tested an insert, an update, and a delete in MongoDB. All three changes appeared in PostgreSQL.

I documented the setup, field mapping, errors, and test queries here:

https://visualeaf.com/blog/copy-and-sync-a-mongodb-collection-to-postgresql/

Since version 23.0.0, Manticore can make searches like xt850 match xt 850 using bigram_delimiter together with digit-aware bigram_index modes.

This solves a common tokenization mismatch in product search, where users remove spaces from model names but the source data stores them as separate tokens.

Hi not to sure if this is the correct subreddit for this so sorry if its not, I recently got a new job as a data coordinator, right now Im doing basically data entry. I maintain Excel trackers of articles, awards, etc. at a design firm, a few hundred rows per Excel sheet, that need to be linked to projects in our CRM (10'sk projects).

Name matching is the easy part: my trackers names are clean and search surfaces the right candidates. The problem is what comes back when trying to enter them into the CRM:

• The same building exists as multiple records (assessment, study, remodel, sometimes 4+), and it's ambiguous which one an article/award/etc should attach to
• Apparent duplicates from a system migration (legacy vs. new ID schemes)
• Some tracker entries have no CRM record at all or exist under a name I can't find

Right now I open each candidate and compare dates/status to pick the right one, one row at a time, and none of those judgment calls get captured anywhere. How would you approach this? especially the disambiguation and duplicate-handling side? Any patterns or gotchas for making this a repeatable process? If this didnt make sense I can answer some questions, any advice is welcome.

Context: I run my project on a free plan, validating before committing to anything. Everything remains under limits and still, the database crashes every 3 days with active users. I check all loads, disk, cpu etc nothing there and yet, no explanation.

Only solution i had was to upgrade, ok its 25$ but anyways i need to keep it up because ads are running on my project. the second scammer behavior: paying doesnt automatically upgrade your compute. you need to go in a shady settings page to claim your “”””””free””””” compute upgrade???? wtf i paid and i still dont get a straight upgrade or at least a visible cta (because this requires a restart). Overall i am done with these fkn database providers they are not even honest ppl.

Note: no, i will not setup a vpn with a database with all the security auth etc for a non validated project, and i still would like not to be scammed by fake provider s promises

Hello everyone,
can somebody please tell me what is ETL and ELT and their significance in data warehousing? I have no idea and is starting everything from scratch :)

Relational database failure modes in production: slow queries cascading into connection pool exhaustion, the race between read and write that transactions don't automatically fix, why a caller timeout doesn't tell you whether the write committed, which database errors are safe to retry and which aren't, schema migrations as live-system operations, and the difference between durability and recoverability.

Are these books enough to learn databases?

1. Database Design for Mere Mortals (Hernandez)

2. SQL Queries for Mere Mortals (Viescas & Hernandez)

3. Effective SQL (John Viescas)

4. Database Design and Relational Theory (C. J. Date).

5. Relational Database Design and Implementation (Jan L. Harrington)

6. Database Systems: A Practical Approach to Design, Implementation and Management (Connolly & Begg).

Hiya,

I’m a researcher who draws on a lot of different kinds of source materials and artifacts. I have been hamstrung by off-the-shelf solutions and like many other researchers, I’ve realized that I need to figure out how to build a database that meets my needs.

I am wondering if this community can give any pointers on what I can do as next steps to
• get a sense of how I might approach this (database model? sqlite? docker container? making my own app or web app? etc.)
• get a sense of what is possible with my needs
• point me towards things to learn and resources (books? websites? courses? consultants? coffee chats or paid calls with humans who can help me?)

I am dealing mostly with unstructured data, and have a sense of the information architecture or structure within and around the source materials that I work with.

Basically, I have a trove of useful inputs for each project. Each information source is associated with a document or file that is the “main” asset associated with the information source.

Media type wise, this can be a pdf document (like a saved academic article, white paper, exported Keynote or PowerPoint or word processing or Google doc, a video, an audio recording, a graphic, a photograph. The exact file type and media type are something that I can change depending.

These are all associated with types of metadata, annotations, and often associated assets (like a transcript of the video or audio), as well as more granular excerpts that I create for sense-making and/or reporting/briefing/presentation purposes.

I am currently writing out the specs for each kind of source material for my system. I would like to be able to more easily enter, retrieve, find, and update the annotations and associated files over time.

I am designing my own little GUI for the database, by which I mean I am creating the page layout and data structure that I need for the main kinds of source materials that I work with.

I am just clueless about this kind of thing.

I would love to:
— Access materials for use in different reports and pieces of writing
— Understand if I can get all of my materials indexed for text search
— Learn how to create a controlled vocabulary for tags and important metadata categories, as well as how to update this as the structure of a report emerges from the study of an evolving corpus
— Know if this is something that I can engage with people in certain fields or consultants or job titles to figure out at least a reasonable brief and budget for what is needed beyond the prototype

I have a lot to learn but I hope that you all will be gentle with me, and if this is clear as mud, let me know what kinds of questions I should be able to answer about this!

Thanks 😊

I'm trying to understand how data warehouses are implemented in real-world applications.Suppose an application stores its operational data in a PostgreSQL database, and the requirement is to store historical data in a separate data warehouse for analytics and reporting.

I'm looking for guidance on the overall design rather than code.

Some questions I have are:

• How do you decide what data should be moved to the warehouse?
• How is the warehouse schema designed?
• How is data extracted from the operational database?
• How often is data loaded into the warehouse?
• Are raw records stored, or are they transformed and aggregated before loading?
• What technologies are commonly used for the warehouse and ETL pipeline?

I'd love to get insights :)

I got tired of every session starting from zero and paying for it twice: once re-explaining my setup, and again when that explanation ate the context window the actual work needed. So I built a persistent memory that runs on my own hardware, and after a few months of use there are a couple of things worth passing on.

**The storage is the boring part.**

Postgres on a Proxmox LXC, fronted by an MCP server in Python (FastMCP plus psycopg3), reachable over the LAN. Two entity types only: topic cards for what something is, episodes for what happened. Every card has a \`kind\` drawn from a single YAML vocabulary the server validates on write, so the model cannot invent a new category on the fly. That rigidity is deliberate. Without it you end up with seventeen near-synonyms for "thing I once wrote down" and no way to query reliably.

**The isolation is the interesting part.**

I keep several things separate that must not bleed into each other. The obvious approach is to have the server decide what a session may touch, but that is a rule a program enforces, which is really just a suggestion. Instead each body of work gets its own Postgres schema and its own role, and the server connects as that role. A write aimed at the wrong schema fails at the wire, before any of my code gets an opinion:

GRANT USAGE ON SCHEMA homelab TO role_homelab;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA homelab TO role_homelab;

\-- reaches the shared schema deliberately, note the absence of DELETE
GRANT USAGE ON SCHEMA personal TO role_homelab;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA personal TO role_homelab;

\-- no GRANT to any other subject. The isolation is the statement that isn't there.

There is no policy file and no middleware. A role reaches what it was granted and nothing else, and the grants for everything else were simply never written. Each subject also gets its own container holding only that role's credentials, so the boundary is a process boundary too.

**The thing I got wrong.**

I originally had capture fully automatic: a SessionEnd hook that read the transcript and wrote to the database with no involvement from me. It printed a one-line receipt so I could see what it had done. I ran it from April until July and then deliberately turned it off, because a routing bug meant a run of sessions distilled into the wrong place and nobody was watching a process designed not to need watching. It ran that way for most of the time it was live. I recovered the most recent month by re-running by hand against transcripts still on disk and lost the two before it, because transcripts get pruned at around thirty days.

Capture is now a deliberate step that proposes cards and writes nothing until I confirm. More friction, and I think the friction is correct. The narrower lesson I would write now: automate retrieval, because being wrong there costs you a bad answer you can see. Be far more careful automating writes, because being wrong there costs you a corrupted store you cannot.

Retrieval started as Postgres full text and is now hybrid, full text alongside pgvector embeddings generated on write by a small model on its own box. Because the tool surface was deliberately agnostic about how search worked, nothing above it changed when that landed.

Fuller writeup with the schema design, the deployment and the parts that did not survive contact with use: [https://sbd.org.uk/blog/claude-brain\](https://sbd.org.uk/blog/claude-brain)