HNHacker News
TopNewBestAskShowJobs

oliverrice

528 karma · joined August 15, 2019

submissionscomments
oliverrice··on Supabase Wrappers: A framework for building Postgres foreign data wrappers
> I wonder why this wasn't built on top of, or an enhancement to, the existing (excellent) multicorn[1] project

Have to agree with you there, multicorn is extremely cool. I'm a big sqlalchemy fan so their default SQLA wrapper was a killer feature to give up (although maybe we could do something similar with launchbadge/sqlx[1]). We investigated using multicorn early this year and had a few hiccups. Activity on the original repo[2] quieted way down ~3 years ago. For example, pg14 support hasn't landed and the newest supported python version is EOL in 2022. There is new fork[3] with pg14 support (15 in the pipe) that might pick up in adoption but thats still TBD.

Supabase aims to support new major Postgres versions within 2-3 months so we have to be very careful taking on dependencies that might slow that process.

> I'd love to understand more about the technical rationale that drove this.

Architecturally, multicorn has postgres communicate with a separate python process on the host that does all the hard work. That's convenient, but it can bloat over time and/or be memory hungry for larger result sets. The rust implementation runs in-process and is generally a lot lighter.

Currently I'd say supabase/wrappers is a safer/easier version of the C API vs a direct analog to multicorn. Over time I think we'll see that comparison become more appropriate. There's a lot of excitement around the concept internally and we've already been floating some ideas wrt `auto-mapping` tables for common SQL dialects, a generic JSON HTTP API wrapper, etc. Stay tuned!

[1]https://github.com/launchbadge/sqlx [2]https://github.com/Segfault-Inc/Multicorn [3]https://github.com/pgsql-io/multicorn2

oliverrice··on Supabase Wrappers: A framework for building Postgres foreign data wrappers
> How do you mock it? How do you test it?

Stripe and Firebase both offer test endpoints for their services. The foreign data wrappers for each allow subbing in a user defined endpoint in their `options` so thats how I'd recommend testing the two that have released. Some of the pre-release integrations can be spun up locally in docker e.g. clickhouse. The FDWs for those similarly take an arbitrary connection string making it pretty straightforward to write tests. Here's an example from the repo https://github.com/supabase/wrappers/blob/5fac8afb62e6e8362b...

> Add custom logic?

Views!

> tasks that the database is not designed to do

The C API for foreign data wrappers is baked right into Postgres proper. They've been around since 2013 and are pretty battle hardened at this point. Supabase Wrappers exposes that API in rust so users can write FDWs without worrying about accidentally tanking the Postgres process. Its more about making a great Postgres feature more accessible than tricking the database into doing anything new.

oliverrice··on What is PostgreSQL commitfest and how to contribute
what they're doing is clearly working for them, but have maintainers shown any interest in transitioning the contribution workflow to something more common nowadays like Issues/PRs to make contributing accessible to a wider audience?
oliverrice··on Show HN: Pg_jsonschema – A Postgres extension for JSON validation
thanks for the great work on the jsonschema crate!

I didn't know about the pgx_json_schema (or is_jsonb_valid) and will add both of them to the README today

oliverrice··on Show HN: Pg_jsonschema – A Postgres extension for JSON validation
> 4 different versions that differ in their capabilities

pg_jsonschema is a (very) thin wrapper around https://docs.rs/jsonschema/latest/jsonschema/ it supports versions 4, 6, and 7

> appears to be a conformance test suite; does this carry the same, or was the focus more on speed?

it was mostly an excuse to play with pgx that went surprisingly well. The performance is very good but safety was our main interest.

oliverrice··on Show HN: Pg_jsonschema – A Postgres extension for JSON validation
if you add a check constraint to an existing table only new/updated records are checked. Existing records will not be checked
oliverrice··on Show HN: Pg_jsonschema – A Postgres extension for JSON validation
> fast-fail (the first check constraint violated would bubble the error upward) rather than slow-fail (collect all invalid fields and return the collection of errors, which IME is more common on average web forms or api requests)

... > Has a more ergonomic approach to validation-error collection been developed other than writing a large function to iterate the new record fieldwise against the schema?

Thats an interesting problem that makes sense in the context of form validation.

re: have the ergonomics improved -> no

but you could hack that behavior together like this:

