Thin PostgreSQL Clones • Hacker News Board

Re: Thin PostgreSQL Clones

#11

Post by glogla » Tue, Jan 25, 2022, 11:13 AM UTC

jaxn wrote: post #5

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.

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.

Re: Thin PostgreSQL Clones

#12

Post by nextaccountic » Tue, Jan 25, 2022, 11:14 AM UTC

_xnmw wrote: post #10

jaxn wrote: post #5

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.

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…

> 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)

Re: Thin PostgreSQL Clones

#13

Post by akartasov » Tue, Jan 25, 2022, 11:58 AM UTC

nextaccountic wrote: post #12

_xnmw wrote: post #10

Earlier quoted context omitted.

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…

> 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

#14

Post by grncdr » Tue, Jan 25, 2022, 12:10 PM UTC

This looks like a really useful tool for staging and development environments, and it's even OSS that can be self-hosted! Does anybody here have any real-life experiences with it they could to share?

Re: Thin PostgreSQL Clones

#15

Post by MDcker » Tue, Jan 25, 2022, 12:31 PM UTC

This blueprint about Database Lab from GitLab is quite interesting to understand the use case and what benefits it brings: https://docs.gitlab.com/ee/architecture/blueprints/database_...

Re: Thin PostgreSQL Clones

#16

Post by akartasov » Tue, Jan 25, 2022, 12:31 PM UTC

grncdr wrote: post #14

This looks like a really useful tool for staging and development environments, and it's even OSS that can be self-hosted! Does anybody here have any real-life experiences with it they could to share?

There are several public Case Studies of real users (from small teams to quite large companies): https://postgres.ai/resources/

For more real-life feedback, welcome to the Database Lab Community Slack: https://slack.postgres.ai/

Re: Thin PostgreSQL Clones

#17

Post by hungryforcodes » Tue, Jan 25, 2022, 12:47 PM UTC

vosper wrote: post #2

It’s a tool that clones Postgres databases. It’s not a clone of Postgres the application.

I had that exact idea!

Re: Thin PostgreSQL Clones

#18

Post by akartasov » Tue, Jan 25, 2022, 12:51 PM UTC

glogla wrote: post #11

jaxn wrote: post #5

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.

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.

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

Re: Thin PostgreSQL Clones

#19

Post by krab » Tue, Jan 25, 2022, 1:16 PM UTC

If you're interested how it works (and how to possibly implement it by yourself), I wrote about the idea a few years ago. [1] To Postgres, recovering as a clone looks the same as if someone suddenly unplugged the computer power cable.

The shell script that implements the idea with LVM snapshots (relies on an existing Postgres physical replica) is not too long. It's used over SSH.

    $ wc -l /usr/local/bin/snapshot-*
     164 /usr/local/bin/snapshot-create
      16 /usr/local/bin/snapshot-drop
     180 total

Had this tool existed at the time, I'd have probably used it (monitoring and REST API might be handy). Still, the core idea can be implemented very easily.

[1]: https://www.sedlakovi.org/blog/2019/03/fast-postgres-snapsho...

Re: Thin PostgreSQL Clones

#20

Post by madjam002 » Tue, Jan 25, 2022, 1:17 PM UTC

I'm doing something similar by cloning the data directory using BTRFS snapshots for backups and testing, apart from the UI is there a reason why this tool might be worth looking at?