I was very confused by the title. I thought it was a blog post that implied that CockroachDB was in-use at Twitch, but it's actually a Twitch channel from which viewers can play with CockroachDB (still cool).

Ok, let's use that phrase in the title instead. Thanks!

Very unfortunate username on an iPhone (8).

I had to click through to understand this. Only the first syllable of CockroachDB is visible with the default page width

This is pretty amazing! A social aspect is a really nice touch, not just a repl but a social repl with chat. I've been considering CockroachDB before, and wouldn't say this changed any of my opinions, but it is a fun way to play with it.

Insanely great idea. Congrats to the cockroachdb team for coming up with this idea !

So I’m a bit embarrassed to say I’ve never taken cockroachdb seriously because of the name, but what are the experiences of people using it?

Seems to do the same thing as Citus. Is that accurate?

It seems pretty compelling.

I can't say much about Citus, but cockroachdb mostly works as advertised. Plus it's getting better with every release, which come pretty frequently. Support (forums/slack) is also unimaginably helpful and friendly.

There's at least 2 big caveats though:

1. Although they are very transparent about limitations / PG compatibility issues, you'll almost certainly run into things you weren't expecting (because some of the limitations are only documented as bugs in github issues, which you probably won't go through every single one of before you start). Examples:

a. No partial indexes (which can be a pain if you had a unique partial index, solution add a nullable column to the unique index) - but there's clearly work being done on this as we speak

b. Index isn't used with now() (solution, pass it as a parameter)

c. No way to tell if an upsert did an insert or update (like using xmax in postgres)

d. More obviously: no full text search or geo

2. Backup options for the community edition is limited (which I think is a generous way to describe it). If you're used to (free) Barman, even their enterprise backup capabilities are poor.

For any serious work, I'd recommend you not use the community edition until #2 improves.

Thanks for the kind words. We're working on it.

1a) (as you noted) should be in the next release

1b) working on it, should be in the next release: https://github.com/cockroachdb/cockroach/pull/50320

1c) I filed https://github.com/cockroachdb/cockroach/issues/50418 to track. It seems plausible that we could fit in a story here with the work we're doing to expose MVCC timestamps to SQL.

1d) No plans on any sort of text search story but geo is underway (see https://github.com/cockroachdb/cockroach/issues?q=is%3Aissue...)

Curious to hear about your use of full text search in postgres.

Have you ever used pgtrgm? Would something like that or compatible with that be interesting to you? https://www.postgresql.org/docs/12/pgtrgm.html

For "even their enterprise backup capabilities are poor." are there specific things you have in mind?

I'm going to be evaluating CRDB (Enterprise version) for us next week - we also use pgtrgm with GIN as a really convenient way of making some simple searches like "SELECT * FROM users WHERE name LIKE '%bob%'" not need a full table scan. So would be very interested in that being added.

Cool, pgtrgm has come up a few times in recent weeks. It's tracked in the below issue. I'm interested in hacking on it as it seems like a big reward for not that much work.

https://github.com/cockroachdb/cockroach/issues/41285

Yes, pgtrgm is what we used. Right now, we're using it for indexing very small data, e.g. filtering users by name/email/phone.

Our solution is to store our own ngram-ish stuff into a _search table.

Given a user:

  id: 1
  name: Leto Atreides
  email: leto@calagan.gov

user_search (user_id, search, score):

  1, let, 10
  1, atr, 10
  1, leto, 600 
  1, atreides, 500
  1, leto atreides, 1000
  1, leto@caladan.gov, 100
  1, caladan, 10

And join via:

  select user_id, sum(score) score
  from user_search
  where search like 'let%'
  group by user_id
  order by score desc

For backups...

1. It looks like 20.1 addressed 2 limitation I saw with enterprise: point in time restores and encryption. My mistake for not staying up to date.

2. When I look at PG's replication capabilities, which enables tools like barman, stand-by servers (via delayed replicas) and read-only replicas, I feel like PG is way ahead. I know this isn't strictly "backups", but to me it's all foundation that enables robust disaster recovery.

What I would like to see is:

1. community edition with a 100% working (there are edge cases now where it can fail) full + incremental backup + reasonable restore capabilities (restore of a full backups is slow enough now to undermine any serious DR plans)

2. enterprise edition adds:

  a- encryption
  b- configurable storage (s3, gc, ...)
  c- delayed stand-by server
  d- more configuration  (# of full backups to keep, number of days to keep, etc etc (e.g, everything barman offers, but point e:)
  e- 100% managed in web, including being able to do PITR to a separate server/cluster, and verifying DB integrity

While I have you here, my dream list (I know these are all known issues)

  1 - Can't defer referential integrity check
  2 - Can't index arrays
  3 - No enums
  4 - No ranges
  5 - No triggers (i imagine this would be a big one)

And, I really wish that start-single-node with a mem store would be faster (for our integration tests).

Enums should be coming in 20.2 as well :)

We are held back from adoption here due to our extensive use of Postgres Triggers. Seems like the issue hasn't moved very far in a while [1] - any plans?

[1] https://github.com/cockroachdb/cockroach/issues/28296

A prerequisite of triggers are functions. Functions are a good chunk of work in themselves. I'd say we've built some more building blocks which might be useful but I don't think we're particularly close to starting to talk about implementing functions let alone triggers just yet.

Out of curiosity, what are your use cases? I'd expect before triggers are implemented, we'll make changefeeds more powerful and robustly supported.

Change. The. Name.

Please!

Do it yourself - https://github.com/tbg/bikesheddb