```

create or replace function handle_errors(errors text[])

    returns bool

    language plpgsql
as $$

begin

if errors = array[]::text[] then

  return true;

 end if;

 raise exception using errcode='22000', message=(select string_agg(x, ', ') from unnest(errors) v(x));
end;

$$;

create table account(

id int primary key,

email text,

first_name text

check (

  handle_errors(

   array[]::text[]

   || case when email is null then 'email must not be empty' else null end

   || case when first_name is null then 'first_name must not be empty' else null end

  )

 )
);

insert into account(id, email, first_name)

values (1, null, null);

-- ERROR: email must not be empty, first_name must not be empty

```

not that I'd recommend it :)

oliverrice··on GraphQL is now available on Supabase
Not yet, but we're evaluating options for how we'll be able to support this in the future
oliverrice··on GraphQL is now available on Supabase
Yes, its on the roadmap to implement custom mutations reflected from user defined functions
oliverrice··on GraphQL is now available on Supabase
Thanks!

Yes, we currently support extending table types via functions using a naming convention. The docs for that feature are available here https://supabase.github.io/pg_graphql/computed_fields/#exten...

It's also on the near-term roadmap to implement custom mutations from user defined functions, similar to postgraphile

oliverrice··on GraphQL is now available on Supabase
Yes! We've been exploring https://github.com/tcdi/pgx internally so we'd be able to re-work the hotspots in rust.

We haven't decided to go that route yet but its been incredibly easy to work with & I'm psyched about what options it could open up like:

- Stateless requests (no schema cache)

- Subscriptions (via a background worker)

oliverrice··on GraphQL is now available on Supabase
> How does pg_graphql compare to Postgraphile?

PostGraphile is very close to pg_graphql in goals. It was a big help during development to be able to reference their implementation.

> Did you think about integrating Postgraphile with the Supabase ecosystem..?

For sure! We wrote a blog post about why we chose to implement the reflection engine as a Postgres extension here https://supabase.com/blog/2021/12/03/pg-graphql w/ a direct comparison against Postgraphile and Hasura.

The summary is that we tried out Graphile and Hasura and found that they were both very workable options. We had a few other requirements that led us to the extension instead:

- An extremely minimal memory footprint

- Performance scales with the DB

- 100% security and visibility interop with PostgREST (powers Supabase REST API)

The blog post's "motivation" has some more specifics if you're interested!

oliverrice··on GraphQL is now available on Supabase
pg_graphql and EdgeDB both provide a graph-like query language on top of Postgres. EdgeDB

philosophically (I think) EdgeDB tries to do a more complete job of abstracting over Postgres. It has a custom query language (and GraphQL support via a plugin), and a migration system. Its runs separately from the DB.

In contrast, pg_graphql is more narrowly scoped. It enables you to query your existing Postgres database without caring how your data is structured or having any interactions with migrations. It also runs directly inside the database, so theres no separate process/server to manage.

The EdgeDB folks have a great comparison of GraphQL vs EdgeDB syntax on their site https://www.edgedb.com/docs/graphql/mutations

oliverrice··on GraphQL is now available on Supabase
If you're an existing supabase customer you don't need to change any of your existing permissions.

pg_graphql filters out any tables/columns that aren't accessible to the user making the request. It also respects your row level security policies.

If you can't see something in the introspection schema, you'll get a friendly error if you try to access it. pg_graphql does not used any privileged access so the queries are being executed in a context that is identical to supabase REST

oliverrice··on GraphQL is now available on Supabase
heyo, thanks for the ping

I'm the dev lead on pg_graphql. Happy to answer any questions!

oliverrice··on Postgres Auditing in 150 lines of SQL
Supabase passes all the JWT info to postgrest so it is still available in transaction local config
oliverrice··on Postgres Auditing in 150 lines of SQL
All the data would be available and joining it would be possible with some jsonb gymnastics but its optimized more for compliance style auditing than rapid lookups for complex queries.

For that scenario replicating audit data to a OLAP system may be a better fit

oliverrice··on Postgres Auditing in 150 lines of SQL
if `user_id` is in the table you're auditing you could do

``` alter table audit.record_version add column user_id bigint default (coalesce(record, old_record) ->> 'user_id'); ```

or if you meant the SQL `current_user` you could extend the trigger to track it.

