How Online Primary Key Changes Work in CockroachDB

How Online Primary Key Changes Work in CockroachDB

As of our 20.1 release, CockroachDB supports online primary key changes. This means that it’s now possible to change the primary key of a table while performing read and write operations on the table. In this blog, we’ll review how data is stored in CockroachDB and how we overcame technical challenges involved in getting online schema changes to work in CockroachDB. Get your essays to stand out from the rest with impressive covers. Deliver quality projects with an impeccable presentation and guarantees the best results. Choose one of our templates and customize it in seconds. Complete your Working at Heights training online. Print off your Certificate on successful completion. More info? Call 1300 307 445. Find your career fit in B.C. Search BC jobs with the WorkBC job board and job alerts. Bring careers to life with WorkBC’s Career Trek videos and profiles. ad by HBR Ascend

Get blog posts to your inbox.

As of our 20.1 release, CockroachDB supports online primary key changes. This means that it is now possible to change the primary key of a table while performing read and write operations on the table. Online primary key changes make it easier to transition an application from being single-region to multi-region, or to iterate on the schema of an application with no down time. Let’s dive into the technical challenges behind this feature, and our approach to solving them. As part of the deep dive, let’s review how data is stored in CockroachDB, and how online schema changes work in CockroachDB.

In many database systems, a primary key is just another index on a table, with extra NOT NULL and unique constraints. Changing the primary key of a table usually just involves changing what index is denoted as the primary key. In CockroachDB, the primary key is special — all the data for a table is stored on disk as part of the primary key.

In order to see how and why CockroachDB stores all data in the primary key, let’s dive into how data storage works in the database. The SQL layer of CockroachDB is built on top of a distributed, transactionally consistent key-value store (for the rest of the blog post we’ll refer to key-value as KV). The SQL system in CockroachDB uses this KV map to store SQL table data and indexes. Let’s take a look at what a table and some corresponding data would look like in CockroachDB. Consider the following table and data:

In CockroachDB, row entries in the table are stored as KV pairs. The primary key columns form the key, and the remaining columns form the value. A row of data would be stored something like this:

In order to change the primary key of a table, we have to rewrite the table data so that the KV pairs are organized according to the new primary key columns. Secondary indexes are stored in a similar way as the primary key — the indexed columns form the key, and the value is generally empty. This encoding strategy limits data duplication, as only the indexed columns are duplicated. If we created an index using CREATE INDEX i ON t (y, z) the KV’s for index i would look something like:

However, if we use a secondary index in a query to find a particular row, we need to be able to find the rest of the row’s data from the secondary index entry. In order to “point back” to the primary index entry for a row, all secondary indexes need to implicitly contain all of the primary key columns. After including the remaining primary key columns, our index entries end up looking  like:

So our KV’s for the primary index and secondary index i together look like:

For more details on CockroachDB’s architecture take a look at our docs or an architecture overview webinar.

This strategy for encoding table data means that there is an implicit dependency between the indexes of a table and the table’s primary index. If we want to change the primary index of a table, we need to change it and all the indexes that depend on it. For example, if we changed the primary key of t to be w we’d also need to update i to contain w and not x The difficult part of performing this schema change is ensuring that users of the table only observe the table in a consistent state either before or after the primary key has changed, and that all operations on the table during the primary key change return correct data.

Let’s examine how online schema changes work in CockroachDB, and how we can build support for primary key changes on top of this infrastructure.

To perform a primary key change in CockroachDB, the primary key and some new indexes must be rebuilt. To do this, we can take advantage of the existing online schema change infrastructure, so let’s dive into how it works.

