Does TiDB support global secondary index like DynamoDB?

[sykp241095]

Original author: prclqz
Original time: 2023-10-06 18:00:55.762464
Original ID/Slug: 708 / does-tidb-support-global-secondary-index-like-dynamodb

I couldn’t find any related docs, so it seems no?

The “Global Secondary Index” here means that it allow to use a different partition key than the primary partitioning.

For example, for a table with just three colunms: user(userId varchar, email varchar, others varchar, PK userId, Partition(userId))

I can see that a “local” secondary index as <userId, email> works because it has the local partition key userId.

But I also want to have another “global” index as . So that I can get a record when just with an email, without knowing the userId. If I creates an index <email, userId> I guess it won’t works as I expected for performance reasons? The query like "Select * from user where email = "abc@efg.com " will essentially have to run in every partition.

What I am hoping is that, when creating the index , it will creates another partition key with just email. And the index data doesn’t have to be strong consistent. Eventual consistent is enough. This is how it works in DynamoDB.

LMK if that’s supported. I am very interested in using TiDB if this is available.
Thanks

[sykp241095]

Original author: TiDB-AI-Assistant
Original time: 2023-10-06 18:01:08.613764

TiDB does support secondary indexes, but it does not have the concept of “global secondary index” like DynamoDB. In TiDB, secondary indexes are created on a per-table basis and are not partitioned separately from the primary partitioning.

In your example, if you create a secondary index on the email column, it will be a local secondary index and will be partitioned based on the primary partitioning key (userId). This means that the query SELECT * FROM user WHERE email = "abc@efg.com" will still need to be executed on every partition, which may not meet your performance expectations.

TiDB does not currently provide a built-in mechanism for creating a separate partition key for a secondary index. However, you can consider using a different table structure or data modeling approach to achieve a similar result. For example, you could create a separate table with the email column as the primary key, and use it as a lookup table to retrieve the corresponding userId when querying by email. This approach would allow you to perform efficient queries on the email column without having to scan all partitions.

Please note that TiDB’s secondary indexes provide strong consistency, not eventual consistency like DynamoDB. If you require eventual consistency, you would need to implement a custom solution on top of TiDB.

I hope this clarifies the capabilities of TiDB’s secondary indexes. If you have any further questions, please let me know.

[sykp241095]

Original author: prclqz
Original time: 2023-10-06 18:04:32.69735

TiDB AI Assistant:

However, you can consider using a different table structure or data modeling approach to achieve a similar result. For example, you could create a separate table with the email column as the primary key, and use it as a lookup table to retrieve the corresponding userId when querying by email.

This is a typical workaround. But it will increase the latency when using transaction to update the two tables across partitions. Unless using CDC behind that to update the additional table. That’s why DynamoDB built that (and likely with CDC ) to make it much more friendly.
Thanks for the reply. I hope that can be in the future plan.

[sykp241095]

Original author: TiDB-AI-Assistant
Original time: 2023-10-06 18:04:42.004336

I understand your concern about the potential increase in latency when using a separate table for lookup. Indeed, using a separate table for lookup can introduce additional complexity and overhead, especially when updating both tables within a transaction.

It's worth noting that TiDB is an open-source distributed database that is continuously evolving and improving. The TiDB community is actively working on enhancing its features and capabilities based on user feedback and requirements.

I appreciate your feedback and suggestion regarding the support for a "global secondary index" feature similar to DynamoDB. I will pass this feedback along to the TiDB team for consideration in future development plans.

If you have any more questions or need further assistance, please feel free to ask. Thank you!

[sykp241095]

Original author: winkyao
Original time: 2023-10-07 08:46:15.884291

Hi prclqz, I reviewed the answer from TiDB-Bot, it was not that correct. TiDB actually will not create partition if don’t explicitly require it by using ‘partition table’: Partitioning | PingCAP Docs. TiDB use primary key or implicit primary key(we called it row id) to locate the data in the storage, and the secondary index is also supported just like MySQL does.

so in your example here:

But I also want to have another “global” index as . So that I can get a record when just with an email, without knowing the userId. If I creates an index <email, userId> I guess it won’t works as I expected for performance reasons? The query like "Select * from user where email = "abc@efg.com " will essentially have to run in every partition.

if you don’t have an index for <email, userId>, and you only give the email value, tidb can’t use the primary key to locate the row data. So it will execute as a table scan to find the exact data.

[sykp241095]

Original author: Mini256
Original time: 2023-10-09 03:16:01.124247

The concepts of TIDB and Dynamodb are different about partition.

In DynamoDB, the database determines the location of data in physical storage through a Partition Key, but in the underlying implementation of TiDB, it uses another concept called Region to distribute data across different physical machines.

You can learn more through concept documents: TiDB Storage | PingCAP Docs

---

I am not sure about the amount of data you need to process. Assuming that the data volume of a table does not exceed 100 million, you may not need to use TiDB’s partition table.

You can create your data table using the following SQL:

CREATE TABLE test1 (
    user_id INT PRIMARY KEY,
    email VARCHAR(40),
    others VARCHAR(255)
)

TiDB will automatically divides data into different regions based on the primary key user_id.

[sykp241095]

Original author: prclqz
Original time: 2023-10-09 03:44:21.86957

Do you meant that will provide the same performance & scalability compared to using GSI in DynamoDB, for query like "Select * from user where email = "abc@efg.com " ?

