giovannibonetti

FOR UPDATE SKIP LOCKED The best way to think about this Postgres feature is that it reserves the rows that you’re selecting for use in your transaction without interfering with other queries. We use it primarily for implementing our job queue;

SKIP LOCKED is useful for implementing job queues with interactive transactions – you lock the row while working on it in the application and keeping the transaction open. For high-performance applications it is best to avoid interactive transactions at all, and just update the rows to "pending" immediately. There is no need for SKIP LOCKED in this case.

As a rule of thumb, as you scale up the application, you want to have less state in the database memory, and interactive transactions are just that. Idempotence beats atomicity at scale.

The reason I think it’s useful to view queries as binary—they either seq scan or they don’t seq scan—is: the more you micro-optimize a query, the more of a risk you take that the query planner goes rogue. If you stick to querying by primary keys and indexes, the query planner will have a much easier time.

It's also important to notice the query planner optimizes for the average case, but often it would be better for the app developer if it was optimized for the worst case. But optimizing for the former is a much more tractable problem, so no wonder that's what is implemented.

I had to fight against the query planner when it would optimize a query for the average user, with few rows in a given table, and it would pick one index that made sense for that situation and return a result in less than 10ms. However, when a heavy user issued the same query, depending on the exact parameters the worst case could take over 1 second. So I had to write a much more complex query to force it to take another path with a different index, which would be slower in the average case, but in the worst case would take still less than 100ms. Avoiding timeouts was much more important for my company than taking 10ms more in the average case.

Because of all these connection footguns, external connection poolers like pgbouncer are great! If you can’t add this for whatever reason, in-memory connection poolers are a great second option. For example, because Hatchet is open-source, we don’t assume that all user databases use connection poolers, so we use pgxpool (an in-memory connection pool for Go) for this purpose.

Few people know that there is a major bifurcation when it comes to connection pooling implementation.

1. Most application connection poolers follow a first-in-first-out (FIFO) algorithm, which is simple enough to implement and is enough to make sure the application always has a connection available to connect to the database. It optimizes low latency, and works great from the point of view from the application. The problem is that it has few mechanisms to remove redundant connections, since the application is constantly keeping them all "warm".

2. PgBouncer and very few external poolers follow the inverse idea – last-in-first-out (LIFO), and they optimize for reducing the number of connections that reach Postgres, thus improving its throughput. The idea might seem crazy at first – the last connection used is the first one to be picked up again – but this algorithm automatically removes excess connections, which will get cold and get closed.

When starting a new application, option (1) is enough, but as it scales up enough, at some time it is recommended to use (2), since having hundreds of open connections to Postgres is bad for performance if you can use PgBouncer or similar to cut it by 90%. Postgres' process-per-connection design works much better when there are fewer connections reaching it.

This reminds me of the Flix programming language [1], which has annotated effects like so:

def main(): Unit \ { Clock, Http, Logger, IO } = ...

[1] https://flix.dev/

This reminds me of this Bjarne Stroustrup's Rule (creator of C++): - For new features, people insist on loud, explicit syntax. - For established features, people want terse notation

Hillel Wayne [1] argues that the same applies for the differences between what beginners and experts desire from a language: Beginners need explicit syntax, experts want terse syntax.

In my mind, DSLs are related to that – a short notation to avoid repetition. And LLMs are the experts.

I wonder if Lisp with its powerful DSL-creating macros will enjoy more popularity in the near future.

[1] https://buttondown.com/hillelwayne/archive/stroustrups-rule/

That's fewer moving parts, one load-bearing system instead of two...

When I saw "load-bearing" I remembered the other discussion in the frontpage explaining how to prevent Claude from saying that so often.

Perhaps what is missing is a better memory/caching layer to avoid doing the same for explorations over and over again.

I wish LaunchDarkly and other feature flag providers supported protocol buffers to MN define the feature flag schema. It would be a game changer when you have complex variations and end up reaching for untyped JSON.

Property testing and deterministic simulation seem like good alternatives.

Shout-out to PowerSync for making it easier to develop fast offline-first mobile apps. It pushes data from Postgres/MySQL/SQL Server subscriptions to a SQLite into the user's mobile device, avoiding the need for many loading animations when the data is there ahead of time. My company is a customer and we recommend it.

It would be even cooler if it was made with a Lisp and took advantage of it being homoiconic.

Those looking for a more mature solution in this space will probably enjoy SQLc [1]. It was initially developed for Go applications, but over the years it got pluggins for many other languages, including JavaScript/Typescript.

[1] https://sqlc.dev/

but seeing how Lustre does HTML templating versus how Phoenix does Heex was my deciding factor to try the latter. My understanding is this is because of a current lack of any macro system.

When I worked with Ruby on Rails I was "addicted" to macros for everything, but after working for a while with statically-typed languages like Elm and Gleam, I see that there are many other ways to solve those same problems. The code can be quite repetitive sometimes, but as long as the compiler ensures everything is in place, it works quite well.

For those of you a similar SPA-type app with more type safety – which is even more useful for writing code with AI – you may want to have a look at the Gleam language and the Lustre web framework [1]. It combines the best of Elixir and Elm. You can mix and match having more logic in the backend or the frontend, as Gleam compiles both to Erlang and to JS.

