Re:Show HN: Mathesar – open-source collaborative UI for Post...
by mathemancer on Thursday March 02, 2023 @08:49PM
(#35004798)
Mathesar dev here.
I like the idea of being able to track multiple data migrations or schema changes in parallel. We do currently support basic point-and-click DDL, and the data gets migrated when you (for example) set up a linked table based on some columns from an initial table, but we don't really have a way to track multiple such requests in one view.
I suppose one general solution would be to provide a UI for `pg_stat_activity` or other similar system tables that lets you filter easily.
Re:Show HN: Mathesar – open-source collaborative UI for Post...
by winrid on Thursday March 02, 2023 @10:42PM
(#35005520)
Mongo's Ops Manager is kind of like this with all its automation (managing machines, shards, index rollouts, etc) and monitoring, but not really the long running query part (there is a profiler though).
It's also not free and breaks in all kinds of fun ways.
Specifically, I've been in the market for a while now for a webapp you can run on a server, that presents a UX for submitting, watching the progress, and managing, long-running schema changes / data migrations (think: index creations, table re-partitionings) that have been written out as long screeds of custom SQL, and then either submitted through said UI, or which are uploaded to the server out-of-band and then pointed at. (And also, support for kicking off + observing + managing native DB-server commands like Postgres's `vacuumdb` or `pg_restore`, and relating them to their DB activity.)
Ideally, the UX would be designed so that you could have many of these jobs running in parallel, without everything descending into incoherence. (I'm picturing: the UI of a torrent client. A table of progress bars + error counts; click in to see the SQL execution log and per-job metrics.)
Ideally also, there'd be a library of templates, where you could generate and fire off N parallel copies of the query with template-variables populated from some combination of running queries and pasting in lists of values; with a configurable max concurrency limit that's for the job-batch rather than global.
Maybe as well, some macro-language for those templates, to let you write "if table_exists(foo), do: [sql], else: [other sql]", or "create_index_if_none_satisfy(table, {colA, colB})", etc — rather than having to look up the unintuitive tables and columns in ANSI-SQL INFORMATION_SCHEMA for the Nth time to write either "compile-time" SQL or sproc SQL to get this effect.
Right now at $JOB we're doing this kind of thing ad-hoc, by writing scripts that take arguments and query the DB to generate directories full of .sql files, that we then run concurrently using shuf(1), split(1), bash for loops, and named shared tmux(1) sessions; and then visualizing progress in various weak ways, e.g. querying `pg_stat_progress_create_index` during index creation.