MarkusWinand

Not's not just outdated, its wrong (in context of question 2) which is about indexing both, `where` and `order by` clauses. In that case you must provide a single index to benefit from the index order (so that the database doesn't actually need to perform a sort operation).

The mentioned proof is confirming SQL:2003.

See also: https://wiki.postgresql.org/wiki/Cyclic_Tag_System

Not to forget SQLite: https://sqlite.org/lang_with.html

PostgreSQL being the one noteworthy: http://modern-sql.com/feature/with/performance

Related posts from the past:

What ORMs have taught me: just learn SQL http://woz.posthaven.com/what-orms-have-taught-me-just-learn...

ORM Is an Offensive Anti-Pattern http://www.yegor256.com/2014/12/01/orm-offensive-anti-patter...

I've added the other frameworks.

As for the update marker, I've just added an "updated" date to next the the publishing date in the breadcrumb.

Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks

Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks

Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks

> any updates on this list since then?

When I notice, I add/update this list.

I'll check those mentioned in the reply's and add them if the seem to be right.

How about "do not use offset for pagination"?

http://use-the-index-luke.com/no-offset

Watch out—it's not that easy: http://modern-sql.com/feature/with/performance

I'm curios if this is a fair benchmark (considering it was conducted by one of the vendors).

So I downloaded the mentioned whitepaper to look at the indexes they created in PostgreSQL.

The only thing mentioned in the whitepaper is this table definition:

  CREATE TABLE IF NOT EXISTS t1 (
  "uuid" VARCHAR,
  "ts" TIMESTAMP, "tenant_id" INT4, "sensor_id" VARCHAR,   
  "sensor_type" VARCHAR, "v1" INT4,
  "v2" INT4,
  "v3" FLOAT4,
  "v4" FLOAT4,
  "v5" BOOL,
  "week_generated" TIMESTAMP, "taxonomy" ltree
  );

The table definition in Crate is a little different:

  CREATE TABLE IF NOT EXISTS b.t2 ( "uuid" STRING,
  "ts" TIMESTAMP,
  "tenant_id" INTEGER, "sensor_id" STRING, "sensor_type" STRING,
  "v1" INTEGER,
  "v2" INTEGER,
  "v3" FLOAT,
  "v4" FLOAT,
  "v5" BOOLEAN,
  "week_generated" TIMESTAMP GENERATED ALWAYS AS     date_trunc('week', ts),
   INDEX "taxonomy" USING FULLTEXT (sensor_type) WITH (analyzer='tree')
  ) PARTITIONED BY ("week_generated") 
  CLUSTERED BY ("tenant_id") INTO 3 SHARDS;

I don't know anything about how Crate works, but I see PARTITIONED BY ("week_generated") and CLUSTERED BY ("tenant_id") INTO 3 SHARDS.

Now looking at the first query, both, their PARTITION BY as well as CLUSTER BY columns appear in the query:

  SELECT min(v1) as v1_min, max(v1) as v1_max, avg(v1) as v1_avg, sum(v1) as v1_sum
  FROM b.t2
  WHERE tenant_id = ? AND week_generated BETWEEN ? AND ?;

Unless there is a sufficiently good index used in PostgreSQL, this doesn't seem to be a fair benchmark.

In the Appendix they mention how the partitioning could be implemented in PostgreSQL, also mention the indexes to be used together with the partitioning, but also say: "Because of this, we abandoned the partitioning approach for our benchmark, instead opting for a single PostgreSQL table with no partition logic."

I didn't find any statement about if/which indexes they were using in PostgreSQL when measuring their benchmarks.

They also use a rather old PostgreSQL version (9.2 — released in 2012). Now, we have PostgreSQL 9.6 which has some parallel query execution support that might also change this figures dramatically.

I think the point of the article is that adding an index to a big table with lots of writes is practically not possible:

Quoting from the article:

  real problem for big tables

  as adding a few columns to our biggest tables started to take 2+ hours
  or sometimes was completely unpredictable and exceeded our announced downtime windows.

Apparently, adding a column was even a problem during a maintenance window because the runtime was even longer than they expected.

Compared to PostgreSQL:

  As long as the new column is NULL and does not have a default value,
  it’s in practice a no-op to add it. No matter if your table size is 100MB or 100GB

From https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...:

  Add column
  In-Place?: *Yes*
  Rebuilds Table?: *Yes*
  Permits Concurrent DML?: *Yes* (Concurrent DML is not permitted when adding an auto-increment column.)
  Only Modifies Metadata?: *No*
  Data is reorganized substantially, making it an expensive operation.

In practice, the last no is a serious problem.

Just FYI: starting with 9.4, PostgreSQL supports the FILTER clause. This would change this:

> array_remove(array_agg(producer.last_name), NULL) AS last_names

to that

> array_agg(producer.last_name) FILTER(WHERE producer.last_name IS NOT NULL) AS last_names

I wrote about FILTER here: http://modern-sql.com/feature/filter

And how to use ARRAYs rather than concatenated strings here: http://modern-sql.com/feature/listagg#alternative-array

I find their locking issues interesting: they are supporting snapshot isolation for quite some time now, but it's still not default and the community even hardly knows about it. Hint's like NOLOCK are more often suggested as "fix" as considering snapshot isolation.

Although SQL Server vNext (14.x) will get string_agg: https://msdn.microsoft.com/en-us/library/mt790580.aspx

In the latest version of the SQL standard (SQL:2016) the LISTAGG function was added for this purpose. I find it "disturbing" that the next SQL Server release gets a function for this, but the new function doesn't follow the new standard.

Even worse: the name "string_agg" was apperently borrowed from PostgreSQL. But if you think that SQL Server will stick to the syntax of PostgreSQL's string_agg — nope. They use the same function name, but a different syntax.

Microsoft double fail, I'd say.

I've just written an article about LISTAGG, btw: http://modern-sql.com/feature/listagg

The right charts on slide 45 says "X" for SQLite. That changed recently: https://www.sqlite.org/releaselog/3_15_0.html

DB2 LUW has also decent row-values support: http://use-the-index-luke.com/blog/2014-11/seven-surprising-...

Need to update the slides...

I see the value of FILTER mostly in ease to read and understand.

Many things — e.g., Pivot — can be understood way more easily using FILTER rather than CASE.