Tuning Babelfish When the Client Speaks T-SQL but the Planner Is PostgreSQL

Babelfish is interesting because the application-facing experience looks familiar to a SQL Server engineer: TDS connectivity, T-SQL, SQL Server-oriented clients. But the planner and executor underneath are PostgreSQL. That makes performance incidents easy to approach with the wrong mental model.

A slow stored procedure may arrive through a SQL Server-shaped interface, but the useful questions are PostgreSQL questions: how many rows flowed through this node, was the estimate wrong, did a sort or hash spill, was a sequential scan reasonable, how much buffer activity occurred, which operator actually consumed time. The first task is to obtain a plan with the right level of evidence.

Configure Babelfish explain output§

Babelfish exposes configuration options that control plan detail. I normally start with:

EXECUTE sp_babelfish_configure 'explain_costs', 'on';
EXECUTE sp_babelfish_configure 'explain_verbose', 'on';
EXECUTE sp_babelfish_configure 'explain_buffers', 'on';
EXECUTE sp_babelfish_configure 'explain_format', 'json';

To inspect explain-related settings:

EXECUTE sp_babelfish_configure '%explain%';

The important distinction is the same one that matters in native PostgreSQL: do I want an estimated plan, or am I willing to execute the statement to get runtime evidence?

Estimated plan: do not execute the statement§

For a plan without executing the query:

SET BABELFISH_SHOWPLAN_ALL ON;

This is the safer starting point for expensive unknown queries, data-changing statements, or procedures where a representative production execution would be risky. It tells you what the optimizer intends to do. It does not tell you what actually happened at runtime.

Runtime plan: execute and measure§

When it is safe to run the statement and actual runtime evidence is required:

SET BABELFISH_STATISTICS PROFILE ON;

This executes the query and returns runtime statistics, filling a role similar to EXPLAIN ANALYZE in native PostgreSQL.

Make the plan readable§

I prefer JSON when downstream tooling accepts it:

EXECUTE sp_babelfish_configure 'explain_format', 'json';

PostgreSQL plan visualisers can make a large plan easier to navigate. One practical issue is that a tool may not accept the exact JSON shape Babelfish emits — in that case, switching to text can be simpler:

EXECUTE sp_babelfish_configure 'explain_format', 'text';

The visualiser is optional. The evidence is not.

What I look for first§

Where does row volume explode? Look for a node that receives a small input and produces or processes a much larger number of rows. Large intermediate rowsets make everything above them more expensive.

Where are estimates far from reality? With runtime statistics, compare estimated and actual row counts. Poor estimates can influence join type, join order, parallelism and memory decisions.

Are large tables being scanned? A sequential scan is not automatically wrong. The question is whether the number of rows required makes it reasonable, and whether available predicates can make useful use of an index.

Did sort or hash work spill? A spill is evidence that an operation exceeded the memory available to it, which naturally leads to PostgreSQL’s work_mem.

Test PostgreSQL settings from the Babelfish session§

A useful part of the workflow is changing a PostgreSQL setting at session scope without changing the whole instance:

SELECT set_config('work_mem', '64MB', false);

Verify it:

SELECT current_setting('work_mem');

The third argument controls scope — false for the current session, true for the current transaction — which makes it possible to test whether a sort or hash stops spilling when the session has more work_mem, without immediately making a global change.

The useful mental switch§

For a SQL Server engineer, the biggest productivity improvement is accepting that the client protocol does not define the performance engine:

APPLICATION / CLIENT SIDE
TDS, T-SQL, SSMS, stored procedures

        ↓ Babelfish boundary

PLANNER / EXECUTOR SIDE
PostgreSQL plans, row estimates, buffers,
work_mem, sequential scans, sort/hash behaviour

When a problem crosses that boundary, use evidence from the layer that actually makes the decision.

A repeatable workflow§

1. Reproduce the slow statement safely.
2. Get an estimated plan first.
3. Enable verbose/cost/buffer detail.
4. If safe, capture runtime statistics.
5. Inspect row flow and estimate errors.
6. Check scans, joins, sorts, hashes and spills.
7. Test a session-level setting only when the plan gives a reason.
8. Re-run and compare the same evidence.
9. Only then decide whether the fix is query, index, statistics or configuration.

That avoids a common anti-pattern: slow query, increase memory or add an index or change a parameter, hope. The plan should tell you which hypothesis deserves a test.

Tooling limits still matter§

Babelfish’s showplan/profile path is useful, but it is not a complete replacement for every SQL Server profiling scenario. AWS documents limitations around retrieving plans from functions, control-flow statements and cursors through these mechanisms.

That matters when a slow procedure contains several layers of procedural logic: failure to get a useful plan from one of those paths is a tooling boundary, not evidence that the work underneath is cheap.

What this note is not§

This is not a claim that Babelfish performance can be reduced to a few switches, and it is not a replacement for PostgreSQL-native workload and activity statistics. It is a practical bridge for the moment when a SQL Server-shaped workload has to be understood using PostgreSQL execution evidence.

Takeaway§

Babelfish makes compatibility convenient for applications. It does not remove the engine boundary for performance engineering. When a T-SQL procedure is slow, I want to know what PostgreSQL planned, what PostgreSQL executed, how many rows moved through it, what buffers it touched, and where memory or I/O changed the shape.

Once that evidence is available, the problem stops being “Babelfish is slow.” It becomes a normal database-performance problem again.