> The number of interviewees (I do the sys design question) who want to jump straight into massively distributed eventually consistent complicated systems for 5 reads/second is too damn high. 1,000,000 users is not a lot.

Not only can you get quite far w/ PG and w/o sharding, but if you run out of write bandwidth you might be able to (depending on what your schema looks like) shard your PGs. For example, suppose you have a ride sharing type app: you might have one DB per market with only user and driver data for that market, then have the apps go to the user's home market for authentication, and to the local market for rides (similarly if a driver ends up driving someone to a different market, you can add them there temporarily).

No worries, it was a really great talk - one of the highlights of the conference for me!

I believe the videos of the talks will be published at some point - I think "a few weeks" was mentioned.

That second trick is neat, I'll need to remember that. I also really wish I had known about it back in 2015, when we had to massage an inconveniently big (at the time) PG database and started to carve it out into smaller, purpose-specific instances.

Being able to verify that an index was either useless or inefficient without jumping through hoops would have saved quite a lot of time.

Sorry, I see this has caused some confusion, I should have clarified in my comment. I was responding to the section by Lao Feng at the end, where their conclusion tries to make the case for Pigsty and self-hosted pg.

I would never say that self-hosting anything is trivial. Linux administration, tuning, and troubleshooting can be learned, obviously, and the same is true of RDBMS. Neither is a trivial skillset if you want to actually be able to run stuff at scale, though. There's a massive difference between 25 QPS and 25,000 QPS (TFA states ~40 replicas, and an aggregate of 1,000,000 QPS).

That shouldn't deter anyone from trying, though. You can't learn if you don't try.

Back in the 2000s, Database Administrator (DBA) was a pretty popular job title, and they usually got paid way more than regular software developers. It probably wouldn’t have been like that if managing databases was "trivial".

Yeah, that's baffling to me too. I started self-hosting my databases with MySQL 21 years ago, before the cloud even existed.

That sounds like chaos engineering with extra steps! Have the AI randomly tweak your DB, and see if your infra holds up? :)

No large scale database heterogeneous cluster is maintenance-free - plans change because data changes, query patterns change, resource utilization of certain features grows or shrinks, new application features are launched, new indexes need to be added, things are constantly migrated and might need a special case because the default migration strategy doesn't perfectly handle giant bulk changes, etc etc etc etc

Doing backups and validating backups is very error-prone and time consuming. To me this reads as: "If you do all the hard complex work yourself, what's making you nervous about it?"

It's far easier to do backups and database hosting at scale. Database failures are rare, so it's this one-off situation that you have to be prepared for. That requires clearly defined processes, clearly defined roles and responsibilities, and most importantly: feedback from unfortunate incidents that you can learn from. All that is very hard to accomplish when you do self-hosting.

Yeah, that's what is a bit odd to me. I feel like AWS and everything like that is much more of a black box compared to some things like Postgres that is so fully tested, proven to be reliable, etc.

I think managing stateless infrastructure is much easier, if anything goes haywire you can expect a readiness probe to fail, k8s quietly takes down the instance, and life continues with no downtime.

It is also perfectly possible to roll your own highly-available Postgres setup, but that requires a whole another set of precise configuration, attention to details, caring about the hardware, occasionally digging into kernel bugs, and so forth that cloud providers happily handle behind the scene. I'm very comfortable with low-level details, but I have never built my own cloud.

I do test my backups, but having to restore anything from backups means something has gone catastrophically wrong, I have downtime, and I probably have lost data. Everything to prevent that scenario is what's making me sweat a little bit

Backups with periodic restore validation (which is not trivial) are a must, but don’t make database maintenance any less nerve wracking.

Sure, you won’t lose data, but the downtime …

How do you prefer to collect backups when self-hosting postgres?

In Spain when you want to travel by high-speed train, you need to though security check, just like at an airport. Do the security checks make sense? No. But nobody wants to be the politician that removes the security checks, and then something bad happens. So the security checks stay.

There's lots of extensions that automatically create audit tables and record history in them. The trick is to a) create audit tables for all _existing_ tables, b) create an event trigger so you can have audit tables created automatically for any future new tables. Here's an example of how to do it: https://github.com/twosigma/postgresql-contrib/blob/master/a...

