Re:Thin PostgreSQL Clones
by nextaccountic on Tuesday January 25, 2022 @06:14AM
(#30070559)
> 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-... (disclaimer: no affiliation with them, I've not used their product but it appears to be fully open source)
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.
Re:Thin PostgreSQL Clones
by nextaccountic on Tuesday January 25, 2022 @08:36AM
(#30071608)
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.
Re:Thin PostgreSQL Clones
by heavenlyblue on Tuesday January 25, 2022 @08:19AM
(#30071439)
Do you also store customer personal details and credit cards locally too?
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.
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.
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.