SAsamokhvalov·Dec 28, 2021
This is great that you can do that. An experimental environment must be of the same size.
Ideally, absolutely the same physically, to keep the same physical layout as on prod – bloat, etc – though, sometimes it's not allowed.
Another problem here is that for each experiment, you might need a new environment. Plus, in larger teams, many people might want to do this work at the same time – so you end up combining efforts, synchronizing them, sharing environments. It slows you down. Or increases your cloud spending a lot.
This is a kind of problem Steve Jobs described in his famous 1980 speech [1] about Apple: if we give each person their own computer, something special happens compared to the case when 10 persons share a single computer. Here it is the same: if we can give each engineer their own full-size DB copy for experiments, something
Read the rest
special happens. They start moving much, much faster. SQL quality improves. DB changes (DDL, massive DML) stop failing. Finally, engineers start learning SQL, it's an eye-opener – they now can see how it works on large volumes (because they didn't have good environments for experiments before!)
This is what we (Postgres.ai) have learned over the last couple of years developing Database Lab Engine [2]. It's an open-source tool for superfast cloning of Postgres databases of any size, based on copy-on-write provided by ZFS or LVM. On a single machine, you can have dozens of full-size clones up and running, conduct a lot of experiments at the same time, and still be paying only for 1 machine. And clone provisioning takes only a few seconds, it feels like magic. But the main magic is how engineering processes change.
One of great use cases is how GitLab uses it for Postgres query optimization and change control [3].
[1] https://youtu.be/0lvMgMrNDlg?t=586
[2] https://github.com/postgres-ai/database-lab-engine
[3] https://postgres.ai/resources/case-studies/gitlab