[1] https://lustre.hexdocs.pm/lustre/server_component.html

Given that it has only two commits, where the first one is just "done", I would guess a substantial amount.

If it's going to be read-only, why not make it a Parquet file instead? It should result in a smaller file size due to columnar compression.

DuckDB has built-in capability to read Parquet files with HTTP range requests.

There was a time that Google cared deeply about UX. Now, on macOS Google remaps CMD-G in Google Docs to launch some LLM bullshit

That reminds me of a few years ago when Android phones replaced the behavior of "long press sleep/power button" from "shut down" to "ask AI about what's in your screen". Perhaps a manager got promoted somewhere for "raising AI usage" in Android phones.

As far as I can tell, Postgres is not designed with this inclination towards doing lighter work when clients are waiting and piling up maintenance work to do in background. I think the background work it does is mostly running vacuum on tables now and then.

Contrast that with ClickHouse, for example. It operates in a different niche than Postgres (OLAP instead of OLTP) – with their merge tree engine family [1] that does data deduplication in background.

There is one project of modernizing Postgres' storage engine called OrioleDB [2], but I think the company got acquihired by Supabase [3] and maybe the project has not been progressing very quickly since then.

[1] https://clickhouse.com/docs/engines/table-engines/mergetree-... [2] https://www.orioledb.com/ [3] https://supabase.com/blog/supabase-acquires-oriole

Yes, partitioning will decrease a bit the read performance of queries not correlated with the partition key. That's why you need to periodically merge smaller partitions, so that you can keep the overall partition count bounded.

It is a lot of admin work, but if you really need to scale up Postgres write throughput, I don't see many other options without increasing hardware costs.

I assume you have already picked the low-hanging fruit discussed in the neighboring comments - batch writes, make sure you are using COPY instead of INSERT, tune Postgres parameters adequately and use the fastest disk you can grab for the WAL.

Say, if you know the function is a polynomial of degree N, with N+1 datapoints you can find it – e.g. with Lagrange's polynomial, although the finite precision of computer numbers might make that more complex.

With some extra admin work, you can greatly increase your insert throughput, as long as the table load is comprised mostly of inserts: 1. Partition your table by range of a monotonic ID or timestamp. Notice the primary key will have to contain this column. A BIGINT id column should work fine; 2. Remove all the other indexes from the partitioned table. Add them to all the partitions, except the latest one. This way, the latest one can endure a tough write load, while the other ones work fine for reads; 3. Create an admin routine (perhaps with pg_cron) to create a new partition whenever the newest one is getting close to the limit. When the load moves to the newer partition, add indexes concurrently to the old one; 4. You'll notice the newest partition will the optimized for writes but not reads. You can offset some of that by replacing BTREE secondary indexes with BRIN [1], particularly the one with bloom operator (not to be confused with Postgres Bloom regular indexes [2]). BRIN is a family of indexes more optimized for writes than reads. If the partition is not too large, it shouldn't be too bad to read from it. 5. Later you can merge partitions to avoid having too many of them. Postgres has commands for that, but I think they lock the whole table, so a safer bet is to copy small partitions into a new larger one and swap them manually.

[1] https://www.postgresql.org/docs/current/brin.html [2] https://www.postgresql.org/docs/current/bloom.html

That's why these niche languages need state-of-the-art compilers that enforce invariants more strongly. This way, they can catch most of the subtle bugs the LLM produces, sort of like antibodies.

For those of you using a browser to generate PDFs, the Rust crate you should look into is Typst [1]. Regardless of your application language, you can use their CLI.

It takes some time to get used to their DSL to write PDFs, but nowadays with AI that shouldn't take too long.

[1] https://crates.io/crates/typst

I find it fascinating when different people independently arrive at the same architecture when working on a hard problem like this. In my company we built our offline-first apps with PowerSync, which has the same idea of optimistic local changes while waiting for the central server to acknowledge the definitive changes. In PowerSync's case, the sync engine reads Postgres replication logs directly.

Micro-payments, probably

For those of you in Brazil, my company jota.ai has built a financial AI-assistant that you can chat with to open a bank account, connect with accounts from other banks, make instant Pix payments with any of them, all of that through WhatsApp right now. We're working hard to release long-running agents soon that can do increasingly complex workflows involving payments and whatnot.

Please let us know if you have suggestions of what complex workflows you would like to build.

You might be interested in a library that flips around the concept of an ORM, like sqlc [1] or aiosql [2]. You specify just what you want from the database, without needing to couple so much API with database tables.

[1] https://sqlc.dev/ [2] https://nackjicholson.github.io/aiosql/

That's the space where Go shines

OTOH, if and only if you design your schema to exploit MySQL’s clustering index (like for 1:M, make the PK of the child table something like (FK, some_id)), your range scans will become incredibly fast. But practically no one does that.

You can achieve that with Postgres as well if you accept duplicating the data by adding an index with an include clause containing all the non-key columns you want to return in the SELECT clause. This way, you'll get fast index-only scans.

I think the PowerSync [1] team is missing out on an opportunity to showcase their impressive data sync technology by building a minimalist Slack clone.

[1] https://www.powersync.com/