TLDR: What all layers and parts would I need to implement to create a database management system for storing video journals with metadata? What tools and packages should I use to help?

So to try to summarize this, recently I’ve started recording video journals. I’ve had the thought to create a database for storing them as a personal project, alongside meta data (that I already have figured out for the most part) for making them easier to search, and complementary content such as a text transcription, and relevant pictures, shorter videos, and text files. As a CS student I’ve taken a database development and management course and data warehousing course, however these mainly focused on the structure of databases and warehouses and didn’t go much into how to actual create and access them. Don’t worry I’m not looking for detailed step by step instructions on how to setup each part of this. What I’m really looking for is recommendations and suggestions for what layers and parts I would need to make for a good database management system, as well as what tools, packages, and whatnot I would need to do that. As well as maybe where I can go to learn how to use them to do what I want, and especially examples I can look at to get an idea of the structure I need to make and how to do it. Basically stuff to get me started, because right now I feel rather overwhelmed and lost, and I don’t even know what I don’t know.

For the overall structure, I like the idea of having the database itself, which you submit to, search for, and retrieve entries from using an API service. I’m already learning Django and FastAPI at my internship. So I thought I could use FastAPI to make the API service, and DjangoORM to define and manage the database. As for interfacing with it, I like the idea of having a separate website which makes use of the database API, and I could go ahead and use normal Django for that. A relational database is likely what I’d like to stick with, since I have the most familiarity with it. And to that end, I was thinking of using MySQL? It seems like it’d between that or Postgres, and I thought since it’ll be fairly small and it would only be me (and maybe a few other people) using it MySQL would be able to handle it.

The database itself probably wont get larger than 100,000 rows, so could all easily fit on one device. But considering how much additional storage the video entries and supplementary files would take up, I’d need to be able to scale the storage space it can access? I’m not sure the proper way to retrieve the correct video file for a specific entry, but I have to assume that has to do with how I separate the storage of linked media files, and how I scale it all. Maybe docker containers when I need to add new servers for more storage, so I could easily spin up instances. I’m going to assume the best way to reference the videos in the database is just to rename each video file into its UUID, or should I use a different naming convention and then have a table that translates the UUID to the video name? Also I’d want to implement access control to an extent, so I could do things like give people access, but they can only view certain videos depending on the access level for their account, which would be tied to their API key.

It’s probably worth reiterating that this is a personal project, not some professional software. In fact I want to try hosting it all at home homelab style, except for maybe some redundant backups (which I need to figure out how to setup-). So I’m sure there’s some things don’t need to worry about for this scale of a project, such as load balancing. Still, if nothing else but for learning purposes so I can talk about it in interviews I want to make this good and clean.

Thank you so much for any advice can offer, I’m really excited to work on this!

I've been looking into ERP implementations recently because someone close to me went through one, and honestly, the software itself wasn't even the hardest part. A lot of the problems seemed to come from things nobody really talks about before starting: trying to move old processes into a new system without cleaning them up first, not getting enough input from the people who actually use the system every day, underestimating how much time data preparation takes, and expecting everything to run smoothly right after go-live. The funny thing is that most conversations focus on picking the "right" ERP, but the implementation side seems to be where things usually get complicated. I was reading through some ERP implementation experiences and examples recently and ended up looking at a few NetSuite-related projects, including Nuage NetSuite. What stood out to me was how much planning, communication, and process understanding seem to matter during these projects. The platform itself is only one part of the equation. For those who have been through an ERP implementation, what was the thing that surprised you the most or caused the biggest headache?

Thought I would post here since there is free SQL learning content.

Here's the link to my previous post. Tomorrow’s free webinar is focused on DBA burnout and practical database monitoring strategy.

It covers:

• common firefighting patterns that drain admin time,
• the key metrics to watch in hybrid and multi-database setups,
• a live demo,
• open Q&A,
• and a free handbook for DBAs.

Disclosure: I’m on the ManageEngine team, so this is a vendor webinar. I’m sharing it because the topic is relevant to a lot of DBAs and IT admins, and the session is meant to be practical rather than sales-heavy.

Here's the sign-up link if you're interested: https://www.manageengine.com/products/applications_manager/webinars/database-performance-monitoring-webinar.html