but if the user_id is only available in application space (not in the db) it will not be possible using straight SQL

oliverrice··on Postgres Auditing in 150 lines of SQL
thats a cool solution for reducing storage. nice!
oliverrice··on Postgres Auditing in 150 lines of SQL
> I'm curious if any thought was given to using JSON over JSONB

For sure! There is a conversion penalty that is paid incrementally (at write time). For paying that penalty, jsonb gets you reduced storage size and dramatically faster column value extraction with `->` or `->>` if you end up having to filter the audit table by something other than the `record_id`

It is a tradeoff though and depending on the specifics of your use-case JSON could be a better choice.

oliverrice··on Postgres Auditing in 150 lines of SQL
Being able to see when the primary key changes using `record_id` and `old_record_id` is a perk, but we also wanted to make sure a snapshot of the existing data was available when auditing is enabled on a table that already has data in it. See comment ^ for more color

Long story short: it is avoidable if you're okay with paying the penalty of querying the audit table by `record_id` before every write. We decided to use more disk and keep the higher write throughput

oliverrice··on Postgres Auditing in 150 lines of SQL
> Are you at all worried about the size of the audit table? Keeping a JSON copy of both the old and the new data is going to add up pretty quickly for fast-changing tables.

That's def a valid concern if you're auditing a high frequency insert/update table, or have your entire database under audit. If you do have those needs a system that logs outside of postgres (like pgaudit) would be a better fit.

In my experience most startups and mid-size enterprises sprinkle in auditing around the sensitive parts of the DB, like `account` or `access_control` tables where writes load isn't much of a concern.

> I've built audit systems in the past which track just the new values

Yeah, that solution works great! The one place it breaks down is if you apply auditing to an existing table, and need to be able to recover the records from when auditing was enabled (initial state is not snapshotted)

We toyed with another approach to avoiding having to track old_record by first checking to see if the `record_id` exists in the audit table, and then inserting a row with the OLD values with a `SNAPSHOT` `operation` if it does not.

Even though that query/check was operating on an index, the performance overhead was higher than we were comfortable with.

Storage is pretty cheap these days so we opted to optimize for write throughput + reduced memory usage rather than any considerations wrt disk.

oliverrice··on Postgres Auditing in 150 lines of SQL
author here (of the blog post and https://github.com/supabase/supa_audit)

happy to answer any questions

oliverrice··on Pg_GraphQL: A GraphQL Extension for PostgreSQL
Like Timescale (below), I'm not aware of anything in these implementations that would be incompatible with pg_graphql, but it has not been tested

As long as pg_catalog schema is visible & the base postgres version is >= 13 it should be good-to-go

oliverrice··on Pg_GraphQL: A GraphQL Extension for PostgreSQL
There have been a few discussions about offline-sync support for the postgREST clients but nothing concrete to announce yet

On the graphql side, we'll be targeting making mutations offline capable but are still in the brainstorming phase. If you have any suggestions for how you think it could work please open an issue on supabase/pg_graphql and we can discuss over there

oliverrice··on Pg_GraphQL: A GraphQL Extension for PostgreSQL
yep!
oliverrice··on Pg_GraphQL: A GraphQL Extension for PostgreSQL
It has not been tested, but I'm not aware of any differences Timescale introduces that would be a compatibility issue.
oliverrice··on Pg_GraphQL: A GraphQL Extension for PostgreSQL
Yes, event triggers are built right into Postgres so they're supported out-of-the-box

Event triggers are the mechanism pg_graphql uses to keep the GraphQL schema up-to-date with the SQL schema

oliverrice··on Pg_GraphQL: A GraphQL Extension for PostgreSQL
Currently, any datatypes that are not in the allow list

https://supabase.github.io/pg_graphql/reflection/#type-conve...

are cast as strings, but prioritizing a JSON conversion if one is available is a great idea that we'll look into

oliverrice··on Pg_GraphQL: A GraphQL Extension for PostgreSQL
> What's the relationship between Supabase and Postgrest?

Supabase uses PostgREST for its automatic/reflected REST API

> Supabase is just an user. Is that correct?

yep!

> Does Supabase employs engineers dedicated to it?

https://supabase.com/blog/2020/06/15/supabase-steve-chavez

← PreviousPage 2 of 3Next →