Postgres.ai Blog

I've used SQLite with HashBackup for 13 years and it's been awesome. A lot of the stories I read about "data corruption" with SQLite are, IMO, really about faulty applications that do database commits with the data in an inconsistent state from the application point of view. I've done it myself - it's an easy sin.

I've migrated database versions 35 times over the last 13 years, ie, every individual HB database has been migrated 35 times. You don't always need to make a new table, do a copy, and switch over. In the latest migration, I added new columns, initialized them, dropped columns, etc. without doing a db copy.

For this migration I wanted to switch to strict tables, where typing is strict. I could have done this by just altering the schema (it's just a bunch of text in the SQLite db) and then using SQL to make sure existing columns had the right data (using CAST). But instead, I created a general framework to allow me to migrate data from one schema to another, mainly so I could reorder columns if I wanted. That can't be done with ALTER statements, so I did end up doing a complete copy, but I've done many migrations without a copy.

I found this paper interesting on "zero downtime migrations".

https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries

After reading it, the bottom line is that changes happen within transactions (true for SQLite too), and the key to zero downtime migrations is to use short transactions,…

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

I do host a medium-sized PostgreSQL database, but i would love not to. I need the control you won't have with a cloud offering (you can't install extensions) but i don't want to do all the other steps, and you always have the fear that backups do not work correctly.

And i am not the only one hosting it by my own, many people are using self-hosting PaaS like dokku, flynn or caprover. And all these solutions have a common problem, they all need to reinvent the database as a service layer. What is currently really missing is a good open-source PostgreSQL as a service appliance, something "simple" like Heroku. There have been attempts like Elephant Shed, but they all try to do too much and therefore fail in adoption as they never reach stability. Or people are forced to use complex solutions like patroni which is doing many things, but if something fails you have no clue what to do.

So what is really missing?

1. A simple old-school PostgreSQL vm image
2. Built on a copy-on-write file system to clone production environments like https://postgres.ai/ and Heroku for development reasons which will not really need any storage space.
3. A built-in backup solution like pgBackRest storing the files encrypted in some cloud storage (and restore options!)
4. It does not need a complex ui or inspection/monitoring software, there are many solutions you can run on a different machine.
5. Replication, Auto-Failover etc. are hard just make it a single server which you scale vertically, if…

Joe running in Slack is indeed a way to simplify and speed-up SQL optimization workflow for developers, as it takes seconds to get initial query execution plans and optimization recommendations.

>... With a Slack interface, so you don't need to bother with connection, provisioning, credentials and decommissioning.

It's worth noticing that Joe is a use case of Database Lab (https://gitlab.com/postgres-ai/database-lab) and it's still possible to use Database Lab features like thin-clones provisioning of production-sized databases and fast data state reset with Database Lab client CLI (https://postgres.ai/docs/database-lab/6_cli_reference) for purposes of SQL optimization without Slack integration. If a user has sufficient access to the data they can provision thin-clone with Database Lab client CLI and use psql to work with the clone.

Also, we have plans to add support of recommendations and statistics in CLI and REST API to the support of various messaging platforms in the future.

you could have a look at dblab[1] which (afaik, I've not yet tried using them) has some support for streaming in from a primary source and applying sanitisation functions/transforms

The main value is the use of ZFS snapshots to give you almost-instant (2-3s for a 20G DB on my dev laptop) writeable clone of an import, which you can test your migrations etc against, and then just revert or destroy, which has been extremely helpful for me.

Happy user, no relationship, etc.

[1] https://postgres.ai/products/how-it-works