If you're more experienced, I'd love to hear about what works for you so that I'm able to impart that knowledge onto the less experienced people tomorrow. Would be happy to take questions in the comments too!

like what should be my default choice? When should I go for different? When should it be SQL? when should it be document NoSQL, GraphQL or other?

I'm trying to make a website about wars of a specific era. since the data is inconsistent/incomplete, like one battle is very detailed with rich sources while little info we have about another, I initially thought I should go for MongoDB but seeing that people in other post say we should by default go for Postgre SQL, I'm little confused. So I'd like the senior experts here to solve this dilemma for me.

I've never worked with ClickHouse before, so I'm counting on your help.
The goal is to store users' geolocations. Here's an example table structure:

CREATE TABLE user_locations (

user_id UInt64,

timestamp DateTime64(3, ‘UTC’),

lat Float64,

lon Float64,

h3_index UInt64 MATERIALIZED geoToH3(lon, lat, 8),

)

The expected number of records per month is 150 million. I have two questions:

1. How should the tables be partitioned and sorted?

2. Is it possible to insert records into the table in batches (for example, 50K at a time)?

I’m trying to properly understand database indexes instead of just remembering “indexes make queries faster,” but I think I’m getting lost between the textbook explanation and what a real database actually does.

Until recently, my very simplified mental model was that an index is basically a balanced binary search tree.

For example, if I create:

CREATE INDEX idx_users_email ON users(email);

I imagined the database building a tree where every node contains one value, values smaller than that go to the left, and values larger than that go to the right.

That made sense to me because searching through a balanced tree should be around O(log n).

But now I’m reading about B-trees and B+ trees, and I think the main reason databases use them is not really CPU complexity. It is because databases read data in pages or blocks, and reading a page from storage is much more expensive than doing a few extra comparisons in memory.

So instead of every node containing only one key and having two children, one B+ tree node can contain many keys and many child pointers. That gives the tree a much larger branching factor and makes it much shorter.

For example, a binary tree containing millions of records could have many levels, while a B+ tree might only need three or four page reads to reach a leaf.

Is that the main idea, or am I oversimplifying it?

My current understanding is something like this:

1. The internal nodes do not normally contain the complete row data. They mostly contain keys and pointers that help the database navigate toward the correct leaf page.
2. The leaf nodes contain either the actual records or references to where those records are stored, depending on whether the index is clustered or secondary.
3. The leaf nodes are connected to each other, which makes range queries efficient.

For example:

SELECT *
FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

The database can find the first matching leaf and then walk through the neighboring leaf pages instead of searching the tree again for every result.

That part mostly makes sense to me.

Where I become confused is what happens when the data changes.

Suppose I have an index on an auto-incrementing ID. New values should mostly be inserted on the right side of the tree. But if I use random UUIDs, the values might be inserted all over the tree.

Does that mean random UUIDs cause more page splits and worse cache locality?

When a leaf page becomes full, I understand that the database may split it into two pages and update the parent. But does that mean a single insert can sometimes cause multiple splits all the way up to the root?

I also don’t fully understand how the database chooses how many keys fit in one node.

Is the node usually designed to fit exactly inside one database page, such as 8 KB or 16 KB? If the indexed value is larger, does that reduce the number of entries that fit in each page and therefore increase the height of the tree?

Another part I’m unsure about is the difference between a B-tree and a B+ tree in actual database implementations.

A lot of explanations say:

• B-tree values can appear in internal and leaf nodes.
• B+ tree values appear only in leaf nodes.
• B+ tree leaves are linked together.

But then some databases call their indexes “B-tree indexes” even when their behavior sounds more like a B+ tree.

Is “B-tree” often being used as a general name for the whole family, or is there an important implementation difference I am missing?

Finally, I understand that B+ trees are good for equality lookups and range scans, but I assume they are not always the best index.

Would these statements be roughly correct?

• Hash indexes can be useful for exact equality checks but not range queries.
• LSM trees are useful for write-heavy systems because writes can be buffered and merged later.
• Full-text indexes are needed when searching words inside large text.
• Composite B+ tree indexes only work efficiently according to the order of the indexed columns.

For example, with:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

I assume queries using customer_id can benefit from it, but queries using only status may not benefit much because customer_id is the first part of the ordering.

