[Feature]: Support for modifying partition schemas and add new partitions via TDS endpoint

[PauloHMattos]

Description

Currently, Babelfish supports creating table partitions (via CREATE PARTITION FUNCTION and CREATE PARTITION SCHEME). However, when attempting to modify the partition schema to accommodate new data using T-SQL commands like ALTER PARTITION FUNCTION ... and ALTER PARTITION SCHEME ..., the operations are not supported over the TDS endpoint. This prevents us from doing ongoing partition maintenance using standard SQL Server syntax.

We would like full support for T-SQL partition modification commands over the TDS endpoint so that we can add new partitions to existing partitioned tables without leaving the SQL Server context.

Possible workaround

Currently, our assumed workaround is to connect directly to the PostgreSQL port and manage the addition of new partitions using native PostgreSQL declarative partitioning syntax.

Could the maintainers confirm if:

• This is the recommended and safest approach for ongoing partition management?
• Are there any catalog synchronization risks we should be aware of when doing this?

[PauloHMattos]

After a few days experimenting with this the only issues I identified are:

• Partitioned tables this way are not returned by the sys.tables catalog
• BulkCopy into partitioned tables fails
  • As documented here
• Babelfish requires the partition column to be part of the PK
  • Is a postgres requirement so is expected

[sumitj824]

Thank you for raising this issue and sharing your findings.
Regarding your question about whether connecting directly to the PostgreSQL port to manage partitions is the recommended approach — we would strongly advise against modifying tables from the PostgreSQL port that were created through the TDS endpoint.

Babelfish maintains additional metadata related to partitions in catalogs. Modifying the table from the PG endpoint will break this metadata consistency, so such operations are blocked.

Since ALTER PARTITION FUNCTION / ALTER PARTITION SCHEME aren't yet supported, the safest approach is to define all partition boundaries upfront during creation to accommodate future data growth.

[PauloHMattos]

@sumitj824 thank you for the clarification regarding the risks of using the PostgreSQL endpoint for partition management.

However, this puts us in a difficult architectural catch-22. Because ALTER PARTITION FUNCTION ... SPLIT RANGE is not yet supported via the TDS endpoint, our only purely T-SQL-compliant way is to massively over-partition the table upfront (e.g., pre-creating 5 to 10 years of daily partitions) to guarantee our inserts don't fail in the future and to meet strict data retention laws.

I'm worried that by pre-creating thousands of unused future partitions I would introduce performance penalties in the form of:

• Planner overhead: Substantial increases in query compilation time due to partition pruning evaluating thousands of boundaries.
• Memory/Locking Bloat: Unnecessary memory consumption and locking overhead during routine operations.
• Autovacuum lag: Forcing the autovacuum launcher to constantly track and process thousands of empty tables.

I'm not a PostgreSQL expert, so I ask based on your experience: is this worry founded, or would this not be an issue with PostgreSQL/Babelfish?

I also considered another workaround to maintain a sliding window, like we do on SQL Server. I was thinking about creating in T-SQL a new table with an updated partition scheme (current partitions + a reasonable future buffer), migrating the data, and swapping the tables. However, staying strictly within Babelfish T-SQL would force us to physically move the data via INSERT INTO ... SELECT. Because our dataset is very large, this physical data movement would be unfeasible.

My questions for the team:

1. To avoid this massive physical data movement, if we use the PostgreSQL endpoint strictly to run metadata operations, specifically using ALTER TABLE ... DETACH PARTITION on the old table and ATTACH PARTITION to the newly created buffered table, is this also strongly discouraged? Does detaching/attaching break the Babelfish sys catalogs in the exact same way as creating new partitions from scratch?
2. If all PG-endpoint partition workarounds are inherently unsafe for the catalogs, is there a planned milestone or timeline for supporting native T-SQL ALTER PARTITION FUNCTION ... SPLIT RANGE and MERGE RANGE over the TDS endpoint?

Knowing if this metadata swap is a viable bridge, or if native support is on the near-term roadmap, is critical for us to safely structure our multi-terabyte tables today.

Currently, we do not have a viable backup plan. Without a safe way to manage these partitions, our largest clients will have to remain on SQL Server until Babelfish fully supports the partition management operations, or we will be forced to roll the dice with the discouraged workarounds.

Thanks in advance!

[sumitj824]

@PauloHMattos Thanks for the detailed follow-up. I hope following answer your questions:

1. To avoid this massive physical data movement, if we use the PostgreSQL endpoint strictly to run metadata operations, specifically using ALTER TABLE ... DETACH PARTITION on the old table and ATTACH PARTITION to the newly created buffered table, is this also strongly discouraged? Does detaching/attaching break the Babelfish sys catalogs in the exact same way as creating new partitions from scratch?

Yes, same risk as previously mentioned. A Babelfish partitioned table is a normal PostgreSQL declarative-partitioned table underneath. But Babelfish maintains its own metadata catalogs, written only by the T-SQL DDL path and the T-SQL surface resolves from those, any operation from PG endpoint will break this metadata consistency.

2. If all PG-endpoint partition workarounds are inherently unsafe for the catalogs, is there a planned milestone or timeline for supporting native T-SQL ALTER PARTITION FUNCTION ... SPLIT RANGE and MERGE RANGE over the TDS endpoint?

We've noted this feature request and will prioritize it, but we cannot commit to a specific timeline at this point.

Regarding your concern on pre-creating thousands of empty partitions:
In PostgreSQL an empty partition is cheap to store, no heap data and no autovacuum overhead, though each one is still a full relation (its own catalog entries plus a child index per parent index). The cost shows up at query time: planning time and memory usage grow with the number of partitions that survive partition pruning, and each partition a session touches has its metadata cached in that session's memory.

PostgreSQL handles a few thousand partitions well as long as typical queries prune down to a small subset (i.e., they filter on the partition key). The problem case is queries that can't prune where the planner must open and lock many partitions at once. So a modest forward buffer sized to your ingestion horizon is fine, tens of thousands of mostly-unused partitions mainly risks
planning-time and memory overhead, not storage.

[PauloHMattos]

@sumitj824, thanks for the response!

This is exactly what we needed to know, and it definitely saves us from accidentally breaking our catalogs.

We'll steer clear of the PG endpoint for partition management. Instead, we'll go ahead with your advice and pre-create a few years' worth of partitions upfront as a buffer. Since empty partitions mostly just impact query planning, we'll be careful to ensure our queries are optimized for partition pruning to keep overhead low.

It’s good to hear that native T-SQL support for partition management is on the radar and being prioritized!
Fingers crossed it makes it into a release before we burn through our buffer. 🤞

Thanks again for pointing us in the right direction!