News for hackers. Stuff that matters.
Re:A Twitch channel from which viewers can play with Cockroa... by vvern on Thursday June 18, 2020 @10:03PM (#23570276)
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?

Re:A Twitch channel from which viewers can play with Cockroa... by jordanthoms on Friday June 19, 2020 @01:32AM (#23571299)
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.
Re:A Twitch channel from which viewers can play with Cockroa... by vvern on Friday June 19, 2020 @10:51AM (#23575081)
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

Re:A Twitch channel from which viewers can play with Cockroa... by latch on Thursday June 18, 2020 @11:07PM (#23570558)
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: [email protected]
user_search (user_id, search, score):

  1, let, 10
  1, atr, 10
  1, leto, 600 
  1, atreides, 500
  1, leto atreides, 1000
  1, [email protected], 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).
Re:A Twitch channel from which viewers can play with Cockroa... by rohany on Friday June 19, 2020 @11:07AM (#23575223)
Enums should be coming in 20.2 as well :)