PostgreSQL Storage

[nickdollimount]

It would be really nice to have the ability to use PostgreSQL as a configuration storage option. Allowing all masters to simply connect to a PostgreSQL database to share configuration data would be ideal. I haven't looked at the structure of the SQLite database but I presume it could simply use the same schema.

The access to the PostgreSQL database can be achieved using PG-Promise. I've used this in a personal project of mine and it works great; I've never had any issues with it. Though I wouldn't be able to speak to how well it would scale to a larger application like xyOps.

[jhuckaby]

Just so I understand the feature request, you're talking about configuration storage only? Meaning, the /opt/xyops/conf/config.json file?

I'd hate to add another NPM dependency just to sync the config file, not to mention reliance on a 3rd party database. I actually have a better way planned for a future release, where the backup conductors won't need anything except the secret key, and the current primary will send them an encrypted copy of the config file. In short, no file juggling required.

Would that satisfy this request?

[nickdollimount]

Hey, for the configuration, I'm referring to what the current SQLite database is being used for. Correct me if I'm wrong, the current design for a multi-node configuration would be to store the SQLite files in a cloud storage of choosing from a few options available?

For my use case, we have almost 150 PowerShell scripts that run, performing different tasks, mostly data conversion and enrichment across systems. We currently use PoweShell Universal for this. Our configuration consists of 4 worker nodes that run the jobs themselves and 3 web interface nodes for admin and dashboard access. We have a separate host that hosts the PostgreSQL database that all the nodes connect to. Maybe it's my misunderstanding of how xyOps is actually architected but a quick look at the SQLite database, it appears to store all the events/workflows, etc., as well as the jobs themselves in that database.

If there is more than one conductor server that would need access to this data, this is where the online storage currently comes into play to store this SQLite database and each conductor is configured to connect to it? So my thoughts would be to alternatively allow connection to a PostgreSQL database for those operations (in place of the SQLite database) that can be hosted on-prem, without the need for using cloud storage. I'm also thinking about scaling, comparing SQLite to PostgreSQL; PostgreSQL definitely feels better for large-scale.

As an aside, is my understanding correct in that the worker nodes don't actually need access to this data because the conductor sends all the necessary details directly to the xySat client for an event for it to be able to execute the job?

Thanks for being so responsive! I'll try to get some more reading done to educate myself a little better about how things work.

[jhuckaby]

Ohhhh, okay. I get it now. You said "PostgreSQL as a configuration storage option", so I was totally confused 😝

So, I'm hesitant to add a new external SQL database backend to the system, for a variety of reasons. First, they aren't really designed for key/value pairs (KV), nor are they really designed for binary files. xyOps uses both of these data types heavily.

I know, I know, SQLite is one of the engines, and that's technically a SQL "database", but I consider it an exception because: it runs locally and INSIDE the xyOps process, and it handles a KV table incredibly well. Also, the xyOps stock config doesn't use it solely -- it uses a Hybrid / combination of SQLite for KV, and the Filesystem for files.

And yes, I know of the pg NPM module, and honestly, adding a PostgreSQL storage engine would be trivial from a code standpoint. I'm sure Codex / Claude could vibe one out in a single shot 😝. But the ongoing support and maintenance burden would be significant. I know close to nothing about Postgres databases, and I'd have to have one running 24x7 to test every single new release.

All the engines currently listed I fully support, and test constantly. SQLite, Filesystem, Redis, S3, and S3-compatible (e.g. MinIO). For each of these I have a local configuration setup to test it, and I am confident in shipping xyOps with support for them. Postgres, unfortunately, is not something I know or use, and I am not confident in shipping support for. I'd have to spend six months learning all about it from an operations perspective.

With all that said, you do have a variety of options for external storage. What I recommend is an S3-compatible service like MinIO. It can be installed with a single Docker command, and it handles both KV and files with ease (no hybrid needed). Here is a quick setup guide: https://docs.xyops.io/hosting/external-storage

I am open to expanding the storage engines sometime in the future, but honestly, because xyOps essentially just needs a KV store, and the S3 API is so ubiquitous nowadays (e.g. MinIO, Ceph, Storj, CloudFlare R2, Blackblaze B2, Wasabi, DigitalOcean Spaces, IDrive e2, Linode Object Storage, Vultr Object Storage, OVHcloud Object Storage), that all you really need is the S3 engine with a custom API endpoint.

I hope this makes sense.

[jhuckaby]

I reversed course on my thinking here, and Postgres is now a supported storage engine in xyOps v1.0.45.

New storage doc explains the setup and tradeoffs: https://docs.xyops.io/storage

😊