Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Databases should contain their own Metadata – Use SQL Everywhere

floedb.ai

Post reply on HN
Print view View original

21–30 of 38 posts

Re: Databases should contain their own Metadata – Use SQL Everywhere

#21

Post by n4r9 »

zabzonk wrote: post #16
da_chicken wrote: post #8

Earlier quoted context omitted.

Yeah, this seemed like a very long way to say, "Our RDBMS has system catalogs," as if it's 1987. But then, they're also doing JOINs with the USING clause, which seems like one of those things that everybody tries... until they hit one of the several reasons not to use them, and then they go back to the ON clause which is explicit and concrete and works great in all cases. Personally, I'd like to hear more about the c…

> doing JOINs with the USING clause I'm ashamed to say that despite using SQL from the late 1980s, and as someone that likes reading manuals and text books, I'd never come across USING. Probably a bit late for me now to use it (or not) :-(

I've used SQL for around a decade and also never came across it. I'm maintaining SQL code with hundreds if not thousands of basic primary key joins and this could make those queries way more concise. Now I want to know the reasons for not using USING!

Re: Databases should contain their own Metadata – Use SQL Everywhere

#22

Post by tkejser »

zabzonk wrote: post #16
da_chicken wrote: post #8

Earlier quoted context omitted.

Yeah, this seemed like a very long way to say, "Our RDBMS has system catalogs," as if it's 1987. But then, they're also doing JOINs with the USING clause, which seems like one of those things that everybody tries... until they hit one of the several reasons not to use them, and then they go back to the ON clause which is explicit and concrete and works great in all cases. Personally, I'd like to hear more about the c…

> doing JOINs with the USING clause I'm ashamed to say that despite using SQL from the late 1980s, and as someone that likes reading manuals and text books, I'd never come across USING. Probably a bit late for me now to use it (or not) :-(

I didn't really write USING in anger until around 10 years ago, and I have been around a long time too.

Not all databases support it. But once you start using it (pun) - a lot of naming conventions snap into place.

It has some funky semantics you should be aware of. Consider this:

  CREATE TABLE foo (x INT);

  CREATE TABLE bar (x INT);

  SELECT \* FROM foo JOIN bar USING (x);
There is only one `x` in the above `SELECT *` - the automatically disambiguated one. Which is typically want you want.

Re: Databases should contain their own Metadata – Use SQL Everywhere

#23

Post by tkejser »

n4r9 wrote: post #21
zabzonk wrote: post #16

Earlier quoted context omitted.

> doing JOINs with the USING clause I'm ashamed to say that despite using SQL from the late 1980s, and as someone that likes reading manuals and text books, I'd never come across USING. Probably a bit late for me now to use it (or not) :-(

I've used SQL for around a decade and also never came across it. I'm maintaining SQL code with hundreds if not thousands of basic primary key joins and this could make those queries way more concise. Now I want to know the reasons for not using USING!

There are reasons for not USING.

First, you need to be aware of the implicit disambiguration. When you join with USING, you are introducing a hidden column that represents both sides. This is typically what you want - but it can bite you.

Consider this PostgreSQL example:

  CREATE TABLE foo (x INT);
  INSERT INTO foo VALUeS (1);

  CREATE TABLE bar (x FLOAT);
  INSERT INTO bar VALUES (1);

  SELECT pg_typeof(x) FROM foo JOIN bar USING (x);
The type of x is is double, - because x was implicitly upcast as we can see with EXPLAIN:

  Merge Join  (cost=338.29..931.54 rows=28815 width=4)
    Merge Cond: (bar.x = ((foo.x)::double precision))
Arguably, you should never be joining on keys of different types. It just bad design. But you don't always get that choice if someone else made the data model for you.

It also means that this actually works:

  CREATE TABLE foo (x INT);
  INSERT INTO foo VALUeS (1);

  CREATE TABLE bar (x INT);
  INSERT INTO bar VALUES (1);

  CREATE TABLE baz (x INT);
  INSERT INTO baz VALUES (1);

  SELECT \*
  FROM foo
  JOIN bar USING (x)
  JOIN baz USING (x);
Which might not be what you expected :-)

If you are both the data modeller and the query writer - I have not been able to come up with a reason for not USING.

Re: Databases should contain their own Metadata – Use SQL Everywhere

#24

Post by n4r9 »

tkejser wrote: post #23
n4r9 wrote: post #21

Earlier quoted context omitted.

I've used SQL for around a decade and also never came across it. I'm maintaining SQL code with hundreds if not thousands of basic primary key joins and this could make those queries way more concise. Now I want to know the reasons for not using USING!

There are reasons for not USING. First, you need to be aware of the implicit disambiguration. When you join with USING, you are introducing a hidden column that represents both sides. This is typically what you want - but it can bite you. Consider this PostgreSQL example: CREATE TABLE foo (x INT); INSERT INTO foo VALUeS (1); CREATE TABLE bar (x FLOAT); INSERT INTO bar VALUES (1); SELECT pg_typeof(x) FROM foo JOIN bar…

Thanks for the reply. The use case I have in mind is joining onto an INT primary key using a foreign key column of another table. This alone would remove a massive amount of boilerplate code.

Re: Databases should contain their own Metadata – Use SQL Everywhere

#25

Post by tkejser »

matheusalmeida wrote: post #18
galaxyLogic wrote: post #2

Isn't his like it is in many relational databases, you can query them about the tables in them?

The key difference is that it's not just about schema metadata (tables, indexes, views, columns, etc...). PostgreSQL is fabulous regarding this. Even native types are part of the catalog (pg_catalog). Things are great in your DB... until they aren't. The post is about making observability a first-class citizen. Plans and query execution statistics, for example, queryable using a uniform interface (SQL) without the ne…

Thank you and yes!

By making the entire architecture of the database visible via system objects - you allow the user to form a mental model of how the database itself works. Instead of it being just a magic box that runs queries - it becomes a fully instrumented data model of itself.

Now, you could say: "The database should just work" and perhaps claim that it is design error when it doesn't. Why do I need instrumentation at this level?

To that I can say: Every database ever made makes query planning mistakes or has places where it misbehaves. That's just the way this field works - because data is fiendishly complicated - particularly at high concurrency of when there is a lot of it. The solution isn't (just) to keep improving and fixing edge cases - it is to make those edge cases easy to detect for all users.

Re: Databases should contain their own Metadata – Use SQL Everywhere

#26

Post by tkejser »

da_chicken wrote: post #8
zabzonk wrote: post #4

Not clear if the author realises that all commercial SQL database engines support querying of the database's metadata using SQL. Or maybe I have misunderstood - I only skimmed the article.

Yeah, this seemed like a very long way to say, "Our RDBMS has system catalogs," as if it's 1987. But then, they're also doing JOINs with the USING clause, which seems like one of those things that everybody tries... until they hit one of the several reasons not to use them, and then they go back to the ON clause which is explicit and concrete and works great in all cases. Personally, I'd like to hear more about the c…

@da_chicken: You can read more about Snowflake ID in the Wiki page linked in the article.

The short story:

They are bit like UUID in that you can generate them across a system in a distributed way without coordination. Unlike UUID they are only 64-bit.

The first bits of the snowflake ID are structured in such a way that the values end up roughly sequentially ordered on disk. That makes them great for large tables where you need to locate specific values (such a those that store query information).

Re: Databases should contain their own Metadata – Use SQL Everywhere

#27

Post by whynotmaybe »

zabzonk wrote: post #4

Not clear if the author realises that all commercial SQL database engines support querying of the database's metadata using SQL. Or maybe I have misunderstood - I only skimmed the article.

I don't think it's as easy to do the example in the article just by using information_schema.

> Which tables have a column with the name country where that column has more than two different values

But on their product page, the definition of floesql left me puzzled

> It uses intelligent caching and LLVM-based vectorized execution to deliver the query execution speed your business users expect.

> With its powerful query planner, FloeSQL executes queries with lots of joins and complicated SQL syntax without breaking your budget.

Re: Databases should contain their own Metadata – Use SQL Everywhere

#28

Post by da_chicken »

rbanffy wrote: post #14
da_chicken wrote: post #9

Earlier quoted context omitted.

That's quite expensive. Most systems that need this sort of data will instead implement some form of audit log or audit table. Which is still quite expensive. At the record level, I've seldom seen more than an add timestamp, and add user id, a last change timestamp, and a last change user id. Even then, it covers any change to the whole row, not every field. It's still relatively expensive.

> Which is still quite expensive. OTOH, if they managed to do that in an efficient way, they have something really interesting.

Writing to disk is never free.

Re: Databases should contain their own Metadata – Use SQL Everywhere

#29

Post by tkejser »

zabzonk wrote: post #7
umairnadeem123 wrote: post #3

[dead]

> which queries produced which rows I doubt many real-world applications could tolerate the amount of data/performance degradation this implies. If you need this (and I can't think why you would), then I think writing your own logging code is the answer, rather than lumbering everyone else with it.

To clarify a point: We store the row count of each operator in the query - not the actual row (that would indeed be madness!). Though we DO have tracing you can control that allow you to enable rows for very specific diagnostics - but the stream is massive and you need to opt in for that.

With that clarified, the logging not as large as you might think (see my other response).

Think of web servers - they routinely store much larger log streams than this with metadata about each hit. You rely on that stream to do various forms of web analytics - yet you would not dream of rolling your own - nor worry about the small overhead you are already paying.

Example:

SELECT x, y FROM foo JOIN bar USING (k)

In a query like this, you will have these operators:

SCAN of foo

SCAN of bar

JOIN (on k between Foo and Bar)

Hope that clarifies the point... That's 3 operators, and we log the row counts of each. That in turn allows you answer questions about your data model and how it is being used.

Re: Databases should contain their own Metadata – Use SQL Everywhere

#30

Post by rbanffy »

da_chicken wrote: post #28
rbanffy wrote: post #14

Earlier quoted context omitted.

> Which is still quite expensive. OTOH, if they managed to do that in an efficient way, they have something really interesting.

Writing to disk is never free.

True, but you do it with blocks that often contain padding. If you can make the padding useful, that's a win.
Post reply on HN