Another thing you might do is to go with a schema that follows the event shipping pattern. In this pattern you have the "truth" held in insert-only tables (deletes and updates not allowed), then turn those "event" tables into ones you can query naturally using VIEWs, MATERIALIZED VIEWs, or live tables that you update with triggers on the event tables. Then your event tables _are your history/audit_ tables.

    CREATE TABLE public.audit (
        id uuid NOT NULL,
        created_time timestamp without time zone DEFAULT now() NOT NULL,
        schema_name text NOT NULL,
        table_name text NOT NULL,
        record_id uuid NOT NULL,
        user_name text,
        action text NOT NULL,
        old_data jsonb,
        new_data jsonb
    );

    CREATE OR REPLACE FUNCTION audit_if_modified_func() RETURNS TRIGGER AS $body$
    DECLARE
        v_old_data JSONB;
        v_new_data JSONB;
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            v_old_data := to_jsonb(OLD.*);
            v_new_data := to_jsonb(NEW.*);
            IF (TG_TABLE_NAME::TEXT = 'users') THEN
                v_old_data = v_old_data - 'last_login_time';
                v_new_data = v_new_data - 'last_login_time';
            END IF;
            IF (v_old_data <> v_new_data) THEN
                INSERT INTO audit (id,record_id,schema_name,table_name,user_name,action,old_data,new_data)
                    VALUES (uuid_generate_v4(), NEW.id, TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT,
                            session_user::TEXT, substring(TG_OP,1,1), v_old_data, v_new_data);
            END IF;
            RETURN NEW;
        ELSIF (TG_OP = 'DELETE') THEN
            v_old_data := to_jsonb(OLD.*);
            INSERT INTO audit (id, record_id,schema_name,table_name,user_name,action,old_data)
            VALUES (uuid_generate_v4(), OLD.id, TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data);
            RETURN OLD;
        ELSIF (TG_OP = 'INSERT') THEN
            v_new_data := to_jsonb(NEW.*);
            INSERT INTO audit (id, record_id,schema_name,table_name,user_name,action,new_data)
            VALUES (uuid_generate_v4(), NEW.id, TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data);
            RETURN NEW;
        ELSE
            RAISE WARNING '[AUDIT_IF_MODIFIED_FUNC] - Other action occurred: % at %',TG_OP,now();
            RETURN NULL;
        END IF;
    
    EXCEPTION
        WHEN data_exception THEN
            RAISE WARNING '[AUDIT_IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: % SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
        WHEN unique_violation THEN
            RAISE WARNING '[AUDIT_IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: % SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
        WHEN OTHERS THEN
            RAISE WARNING '[AUDIT_IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: % SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
    END;
    $body$
    LANGUAGE plpgsql
    SECURITY DEFINER;
    
    CREATE TRIGGER audit_logger_accounts
        AFTER INSERT OR UPDATE OR DELETE ON accounts
        FOR EACH ROW EXECUTE PROCEDURE audit_if_modified_func();

    CREATE TRIGGER audit_logger_users
        AFTER INSERT OR UPDATE OR DELETE ON users
        FOR EACH ROW EXECUTE PROCEDURE audit_if_modified_func();

:)

Hey, I see you've added a web server. How does it compare to PostgREST?

> EDIT: they have an aggregate of 1 million QPS across ~40 read replicas, so 25K QPS each, modulo writes. I am less impressed.

Yeah that's my point! That's the load per instance that I see at my current company, we just have fewer replicas.

> Assuming your table statistics are decently up to date and representative (which you can check), this basically comes down to knowing your RDBMS, and your data

I'm pretty good at this stuff, and I don't often dabble with complex indexes. And yet I don't have 100% confidence. No-one is perfect: maybe I made a mistake in assessing index equivalence, maybe I forgot to check replicas, maybe there's _something somewhere_ that depends on this index without me being aware of it... It's a destructive operation where the only confidence you can have is _theoretical_, not operational: it's kind of crazy and people have been requesting this feature for years for good reasons. If you get it wrong (and getting it right is not trivial), production is on fire and it's potentially hours of downtime (or days, if it's a massive table!).

For example, RDS forces you to shutdown an instance before deleting it. At this point, if anything was relying on it then alarms go off and you can quickly turn it back on. This should be standard functionality of anything stateful.

I can sympathise with wanting to stay on a single master architecture as long as possible. Sharding is a major step change in terms of maintenance burden.

My guess is that they didn’t design their schema around multi tenancy, and going back to pick apart all the shared tables, rewrite queries etc… will take them more time than they want to spend.

> Also, not all developers can roll their own sqls

