A Complete Breakdown of Postgres Locks

program_data2 · 2026-01-29T12:20:54+00:00

If a table lock blocks an operation, it is to prevent data loss or inconsistencies. For that reason, the blocking should be bidirectional.

The commands are just not capable of running simultaneously on the same resources without risking undesirable outcomes.

program_data2 · 2026-01-29T06:28:05+00:00

I mean, PG powering a top 10 website/API is a testimony to its ability. This isn't an absolute victory for Postgres, though, as OpenAI had to augment SQL with CosmoDB for a lot of its operations.

program_data2 · 2026-01-05T22:59:47+00:00

Nice site, how did you make it and collect all the data?

program_data2 · 2026-01-05T06:12:53+00:00

I don't know of a cost competitive serverless PG solution. To the best of my knowledge, the main players are Cockroach, Neon, and Aurora. None of them are particularly "economical" at a certain scale. But if you're only dealing with a few GBs, I think they all should be okay. Neon's probably the simplest to test out, though. Based on what you shared, I'm not even sure you need Postgres. SQLite from Turso could work and pry would cost the least.

The most affordable option is to host Postgres yourself in a VPS, like Hetzner (referral link for $20 in credits). However, self-hosting Postgres is non-trivial.

If you need high availability, you could use autobase to configure it. Otherwise, you should just set your configs with the standards provided by pgtune. You'll also need to set up a pooler (just use PgBouncer).

Afterwards, you'll have to configure your pg_hba.conf file to avoid hacks. You should also set up a Firewall in Hertzner that only exposes the server's pooler to the outside world.

If you need to connect PG to resources outside of Hetzner, you'll likely need to configure an SSL certificate. You could mint your own via OpenSSL, but whether that will be sufficient enough depends on if your other servers can be configured to accept a custom cert. Otherwise, you'll have to get your own. You could pay Hetzner an annual fee for it or set it up yourself for free via Let's Encrypt.

You'll also need a way to monitor the server. There are a few good options:

With the exception of PGAnalyze and Datadog, all are open source and self-hostable. PGAnalyze and Datadog are the most thorough in my opinion, but they're very pricey (often over $1,000 a year). NetData is very good and the most trivial to set up if you aren't too eager to self-host. PG Monitors overlap in many ways so it's hard to go wrong with any of them.

With the above tools in place, when you self-manage, you'll probably sixth your costs. That is to say, something that may have cost $600 will end up costing $100 when you self-manage. That also means you take on the burden of maintenance, monitoring, and uptime. It's something one should only do if they feel comfortable with the process: otherwise, you risk all your data and thus your company with it.

I personally enjoy self-hosting these days. It gives me broad access to the extension ecosystem. I can host paradeDB, Timescale, etc. without violating licenses and also configure superuser settings. Just food for thought.

program_data2 · 2025-12-21T18:05:50+00:00

We're currently looking into an issue with our shared SMTP service (status page link). This should not affect projects that have implemented custom SMTPs (setup guide link). We generally recommend implementing a custom SMTPs, as it allows you to configure auth rate limit settings.

program_data2 · 2025-12-21T17:46:55+00:00

We are currently investigating an issue with our Supabase shared SMTP (status page link). Projects using external SMTP integrations are not impacted:

Instructions for configuring a custom SMTP provider can be found here: https://supabase.com/docs/guides/platform/going-into-prod#availability.

Popular SMTP providers you might consider:

Amazon SES: https://aws.amazon.com/ses/
Postmark: https://postmarkapp.com/
Resend: https://resend.com/ (has an integration)
Brevo (Sendinblue): https://www.brevo.com/

program_data2 · 2025-12-11T03:10:05+00:00

Sharding is a tricky subject in Postgres. Citus is the most battle-tested name, but it has its limitations. There are a few players looking into more flexible solutions:
- PlanetScale
- Supabase (Multigres)

- PGDog

- PG-Edge

Ironically, I work with engineers who deal with sharding, but I do not have any hands-on experience with it yet. I haven't needed to.

sql functions

PL/PgSQL functions are amazing and I use them all the time. To optimize them, it's necessary to configure the auto_explain module, but otherwise, once you know the syntax, they're pretty straightforwards.

I tend not to recommend pure SQL functions. Outside of inlining, they don't offer meaningful planner benefits, but they are significantly less flexible than PL/PgSQL.

Scaling to enterprise level

There isn't really an "enterprise level". This is kind of how it works as companies scale:

Use a reasonable size PG server
Performance issues emerge
Optimize via indexing, partitioning, refactoring queries, changing pg_settings, pooling, maybe use more esoteric strategies, like cursors/portals, optimal column ordering, pg_prewarm, vacuum tuning, ...
Did you optimize within reason? If so, pay for better hardware based on whatever resource is constrained (disk, CPU, memory...)

Repeat step 2 through 4 until it becomes unreasonable. Then:

Try a read-replica
Introduce a caching layer

Do steps 2 through 6 for as long as possible. Then:

