CockroachDB support

[chappjc]

CockroachDB support should be a new feature for 5.0.

I'm fairly certain this is doable since CockroachDB is largely compatible with PostgreSQL, and github.com/lib/pq seems to support CockroachDB (see this recent pq PR).

However, there are a number of required changes, including:

• removal of custom types (e.g. vin_t) since cockroach does not support them. CockroachDB support #1294
• Detecting use of cockroach. CockroachDB support #1294
• Not erroring out when checking postgresql-specific information (e.g. synchronous_commit and almost all the pg_settings dumped in the table at startup). CockroachDB support #1294
• Changing the versioning scheme since table comments are not supported in CockroachDB. This has been planned for sometime anyway, but it has to be done for cockroach support. Resolved by dcrpg: meta table versioning #1292
• Updating docs (some PostgreSQL syntax is not supported, such as CREATE DATABASE dcrdata OWNER dcrdata.) https://github.com/decred/dcrdata/wiki/CockroachDB
• SERIAL in CockroachDB does not create a sequence (by default). CockroachDB support #1294
• DELETE in CockroachDB does not support the USING clause (like a JOIN). https://www.cockroachlabs.com/docs/stable/delete.html CockroachDB support #1294
• Empty arrays made using the ARRAY[] constructor in CockroachDB require type annotation (e.g. ARRAY[]:::BIGINT[] with three : characters for annotation instead of conversion), a concept that does not exist in PostgreSQL). May need to use the literal format like SELECT '{}'::int[]; or whatever works for both PostgreSQL and CockroachDB. CockroachDB support #1294
• unknown function: cardinality() for RetrieveUTXOs CockroachDB support #1294
• Deleting duplicate rows using most common methods fails to due to CRDB memory use. CockroachDB support #1294
• A faster solution for deleting duplicates is needed. CockroachDB support #1294 makes a new temporary table using the distinct rows from the unindexed vins and vouts tables. This is incredibly slow.
• Dropping a unique index that is used for a constrain on an existing table requires the CASCADE clause. CockroachDB support #1294
• Slow INSERT. Performance is about 1/5 of PostgreSQL. Tried multi-line inserts https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview.html to no avail: https://github.com/decred/dcrdata/compare/3625acdcd48121bfe5b80c45a7fb7710cf6bdd40...chappjc:multi-line-inserts?expand=1

These changes are just what I can come up with now, so it is very likely that there will be many more roadblocks along the way.

[chappjc]

Quoting cockroachdb/cockroach#23061 (comment) in a thread about CockroachDB vs. PostgreSQL performance:

Your comparing a database ( PostgreSQL) that has had 21 years of development and is highly optimized for single system performance. If you go back 8 years in time, PostgreSQL lost hard to even MySql in every benchmark. They did a great job at fixing postgresql its performance issues.

Compare this to CockroachDB that is barely 3 year old. And has a totally different design philosophy.

CockroachDB is power is not speed. Its the ability to get a replicating, sharding database up and running with minimal effort. CockroachDB has lots of nifty features. Its the same reason why a lot of people started using PostgreSQL in the past. Because PostgreSQL was able to do things, that Mysql did not do ( or did not follow the standards ).

I doubt that CockroachDB will ever get the same performance in single system performance compared to PostgreSQL / Mysql. The real gain is again the ability to easily scale with more databases horizontally and still have a secure system for your data.

Try clustering on PostgreSQL vs CockroachDB and your results will be much more interesting. That is after you spend a few hours setting up clustering on PostgreSQL. Watch what happens when a PostgreSQL master or a slave drops. Or try having Master - Master on PostgreSQL. -1

Think of it like this: What is more valuable. The money you spend on a extra server or the money you spend on a database engineer. The extra server is maybe 100 a 200$ per month, where as the database engineer is going to be way more.

If your planning on running a single server website, then stick with MySQL / PostgreSQL / ... as they are heavily optimized the last 20 years for that task. If your goal is to expand your hosting horizontally, then MySQL / PostgreSQL / ... are really not the best choice.

Now some points about dcrdata:

• dcrdata does not even support (yet) read-only dcrdata instances. Each dcrdata backend maintains its own PostgreSQL database.
• It is not important for the dcrdata hosts to share the same DB backend. They area all processing the same data by virtual of being connected to the distributed Decred network (via dcrd).
• Clustering and replication of the database for availability and fault tolerance is a non-issue since the entire DB is rebuilt from blockchain data.
• Performance is very important for dcrdata, both the initial sync (inserts) and queries from the frontend.

[ukane-philemon]

@chappjc is this still a concern? so far there hasn't been a clear use case for cockroachDB, plus dcrdata has been using postgresql for this long.
What are the benefits of providing support for cockroachDB on dcrdata?

[chappjc]

No we are ditching cockroach. It's not performant.

[ukane-philemon]

@chappjc there are unused function for cockroachDB, a new issue should be opened to clean-up codebase?

[chappjc]

Yah, that would be fine