If you can't write SQL, don't use an RDBMS until you learn it. This sounds like gatekeeping because it is: I don't understand why so many people are willing to accept that they need to know their main language to use it, but not that they need to know SQL to use it.

Django ORM is great if you want it to manage your entire data model and are OK with living entirely within it's little world. But it can't support even basic stuff like composite primary keys, complex filters in a join clause, or any kind of lateral joins at all. It's also among the biggest offenders at hidden (and explosive) N+1s all over your codebase and templates unless you pay very special care. Also, the SQL it generates is usually very naive and often-times just not even performant.

The talk seems to be mostly about all the limitations and workarounds they've had to deal with, because they choose not to shard. Apparently, they have a policy of adding no new functionality to the database, which presumably means additional separate database services being setup for each new feature. That sounds a lot like accumulating tech debt very rapidly, just because sharding is not on the table, for whatever reason.

> Not sure I would qualify sharding a DB that get 1M qps as straight forward.

Sharding at the application layer (basically figure out the shard from org/user in your application code prior to interacting with the DB), will scale to any QPS rate. This is what I was referring to.

That's true, but that's also why you really should shard long before hitting that point...

If your company is growing at this insane rate, it should be obvious that eventually you must shard. And the longer you delay this, the more painful it will be to accomplish.

I thought OpenAI has an AI that can code.

It’s open source, you make your own fork and move on with or without the community.

> The only feature that Aurora (MySQL) has that is remotely impressive

Aurora is one of the only options if you need low-lag physical replication in a MySQL-compatible environment. That makes it operationally feasible to execute large/heavy writes or DDL which would normally cause too much replication lag on traditional (async binlog-based) MySQL replicas.

Granted, there's some important fine print: long transactions will still block InnoDB purge of old row versions, and in Aurora that's cluster-wide. But in any case, personally I'd consider nearly-lag-free replication to be an important differentiator. This can be leveraged in interesting ways, for example CashApp's `spirit` OSC tool (https://github.com/block/spirit) can do online schema changes blazing-fast because it doesn't need to throttle its write rate to avoid replication lag.

Scale-to-zero is also nice for dev/test environments.

That said, I do agree with your overall point that Aurora was majorly over-marketed. And Amazon's capture of so much revenue in the MySQL space has been extremely detrimental for the MySQL ecosystem, especially considering Aurora's modifications are proprietary/closed-source.

> The only feature that Aurora (MySQL) has that is remotely impressive is its ability to restart the DB process

I dont really care about Aurora MySQL...only Aurora Postgres, but you forgot about Parallel Query and Clones. For clones you dont pay for the extra storage for the new database, only the delta if you add new data...

https://aws.amazon.com/blogs/aws/new-parallel-query-for-amaz...

https://aws.amazon.com/blogs/aws/amazon-aurora-fast-database...

"...AWS successfully hoodwinked companies everywhere with bullshit, and are absolutely raking in cash because of it."

Really?...

"How Twilio modernized its billing platform on Amazon Aurora MySQL" - https://aws.amazon.com/blogs/database/how-twilio-modernized-...

"No observable Aurora downtime taken in over 5 months of experimentation, and almost 2 months of running shadow production..

Steady state metrics on over 40 accumulated days of live production data across all Aurora clusters:

- Over 46 billion transaction records indexed and available, compared to less than one billion stored in the former online Redis system

- 4.8 TB of data across all tables

- Over 11,000 active database connections to all clusters

- Less than 10 milliseconds median end-to-end transaction run latency

- Less than 60 milliseconds 99th percentile end-to-end transaction run latency..."

"Increasing Scalability and Reducing Costs Using Amazon Aurora Serverless with BMW" - https://aws.amazon.com/solutions/case-studies/bmw-group-auro...

"FINRA CAT selects AWS for Consolidated Audit Trail" - https://aws.amazon.com/blogs/publicsector/finra-cat-selects-...

https://aws.amazon.com/rds/aurora/customers/

Yes they should have started with Aurora Postgres. Note that for Aurora Postgres you can just import a Postgres backup and its the same SQL Code. Its Postgres+plus....

They're deployed on Azure and have a deep partnership with Microsoft, so they can't "simply" use a different cloud.

Also, recommending a black box managed solution isn't an option for some large companies that have their own hardware & datacenters and which may want to use open source solutions they can easily deploy, fork and support themselves to keep costs under control.

Yes, they absolutely do and this is an example of it.