How to organize a database for developers?

Question

Database

Now each of the developers has their own copy of the project, but they work with one combat slave as a database. This is not buzzing because someone inadvertently can delete data or simply hang the database with a heavy query.
But the project base + auxiliary base for 200 GB + sphinx indexes + separate statistics base. There is an idea to raise all this in 1 copy for all developers, but when rolling out some change to master, the development database must also be updated, and this may overwrite someone's current work. Make a slice on 1/10 of the project for example. It is not clear how to share the same indexes or test a client bug that did not get into this developer base and remained only in the combat one.

Answer 1

It is solved thanks to the properties of file systems, such as Copy-on-Write. For example ZFS.
There are also hardware solutions (in fact, software and hardware).
There are also software solutions that work without ZFS, moreover, they are sharpened just for the DBMS. There was an article on Habré about creating database copies for development purposes: www.habrahabr.net/thread/5851
The idea is simple.
1) Make a clone or snapshot of the file system. This is done almost instantly.
2) We set up our copy of the DBMS on the cloned file system.
3) We have 2 versions of the data, that part of the data that is the same (before step 1) is not duplicated, does not take up space.
4) If necessary, refresh the data - kill the clone / snapshot and create it again. This happens almost instantly.
5) You can have multiple independent clones/snapshots at the same time.
You can do the same kind of branching as in Git
. Only merges, unlike Git, are not possible.