Is this SQL databases or No-SQL? [LWN.net]

Is this SQL databases or No-SQL?

Posted Apr 10, 2014 10:02 UTC (Thu) by nix (subscriber, #2304)
In reply to: Is this SQL databases or No-SQL? by Wol
Parent article: A discussion between database and kernel developers

The other place you will score is indices. Your optimiser will spot it needs to scan INVOICES multiple times, and it will build indices on the fly if they don't exist.

You need to use some actual databases, rather than just reading Codd and assuming everyone implemented what he suggested. While doing that (incrementally, no less) would be really cool, nobody ever does that and I don't know of any database system that implements it. Indices are explicitly-constructed entities.

Yes, this does mean that in a lot of databases you end up with a rule that on particular large tables all queries must use an index, and often the database can even enforce that rule. But... you can have more than one index, allowing for more forms of lookup than just primary-keyed, and you can still have totally ad-hoc queries on smaller tables.

As far as I can see, Pick throws all that flexibility away. I can easily see myself having to rethink half an application because I want to add one query, since oops now I need to change what the primary key is! This seems very far from ideal.

(Disclaimer: I work for Oracle now, but in the period of my life when I did things with databases, I didn't.)

to post comments

Is this SQL databases or No-SQL?

Posted Apr 10, 2014 20:14 UTC (Thu) by cladisch (✭ supporter ✭, #50193) [Link] (3 responses)

> > […] it will build indices on the fly if they don't exist.

> I don't know of any database system that implements it.

As shown in this example, SQLite is happy to create a temporary index if it is estimated to be faster overall.

Is this SQL databases or No-SQL?

Posted Apr 15, 2014 14:45 UTC (Tue) by nix (subscriber, #2304) [Link] (2 responses)

Neat!

I did some digging in response: other, bigger RDBMSes (including PostgreSQL) will also create temporary indexes in temporary tablespace if needed. They're not visible to the database user, though, and are thrown away after the query terminates.

Is this SQL databases or No-SQL?

Posted Apr 15, 2014 15:27 UTC (Tue) by intgr (subscriber, #39733) [Link] (1 responses)

> bigger RDBMSes (including PostgreSQL) will also create temporary indexes

I think you are misunderstanding. The "temporary indexes" you speak probably refers to explicitly created indexes CREATE TEMPORARY TABLE tables issued by the client. I guess you were reading this: http://www.postgresql.org/docs/current/static/runtime-con...

PostgreSQL can create temporary in-memory hash tables, do temporary sorts (such as for merge joins or ORDER BY) and mid-query materialization of intermediate results. Sorts and materialized results can spill out to disk when there isn't enough memory to keep them, but they aren't called "temporary indexes" since they don't resemble actual index data structures.

Is this SQL databases or No-SQL?

Posted Apr 20, 2014 18:27 UTC (Sun) by nix (subscriber, #2304) [Link]

Yes, but those appear to be what wol is referring to as 'indexes' (they index the data, after all). They're not SQL-level entities, or user-visible, of course.