1,995 karma · joined May 20, 2020
https://github.com/microsoft/documentdb
I am skeptical that SQL with Mongo backing it would be at all performant except in the most trivial cases. On the flip side, Postgres's jsonb indexing makes the inverse very doable.
Yes!!!
> Add OLD/NEW support to RETURNING in DML queries
Wow! That's a feature I'd never considered and now can't imagine living without moving forward. Kudos to the team!
So if you're cool with C++ or Java compile times, Rust will generally be fine. If you're coming from Go, Rust compiles will fell positively glacial.
Programmer's fault: runtime exception
Not programmer's fault: checked exception
Reading from a file but the disk fails? Not programmer's fault. IOException (checked). Missed a null somewhere? Programmer's fault. NullPointerException (unchecked).
In Rust, you can't just skip error handling. You have to proactively do something generally unwise (and highly visible!) like call .unwrap() or you have to actually handle the error condition.
Go still relies on goodwill and a good night's sleep. The Rust compiler will guard against laziness and sleep deprivation, because ultimately programming languages are about people, not the computers.
Remember that Postgres's feature set is far larger than those alternatives. If you can use a range with an exclusion constraint, an unnest with an array, or the like, you'll be seeing Postgres leave the alternatives in the dust.
Imagine writing a benchmark comparing programming languages, but the benchmark only includes idioms that all tested languages shared in common. Wouldn't be a fair comparison, would it?
https://aws.amazon.com/cloudfront/pricing/?nc=sn&loc=3
Were you trying to route S3 directly out to the internet?
So we can reuse old reactors with the knowledge gained over the last 50 years of operating nuclear plants or we can have these AI engines running on burning fossil fuels when the renewables aren't at capacity.
1. Stop AI build out in the US
2. Reuse old nuclear reactors
3. Expand fossil fuel plant usage
That's it. Those are the options. Battery storage won't get renewables over the finish line here. Solar and wind should absolutely be in the mix and doing their part in transferring supply away from fossil fuels. Personally I consider global climate change to be the largest threat of our time. Anything that worsens that threat rather than being either neutral or reducing it is a non-starter to me.
"Perfect is the enemy of good."
For example, I like Rust. But if Rust had been introduced as-is fifty years ago, no one would have used it, because the hardware requirements to make Rust compilation practical simply didn't exist yet. Taking a week just to compile "hello world" would have been a nonstarter. Not because Rust is bad but because hardware requirements at the time ruled something like it out.
2024 is not 1964 however, and it's always good to re-examine old assumptions.
Tonight we're gonna party like it's the year 2009!
I like to consider myself a guest on a client CPU, GPU, and RAM. I should not eat all their food, leave an unflushed turd in their toilet, and hog the remote control. Be a thoughtful guest that encourages feelings of inviting me back in the future.
Load fast, even when cell coverage is marginal. Low memory so a system doesn't grind to a halt from swapping. Animate judiciously because it's polite. Good algorithms, because everyone notices when their cursor becomes jerky.
https://ardentperf.com/2024/02/03/uuid-benchmark-war/
My PK preference in descending order: natural key if it makes sense and would speed up queries, 16-bit for unambiguous lookup tables with a hard finite limit of values, UUIDv7, 64-bit sequence, UUIDv4, and then finally 32-bit sequence.
UUIDv7 for performance, reasonable non-guessability, client-generation, and potential for sharding. 64-bit for cases that really need a sequence. UUIDv4 when randomness is required for security. 32-bit when there's a hard finite limit that can guaranteed to be below 2 billion even given the most optimistic of projections.
If the database is small, it doesn't matter. If it's large, it is 99.999% unlikely the primary key will be anything more than a rounding error in storage, access time, or cost. No one ever said, "We would have made payroll if only we had used 32-bit primary keys."
To really save space, extract the timestamp in the UUIDv7 and use an expression index to use as the creation date. Then you're not "wasting" any storage space. Personally I don't think the storage optimization is necessary. Premature optimization being evil and all that.
Your experience does not match mine. Tools like ORMs make horrible schemas in my opinion that cater to the lowest common denominator of SQL engine functionality. This means leaving a lot of performance and scalability on the floor. In order to make the ORMs generate decent schema definitions, you need to know the underlying engine and therefore SQL. At that point, you might as well use SQL.
Ever try changing a column's data type from a table with hundreds of millions of rows with an ORM definition file? Hope you like downtime.
To be fair, this blows out any db that supports clustered indexes as well. Non-k-sortable primary keys are just a bad idea all around.
With UUIDv7, the WAL write amplification problem goes away just as the clustered index issues do.
I have mixed feelings about this. On the one hand I agree that ownership should be shared. On the other, app developers really don't consider their data structures as carefully in SQL as they do in-memory. It's odd. The right data structure matters more than a good algorithm since algorithms are easier to change. Once you settle on a list vs a set vs a queue, you're stuck once code is built around it.
The same is doubly true for the database schema. Lack of planning and knowledge of expected access patterns can turn an otherwise fast database to mud in no time flat. Once your data is in there, changing the schema is exponentially harder.
"I’m a huge proponent of designing your code around the data, rather than the other way around, and I think it’s one of the reasons git has been fairly successful… I will, in fact, claim that the difference between a bad programmer and a good one is whether he considers his code or his data structures more important. Bad programmers worry about the code. Good programmers worry about data structures and their relationships." – Linus Torvalds (2006)
What is your database but a bunch of data structures and relationships? I get why the gatekeeping occurred. I don't agree with it, but I understand it. Far too many folks consider expertise in data stores to be optional as developers.
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
;
1. Yep, I definitely miss clustering indexes in Postgres sometimes. I can sometimes fake it with covering indexes when all I want are an extra column or two along with the primary key or similar without seeking to the main table, but you're right about that MySQL/MariaDB win here.
2. The dynamic computed column is an easy workaround with immutable functions that take the record as a param.
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;
3. ON UPDATE CURRENT_TIMESTAMP works in Postgres with a trigger function, which you can reuse if all your tables use the same name for your "last_modified" column (probably a good idea anyway). Not as convenient as the declarative syntax, but it's a fairly trivial workaround.
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();
I don't mind workarounds so much as functionality that simply cannot be replicated. For example I miss real temporal table support in Postgres like what you can find in MariaDB or MS SQL Server. The painful kludges for missing PIVOT support like in MS SQL Server is another one.
You never know how much you need deferred foreign key constraints until you don't have them anymore. Or a materialized view.
When all you know is an ORM, you tend to treat SQL databases like dumb bit bucket add-ons to your app server. It's amazing how much potential performance and scalability are left on the floor because app developers can't shift their mindset when needed. Objects/structs cannot be assumed to map 1:1 with relations. What a world we'd live in if devs spent even 1/10 the effort examining their relational schema design that they spend arguing over whether a set, a list, or a queue is better for a given situation. It's like thoughts on Big-O stop cold at the database driver interface.
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 see support for network ranges, but a definite improvement.
> MariaDB supports RETURNING.
That's honestly wonderful to see. Can these be used inside of CTEs as well for correlated INSERTs?
Whether you go from v14 to v17 or v14 to v20, I doubt it'll make a difference in migration strategy. You've still got a fair amount of time before EOL. No need to stress or FOMO.
Yes, more drivers and libraries should support the more expansive data type list, but even just within the database itself there are multiple advantages.