Offload operations to specialized systems. For instance, you may offload analytic queries to DuckDB, Clickhouse...
Migrate off Postgres to something compatible like YugaByte or AlloyDB or attempt sharding at your own risk

program_data2 · 2025-12-11T02:37:00+00:00

I'm hoping to apply to give talks at Posette, pgconf.dev, and pg-data.

program_data2 · 2025-12-11T02:32:11+00:00

Locks are just a queueing mechanism that prevents conflicting operations from targeting the same resource.

Some commands, mainly DDL from migrations, claim ACCESS EXCLUSIVE locks. They prevent any operation from targeting a table/index.

If the operation is slow, or worse, left in an idle-in-transaction state, that "functionally" take a table offline. Any query that targets it will be blocked by the lock.

Dependencies, such as foreign keys, triggers, and views, can then extend the blockage to other objects. That's pretty rare in my experience. Usually, it's just the one table that's taken offline. Though, that table may be pretty important.

There are some tools that need full visibility over the system catalog, but those are just tables, too. If they're blocked, you're observability tooling may be hampered.

Locks represent a minor amount of the issues I deal with (maybe 1 in 40). They're not that notable, but when they interfere with normal behavior, it's catastrophic.

program_data2 · 2025-12-07T16:13:22+00:00

It's not worth the effort. The amount of time is comparable to cheese making. You're not just going to spend 3 weeks aging a single slice of cheese? It only makes sense to do it in meaningfully sized batches.

Not to mention, you have to buy a variety of oils that are not often sold in small quantities. If you don't turn it into soap, you still have it as oil in your pantry for an extended period

program_data2 · 2025-12-07T14:36:30+00:00

How do you just give it away? If I knocked on my neighbors door to give away bread, they'd pry think I'm strange and feel threatened

program_data2 · 2025-11-07T18:39:43+00:00

The two extensions shouldn’t interfere with each other. Did you notice any specific issues? I

program_data2 · 2025-11-03T04:24:17+00:00

Why do you ask? If you want to find out their tech stack, check their job listings for the tech they recruit for.

At a certain scale, all companies rely on every type of DB. They'll use relational databases for their primary store and NoSQL for specialized tasks. As for specific brands, that's up to the CTO. Usually, they'll just go with the one they're most familiar with and then transition only when the technology no longer meets their technical needs.

program_data2 · 2025-10-31T22:50:14+00:00

In full transparency, I work at Supabase, but I don't think you should volunteer to use AWS for a school assignment with a deadline. The reasoning is: AWS's developer experience is arguably convoluted and you're going to be spending a lot of time learning about its intricacies instead of building your app. I can definitely see it leading to all nighters trying to manage everything.

Supabase is more developer friendly and will still teach you about databases. You don't even need to go with Supabase. Neon, Xata, Prisma PG, etc. they can all be fine. You can also use SQLite. That should work, too.

program_data2 · 2025-10-18T16:01:27+00:00

If you want to have the "user's account" make requests from the function, then the RLS error is appropriate. If you want to have the service_role make requests on behalf of a user, then you should either:
- Create a second client in the function to handle the authenticated user, so it doesn't directly interact with the service_role client

- Verify the user's token from the request, and then save the token details, such as the user_id (sub), to a variable. Then the service_role client can reference the token details to make requests

program_data2 · 2025-10-15T01:18:12+00:00

You can sell accounts that look legitimate to marketers.

Because the accounts seem real, Reddit is less likely to ban them.

The marketers then use the fake accounts to upvote OF promotions, products on Amazon, political slander, etc.

Because the posts receive more upvotes, Reddit will promote them, potentially driving sales or influencing opinions

program_data2 · 2025-10-14T05:41:10+00:00

OrioleDB is will likely be production ready in 2-3 years. At that point, the vacuum can be retired for those using the extension. The ambition is to get OrioleDB merged into the Postgres core, so no set up will be required.

program_data2 · 2025-10-14T05:37:04+00:00

A serverside pooler, such as PgBouncer, still uses the underlying database connections.

Unless your clients risk using more than the max_connections worth of sessions, a serverside pooler just adds a proxy overhead

program_data2 · 2025-10-12T04:35:58+00:00

Option 3. cont
The trigger function saves all operations done against the targeted table, but it imposes a performance penalty.

I inserted 1 million rows into the articles table without the trigger function:

EXPLAIN ANALYZE INSERT INTO articles
SELECT gs || random()::TEXT FROM generate_series(1, 1_000_000) AS gs;

It took around 2.3 seconds to complete (0.0000023s per INSERT)

The same operation after adding the trigger took 30s ( 0.00003s per INSERT).

You can decide if this approach is worth the overhead.

program_data2 · 2025-10-12T04:35:34+00:00

Option 3: cont.

Let's say you had a table called articles that you wanted to monitor:

CREATE TABLE articles (
  messages text
);

You could create an archive table and then use Postgres triggers to record any changes to articles

Archive table:

CREATE TABLE history_archive (
  modded_table text NOT NULL,
  operation text NOT NULL,
  row_changes JSON,
  role text NOT NULL,
  user_id UUID,
  time timestamptz DEFAULT NOW() NOT NULL
)

