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).
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?