Best practices for greenfield projects

Hi there,

We have started a greenfield project using AWS Aurora Postgresql as our main DB.

We are still evaluating when (and if) we will switch to CRDB, but if we do we would like to have as little friction as possible in our code.

Now, is there any checklist we should follow to allow an easy migration in the future?

Things like (just an example, as I really don’t know what the recommendations are):

  • Add a Country and Domicile column in every table;
  • Don’t use “select for update”;
  • Don’t rely on external PG add-ons like PosGIS;
  • Use UTC (with timezone) for date columns;

I did a quick search on Google and didn’t find a good answer.

Thanks

Hi @jfbaro,

Since Postgres has been around for such a long time, there’s a very wide surface area of features that we will take a while to catch up on. For that reason, it’s difficult to specify a hard list of Postgres features to avoid. Our goal is to get to a place where for the most part, Postgres apps will just run on Cockroach with no fuss, and we’ve been working towards that steadily (for instance, our upcoming 2.0 release will include sequences). As far as ensuring that you’re not stepping outside the bounds of our compatibility, it’s difficult to give a precise answer since we’re constantly improving our compat, the only sure way I think would be to test against Cockroach every once in a while to make sure that your app works with it (and let us know the ways in which it doesn’t!).

As far as non-compatibility answers go, largely this question depends on what you expect to need to switch to Cockroach for. For instance, your example of storing the country and domicile - are you expecting to have to deal with GDPR restrictions? In that case, there are additional restrictions on how you need to structure your schema, since geopartitioning requires that the region a row is restricted to is derivable from a prefix of its primary key. Can you share some more details on what you’re thinking?

Thanks,
Justin

You could use CRDB as your Dev environment database to keep stay feature compatible. Not sure if this would have any consequences as there are some functions that are specific to CRDB that are not in Postgres as well, but as far as I can tell they are well documented.

We also have a list of the features we currently support in comparison to the sql standard: https://www.cockroachlabs.com/docs/dev/detailed-sql-support.html

We are also working on a guide meant to help users like you port from Postgres/understand where we might be a little bit different.

Without meaning to hijack this thread, besides SQL (in)compatibility, are there structure / data-architecture related things that should be taken into consideration?
If this isn’t related enough to the main question, I’ll open a new thread instead. :slight_smile:

I would also like to add a few other things:

  • Our application serves customers all around the globe, so GDPR is a major concern for us;
  • Some data will be the same for all regions (product catalogue, for instance), other will be located in one continent only (user data, for instance);
  • We also want to achieve as many 9’s as we can with CRDB;
  • Good performance is important;
  • CRDB will be used for transactional load only (OLTP);

jorygeerts, I have the same question. Any tip on that?

Thanks

Nope, just started playing around with CRDB last week for a project where we want a lot of (write) scalability. Since we’re using an ORM for the project, something that is more or less a drop-in replacement is is very interesting to us - but if we need to change the data structure a lot, that would mean CRDB wouldn’t be very drop-in-ish, hence my question.