Are there equivalents to SqlServer .NDF and .LDF files in all Database Managers?

The location depends on the operating system and, within the operating system, the location of the data also depends on the engine. If you follow an installation wizard, it doesn't matter where you choose to put your data, because the configuration file will tell you how to access it.

The internal file structure is different for each engine. As you have said, the internal structure of SQL Server consists of keeping the tables (and triggers, and functions) in a .MDF(which is the primary data file), a .NDF(secondary data file) and .LDF(log data file).

That said, I am going to tell you a little about how the physical files of the engines I have worked with are structured. This is not a complete answer and no one answer to this question is going to be correct , but if more people answer about engines they know about , the set of answers as a whole should be supplemental information.

in mysql

(which in linux runs in /var/lib/mysql)

• the concept of "database" is the same as the concept of schema in other engines. There is not, as in other engines, a hierarchy where a database contains N schemas.
• each schema is represented by a directory, and within that directory there are N files.
• When using the MyISAM engine, for each table there are three files (one for structure, one for data, and one for indexes).
• When using the InnoDB engine, there are two ways to store data.
  • If you use the setting innodb_file_per_tableit behaves like MyISAMkeeping a file .frmand one .idbfor each table.
  • Without that setting, which didn't even exist before, each schema maintains a collection of ibdatanumbered files that are actually a single file separated into volumes. And inside ibdata are all the InnoDB tables and their attributes.

MySQL supports the use of tablespaces by indicating the DATA DIRECTORY attribute when creating the table .

CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/alternative/directory';

Using this configuration causes, below, that within the indicated directory a tree is created that replicates the structure of subdirectories (one per schema) of the main MySQL data directory, but containing only the tables that have been created in that tablespace.

Within the main directory, in the subdirectory of the respective schema, MySQL creates symbolic links for each table that resides in another tablespace.

Tables that are created in one tablespace cannot be moved to another with a simple ALTER TABLE. You have to go to the trouble of recreating the table by copying the contents.

in postgreSQL

(which in linux is hosted in /var/lib/postgresql), the structure is a bit more complex. The configuration file allows you to put the data wherever you want, and internally creates symbolic links where appropriate. Basically, there is a subdirectory basecontaining a subdirectory for each database on the server (a server can run N databases).

- PG_DATA
  - base
    - base1
    - base2
    - base3

But also, in postgres, each tablespaceis mapped to a physical directory on the machine, so you can have different tablespaces on different physical disks, different partitions, different file systems. To keep this under control, there is also a subdirectory pg_tblspcwith symbolic links to each tablespace.

Given a database and a tablespace, the tables themselves are files of the form

base/12345/6789

Where baseis the subdirectory that I indicated before, 12345is the one oidof the database, and 6789is the one oidof the table. All this amounts to saying that looking at the postgres installation directory you have no way of knowing how the hell the base is organized or what it contains.

Anyway, just for reference, if you wanted to know which file a table corresponds to, you can find that out by doing:

select pg_relation_filepath('mitabla');

And that is used in the rare case that there is a physical problem that requires you to operate directly with the file.

Postgres tablespaces are entities such as tables, indexes, schemas, etc. The only difference is that they represent a physical location on the file system. Entities such as indexes and tables can be moved freely from one tablespace to another as you want to tune performance or run out of space.

in SQLite

In SQLite, a single file is self-contained, containing the logic, engine, and database all in one. The idea is that in simple applications (such as a mobile app) you can have a local and self-contained database without calling any service or system program. Everything works in a single file and that file does not have a defined extension. By convention, the extension is used .sqliteso that someone who finds it in an application knows what that file is.

SQLite does not support tablespaces. Since it's self-contained in a file, it wouldn't make sense to break that integrity by creating more files.

In MongoDB

MongoDB is installed in /var/lib/mongodband its most basic structure is to keep all the databases in a single file, but everything is configurable.

In MongoDB, similar to MySQL, there is no separate hierarchy for database and schema. Collections reside in a database and that's all the hierarchy there is.

• The directory that hosts the data is in the configuration file and you can put it wherever you want
• The data file can be divided into X volumes of Y size, which are added over time.
• It can be configured so that each database occupies a different subdirectory
• Inside that subdirectory, again depending on your configuration, there is a monolithic file or N volumes

This layout, which was the only one existing until MongoDB 2.x, had the problem that when you drop a table or index you don't get the disk space back until you optimize the entire database. As this engine is offered to host massive collections of documents, optimizing a database and rebuilding indexes can take hours, and the wasted space can be massive.

Since version 3, the WiredTiger engine was incorporated into MongoDB, which has a more classic structure

• Each database is a subdirectory
• Each collection of each database is equivalent to a file in its subdirectory
• Each index of each collection in a database is equivalent to a file in the same subdirectory

The above means that if a collection has 5 indexes, then it generates 6 files in its subdirectory. All of these files have the extension .wt.

MongoDB does not support the concept of a tablespace. Its storage documentation is limited to listing the storage engines.

However, it is always possible to emulate a tablespace by mounting a subdirectory on another drive (on linux this is trivial).

in oracle

Unfortunately I have never installed Oracle locally. I've only used it as a client while the DBA kept the installation closely guarded on a rack of blade servers running Solaris. They were different times.

TLDRs; (Ultra short version)

• Almost all database engines allow you to choose which subdirectory the data will go in, regardless of where the DB service executables run.
• Many engines allow you to choose between keeping monolithic files, separating each database or schema into a subdirectory, and in that subdirectory separating each table into its own file(s).

The grace of keeping different databases or different schemas in different subdirectories is that it becomes very simple to mount each subdirectory on separate disks, thus multiplying the physical transfer speed of a query.