Compatibility with Preexisting DBs project feedback
Brent Moran
I've written up a first draft of the project to investigate
compatibility with preexisting databases. Here's the link:
https://wiki.mathesar.org/en/projects/preexisting-postgres
I'd appreciate feedback on it. In particular, it would be good for
Ghislaine, Pavish, and Sean to look at it. Nothing should be too
surprising, but I want to make sure we're all on the same page.
Please use this thread to discuss and give feedback. I think we need
to be pretty quick on this, since we'll want to finalize it by the end
of the week.
Brent
Ghislaine Guerin
Views
- This feature might overlap with Data Explorer.
- We either need a distinct representation from saved explorations or an integrated approach.
Constraints
- How do we determine which database configurations to show in the UI?
- We should find a balance to avoid overwhelming users if we opt to display all database configurations in the UI.
Prioritization
- I agree with prioritizing fixes for features that could lead to errors or data loss, like moving columns.
All else looks good to me.
Kriti Godey
Pavish Kumar Ramani Gopal
Sean Colsen
Looks good, Brent!
I’ll add some thoughts…
You said:
Find realistic sample PostgreSQL databases
I would imagine it being important to do this work soon in the project, maybe first. But it’s a challenging task!
I spent some time pondering and researching this today. Here are some take-away thoughts:
-
The Sample Databases on the PostgreSQL wiki seems to have some useful leads that would be worth chasing down in more detail.
-
Finding open-source self-hosted applications that uses Postgres would be helpful because it could provide a sample database with some interesting structure.
-
Finding real data sets distributed openly would be helpful because it could provide a sample database with some interesting content.
-
Ideally we’d find examples with both interesting structure and interesting content, but this seems a bit tricky.
-
Applications would be especially useful if their installation steps included an option for loading sample data.
We could comb through the awesome-selfhosted list, but it’s enormous and hard to filter for projects that use Postgres. I wonder if there might be similar resources that would get us further toward the sort of filtering we’d need to do to avoid tons of manual research.
-
Data sets would be especially useful if they were distributed as a pg_dump SQL file.
I browsed some open data sites to see if I could find any open data distributed via pg_dump SQL. No luck. This is not really surprising though. If I were using Postgres as the source-of-truth for my data, I’d still want to distribute it in a format with broader compatibility. I did some digging in awesome-public-datasets, kaggle, fivethirtyeight, NYC, data.gov. I don’t imagine that stress-testing Mathesar with such data would prove to be particularly useful because what we need is data that is taking advantage of Postgres-specific features.
This seems like it might be a dead end. I’m just forwarding this thought though in case it saves anyone else some time during research.
Perhaps it’s worth asking the team:
Does anyone have any products in-mind that use Postgres?
Kriti Godey
Sean Colsen
Years ago, I helped build a tool like this for CiviCRM. It’s an extension called anonymize (not part of the core product) but it was useful for some clients who needed to send redacted prod data to vendors building customizations for them. In this case, some clients were willing to do so. Granted, that’s a product-specific tool, so it’s quite clear what needs to be redacted.
A general purpose anonymization tool would carry more risk, and we’d need to clearly communicate that risk. I wouldn’t imagine many Mathesar users being comfortable sending us their prod data, even after redaction. But I think some probably would! I know there are a lot of general purpose tools like this, so we'd need to vet them a bit before recommending any.
