Why does everyone run ancient Postgres versions?

Post by ttfkam » Fri, Oct 18, 2024, 8:23 PM UTC

sgarland wrote: post #384

ttfkam wrote: post #303

Earlier quoted context omitted.

> My experience has been that they spin up either a hideously under or over-provisioned RDS or Aurora instance, and then never touch it until it breaks That's a true shame considering how easy it is to make a read replica of any size and then fail over to it as the new primary. Definite skill issues.

It’s more like a “not knowing how fast something should be” in the case of under-provisioning, and “not knowing or caring to look at metrics” for over-provisioning. I once was examining some queries being generated via Prisma, and found it was using LIMIT/OFFSET for pagination. I pointed this out to the devs, who replied that the query times were acceptable for their SLOs. I guess if you don’t know that a simple SELE…

Post by ttfkam » Fri, Oct 18, 2024, 8:50 PM UTC

sgarland wrote: post #383

ttfkam wrote: post #299

Earlier quoted context omitted.

> none of them could articulate what part of its feature set they actually needed to use. Transactional DDL: migration errors never leave the database in an intermediate/inconsistent state. Range types + exclusion constraint: just no way to do this in MySQL without introducing a race condition. Writeable CTEs: creating insert/update/delete pipelines over multiple tables deterministically. Seriously though, the RETURN…

That is a well-thought out list, and you’re clearly aware of and take advantage of the DB’s capabilities. Seriously, congrats. Especially RETURNING – it’s always baffling to me why more people don’t use it (or its sad cousin in MySQL that lets you get the last inserted rowid if using an auto-increment). Most devs I’ve worked with don’t know about aggregations beyond COUNT and GROUP BY, and do everything in the app. I…

        CREATE TABLE foo ( a int, b int, c int );
        CREATE FUNCTION d(entry foo) RETURNS int LANGUAGE sql IMMUTABLE AS $$
          SELECT foo.a + foo.b + foo.c;
        $$;

        SELECT a, b, c, d(foo) FROM foo;

        CREATE OR REPLACE FUNCTION update_last_modified() RETURNS TRIGGER AS $$
          BEGIN
            NEW.last_modified = now();
            RETURN NEW;   
          END;
        $$ language 'plpgsql';

        CREATE TRIGGER foo_last_modified BEFORE UPDATE ON foo
        FOR EACH ROW EXECUTE PROCEDURE update_last_modified();

        CREATE TRIGGER bar_last_modified BEFORE UPDATE ON bar
        FOR EACH ROW EXECUTE PROCEDURE update_last_modified();

Post by ttfkam » Fri, Oct 18, 2024, 9:13 PM UTC

evanelias wrote: post #372

ttfkam wrote: post #365

Earlier quoted context omitted.

> JSON can often be used in place of arrays This is like storing UUIDs as text. You lose type information and validation. It's like storing your array as a comma-delimited string. It can work in a pinch, but it takes up more storage space and is far more error prone. > convenience types for ipv4, ipv6, and uuid. That's nice to see. A shame you have to decide ahead of time whether you're storing v6 or v4, and I don't…

Regarding using JSON for arrays, MySQL and MariaDB both support validation using JSON Schema. For example, you can enforce that a JSON column only stores an array of numbers by calling JSON_SCHEMA_VALID in a CHECK constraint. Granted, using validated JSON is more hoops than having an array type directly. But in a pinch it's totally doable. MySQL also stores JSON values using a binary representation, it's not a comma-…

        WITH new_order AS (
            INSERT INTO order (po_number, bill_to, ship_to)
                 VALUES ('ABCD1234', 42, 64)
              RETURNING order_id
        )
        INSERT INTO order_item (order_id, product_id, quantity)
             SELECT new_order.order_id, vals.product_id, vals.quantity
               FROM (VALUES (10, 1), (11, 5), (12, 3)) AS vals(product_id, quantity)
              CROSS JOIN new_order
        ;

Post by ttfkam » Fri, Oct 18, 2024, 9:25 PM UTC

dalyons wrote: post #264

sgarland wrote: post #254

Earlier quoted context omitted.

> The split on “which relational database to use” in my career has almost always been perfectly split between SWE vehemently demanding pgsql for the feature set I’ve seen this as well, but when pressed, none of them could articulate what part of its feature set they actually needed to use. > One of the few things I’ve enjoyed with the move into devops and companies forcing previously “pure” developers into operationa…

> My experience has been that they spin up either a hideously under or over-provisioned RDS or Aurora instance, and then never touch it until it breaks, at which point they might ask for help, or they might just make it bigger. Yep that’s exactly what I’ve seen too :). I still overall prefer this distributed database model - yes you spend more and people make mistakes (and learn). But if you can afford it you get hig…

Post by ttfkam » Fri, Oct 18, 2024, 9:35 PM UTC

mschuster91 wrote: post #293

sgarland wrote: post #270

Earlier quoted context omitted.

I don’t mind the model IFF the team has interest in learning how to do it correctly. My biggest complaint as both an SRE and now DBRE has been that dev-managed infrastructure inevitably means during an incident that I had nothing to do with, I’ll be paged to fix it anyway. Actually, that’s not the problem; the problem is later when I explain precisely how and why it broke, and how to avoid it in the future, there’s r…

> Rinse and repeat six months later. I’m aware this is an organizational problem, but from what I’ve seen, it’s endemic. Easy enough: almost no one writes SQL queries by hand these days, not for querying the database nor for doing schema upgrades. It's all done by tools - Doctrine in the PHP world for example. And pretty much no one but actual CS graduates knows anything deeper about databases. Result is, devs are ha…