When adding a new schema element to a table (like an index), CockroachDB won’t just add the element into the metadata of the table. In a distributed system, doing so could result in data inconsistency between nodes in the system. Talks of data inconsistency may seem out of place as CockroachDB is a serializable and strongly consistent database. However, CockroachDB needs to cache table metadata in order to be performant. Without caching this metadata, a node would need to perform a KV operation every time it needed to access information about a table! As with any cache, it needs to be invalidated when the cached data becomes stale. With this in mind, the data inconsistency mentioned above could happen in the following way:

  • Node 1 adds an index i to a table t and starts to build it.
  • Node 2 inserts value v into t before its metadata for t is invalidated. Since node 2 has no idea that i exists, it doesn’t make an entry for v in i.
  • Node 1 attempts to read i after it is done building and cannot find v!
  • In order to safely perform schema changes, CockroachDB progresses schema elements through a series of states, where the new element is finally added at the end of this series. A key part of this state machine is that at any point during the schema change, all nodes are at most 1 state apart in the state machine. If this state machine is an ordered set of states S then nodes at any point in the schema change must have either state S_i or S_{i+1} In CockroachDB, these states are associated with different capabilities on the schema object. The capabilities of each state describe which operations on the table will also be applied to the in-progress schema element. These states are:

  • Delete Only (referred to as D). Schema objects in this state service only deletion operations.
  • Delete and Write Only (referred to as DW). Schema objects in this state service only write and deletion operations.
  • All (referred to as R). Schema objects in this state are now “public” and are accessible for all operations: reads, writes and deletions.
  • To begin an index creation, CockroachDB adds the index to the table’s metadata in state D which starts the schema change state machine. Once all nodes in the cluster have agreed on the index’s state, the index progresses into the DW state. CockroachDB starts to backfill existing data into the index in the DW state, and forwards incoming writes to the index. Upon completion of the backfill and all nodes agreeing on the state DW, the index is moved to state R. Once every node agrees on state R the schema change is complete.

    The procedure to drop a schema element is the same process as adding a schema element, but done in reverse. Instead of incrementally adding capabilities to a schema element, we incrementally remove capabilities until the element has no capabilities. An object with no capabilities is safe to drop.

    To get some intuition about how this series of states ensures that new schema elements have consistent data, we can examine a few cases during the process of adding an index.

    Example 1: Mixed D and DW states.

    Consider two nodes N_1 and N_2 where N_1holds an index i of table t in state D and the N_1 holds i in state DW The state machine in this case ensures that there are no spurious index entries. All write requests sent to N_2will create an entry in the i for the written data. Because of the capabilities assigned to N_1 all delete requests sent to N_1 will also delete the index entry, ensuring that i is not left with a dangling index entry.

    Example 2: Mixed DW and R states.

    Consider two nodes N_1 and N_2 where N_1 holds an index i of table t in state DW and N_2 holds i in state R In this case, the state machine ensures that there are no missing index entries for reads. Because all writes to t on N_1 will be written to i reads on i from N_2 will find the written values.

    For a different perspective on online schema changes, and some extra topics like schema caching and node failure recovery during schema changes, take a look at our previous blog post on online schema changes!

    The schema change system in CockroachDB can support adding and dropping schema elements, but performing an online primary key change requires doing more. In particular, we have all the requirements of an online schema change, but also need to ensure that all nodes in the system either view the target table as completely before or completely after the primary key change. A node must not see that a table has the new primary key, and no updated indexes, or vice versa. Additionally, the whole CockroachDB cluster needs to operate correctly if some nodes don’t know that the primary key has been changed.

    Looking closely at what goes into a primary key change, there are three distinct steps that need to be performed:

  • We need to build the new primary index along with the new versions of dependent indexes.
  • Once the new primary key and indexes are ready to be used, we need to swap out what is designated as the primary key, and update all indexes to the new index versions.
  • We need to drop the old primary key and indexes.
  • An important point to notice is that the end of step 1 must be performed along with step 2 and the beginning of step 3. Specifically, as our new indexes are promoted into a readable state, the old indexes need to be demoted out of the readable state. Doing this sequence of steps as an atomic operation will ensure that a single node sees a consistent and valid view of the table — either before or after the swap. The example below details what requests to different nodes view as the primary key of the table during this process, where old and new are the old and new primary keys of the table.

    As the previous diagram shows, there are states where some nodes view the primary key of the table before the swap, and some nodes will view it after. Let’s look at how all operations on the database will return consistent data in this mixed primary key state.

    For simplicity, consider a primary key change where only a new primary key is built. Consider two nodes N_1 and N_2 as well as old and new primary keys on a table t old and new.

    After the swap in step 2 commits, some nodes will view old demoted to state DW and new promoted to state R and other nodes will view old in state R and new waiting to be promoted to R. Assume that N_1 views the former, and N_2 views the latter. 

    This new state configuration ensures both of the properties from earlier — no dangling index entries, and no missed index entries. All mutation operations that either node issues will also be applied to whichever index the node thinks is not the current primary index. This ensures that all written values can be read by either node, and that deletes from either node won’t leave any dangling entries.

    This is a neat extension of the properties discussed in the section about general online schema changes. A schema change progresses safely if the only states of a schema element present in a cluster are 1 state “apart” from each other in the state machine. This property is maintained by the swapping operation:

  • The new primary key transitions from DW to R.
  • The old primary key transitions from R to DW.
  • Because both the schema elements transition to a state 1 step away in the state machine, we can combine the guarantees on both transitions to ensure the resulting schema change is safe!

    Implementing online primary key changes required some leg work to play nicely with a variety of features in CockroachDB. I’d like to share some of the more interesting feature integrations that went on during development of online primary key changes.

  • Column Families. In CockroachDB, only primary indexes respected KV level separation described by the column families on a table. The process for primary key changes involves building a secondary index like a primary index, and then switching which index the table thinks is “primary”. This process involved teaching secondary indexes in CockroachDB how to respect column families, as well ensuring that secondary indexes without families in existing CockroachDB clusters are still usable after a version upgrade.
  • Foreign Keys. Our representation of foreign key metadata had to be changed to ensure that nodes could find valid indexes to use for foreign key constraint validation in the mixed index/primary key version state detailed above. This work involved tricky reasoning about metadata representations in mixed version clusters.
  • Interleaved Tables. In CockroachDB, it is possible to store a table within the key space of another table to optimize joint access using interleaving. Getting primary key changes to work with interleaved tables had the unexpected result of allowing users to “uninterleave” tables from undesired configurations in an online manner.
  • Other Schema Changes. The ability to change the primary key of a table may have opened up ways to perform other bulk operations within CockroachDB more efficiently. For example, adding or dropping columns could be viewed as creating a new primary key with the desired new column. Structuring bulk operations this way could lead to faster implementations that interact better with the underlying storage layer of CockroachDB. 
  • Even on a serializable and consistent KV store, table metadata caching makes performing online schema changes difficult. Changing the primary key of a table is particularly challenging due to its nature as the primary data source of the table, as well as the implicit dependency that other indexes on the table have on it. Our approach was to extend the state machine based approach used for standard online schema changes with an atomic “swapping” operation and show that guarantees about correctness are maintained.

    If building distributed SQL engines is your fancy, we’re hiring! Take a look at our open positions here.


    Author: Written by Rohan Yadav

    Design cover pages online for free

    Design cover pages online for free

    Now that you have done the hardest part, you should create a cover page that is up to the rest of the project. The grade of the work can vary from 0 to 100 depending on the first impression the teacher who corrects it takes. Create an impressive cover page for an essay in just a few minutes with EDIT. Give your essay a professional and unique touch. A well-designed title page can positively impact the impression your professor has on your homework, which can impact your grade for the better. 

    work cover blue

    work cover blue marketing

    We know that not everyone is a professional designer and that’s why we want to help you. The presentation can influence teachers when they are evaluating the work done by a student. Having a professional title page can give the impression that you’ve put a great deal of time and effort into your assignment. Thanks to EDIT everyone can be a professional designer and have incredible covers. This way you’ll just have to worry about doing a great job on your assignment.

    Within EDIT you’ll find hundreds of fully customizable templates that will help you to create your own cover. You just need to choose what style you like best and “make it yours”. All you need to do is change the information so its unique to you. We guarantee a spectacular result, but if you can take the extra step by adding images related to the work in just one click. Start designing by following these steps:

  • Open the editor and choose the option “Create a cover page”
  • Choose the template that best suits the project.
  • You can add own images or change the background color of the template.
  • Add the information of your thesis and change the font or colors as you’d like.
  • The cover page is ready to make your work shine.
  • As you can see, it is very easy to create cover pages for school works with EDIT and also it won’t take you much time. You can customize the designs to the maximum, they won’t look like any of the templates you can download from the Internet.

    We recommend using the same colors on the cover like the ones that you have used during the essay so that you will achieve a cohesive design for your work. It’s also important to add the name and logo of the institution for which you are doing the essay. It’ll be as easy as uploading the file to EDIT and placing it in the space reserved for it. You won’t waste half a minute doing it and will get your work to the next level. A visually attractive project is likely to be graded very well. Taking care of the little details will make your work look put together and professional.

    Also in EDIT you can keep and reuse all your designs and adapt them to different projects. Thanks to the internal memory that you’ll have in your profile, you’ll be able to access and edit old designs anytime and anywhere. If you have not had time to design the cover page before submitting the work, don’t worry you can do it in less than a minute.

    EDIT is created so you won’t need to know anything about design to achieve a spectacular design!

    Take a look at the other options we propose on the web. EDIT can help you to design flyers, business cards, and other designs that can be useful in the workplace. If you are passionate about the design world but have never known how to evolve with it, then welcome to EDIT. You have found your place.


    Working at Heights Training Online (VOC Refresher: RIIWHS204D)

    Working at Heights Training Online (VOC Refresher: RIIWHS204D)

    Welcome to the Work Safely at Heights Refresher / Verification of Competency (VOC) Training. There are a large number of risks and dangers for anybody working at heights. This course has been designed to give you an understanding of these risks and the system in place to help keep you safe.

    This course is designed for those who are required to work at heights as part of their role. The aim of the course is to give you a safe working philosophy for when you need to perform work at heights.

    Features of this refresher training include:

    – Download a VOC Certificate of Completion on successful completion

    – A Digital Wallet ID VOC Card is also issued once all your documentation is verified (see ‘John Citizen’ sample)

    – Verification of competency by our RTO partner, AlertForce RTO Code 91826.

    This training addresses the theoretical component of working at heights. This training does not give you the practical skills required to perform work at heights. To achieve accreditation, you should complete an in-person training session with a trainer accredited to deliver work at heights training.

    If you perform work at heights before completing your in-person training you could endanger your safety and the safety of those around you.

    This course delivers the theoretical component of RIIWHS204D Work safely at heights and will provide you with information about:

  • the legal requirements for working safely at heights and where you can find out more about them
  • how to select, use and maintain fall protective and rescue equipment
  • the process and procedures to follow to ensure you minimise risk, and
  • how to plan for emergency situations

      Note: Whilst a Statement of Attainments do not expire, different worksites have different policies and requirements of what refresher training should be. You should check if you are unsure. This refresher course is a Verification of Competency undertaken by a Registered Training Organisation (RTO Code 91826).

    • the principles to bear in mind when working at heights
    • safety procedures for work at heights
    • risk assessment procedures
    • fall protection equipment
    • working at heights equipment, and
    • emergency response
    • The equipment and tools we’ll be discussing will include:

      Activities and quizzes appear throughout the course and are part of your assessment. They must be successfully completed to complete the course.

      A Certificate of Completion is issued on successful completion of the course and you are required to get an 80% pass mark to be deemed as understanding the content. It is recommended that training is conducted every 2 years to stay refreshed.

      Entry requirements:

      1. You must upload 100 points of ID

      2. You must upload a previously issued with a Statement of Attainment (or wallet card) by an RTO less than 3 years ago or be able to present a CV that demonstrates industry currency.

      NOTE: this training is suitable as refresher if you have previously obtained a Unit of Competency in any of these qualifications:

    • RIIWHS204D Work Safely at Heights
    • RIIOHS204A Work Safely at Heights
    • You need to previously have held the RIIWHS204D Statement of Attainment. That statement along with this Refresher Training serves as your refresher evidence. 


        WorkBC - Jobs and Careers

        WorkBC – Jobs and Careers

        When you select a value from the salary drop-down menu, that figure is automatically converted to the equivalent annual salary even when a pay period other than Annually is selected.

        The conversion is calculated as follows:

      • Hourly – hourly rate is multiplied by 2080 (approximate number of work hours in a year)
      • Weekly – weekly rate is multiplied by 52 (number of weeks in a year)
      • Bi-Weekly – bi-weekly rate is multiplied by 26 (half the number of weeks in a year)
      • Monthly – monthly rate is multiplied by 12 (number of months in a year)
      • Using this conversion, the search is able to return results matching your salary requirements regardless of whether you select Annually, Hourly, Weekly, Bi-weekly or Monthly as the pay period. The search will include all jobs posted with a salary equal to or higher than the amount selected.

        Example: A user searches for salaries: $15 hourly. This will be converted by multiplying $15 by the conversion factor of 2080 (in this case, approximate number of work hours in a year)

        Salary searches apply to jobs where employers publicly posted salary amounts (e.g., annual, monthly, bi-weekly, weekly or hourly). Note: Some employers do not specify a salary amount.


        What are the trusted sites to do online part time jobs?

        What are the trusted sites to do online part time jobs?

        Ekakshra Roy

        I’d like to share my experience of working online and discuss some websites where you can earn money.

        If you have skills such as web designing, coding, etc. you can work as a freelancer at websites such as:

        Freelancer: Unlike most other platforms, in addition to offering millions of projects, Freelancer allows you to compete with other freelancers in contests to prove your skills. If you’re competitive and confident in your expertise, it’s a great way to showcase your abilities and attract more clients.

        Upwork: With over 1.5 million clients, Upwork (previously oDesk) offers something for every…


        I’d like to share my experience of working online and discuss some websites where you can earn money.

        If you have skills such as web designing, coding, etc. you can work as a freelancer at websites such as:

        Freelancer: Unlike most other platforms, in addition to offering millions of projects, Freelancer allows you to compete with other freelancers in contests to prove your skills. If you’re competitive and confident in your expertise, it’s a great way to showcase your abilities and attract more clients.

        Upwork: With over 1.5 million clients, Upwork (previously oDesk) offers something for every type of freelancer. It accommodates both short- and long-term projects, hourly or per-project work and expert-level and entry-level engagements. Regardless of where you are in your career, Upwork is likely to have something for you.

        Transtutors: I started working at Transtutors as an affiliate and the first salary I received from them was $400 for a week. I was working as an affiliate with them. I was boosting their market visibility which did not require much effort and had a decent payout.

        Toptal: With a distinctly different approach than the other services on this list, Toptal is for seasoned, talented freelancers. Passing Toptal’s screening process gives you unparalleled access to meaningful projects with great clients (JPMorgan, Zendesk, Airbnb, etc.) and fair compensation (no low-bid contests). You’ll also be able to join the Toptal community for frequent meetups and tech events.

        Freelance Writing Gigs: Whether you’re a writer, editor, blogger, publisher or any combination of those, Freelance Writing Gigs is a great option for freelancers who have a way with words.

        PeoplePerHour: This is a great platform, focusing on freelancing for web projects. If you’re a designer, web developer, SEO specialist, etc., peopleperhour is definitely worth checking out.


        How Online Primary Key Changes Work in CockroachDB

        Leave a Reply

        Your email address will not be published. Required fields are marked *