[YSQL] Geo-partitioned tables

[ndeodhar]

Jira Link: DB-1590
Add support for PostgreSQL-style partitions and row-level geo-partitioned tables. Note that table partitioning support is a pre-requisite for row-level geo-partitioning. With this feature, we can control the placement of rows into partitions as well as the physical placement of partitions (into different geographic regions for example). This can help with the following:

• Reduce latencies by keeping data closer to users
• Simplify implementing regulatory compliance such as GDPR
• Tier data (hot / cold, old / new) to appropriate tiers

Prerequisites

Status	Feature
✅	Design doc for YSQL table partitioning
⬜️	Design doc for geo-partitioned tables

Phase 1 - Table Partitions

Status	Feature
✅	Support creating LIST partitions and basic operations (INSERT, UPDATE, SELECT, DELETE)
✅	Support RANGE and HASH partitions (note these are different from range and hash shards)
✅	Other operations and options on partitions (attach and detaching partitions, sub-partitions)
✅	Support cross-partition UPDATE operations
✅	Port over PostgreSQL unit tests to ensure most features are covered and work as expected

Phase 2 - Geo-partitioning support

Status	Feature	GitHub Issue	Comments
✅	Ability to override placement policy at a per-partition (per-table) level in DocDB	-	PR #5368
✅	YSQL syntax (Tablespaces) to query/specify placement info for a table	#6497, #6638	-

Phase 3 - Further Enhancements

Status	Feature	GitHub Issue	Comments
⬜️	Support a way to read from local partitions first	#10477	Main task complete, enhancements in progress
✅	Geo-partitioning support for the transaction status table	#9980	Complete
⬜️	Support per-region backup	#8732	Backend changes complete, UI changes pending
⬜️	Remove dependency on WHERE clause	#11688	-
⬜️	Support unique key constraint across partitions	-
⬜️	Support colocation of geo-partitioned tables	#5823	-

[saulfrank]

Would love to see this implemented. Closes a big gap between Mongodb geo sharding and Cockroach db geo partitioning... it also solves some pretty fundamental data compliance for GDPR purposes.

[nvcnvn]

Sorry for the noise.
I see this tickets in v2.1 milestone, but v2.1 already out.
Is this mean this feature will be release in v2.1.x?

Maybe better to ask this question on your slack?

[rkarthik007]

Hi @nvcnvn,

Asking here is fine! This feature is well underway, but it does not yet have a release target date yet.

• The phase 1 of this feature is under review here: Row-level partitioning support in YSQL #3619
  @ndeodhar can give an idea of the expected timeline on this phase.

• Phase 2 of this feature will follow after, we still need to put out a design and timeline for this.

Hope that helps. And would be happy to discuss your timelines/needs in the Slack channel.

PS: the set of items being worked on is now here.