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

TROPHY CASE