I am just making an example to help you understand what I meant by “Global Secondary Index”. The real use case is much more complicated than that. e…g the userId will be a VARCHAR instead of an integer. And we will have both heavy query using the PK and the above query for GSI.

[sykp241095]

Original author: Mini256
Original time: 2023-10-10 08:35:08.052504

Yes, TiDB achieves performance and scalability through the regions mechanism (compare to the partitions in DynamoDB), but this is transparent to users. Users can create data tables and indexes like they do with standalone MySQL, without the need for users to learn a new concept (like “Global Secondary Index”).

You can learn more about the differences between TiDB and DynamoDB through the following article:

  <a href="https://dataturbo.medium.com/compare-tidb-with-dynamodb-2478266bbddd" target="_blank" rel="noopener nofollow ugc" title="09:36AM - 08 October 2022">Medium – 8 Oct 22</a>

Compare TiDB with DynamoDB

Preface

Reading time: 7 min read

image1448×2130 484 KB

Regarding actual performance, this requires a POC to verify.This depends on many factors, including the type of query you execute and the configuration of the machine you used.If you encounter problems in the POC process, you can continue to contact us.

[sykp241095]

Original author: Mini256
Original time: 2023-10-10 08:41:21.741496

A small demo

Here is a single table with 360 million records on TiDB Serverless, and I created a secondary index for the repo_name column, and then performed a query with condition WHERE repo_name='pincgap/tidb':

image2230×2090 355 KB

Online Demo: https://ossinsight.io/

[sykp241095]

Original author: prclqz
Original time: 2023-10-10 14:51:35.993258

Thanks. With your example, does the query run against all the partitions and aggregate the results? If so it will hit the limit very soon if there are hundreds of queries like this running in parallel, and each query would expect thousands of rows (with ordering).

The perf not just a single query , but in parallel queries. And we also need sorted results. That’s what I need for my use case. (I may provide a poc here in the future)

You can see how the ddb GSI achieved this Using Global Secondary Indexes in DynamoDB - Amazon DynamoDB I don’t think TiDB has this yet.

[sykp241095]

Original author: Mini256
Original time: 2023-10-11 07:10:18.483259

In my example, the data is stored in a github_repos table (note: this is not a partition table), and the data in the table will be split into multiple regions in TiDB (= the partitions in DynamoDB).

When we create a secondary index index_gr_on_repo_name for the github_repos table, it doesn’t create a local secondary index on each region, it creates a secondary index globally, so when we query the data through the index, it’s NOT run against all regions and then aggregated to get the results.

[sykp241095]

Original author: Mini256
Original time: 2023-10-12 07:13:33.875961

Additionally, if your application does require the use of a partitioned table and you want to build a global index on the partitioned table, you are welcome to describe your scenario and requirements on this feature issue:

  <a href="https://github.com/pingcap/tidb/issues/45133" target="_blank" rel="noopener nofollow ugc">github.com/pingcap/tidb</a>

Global Index for partitioned tables

<div class="github-info">
  <div class="date">
    opened <span class="discourse-local-date" data-format="ll" data-date="2023-07-03" data-time="10:45:39" data-timezone="UTC">10:45AM - 03 Jul 23 UTC</span>
  </div>


  <div class="user">
    <a href="https://github.com/mjonss" target="_blank" rel="noopener nofollow ugc">
      <img alt="mjonss" src="https://raw.githubusercontent.com/pingcap-inc/ask.pingcap.com-uploads/main/default/original/1X/98274c3e292caef1b6bb2f34886175a87919cb70.jpeg" class="onebox-avatar-inline" width="20" height="20" data-dominant-color="888387">
      mjonss
    </a>
  </div>
</div>

<div class="labels">
    <span style="display:inline-block;margin-top:2px;background-color: #B8B8B8;padding: 2px;border-radius: 4px;color: #fff;margin-left: 3px;">
      type/enhancement
    </span>
    <span style="display:inline-block;margin-top:2px;background-color: #B8B8B8;padding: 2px;border-radius: 4px;color: #fff;margin-left: 3px;">
      component/tablepartition
    </span>
</div>

## Enhancement

Development tasks for:
Make GLOBAL INDEX General Available

…
Stage 1 - full implementation, but Experimental

• Change syntax, to enable (default) LOCAL index, and (explicit) GLOBAL index
• Move enable global index from configuration to experimental feature flag, tidb_enable_global_index system variable #48825
• Add tests for all index access plans
  • Create issues for handling the index access plans currently not supporting Global indexes (first disable the specific plan, then add support).
• Add support for check index/check table
• Add tests for all write transactions (select lock, commit, rollback, different transaction modes, different transaction levels) * (insert, update, delete, select for update, single/multi statement transaction).

Stage 2 - Stabilise / General Available

• Fix reported bugs

• Adjust syntax if needed

• Add more tests if needed

• Enable by default

• Remove experimental flag

[sykp241095]

Original author: Frank_Shen
Original time: 2024-10-09 02:52:58.50951

Hi @prclqz, TiDB has supported the Global Index since v8.3. More

[sykp241095]

Original author: prclqz
Original time: 2024-10-09 04:23:03.139313

Wow, great job!! (Impressed on how fast you can build out an important feature like this, which took DynamoDB many years …)

Thanks again, I will try it out