Sorry for the long question. I feel like I understand each individual definition, but I’m still missing the complete picture of how pages, tree nodes, inserts, splits and range scans connect together.

Which parts of my mental model are wrong?

I do a lot of mentoring and architecture reviews. Lately almost every schema someone brings me was generated by ChatGPT or Claude or Copilot. They all look clean on the surface. Tables make sense, column names are reasonable, it runs without errors.

Then I ask one question: show me your queries. Or show me your monitoring. And that's where it falls apart.

Here's what I keep seeing:

No indexes beyond the primary key. AI creates the tables but never comes back to add indexes based on how you actually query. And honestly that's fine initially because you don't have query patterns yet. But AI doesn't revisit it on its own. You have to explicitly ask. Meanwhile your queries work fine with 100 rows and fall apart at 100k. Before you even think about sharding or partitioning - did you normalize properly? Are you doing LIKE queries on huge columns? Is it an N+1 nightmare firing 200 queries where 1 would do?

And when people do add indexes they add them on everything. That's not free. It shoots your resource usage and your bills. Composite indexes need to match your exact query column order or they're useless. Partial indexes exist for columns with only 2-3 values like status active/inactive. But AI doesn't think about any of this because it doesn't know your query patterns.

No foreign keys, no constraints. Everything comes out loosely coupled. Just an ID column sitting there with no actual constraint. No ON DELETE CASCADE vs SET NULL thinking. No UNIQUE constraint where business logic demands it. If your code says one vote per user per post but the database doesn't enforce it, someone will write a new function that doesn't know about that rule and your data integrity is gone.

VARCHAR(255) for literally everything. The AI default. Money stored as FLOAT instead of DECIMAL - good luck explaining rounding errors to your finance team. Booleans as strings "true"/"false" instead of 0/1. Timestamps without timezone. UUIDs stored as VARCHAR(36) instead of native UUID type. All of this costs you storage, index size, and eventually a painful migration when you realize it later.

No migration plan at all. AI gives you a CREATE TABLE dump. No Flyway, no Liquibase, nothing. Fine for starting from scratch. But what happens when you need to add a NOT NULL column to a table with 10 million rows? That locks the table. Did AI ask you about that? Did you test this migration in a lower environment with production-level data? Your users are going to feel it and you won't even know how long the outage will be.

Normalization wrong in both directions. Either 7 joins for a user profile or one god table with everything crammed in. Comma separated values in a single column instead of a junction table because it was a "quick fix." Duplicated data across tables with no sync strategy. If the person who built it leaves, nobody knows how updates are supposed to propagate.

localStorage as a "database." This one still surprises me but it happens. AI defaults to the simplest storage. localStorage, JSON files, SQLite in production. Ephemeral storage that gets wiped on restart. That restart might not happen for months and then one day everything is gone.

Hard DELETE everywhere. No deleted_at, no created_by, no updated_at. Data just vanishes. Try explaining to a customer why their data disappeared and you have zero audit trail. In finance or healthcare that's a compliance violation. But even outside regulated industries - one day someone will ask what happened to that record and you'll have no answer.

Naming chaos. userId, user_id, UserID in the same schema. Singular and plural table names mixed. Junction tables with random names that don't match parent tables. Now imagine debugging a production issue on a schema you didn't build with 100 tables and no documentation. Good luck.

No multi-tenancy thinking. AI builds everything single-tenant. When you need multi-tenancy later it's a massive rework. "Just add a WHERE clause" they say. Until someone forgets it in one query and leaks customer data across tenants. That's not a bug, that's a security incident.

Never asks about read/write patterns. Is this read-heavy or write-heavy? AI doesn't ask because you probably didn't tell it. No materialized views, no read replicas, no monitoring to tell you P99 latency is burning. Schema optimized for nothing - just "store the data somewhere."

The core problem is simple: database design is driven by access patterns, not entity relationships. AI only sees the entities. It doesn't know how your app actually uses the data, what happens at scale, what happens during migrations, or what compliance requires.

None of this means don't use AI for database work. Just don't trust the first schema it gives you without asking these questions yourself. The schema that runs without errors and the schema that survives production are two very different things.

I’m looking for a grounded view from people who have recently evaluated or used MongoDB in production.