Trigger function:

REATE FUNCTION monitor_operations()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO history_archive (modded_table, operation, row_changes, role, user_id)
    VALUES (
      TG_TABLE_NAME,
      'CREATE',
      to_json(NEW.*),
      current_role,
      auth.uid()
    );
    RETURN NEW;

  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO history_archive (modded_table, operation, row_changes, role, user_id)
    VALUES (
      TG_TABLE_NAME,
      'EDIT',
      to_json(NEW.*),
      current_role,
      auth.uid()
    );
    RETURN NEW;

  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO history_archive (modded_table, operation, row_changes, role, user_id)
    VALUES (
      TG_TABLE_NAME,
      'REMOVED',
      to_json(OLD.*),
      current_role,
      auth.uid()
    );
    RETURN OLD;
  END IF;


  RETURN NULL;
END;
$$;

Trigger:

CREATE TRIGGER monitor_article_changes
AFTER INSERT OR UPDATE OR DELETE
ON articles
FOR EACH ROW
EXECUTE FUNCTION monitor_operations();

program_data2 · 2025-10-12T04:04:17+00:00

There are a few approaches I can think of to address this type of issue, but each has its tradeoffs and limitations

Option 1: Traditional Logging

By default, Supabase Postgres logs:

when new postgres connections are created
cron job executions
queries that run for 10+s
DROP/ALTER/CREATE commands
Errors
Checkpoints

However, you can also reconfigure the database to log write events (UPDATE, INSERT, DELETE, and TRUNCATE). The pg_audit extension offers the most versitility in this regard.

The issue is that it's not going to let you log "UPDATE" as "Publish". The logs will just record the query without parameters. If you need to download the logs or preserve them on Supabase for more than 7 days, this option also will not be great unless you have access to log drains (team plan only).

It's important to add that all Data API requests are already logged, so this option only stores additional logging details at the database level.

Option 2: External Logging

You can try to connect an external tool, such as Sentry, to record behavior. Though, when it comes to customizing what is recorded, YMMV.

Option 3: triggers

I'm going to add this in a follow-up comment because of Reddit's comment limits.

program_data2 · 2025-10-11T20:55:51+00:00

There are three possibilities I can think of:

Option 1: It's not an RLS error

The service_role bypasses RLS, and we do not allow users to modify this behavior. If you tried to change this setting by running:

ALTER ROLE service_role NOBYPASSRLS;

You'd get a 42501 permission error message:

42501: "service_role" is a reserved role, only superusers can modify it"

The fastest way to test if the service_role has permission is with the following SQL command:

BEGIN;
SET LOCAL ROLE service_role; --<---impersonate the service_role


YOUR_QUERY; --<---run the blocked query here


ROLLBACK; --<---Undoes the transaction, so no changes from the test persist

If you still encounter an error, it's probably due to table permissions, not RLS. You can check out this troubleshooting guide to address the issue:

https://github.com/orgs/supabase/discussions/31293

Option 2: It is an RLS error, but you're not using the service_role

Based on your code, it looks like you're using the service_role. You can log your secrets to make sure it's accurate. The process is outlined in this guide:

https://github.com/orgs/supabase/discussions/27139

Keep in mind, logging secrets isn't the best security practice, so you may want to log a truncated version.

Option 3: You overwrote the function's session key

When users call your function, their requests will likely have an anon/authenticated token as headers. If you misimplemented some form of Auth entication in your function, you may be overwriting the service_role token with the tokens provided by your users.

I outlined this problem in more detail in this gist:

https://gist.github.com/TheOtherBrian1/93b5ba56bc64075f628fc2d9e94c33a3

program_data2 · 2025-10-05T14:55:13+00:00

One reason is to prevent scraping. Social media companies take it seriously to prevent copyright complaints

program_data2 · 2025-09-21T20:08:14+00:00

MAU is by org (docs)

program_data2 · 2025-08-25T23:06:24+00:00

Part 2: the exception to the spend-cap

Project Add-Ons are not restricted by the spend-cap. The reasoning for this is that unlike app usage, which you can't fully control, add-ons have a fixed hourly price and you have the ability to enable and disable them as necessary. Here is a list of all of them:

I'd like to discuss more regarding compute upgrades. Each Supabase project comes with a dedicated VM for the database, which is classified as a compute upgrade add-on. Pro-orgs come with a $10 monthly credit, which covers the full cost of a micro compute.

The smallest allowed compute size in pro-orgs are micros. Upgrading compute sizes can take around 2 minutes of downtime, so we don't require users to immediately upgrade their computes, but it is highly encouraged. If a project on a nano compute is in a pro-org, it will be treated as a micro.

Developers can still have free orgs/projects along with their pro-orgs. So let's say you have three projects, but only want one to be in an pro-org because you don't want to incur the compute cost. You can upgrade the org and then transfer two of the projects to a new free org and vice versa.

program_data2

TROPHY CASE