https://www.washingtonpost.com/about-the-post

As a SQL non-expert, I think this is why we are averse to joins, because it’s easy to end up with more rows in the result than you intended, and it’s not always clear how to verify you haven’t ended up in this scenario.

    SELECT * FROM order JOIN_ONE customer USING (customer_id)

I like that though, I have a preference for REPL style development and fleshing things out interactively. Enough so that I build Java like that, hacking things out directly in tests.

Working with a database you built or can control is kind of a simplistic example.

In my experience this most often arises when it’s someone else’s database or API you’re interacting with and do not control.

An upstream ERP system doesn’t have unique keys, or it changes the name of a column, or an accounting person adds a custom field, or the accounting system does claim to have unique identifiers but gets upgraded and changes the unique row identifiers that were never supposed to change, or a user deletes a record and recreates the same record with the same name, which now has a different ID so the data in your reporting database no longer has the correct foreign keys, and some of the data has to be cross-referenced from the ERP system with a CRM that only has a flaky API and the only way to get the data is by pulling a CSV report file from an email, which doesn’t have the same field names to reliably correlate the data with the ERP, and worse the CRM makes these user-editable so one of your 200 sales people decides to use their own naming scheme or makes a typo and we have 10 different ways of spelling “New York”, “new york”, “NY”, “newyork2”, “now york”, and yeah…

Turns out you can sometimes end up with extra rows despite your best efforts and that SQL isn’t always the best tool for joining data, and no I’m not interested in helping you troubleshoot your 7-page SQL query that’s stacked on top of multiple layers of nested SQL views that’s giving you too many rows. You might even say I’m averse.

This is a problem of form, not method. JOINs are a fantastic, well-defined method to aggregate data. If the form of your data is messed up, then naturally the result may be too.

> no I’m not interested in helping you troubleshoot your 7-page SQL query that’s stacked on top of multiple layers of nested SQL views that’s giving you too many rows

People say this type of thing but SQL is an incredibly high-level language.

Yes debugging a big SQL query can suck. Debugging the equivalent of it is almost always much worse. I'd rather debug a 30-line query than a 400 line perl script that does the same thing. Because that's actually the alternative.

I have manually aggregated data, messy data, in awk, perl, python... it is much, much worse.

Yes, but with Python/etc you can at least do the same logic in a plain loop, which is much slower but serves as a more reliable reference of what the data is supposed to be, which can be used to validate the functionality of SQL output.

Is there an equivalent in SQL of this “slow and dumb” approach for validating? Like, I’m not sure if a lateral join is essentially doing the same thing under the hood.

In practice, I ended up creating a skeleton table that not only had the appropriate constraints for de-duplication. But I would also create mostly empty rows (empty except for the required column key ID fields) with the exact rows they were expecting. And then I would perform an UPDATE <skeleton> ... FROM <complicated join>. It is a hack but if there was no desire to rewrite a process that was written years ago by teams of consultants, I can only do what I can do.

While likely, the question asked if there was any improvement shown with other targets to validate that assumption. There is no benefit in thinking.

> And often query results must be 100% accurate and reliable.

It seems that is impossible. Even the human programmers struggle to reliably convert natural language to SQL according to the aforementioned test study. They are slightly better than the known alternatives, but far from perfect. But if another target can get closer to human-level performance, that is significant.

2. The language has to represent a valid computer program. That is as true of SQL as any other target. You can know that it is correct by reading it.

That said, if you have ever used these tools to generate code, you will know that they are much better at some languages than others. In the general case, the target really is the problem sometimes. Does that carry into this particular narrow case? I don't know. What do the comparison results show?

Perhaps it is the highest-performing in-memory DB in the world, but it's a competitive space and the claim is extremely grandiose. If I were hiring my assumption would be it's BS. I would recommend the OP collapse the resume to a single-page PDF and ensure it contains only supportable claims.

Differences can appear in multicolumn indexes because two ints takes 8 bytes while two bigints takes 16, however the right layout of columns for an index is not always the layout that minimizes padding.

  CREATE TABLE foo
    (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid INT NOT NULL);

  CREATE TABLE bar
    (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid BIGINT NOT NULL);

  CREATE TABLE baz
    (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid BIGINT NOT NULL);

  -- fill each with 1,000,000 rows, then index

  CREATE INDEX {foo,bar,baz}_iid_idx ON {foo,bar,baz}(iid);

  SELECT table_name,
         pg_size_pretty(pg_table_size(quote_ident(table_name))) "table_size",
         pg_size_pretty(pg_indexes_size(quote_ident(table_name))) "index_size"
  FROM information_schema.tables
  WHERE table_schema = 'public';

   table_name | table_size | index_size
  ------------+------------+------------
   foo        | 35 MB      | 43 MB
   bar        | 42 MB      | 43 MB
   baz        | 42 MB      | 43 MB

As your example shows, there is no benefit in index size (e.g for supporting FKs) in going from int to bigint for a single key. You end up with the same index size no matter what, not twice the size which was what I took your original post to mean.

However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables.

Foreign keys referencing surrogate key has different semantics than fk referencing natural key - it is a can of worms actually and can lead to unexpected anomalies.

Lets take the example from the article (with surrogate key):

Restaurant(id, name, city)

Now let's add a possibility to record visits:

Visit(restaurant_id references Restaurant(id), user, date)

We have a procedure to register visits to a restaurant:

register_visit(restaurant_name, user_name, date_of_visit) { INSERT INTO visit SELECT id, user_name, date_of_visit FROM restaurant WHERE name = restaurant_name }

I very much enjoy spending time in "Polish Kielbasa" restaurant in Warsaw and I visit it everyday - I don't visit any other restaurant at all.

Now changes of a restaurant name will lead to the database containing misinformation:

register_visit('Polish Kielbasa', 'mkleczek', 2024-6-4); update restaurant set name = 'Old Kielbasa' where name = 'Polish Kielbasa' and city = 'Warsaw'; insert into restaurant ('Polish Kielbasa', 'Warsaw'); register_visit('Polish Kielbasa', 'mkleczek', 2024-6-4);

Question: what restaurants did I visit this year?

This kind of anomalies are avoided using _natural_ keys and - first of all - defining proper _predicate_ for _each_ relation.

The predicate of relation visit(restaurant_name, city, user, date) is quite obvious: "User [user] visited restaurant [restaurant_name] in [city] on [date]"

Question: What is the predicate of relation visit(restaurant_id, user, date)?