5 ms·

Is this advocating the use of production data in dev/test/qa environments? I am struggling to come up with scenarios where that would be a good idea.

by jaxn 5y ago

akartasov 5y ago
I think it will work well for verifying DB changes and optimizing SQL on real data. This seems like a great tool to eliminate the risk of database-related downtime.
- nicoburns 5y ago
  It is, but it introduces new risks. Like your test code emailing all of your production users. And more generally data security issues.
  - akartasov 5y ago
    You are absolutely right. Depending on the current environment, you should think about the protection of sensitive data (for example, data masking and obfuscation, access control). Database Lab Engine provides some possible solutions to protect sensitive data: https://postgres.ai/docs/database-lab/masking https://postgres.ai/docs/database-lab/masking By the way, the Database Lab Engine maintainer is here.
pella 5y ago
You can clone the "depersonalized" data for your developer teammates in a team: 10 developers working on 32 issues: --> need at least 32 separated environments.
_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.
  - 5y ago
glogla 5y ago
Not sure about webapps but in analytics, this is a common practice. Real data has too many exceptions and edge cases. If you dont develop and test with real data, you will end up effectively developing or testing on production.
- akartasov 5y ago
  Absolutely agreed. In addition, I know some Data Scientists who use thin clones from Database Lab to get data at a specific time for their models
JAequityH 5y ago
I generate "backtests" of a stock trading model, IE I see how it would have done historically. Each backtest reads lots of vendor data from Postgres, and writes some output to Postgres. I can't run a backtest on a "dev" version of the vendor data, I need all of it to make my results correct. The vendor data is too big to easily have two copies. To make this more complex, it's not a simple process where I read vendor data then generate analysis. My model is generated in several steps, which involves reading vendor data, generating output, then combining the output I generated in a previous step with more vendor data to generate the next step of output. Backtests are "prod-like" in that they must use real vendor data and must generate correct results that drive business decisions. But they are "dev-like" since I don't want my backtest to interfere with my production system, which generates the version of the model that I currently trade. For a backtest I might want to make a new database table, or change the behavior of an existing process that generates data. I've tried 2 solutions to this: One is have classes to handle all data reads and writes, and configuration that causes functions to read or write from a dev or production DB as needed. This is a pain to set up, but works well. The other solution is DB clones. One advantage of DB clones is that it lets me write SQL that joins data I've generated on vendor data. I don't love having business logic in SQL for the obvious reasons, but it can be very performant and easy to maintain. Using classes for data access means that I can't easily do a SQL join between vendor data (which is always on the production DB) and data I generate (which might be on the dev DB.)

Is this advocating the use of production data in dev/test/qa environments? I am struggling to come up with scenarios where that would be a good idea.

by jaxn 5y ago

Is this advocating the use of production data in dev/test/qa environments? I am struggling to come up with scenarios where that would be a good idea.

akartasov 5y ago

I think it will work well for verifying DB changes and optimizing SQL on real data. This seems like a great tool to eliminate the risk of database-related downtime.

nicoburns 5y ago
It is, but it introduces new risks. Like your test code emailing all of your production users. And more generally data security issues.
- akartasov 5y ago
  You are absolutely right. Depending on the current environment, you should think about the protection of sensitive data (for example, data masking and obfuscation, access control). Database Lab Engine provides some possible solutions to protect sensitive data: https://postgres.ai/docs/database-lab/masking https://postgres.ai/docs/database-lab/masking By the way, the Database Lab Engine maintainer is here.

pella 5y ago

You can clone the "depersonalized" data for your developer teammates in a team: 10 developers working on 32 issues: --> need at least 32 separated environments.

_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.
- 5y ago

glogla 5y ago

Not sure about webapps but in analytics, this is a common practice. Real data has too many exceptions and edge cases. If you dont develop and test with real data, you will end up effectively developing or testing on production.

akartasov 5y ago
Absolutely agreed. In addition, I know some Data Scientists who use thin clones from Database Lab to get data at a specific time for their models

JAequityH 5y ago

I generate "backtests" of a stock trading model, IE I see how it would have done historically. Each backtest reads lots of vendor data from Postgres, and writes some output to Postgres. I can't run a backtest on a "dev" version of the vendor data, I need all of it to make my results correct. The vendor data is too big to easily have two copies. To make this more complex, it's not a simple process where I read vendor data then generate analysis. My model is generated in several steps, which involves reading vendor data, generating output, then combining the output I generated in a previous step with more vendor data to generate the next step of output. Backtests are "prod-like" in that they must use real vendor data and must generate correct results that drive business decisions. But they are "dev-like" since I don't want my backtest to interfere with my production system, which generates the version of the model that I currently trade. For a backtest I might want to make a new database table, or change the behavior of an existing process that generates data. I've tried 2 solutions to this: One is have classes to handle all data reads and writes, and configuration that causes functions to read or write from a dev or production DB as needed. This is a pain to set up, but works well. The other solution is DB clones. One advantage of DB clones is that it lets me write SQL that joins data I've generated on vendor data. I don't love having business logic in SQL for the obvious reasons, but it can be very performant and easy to maintain. Using classes for data access means that I can't easily do a SQL join between vendor data (which is always on the production DB) and data I generate (which might be on the dev DB.)