[Plugins] Add plugin for primary-only tables

[cdreier]

Hi,

we are currently testing a few approaches to enable db load balancing for a strapi cms instance. we are running on aws aurora and tested pgdog (with the helm chart) with one primary and one replica - while at first sight everything worked really well and speed up the whole instance, we suddenly got problems during login.

the login code inserts a session, and immediatly fetches the session with the returned id. in most of the cases, the session could not be found via pgdog.

we then removed the replica and tested with only the primary and the issue persists!

correction: removing the replica did not trigger a redeployment, it is working with only the primary!

also tested the session transactionmode, but this caused a lot more problems as more sessions routed to the replica had also to write some more. sadly most of the strapi orm code is not accessable, so we are not able to fine tune this with the manual routing. but anyway, as far as i understand, this should not happen with only the primary active?

thanks a lot for any hint and help.

i created a debug script to reproduce this, fails in ~30%

import pg from 'pg';
const { Pool } = pg;

// Configure to match Strapi's pool settings
const pool = new Pool({
  host: process.env.DATABASE_HOST || '127.0.0.1',
  port: parseInt(process.env.DATABASE_PORT || '6432'),
  database: process.env.DATABASE_NAME || 'dev',
  user: process.env.DATABASE_USERNAME || 'dev',
  password: process.env.DATABASE_PASSWORD || 'dev',
  max: 40,
  min: 0,
});

// Reproduce what Strapi's entity-manager create() does:
// 1. INSERT ... RETURNING id  (standalone, no transaction)
// 2. SELECT * WHERE id = ...  (standalone, no transaction)
// These are TWO separate queries, each getting their own connection from the pool.
async function simulateSessionCreate(attempt) {
  const sessionId = `test-session-${Date.now()}-${attempt}`;
  const now = new Date();
  const expiresAt = new Date(now.getTime() + 3600000);

  // Step 1: INSERT (like entity-manager line 184)
  const insertResult = await pool.query(
    `INSERT INTO public.strapi_sessions
      (user_id, session_id, device_id, origin, child_id, type, status, expires_at, absolute_expires_at, created_at, updated_at, document_id, published_at, locale)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
     RETURNING id`,
    ['1', sessionId, 'test-device', 'admin', null, 'refresh', 'active', expiresAt, expiresAt, now, now, sessionId, now, null]
  );

  const id = insertResult.rows[0]?.id;
  if (!id) {
    console.error(`[Attempt ${attempt}] INSERT returned no id!`, insertResult.rows);
    return false;
  }

  // Step 2: SELECT (like entity-manager line 201 - findOne)
  const selectResult = await pool.query(
    `SELECT * FROM public.strapi_sessions WHERE id = $1`,
    [id]
  );

  const record = selectResult.rows[0];
  if (!record) {
    console.error(`[Attempt ${attempt}] FAILED: INSERT returned id=${id} but SELECT found nothing!`);
    return false;
  }

  if (!record.created_at) {
    console.error(`[Attempt ${attempt}] FAILED: record found but created_at is null!`);
    return false;
  }

  // Cleanup
  await pool.query('DELETE FROM public.strapi_sessions WHERE id = $1', [id]);
  return true;
}

async function main() {
  const iterations = parseInt(process.argv[2] || '100');
  console.log(`Running ${iterations} simulated session creates against ${pool.options.host}:${pool.options.port}...`);
  console.log();

  let failures = 0;
  let successes = 0;

  for (let i = 0; i < iterations; i++) {
    try {
      const ok = await simulateSessionCreate(i);
      if (ok) {
        successes++;
      } else {
        failures++;
      }
    } catch (err) {
      console.error(`[Attempt ${i}] ERROR: ${err.message}`);
      failures++;
    }
  }

  console.log();
  console.log(`Results: ${successes} OK, ${failures} FAILED out of ${iterations}`);

  if (failures > 0) {
    console.log('=> PgDog read-after-write inconsistency!');
  } else {
    console.log('=> All reads were consistent after writes.');
  }

  await pool.end();
}

main().catch(console.error);

[cdreier]

so... your example plugin is the only solution?

[levkk]

What version of pgdog are you running?

the login code inserts a session, and immediatly fetches the session with the returned id. in most of the cases, the session could not be found via pgdog.

This is a classic Postgres replica lag problem. The code needs to either RETURN * or run the whole request inside a transaction.

more sessions routed to the replica had also to write some more.

That's odd. Do you have the queries that pgdog thought were reads but were actually writes?

so... your example plugin is the only solution?

Maybe. This requires us to present a strongly consistent view of the database which is difficult and expensive computationally. Postgres is eventually consistent: replica delay is accepted as a trade-off for performance. We're still looking at making it strongly consistent for this specific use case, but it may not be worth it in the end, since it'll slow down queries considerably. TBD.

[levkk]

The plugin will theoretically work with single-node pgdog deployments. I think a simpler fix is to create an allow/banlist for tables that are allowed to use the replica. I would say the sessions table could be primary-only. That being said, it's probably the most read-heavy table in the app and could defeat the purpose of replication.

[cdreier]

thanks for your fast response! we are currently running v0.1.30

more sessions routed to the replica had also to write some more.

perhaps this was missleading, i tested the pooler_mode = "session" - but lots of sessions (not strapi session) started with a read, and our logs were full of failed writes because they were routed to the replica.

imo an allow/banlist would help a lot, for most of our traffic the replica lag is absolutly fine and no problem, but the login should work 😄

[levkk]

perhaps this was missleading, i tested the pooler_mode = "session" - but lots of sessions (not strapi session) started with a read, and our logs were full of failed writes because they were routed to the replica.

Load balancing doesn't work in session mode. We should put a note in the documentation for this.

imo an allow/banlist would help a lot, for most of our traffic the replica lag is absolutly fine and no problem, but the login should work 😄

We can try this out. It should be easy to implement.

[cdreier]

awesome!

happy to test this :)

[levkk]

Awesome. I'm going to write a plugin for this & add it to our Docker image by default. You'll be able to enable it and tweak it with a config. I think that makes the most sense, since the use case is a bit esoteric, but we still want to support it and give you and everyone else the option to easily turn it on/off without complicating our core code significantly.

[cdreier]

since the use case is a bit esoteric

i fully agree, in a normal case we would just change the code and return the created data, but the solution with a "primary only"- plugin for specific tables, sounds perfect for such cases where the implementation is not under control

thanks a lot for your fast help!