6 ms·

I have always made clones of production data on my dev machine for testing -- it has the added benefit of also regularly testing my DB backup restoration proces

by _xnmw 5y ago

I have always made clones of production data on my dev machine for testing -- it has the added benefit of also regularly testing my DB backup restoration process. How else do you test and optimize SQL queries that are only slow with production-size data? If the data is too big to fit on my machine, I might clone to a nearby colocated server. Testing your DB backup and restoration mechanism becomes even MORE important if you have huge amounts of data.

nextaccountic 5y ago
> How else do you test and optimize SQL queries that are only slow with production-size data? With something like this https://www.getsynth.com/docs/blog/2021/03/09/postgres-data-gen https://www.getsynth.com/docs/blog/2021/03/09/postgres-data-... (disclaimer: no affiliation with them, I've not used their product but it appears to be fully open source)
- akartasov 5y ago
  I agree that this is a possible way. The main difficulty of the generated data is related to their quality and structure. Namely, how artificial data correspond (quantitatively and qualitatively) to real data. Random data may give incorrect results when optimizing a query.
  - nextaccountic 5y ago
    Yep! The idea of that is to generate data that isn't just random but has the right structure. But, I don't know if they deliver.
heavenlyblue 5y ago
Do you also store customer personal details and credit cards locally too?
- tfigment 5y ago
  Not every database has customer secrets in it. But you are not wrong that it should at least be understood what is sensitive before cloning.
- coredog64 5y ago
  You can buy a hardware appliance that encrypts credit card numbers for database storage. Then if someone accidentally (or maliciously on purpose) copies your database, you are not exposed to PCI liability.
- Salgat 5y ago
  At least with our DB we tokenize protected information, so all you have in the database is a GUID, the actual sensitive information is stored elsewhere.

by _xnmw 5y ago

nextaccountic 5y ago

> How else do you test and optimize SQL queries that are only slow with production-size data? With something like this https://www.getsynth.com/docs/blog/2021/03/09/postgres-data-gen https://www.getsynth.com/docs/blog/2021/03/09/postgres-data-... (disclaimer: no affiliation with them, I've not used their product but it appears to be fully open source)

akartasov 5y ago
I agree that this is a possible way. The main difficulty of the generated data is related to their quality and structure. Namely, how artificial data correspond (quantitatively and qualitatively) to real data. Random data may give incorrect results when optimizing a query.
- nextaccountic 5y ago
  Yep! The idea of that is to generate data that isn't just random but has the right structure. But, I don't know if they deliver.

heavenlyblue 5y ago

Do you also store customer personal details and credit cards locally too?

tfigment 5y ago
Not every database has customer secrets in it. But you are not wrong that it should at least be understood what is sensitive before cloning.
coredog64 5y ago
You can buy a hardware appliance that encrypts credit card numbers for database storage. Then if someone accidentally (or maliciously on purpose) copies your database, you are not exposed to PCI liability.
Salgat 5y ago
At least with our DB we tokenize protected information, so all you have in the database is a GUID, the actual sensitive information is stored elsewhere.