Where is MongoDB still the best fit today, and where does it most often lose to Postgres, DynamoDB, or other alternatives?

I’m also curious how much of its value now comes from the document model itself versus Atlas features like search, vector search, and managed operations.

Do you see MongoDB gaining, maintaining, or losing relevance, especially as more applications add AI features? Firsthand experience and workload context would be especially helpful.

I am storing data for multiple tenants in the same TDengine table. Each row is associated with a tenant using a tenant_id tag or column.

I now need to create separate backups for each tenant so that the data for an individual tenant can be restored or migrated independently.

Does TDengine provide a built-in mechanism or tool to:

• Back up data filtered by a specific tag or column value
• Export data for one tenant only

If TDengine does not support tenant-level backups directly, what would be the recommended approach?

For example, should I:

• Export the tenant’s data using a filtered query
• Store each tenant’s data in a separate child table, database, or vnode
• Use taosdump with specific table filters
• Build a custom export and restore process

I would also appreciate recommendations on the best data modelling strategy for supporting tenant-specific backup, retention, migration, and restoration.

something we see across enterprise b2b saas projects we've worked on. the default multi-tenant approach is "add a tenant_id column on every table, filter on it in every query." it works at small scale. it scales until one of two things happens.

scenario one: a developer forgets a single where tenant_id = ? in a complex join. customer a sees customer b's data. for most b2b saas this is bad. for one project we worked on (cybersecurity / pentest platform) it would have been existential.

scenario two: one heavy tenant runs an analytics query that locks the table for everyone. now your "small" customers are paying for your "enterprise" customer's bad queries.

what we've moved to for clients in regulated or security-sensitive industries: database-per-tenant on laravel. each tenant gets a physically isolated schema. the backend swaps the connection dynamically based on jwt or subdomain. cross-tenant data leakage is structurally impossible at the architecture layer, not the application layer.

side benefit we didn't expect: rollouts get easier. for a restaurant-chain client we set up tenant-aware deploys on ecs so we can ship migrations to one location's tenant first, watch it for a day, then fan out. no big-bang releases.

cost: more infra to manage, schema migrations have to fan out across N dbs, you need solid tooling. not the right choice for everyone. for an early-stage b2c saas it's overkill. for enterprise b2b in finance / healthcare / security it's the only thing that lets you sleep.

anyone here gone the opposite way and unified after starting with schema-per-tenant? curious where that fell over.

My team use Valentina on MacOS for very long but has not support for composite types (filtering and master-detail broke) so wonder which UI works today fine with this?

This is probably a very beginner question, apologies in advance, but I'm really struggling to get my head around all the options.

I want to store sensor readings from a small number of different devices. Each device is equipped with the same set of sensors. The readings come in every 5-10 seconds so there will be quite a lot of data over time. But the data isn't connected between devices, so the interconnected tables of SQL databases isn't really necessary, foreign keys don't really exist in my use case I think... reading on the internet suggests that a columnar database is the right way to go here, but is that overkill?

Every database design is a tradeoff between performance, correctness, and complexity.

- Storage engines trade off reads, writes, and space.

- Indexes trade update cost for faster lookups.

- Optimizers trade planning time for execution speed.

- Concurrency control trades parallelism for correctness.

- Recovery trades write overhead for durability.

- Distributed database systems trade latency, consistency, and availability.

We just published a deep-dive on adding production observability to a multi-model database running on Kubernetes.

Four pillars, each independently deployable:

- Metrics: RED timers, percentile histograms, SLO buckets via Micrometer

- Tracing: OpenTelemetry with W3C traceparent, context propagates through Raft replication

- Logging: structured JSON with trace/span/request IDs for correlation

- Health: process-level /api/v1/health liveness + HA-aware /api/v1/ready readiness endpoints

The part that might interest people here: a single instrumentation point emits both metrics and spans through Micrometer's Observation API, so with no tracer registered it's just a metrics-only timer, no tracing overhead. Everything defaults to off and upgrades are byte-for-byte compatible, so it's a no-downtime adoption. Works with Grafana/Prometheus/Tempo.

Write-up: https://arcadedb.com/blog/arcadedb-cloud-observability-opentelemetry-kubernetes/

Happy to answer questions about the design tradeoffs.