Thinking about indexing, we know a only a big picture that uses B-Tree structures in memory, but in modern DBMs systems there are some enhancements introduced in indexing where systems like PostgreSQL are utilizing some new OS system calls like io_uring to make syncrhonous IO reading, also direct retrieval of record from disk using functions like fseek() and in memory optimization of traversing like applying binary search on leaf page.

PostgreSQL always keeps a file for indexing, unlike MySQL where it only keeps an indexing file for non-clustered indexing, and clustered indexing is calculated directly from the table, it is interesting to study the indexing file structure as well

Index file structure has line pointers, TID called as tuple ID Tuple is the column value, the same column you registered for indexing, by using which Postgres calculates the actual physical address of the records from the disk to fetch it directly.

Postgres has another interesting feature, while creating an index, it always sorts data and maintains ranges of pages in page 0 of the indexing, where Postgres can identify which page to look for and then load that page into memory, and then it becomes a leaf page, then binary search is performed to get the actual TID so that the record can be fetched directly

For an in-depth explanation about the index file structure, loading the index file into memory, and then searching for the actual record's address, check out the given link

TL;DR: Linux doesn't delete a file that a process still has open. Oracle holds its control file, and every online redo member always opens through it. So for a short, glorious window after that, all of it was still sitting in /proc. We pinned the file descriptors, copied them back byte-for-byte, and opened the database without CREATE CONTROLFILE or RESETLOGS. Zero data loss, 16 minutes down, one entirely new grey hair.

The setup

2-node RAC on an Oracle Database Appliance X9-2 HA. The HA stands for High Availability, which I can now confirm I tested more thoroughly than the vendor intended.

The bit where I ruin my own morning

sudo rm -rf <dbname>/redo. Wrong directory. That directory held one production database's online redo logs, its standby redo logs, and — the good part — its one and only control file.

Both instances were open and serving users at the time. Datafiles were on a different volume and survived, which is the single piece of luck in this entire story.

Yes, we had backups. No, I did not want to be the guy who found out whether they worked.

The ten seconds that actually saved us

Here's the thing nobody thinks of while their stomach is somewhere near the floor: rm deletes a directory entry, not a file. The inode is freed only when the link count reaches zero, and no one has it open. An Oracle instance holds its control file, and every online redo member opens continuously via LGWR.

The files were still there. Intact. Readable through /proc/<lgwr_pid>/fd/.

