HN user
One peice of tooling I've wanted for Vitess is for it to make recommendations about sharding keys based on the statistics it collects + query patterns. Seems like it's in the perfect location to collect the data for that and remove the mental overhead associated w sharding. If much of the implementation has to be rebuilt, is there a path for that in this implementation?
how are you thinking the differences between mysql and postgres will impact multigres vs vitess? The ones the jump to mind are
- Postgres has transactional DDL vs auto-commit. Can that property be retained?
- Postgres leans into extensions much more that Mysql. Will types/indexes/etc introduced by e.g. pg_vector or postgis, have a path for support?
anything similar that will be a challenge?
OrioleDB maintains a very small set of Postgres patches that are targeted for upstream. The storage engine that mitigates the need for vacuuming [1] is implemented in a standard Postgres extension, so that will still need to be installed by the Postgres host in order to take advantage of it.
But yeah, looking forward to that day too!
[1] https://www.orioledb.com/blog/no-more-vacuum-in-postgresql
I don't want to hijack Datadogs+Quickwit's post comment section with unrelated promotional-looking info. Quick summary below but if you have any other questions pls tag olirice in a Supabase GH discussion.
The OrioleDB storage engine for postgres is a drop-in replacement for the default heap method. Its takes advantage of modern hardware (e.g. SSDs) and cloud infrastructure. The most basic benefit is that throughput at scale is > 5x higher than heap [1], but it also is architected for a bunch of other cool stuff [2]. copy-on-write unblocks branching. row-level-WAL enables an S3 backend and scale-to-zero compute. The combination of those two makes it a suitable target for multi-master.
So yes, given that it could greatly improve performance on the platform, it is a goal to release in Supabase's primary image once everything is buttoned up. Note that an OrioleDB release doesn't take away any of your existing options. Its implemented as an extension so users would be able to optionally create all heap tables, all orioledb tables, or a mix of both.
(disclaimer: supabase employee)
OrioleDB continues to be a fully open source and liberally licensed. We're working with the OrioleDB team to provide an initial distribution channel so they can focus on the storage engine vs hosting + providing lots of user feedback/bug reports. Our shared goal is to advance OrioleDB until it becomes the go-to storage engine for Postgres, both on Supabase and everywhere else.
Happy to hear any concerns you have
yeah, fair shout `columnar index` for hybrid analytical workloads and `multi-master` roadmap items haven't been put in an order on the schedule yet so maybe multi-master makes sense to do first. Both align with webscale requirements though. Columnar would move into the AlloyDB space and multi-master would be more like PlanetScale (although not quite the same audience in the latter case)
what's your use-case for multi-master that would not be supported by something like regional routed for read-replicas with high availability? i.e. do you have a specific need for low global write latency, or is it motivated by something else?
At Supabase we also recently switched to Nix for packaging our Postgres+extensions bundle
In Studio you'd need to click on a query to get the recommendations but the most important queries to optimize are on several metrics like "Most time consuming", "Most frequent", "Slowest execution" are sorted worst-to-best in tab views.
But if you do want to scan all of your queries at once for possible indexes you can do it in SQL with
```sql
create extension index_advisor cascade schema extensions;
select ia., pss.query
from pg_stat_statements pss, lateral( select from extensions.index_advisor(pss.query)) ia
order by (total_cost_after::numeric - total_cost_before::numeric)
limit 100;
```
I'm concerned about getting locked in
Supabase is one of the most portable platforms out there.
The whole stack is self-hostable and open source. All of the data are contained in Postgres. You're one pg_dump away from being able to switch to a different Postgres host. Or if you're switching to something else entirely, you can export the data to CSVs and take it anywhere. But we're confident you won't want to :)
disclaimer - Supabase employee
What additional tooling do you think would be needed to turn this concept into a maintainable stack with good UX for mid-to-large sized applications?
hey, author here (although not of this feature)
Exposing postgres functions through the GraphQL API has been one of our most frequent requests. I'm really looking forward to seeing what people are able to build with this flexibility. The coolest one I've come across so far is a full text search entrypoint on the Query object.
Happy to answer any questions
hi, author here & happy to answer any questions
Really looking forward to the next steps with vecs and would love to hear any thoughts about the `future ideas`.
Mainly, what kind of interface would you like to see for adapters/transforms so collections can feel like you're inserting text/images/video/whatever rather than passing vectors around?
PostGIS is a native extension mostly written in C. Since there is no C trusted language it would be hard to fit PostGIS into the TLE paradigm
Its less of a problem for well known and trusted extensions like PostGIS though because it comes pre-installed on most hosted providers (Supabase, RDS, etc)
yeah, exactly. pgxn also houses native extensions, some of which may be TLE compatible. database.dev is trusted language extension specific
We've reached out to David, who runs pgxn to see how we might collaborate and allow pgxn to be used as another registry that users can install TLEs from
RDS does have the underlying native extension that is required to install user defined packages (pg_tle) from database.dev. It doesn't currently have support for the extension that is necessary to make the in-database client work to install packages directly from the registry.
That's great feedback though: We'll add a new page to the website for each package that provides the snippet you can run on an RDS instance to load the extension and then run `create extension`.
We're also in talks with RDS team and experimenting with a few options that would allow users to query the registry directly. The most promising one we have found so far is using AWS Lambda with RDS's aws_lambda extension to do that http part, and pass the contents back to the database. Still a WIP though!
index_advisor is a project that almost made the cut for LW7. We decided to launch it as a pgtle instead to get some feedback before we (spoiler) probably release it in LW8
It uses the hypopg package to very quickly generate an index for each column referenced in the query and then re-run's an explain plan to measure the estimated "total cost".
If the expected "total_cost" reduces, we add the indexes to the list of recommendations and continue testing the remaining columns.
Its currently limited to single column indexes only but in a later update we'll enhance that to do more comprehensive searches with multiple columns in a single index
disclaimer: author of supabase/pg_graphql
What should AWS do -> provide Postgres or similar as a database choice
If you're interested a postgres option you could check out Supabase GraphQL[1] which is based on pg_graphql [2], a native PostgreSQL extension.
You define your schema in SQL (including any indexes you'd like) and it reflects a full GraphQL API. With that stack, the example you gave about filtering for security would use be solved using a Row Level Security [3] policy where the work all occurs on the DB.
While not a part of pg_graphql, using Supabase as a backend also handles Auth [4] & Storage [5] (both OSS) which covers most of the Amplify bases
[1] https://supabase.com/docs/guides/api#graphql-api-overview [2] https://github.com/supabase/pg_graphql [3] https://www.postgresql.org/docs/current/sql-createpolicy.htm... [5] https://supabase.com/docs/guides/auth [5] https://supabase.com/docs/guides/storage
The 255 max length is something you could consider if untrusted people are able to insert data (through an API for example). A column in postgres can store up to 1 GB of data so its a possible DOS vector. Probably not an issue for most use-cases, just something that was considered in the application those code samples came from
totally agree
""Using composite types is mildly annoying since you must wrap the composite column in parenthesis to access the field"""
^ yeah that is frustrating
--
if you create domains over jsonb frequently, check out pg_jsonschema[1] as a way to express the constraints more concisely
sure, if you switch the sql functions for plpgsql functions you can use procedural logic to on each segment and `raise exception` with a custom error message that will get returned to the client
IIRC, hstore pre-dated jsonb. I'm not aware of any performance differences for the object type but it would be worth checking if you have a heavy workload. jsonb will tend to have better ORM support and it also isn't behind an extension which is a (small) perk
while you always want to be careful with database load, that generally wouldn't be a concern for user defined types. In the example case, operating on the composite type would be more efficient than the string alternative for the most common operations (sorting, comparing, extracting sub-fields)
One of the restrictions of composite types is that they can not contain an instance of themselves. So unfortunately, this is not currently possible.
I had this issue when trying to implement an AST type for pg_graphql[1] back when it was written in SQL [2]. In the end we used a JSON type which was much less constrained. That might be solvable using pg_jsonschema [3] if you really wanted to have a good time though
[1] https://github.com/supabase/pg_graphql
[2] https://github.com/supabase/pg_graphql/blob/34cc266da972d356...
Agreed that there should be a data integrity or custom operation/filter/sorting requirement to motivate a user defined type
I think there are a few things:
1) Its only been a few years since the trend of testing on sqlite locally and running postgres in production went away in favor of postgres everywhere (probably thanks to docker). That prevented using any feature that wasn't equally supported by both.
2) There's definitely a knowledge gap, and not just among developers. The features are most useful for building rich applications, so even DBAs didn't have much incentive to use them prior to tools like supabase using the database as the data model source-of-truth.
3) Companies are increasingly deciding that data is their competitive advantage and interest in data integrity is growing. Database constraints are unparalleled at that because they can't be sidestepped
Definitely. For the use-cases shown in the blog post, a table would be equally valid (and easier). The heuristic I use is to create custom types when the values will need to be used by SQL functions, or if support for things like ordering/aggregating are important to the application, and the rules to support those things require custom logic.
For example, SemVer requires that two version that differ only in metadata e.g. `1.0.0+metaA` and `1.0.0+metaB` should be considered equal. It would be error prone and tedious to push that logic onto every query that wanted to sort the table, but with a type we can define the logic once and have it work everywhere.
hi, author here happy to answer any questions
another approach that works great is to use `create type`[1] with an `input_function` and `output_function` to build on top of an existing scalar type. For example, using that method would allow semver to be persisted as a string natively. The only downside to that is you have to be superuser.
[1] https://www.postgresql.org/docs/current/sql-createtype.html
the jsonschema crate is spec compliant and has full implementations for the required parts of drafts 4,6, and 7
pg_jsonschema matches that support with the caveat that it doesn't support loading documents over http since that would be risky behavior inside a database
disclaimer: I'm the author of the pg_jsonschema
On a sidenote, is not the wrappers for Airtable, BigQuery and ClickHouse opensourced? Or why did they skip that column in the second table?
All of the wrappers are open source. You can see the source for the Airtable, BigQuery, and ClickHouse wrappers here https://github.com/supabase/wrappers/tree/5fac8afb62e6e8362b...
The `self-hosted` column is only missing from the "under development" wrappers in the blog post's table because those are not production ready and shouldn't be self hosted (yet).
Is all of supabase opensourced now?
Yep! The whole stack is open source and can be self hosted