But it's a melting ice cube — the second LGWR exits, it's gone. Crash, `shutdown ', reboot, anything.

Find the descriptors:

sudo lsof +L1 2>/dev/null | grep '/path/to/redo/dir'

As root, not as oracle. The oracle binary is setuid, which clears the process dumpable flag, so the kernel hands /proc/<pid>/fd/ ownership to root. Being the same user is not enough. Learning this at speed was a treat.

Note the fd numbers on the ora_lgwr_ line. They differ per node. Read them on each node, don't assume.

Pin them with something that isn't the database:

sudo setsid bash -c 'exec 200</proc/<LGWR_PID>/fd/<ctl> \
  201</proc/<LGWR_PID>/fd/<log1> 202</proc/<LGWR_PID>/fd/<log2> \
  203</proc/<LGWR_PID>/fd/<log3> 204</proc/<LGWR_PID>/fd/<log4>; \
  echo $$ > /tmp/inode_holder.pid; while :; do sleep 3600; done' \
  </dev/null >/dev/null 2>&1 &

setsid detaches it from your terminal so it outlives your SSH session. Do it on both nodes.

Twenty minutes later, the instance died on its own — SMON couldn't open the control file, ORA-00210, instance terminated, database down. The pin held. The files were still readable. That one command is the reason this is a war story, not a resume update.

Copying it back

Two rules:

1. Zero database processes running. Copy a control file while CKPT is writing to it, and you get a beautifully torn, completely useless control file. ps -ef | grep -E "ora_[a-z0-9]+_<SID>" | grep -v grep | wc -l → must be 0.

2. Verify exact byte counts with stat, not ls -h. ls -h rounds, and "8.0G" is not a checksum.

   sudo dd if=/proc/<HOLDER_PID>/fd/200 of=<control file path> bs=1M status=none sudo dd if=/proc/<HOLDER_PID>/fd/201 of=<redo group 1 path> bs=8M status=progress

   ...and the rest

   sudo chown oracle:asmadmin <files> sudo chmod 640 <files> stat -c '%s %n' <files>

Then STARTUP MOUNT, check V$LOG — our sequence numbers came back identical to pre-incident — confirm the datafiles are all there and need no recovery, and ALTER DATABASE OPEN. Crash recovery chewed through the restored redo like nothing had happened.

Final proof: ALTER SYSTEM SWITCH LOGFILE and ARCHIVE LOG CURRENT. If ARCn can open redo by path and archive it, you're actually back and not just optimistic.

Standby redo logs were a write-off — nothing held them open — but recreating them is a two-minute j0b and, honestly, felt like a rounding error by that point.

Lessons, some of them expensive

• Do not shut down a database whose files you just deleted. Every instinct screams, "shut it down cleanly before this gets worse." That instinct will delete your data for real. The correct response to a catastrophe here is to do absolutely nothing, very quickly.
• You can't DDL your way out. Once the control file path is gone, every new foreground process dies with ORA-00210. No ALTER DATABASE ADD LOGFILE. RMAN wants a snapshot control file and hits the same wall. A restart is unavoidable — which is precisely why the pin has to come first.
• An abort It is survivable if the redo is pinned. Crash recovery has everything it needs once the files are back where they belong.
• Multiplex your control files across different disk groups. We had exactly one, plus single-member redo groups, all on the same volume. With normal multiplexing, this would have been a boring online repair instead of a Sev 1. That part is entirely on us.
• A standby at 1-second apply lag is a lovely thing to have in your back pocket. Never needed it. Knowing it was there is what made attempting the clever fix feel responsible rather than reckless.

The part I keep thinking about

The highest-value action of the whole incident cost ten seconds and one command. Everything after it — the analysis, the careful copying, the verification — only mattered because someone grabbed those file descriptors before the instance died.

Anyway. Go check whether your control files are multiplexed. Seriously, alt-tab, I'll wait.

I know this is such a basic question but I can't seem to find an answer online, (yes I know a table and a record is different)

If I am to say, I associate this record, or this associated record. Is "that" record the one with a foreign key, or the one which has a foreign key referencing it?

Thanks

Greetings all,

I have been hired to design the data platform for a small company that is expected to expand soon (next 2-3 years).

Now many of their requirements include having some KPIs or metrics that get calculated once the underlying data in the OLTP changes.

As far as I know there are only 2 approaches for this:

1- Create denormalized tables in the OLTP that gets updated whenever the underlying data changes using triggers or maybe a materialized view that refreshes with every transaction.

2- Create a CDC pipeline to avoid overloading my OLTP with lots of writes and I/O. However, this solution costs alot (streaming pipeline + kafka + debezium connector) compared to the previous solution which is basically free.

I want to know your opinions on this and how would you approach it and if there is maybe a 3rd and a better solution?

EDIT:
An example of one of the requirements. Think of a fleet management system so you have a "vehicles" table master data. Now a vehicle can ofcourse have multiple "fuel_transactions". When the driver adds a fuel transaction he also adds the odometer reading (vehicle's total distance covered) and specifies whether he filled the tank or not.

Now there are some KPIs that needs to be calculated based on all of the transactions that occured between 2 full tanks. for example, the vehicle's fuel_consumption (km/Litre) and fuel_cost_per_km.

I hope this is clear enough.

The tables are still relatively small (1000 vehicles so maximum 1000 fuel transactions per day = 1000 rows per day)

Hey so I have a video game modding project that that features an excel spreadsheet that I have to continually change and update each time I create a new version of the mod.

Over time, this spreadsheet has ballooned in size, which dozens of columns now. Current state of the spreadsheet:

• A column for a "key" value
• A few columns for metadata
• Several groups, which have the same 4 columns inside them.

When I only had two groups, everything was easy. But now I have 8, and could conceivably add more. At like 40 columns, this is simply too big for an excel spreadsheet. I realize I could split apart the rows... but then it makes the spreadsheet really annoying to traverse vertically, and there is still some value in being able to sort/filter with it all in 1 row.

What I would like to do is this: Have 1 "main table" that looks the same.

Key	Metadata: Field 1	Metadata: Field N	Group 1: Field 1	Group 1: Field N	Group 2: Field 1	Group 2: Field N
key1
key2
key3
keyN

And each time I select a row, a pivot table is created, which looks like this:

key1	Field 1	Field 2	Field 3	Field 4
Group 1
Group 2
Group 3
Group N

I need both the pivot table and the main table to be editable. I feel like at this point what I'm asking for is basically a simple database... and when it comes to that, creating a back end seems easy enough, but it is the front end that is the problem.

Options I've investigated:

• Still using Excel - Can't seem to get a pivot table that would allow me to edit it, and have the changes propagate back to the main table (and vice versa)
• Microsoft Access - terrible GUI
• NoCo DB - I don't like that it is browser / web-based.
• Building a custom electron front end - I was getting somewhere, but it's so overwhelming. Had to stop because it was consuming my life
• Grist - Doesn't seem to have the functionality I need.

I'm not sure what else to look at, because I'm just a dude working on a community modding project. It doesn't make sense to pump a ton of money into this, and I am allergic to the idea of a subscription license. I don't mind paying a modest fee for a perpeutal license of nice software though.

Do you guys have any recommendations?

I have made good progress teaching myself Microsoft Dataverse, PowerApps, and general database concepts; and have found success mocking up increasingly sophisticated (for me at least) DB mini-architectures. I am now trying to refine a database structure which can track Components for Projects. Example records of this would be:

Project1 has wood structure and copper pipes; Project2 has steel structure and plastic pipes

...where we have tables for Project; Components (eg, structure; piping); and ComponentOptions (eg, steel structure, wood structure, copper pipes, plastic pipes).

An important requirement is to be able to categorize or order these components. For my first pass, I related the Components table to a Subcategory table, which is in turn related to a Category table. This is shown in the first image.

The first model

This first structure works well, but is limited to only one taxonomic/ordering system. I want to be able to create an arbitrary number of ordering systems (for example ordering components by construction code, or by the report section in which they appear, or in a limited checklist for the guy who only does one or two things, or other future needs).

After chatting some with Copilot, I've come up with the structure shown in the second image. Each record in the Taxonomy table represents a separate system of ordering. TaxonomyNode will provide the category/subcategory/subsubcategory structure. A join table will determine where in each taxonomy system that components are located (or may not be present). The ComponentOption, Project, and ProjCompOptionJoin table I think I've got an OK handle on, but I invite input on all of it.

Proposed new model

Does anyone have any suggestions or comments? These are new frontiers for me and I've been trying to get more input lately, lest I reinvent (a worse version of) the wheel.

Thanks!

I was studying a bit on discord and wanted to for fun try to make a copy (Free time during summer so why not, plus free practice) So I was looking at things and a question came up to me, what would be the proper way to handle multiple different types of chats, (No OOP) but in a FP lang, But I think the distinction doesn't really matter

I thought of a few different ways but I would like to know which one would be be best. I am not a sql wizard, just a newb who knows a bit and would like some opinions on design

So imagine we had

- Direct Message

- Groupchat

- Server

There's 2 approaches that come to mind.

1. We have one table called rooms, or chats or whatever ye call it. Within there is a column that describes the TYPE of the chat (DM, Gc, etc) Now depending on the type of the chat there will be different tables that reference it that is to say.

If I create a server there will now be a moderation table, and this moderation table only has relations to chat's with typeof server. So for the applications end, depending on the type of the chat it will be treated differently and different relations may be made for it. But it is still a chat

This doesn't seem too bad to me but maybe there is some caveat that I have not thought of yet, something immediate that comes to mind is maybe handling all these types would be harder, but it can't really be that difficult, you just have different modules (like in FP) that handle the different types of chats. And likewise, there will only be one memberships table and it will be the applications duty to ensure that for example a direct message will only have 2

Hard decoupling strategy, in short. Direct messages have their own table, it's memberships is it's own tables, etc.

Servers has it's own memberships tables, it's own channels tables, it's own roles tables. and on and on and on.

I think the latter might be better for decoupling and make things simpler, but it might introduce a lot of duplicated code, whereas the former has less duplicated code but may be harder to maintain. Can ayone give any opinions? I am not a DB guy and wouldn't really know

Adjoint by stagDB is a service for developers and agents to get access to production-like data that can be reset.

Regular Indexing works on a column level; it indexes the entire column value, but if you want to index each value in one row, then the GIN indexing technique is used.

GIN can index each element of JSON stored in a JSONB field

How Postgres creates a GIN file physically, what is the format of that file, how that file is retrieved in RAM while fetching records, what data types are used, and how Postgres calculates the exact address of an element of JSON are all explained in the article

Every new serverless infra company is launching with Postgres and ONLY Postgres! (Some exceptions are KV stores)

Koyeb (wraps Neon), Render, railway, Vercel, fly.io amongst sooo many others. Nobody’s shipping mysql, mongo, first it’s always pg.

What are your thoughts ? Is it the diversity of use cases of covers? The extensibility?

Hello. I'm sorry if this is a beginner question.

I'm preparing for an exam in database design, and a question about B-trees has me doubting the knowledge I have about them: it says specifically I have to consider a B-tree of height 3 where the root node has double the minimum amount of children a 3-levelled B-tree can have.

The number of children in question I think is 4, since I know the root of a non-empty B-tree can have as little as one key, and thus at minimum as 2 children, however I also know that, during the insertion, before increasing the level of the B-tree we always try to organize elements towards the top of the tree, so that we fill the upper levels first before going down.

That seeded the question in me about whether or not, in any realistic case, we could expect the root of a 3-level B-tree to have 2 as the minimum amount of children, rather than something higher; the more I think about it, the more my answer seems correct, looking through the material I don't see any confirmations or denials other than that rule that the root can have as little as 2 children theoretically, however I keep asking myself the question: "if a B-tree ever reached 3 levels, wouldn't it always be organized so that it contains more than one key? So shouldn't the minimum number of children be higher than 2?", the answer to this seems to be "not necessarily" but I'm very unsure, so just to be extra sure I decided to ask here.

Sorry again if this question has an obvious answer, I am brawling with some serious doubts here and, since due to lack of time (or probably just me being slow) I didn't do well in the written half of the exam (don't even know if I passed it but whatever), I want to be certain of my knowledge for the oral part.

Also probably won't reply to your comments immediately since I'm going to bed; thanks in advance.

Im not sure if this is the right subreddit, but desperatly need help with this. I have a 3.9 Gb .sqlitedb map file, and my ultimate goal is to:

1. Be able to view it;

2. Crop a needed sector of it (for it to be lighter for distribution among my friends);

3. To upload cropped (or entire file) to atak server.

I would prefer changing the format to something more standart like .kml or smth, because i cant find a single app to open .sqlitedb file. Thanks for any help!

I've been working on a scoring approach for PostgreSQL/MariaDB, turning security, integrity, and performance checks into a single score instead of a wall of raw stats.

A few questions for anyone who's gone near this:

• Who's tried this? Building something that scores or grades database health, rather than just reporting raw metrics.
• What difficulties did you run into? For me it was less about the checks themselves and more about the edge cases: missing privileges silently returning empty results instead of errors, pg_stat_statements not enabled and nobody noticing, bloat numbers that looked fine until compared against real autovacuum history.
• Is there even a real interest in a single score for this, or does it inevitably flatten things a DBA would rather see broken out in detail?

Right now, I'm using excel to track multiple BOMs. But it's getting to be too much for simple Excel sheets to do. There's plenty of things I could do if I could just turn my BOMs into databases and use Excel as the UI.

However, I'm wondering if it's better to use something like Python with SQL to create a lightweight database? Or should I use a Microsoft native solution (I'm sure Microsoft probably has a way to create a database out of Excel).

Additionally, I am wondering if I should just go fully into a BOM software? There are some reservations I have about that because some BOMs are part of IP.

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. For those who have been through an ERP implementation, what was the thing that surprised you the most or caused the biggest headache?