Bookmarks

PHP カンファレンス名古屋 2025 2025/2/22 ルーム シンフォニー 14:45 〜 AI 要約： このセッションでは、...

こんにちは！「ぐるなびウエディング」開発チームの滝口(@ytakiguche)です。普段はサーバーサイド開発を担当しています。 私たちのチームは現在、オンプレミスで長年稼働してきた PHP のシステムをAWSクラウドへ移行し、同時に TypeScript で全面的に書き換えるという、大きな挑戦の真っ只中にいます。 この…

Fast ring-buffer deque (double-ended queue). Contribute to gammazero/deque development by creating an account on GitHub.

A resilient Chord implementation in Go. Contribute to ollelogdahl/concord development by creating an account on GitHub.

Unopinionated and composable channel operations.

Switch statements in Go have unique features that make it easy to write complex flow controls. Read this blog to see what makes them so special.

Discover what dirty pages are in PostgreSQL and how to manage them to improve database performance and reduce I/O spikes.

Ever wonder what happens when you type SELECT * FROM users WHERE id = 42; and hit Enter? That simple query triggers a fascinating journey through PostgreSQL’s internals—a complex series of operations involving multiple processes, sophisticated memory management, and decades of optimization research. This is the first article in a series where we’ll explore PostgreSQL’s query execution in depth. In this overview, I’ll walk you through the complete journey from SQL text to results, giving you the roadmap. Then, in subsequent articles, we’ll dive deep into each component—the parser, analyzer, rewriter, planner, and executor—exploring the details of how each one works.

Postgres gives each connecting client its own process called a client backend. And either because of concerns about 1) resource contention or 2) latency or 3) both, we users tend to limit the maximum number of client connections to a few hundred. Then we introduce connection poolers like pgbouncer or pgcat. These poolers can handle more connections (with lower latency) by transparently multiplexing many client connections onto a single Postgres client backend. But how does this work? And how does it behave? Let's take a look at pgcat to see. Build Postgres and pgcatI was hoping to skip thinking about dependencies and configuration by finding a nice docker-compose.yml for pgcat and Postgres. There's one in the pgcat repo but I couldn't get it to work, possibly because I am using podman, not docker. And I couldn't find any other working docker-compose yamls either. So let's just build Postgres and pgcat from source and avoid docker/podman permissions issues.$ git clone https://github.com/postgres/postgres $ cd postgres $ git checkout REL_18_STABLE $ ./configure --without-icu \   --prefix=$(pwd)/build \   --libdir=$(pwd)/build/lib $ make -j16 && make installThen create and start an instance and add a password to your user (pgcat seems to require this).$ ./build/bin/initdb testdb $ ./build/bin/pg_ctl -D $(pwd)/testdb -l logfile start $ ./build/bin/psql postgres -c "ALTER USER $(whoami) WITH PASSOWRD '$(whoami)'"Now let's grab and build pgcat.$ git clone https://github.com/postgresml/pgcat $ cd pgcat $ cargo buildpgcat requires a config file but the ones that come in the repo involve too many features and I just wanted a minimal config. So I started with an empty file and added settings until pgcat stopped erroring about a missing setting to get to this pgcat.toml:echo ' [general] admin_username = "$(whoami)" # Must be set but we won't use this. admin_password = "" # Must be set but can be blank. port = 6432 [pools.postgres.shards.0] servers = [["localhost", 5432, "primary"]] database = "postgres" [pools.postgres.users.0] username = "$(whoami)" password = "$(whoami)" pool_size = 1 ' > pgcat.tomlWe're telling pgcat how and where to connect and that pgcat itself should run at 6432. The only thing we'll tweak in the rest of this post is pool_size. More on that as we go. Start pgcat:$ ./target/debug/pgcat pgcat.tomlpool_size = 1So we've got Postgres running right now and we've got pgcat running. Let's connect to pgcat with the psql REPL (back in the root of the Postgres repo we cloned):$ PGPASSWORD=$(whoami) ./build/bin/psql -h localhost -p 6432 postgres psql (18rc1) Type "help" for help. postgres=#pgcat is intercepting all interactions between the client (psql) and Postgres's client backend. But it isn't really easy to tell from the client itself that we're connected to a pooler and not directly to Postgres. Let's edit the psql prompt for this session so it'll be easier to tell them apart as we add more.postgres=# \set PROMPT1 '[client1] %/%R%x%# ' [client1] postgres=# \set PROMPT2 '[client1] %/%R%x%# ' [client1] postgres=#Now let's run a simple query.[client1] postgres=# SELECT 'hello world';   ?column? -------------  hello world (1 row)Now let's check the PID of the Postgres client backend our queries are actually being executed on.[client1] postgres=# SELECT pg_backend_pid();  pg_backend_pid ----------------           19584 (1 row)Now let's do something session-local, let's change our timezone. I'm in NYC so my timezone is America/New_York.[client1] postgres=# SHOW timezone;      TimeZone ------------------  America/New_York (1 row)Let's change it to America/Los_Angeles.[client1] postgres=# SET timezone TO 'America/Los_Angeles'; SET [client1] postgres=# SHOW timezone;       TimeZone ---------------------  America/Los_Angeles (1 row)Keep that psql session open and in another terminal open another psql session.$ PGPASSWORD=$(whoami) ./build/bin/psql -h localhost -p 6432 postgres psql (18rc1) Type "help" for help. postgres=#Let's set this one's prompt too to distinguish it.postgres=# \set PROMPT1 '[client2] %/%R%x%# ' [client2] postgres=# \set PROMPT2 '[client2] %/%R%x%# ' [client2] postgres=#Within client2 the timezone should be America/New_York because that's the default.[client2] postgres=# SHOW timezone;      TimeZone ------------------  America/New_York (1 row)Now check the Postgres client backend PID.[client2] postgres=# SELECT pg_backend_pid();  pg_backend_pid ----------------           19584 (1 row)That's so cool! In Postgres that's not supposed to be possible! If we were connected directly to Postgres these PIDs would be unique. One process per client. But we're sharing the same process across clients. Check back on that timezone setting in client1 to make sure it didn't somehow get reset.[client1] postgres=# SHOW timezone;       TimeZone ---------------------  America/Los_Angeles (1 row)Still its own value. So, among other things, the pooler is making it look as if we've got our own session just like each client had its own unique Postgres client backend even though we have multiple clients sharing the same Postgres client backend. Sharing is ... caring?The basic way these poolers work is that there's a pool of open Postgres client backend connections. In transaction pooling mode it will acquire a client backend connection from the pool and then relinquish it when the transaction is over. Each statement in Postgres is a transaction so each client only holds onto a pooled connection for the duration of the statement's execution. If one client runs a statement that takes a while to execute it can starve other clients from access to the pooled connection. We can see this most easily because we set pool_size = 1 so the pooler is only allowed to have a single open Postgres client backend. For example, let's just call pg_sleep() on client2 for 15 seconds. It will appear hang for 15 seconds before returning:[client2] postgres=# SELECT pg_sleep(15);  pg_sleep ---------- (1 row)And immediately after hitting Enter on client2, run a SELECT 1 on client1. client1 will hang for a few seconds and then return an error:[client1] postgres=# SELECT 1; FATAL:  could not get connection from the pool - AllServersDownBut once the client2 sleep completes and the statement is executed, client1 will be able to run commands again.[client1] postgres=# SELECT 1; FATAL:  could not get connection from the pool - AllServersDown -- Shortly later -- [client1] postgres=# SELECT 1;  ?column? ----------         1 (1 row)That makes sense! We're sharing the same Postgres client backend! Long-running statements are bad practice, you say. Indeed. Let's do an interactive transaction instead. On client1 start an interactive transaction with BEGIN.[client1] postgres=# BEGIN; BEGIN [client1] postgres=*#And on client2 try to run a SELECT 1:[client2] postgres=# select 1; FATAL:  could not get connection from the pool - AllServersDownIt hangs and fails. Let's run a query in client1's transaction and then commit it.[client1] postgres=# BEGIN; BEGIN [client1] postgres=*# SELECT 1;  ?column? ----------         1 (1 row) [client1] postgres=*# COMMIT; COMMITNow back in client2 once client1's transaction is complete we can access the Postgres client backend again.[client2] postgres=# SELECT 1; FATAL:  could not get connection from the pool - AllServersDown -- After client1 commits [client2] postgres=# SELECT 1;  ?column? ----------         1 (1 row)It's quite neat, to me, to play with the behavior when you only allow a single Postgres client backend. But now let's take a look at what happens if we allow more than a single client backend. pool_size = 2In the pgcat.toml we overrode above, within the pgcat repo, change the pool_size from 1 to 2. Then Ctrl-c the pgcat server and run it again. Close the two psql sessions and start two new ones. Give them the same prompt prefixes of [client1] and [client2]. See what the current Postgres client backend PID for client1 is. (It will have changed because we restarted the pooler.)$ PGPASSWORD=$(whoami) ./build/bin/psql -h localhost -p 6432 postgres psql (18rc1) Type "help" for help. postgres=# \set PROMPT1 '[client1] %/%R%x%# ' [client1] postgres=# \set PROMPT2 '[client1] %/%R%x%# ' [client1] postgres=# SELECT pg_backend_pid();  pg_backend_pid ----------------           20809 (1 row)And do the same for client2:$ PGPASSWORD=$(whoami) ./build/bin/psql -h localhost -p 6432 postgres psql (18rc1) Type "help" for help. postgres=# \set PROMPT2 '[client2] %/%R%x%# ' postgres=# \set PROMPT1 '[client2] %/%R%x%# ' [client2] postgres=# SELECT pg_backend_pid();  pg_backend_pid ----------------           20809 (1 row)So even though we allowed pgcat to use more than one Postgres client backend it's still only using one, interesting. Certainly any observations we make at this point are observations only. How a pooler chooses to implement its pooling policies are likely considered not part of its public API. Still though, let's poke and see what it does. What happens if we have a blocking client connection now? If we start an interactive transaction on client2:[client2] postgres=# BEGIN; BEGIN [client2] postgres=*#Let's see what its client backend PID is:[client2] postgres=# BEGIN; BEGIN [client2] postgres=*# SELECT pg_backend_pid();  pg_backend_pid ----------------           20809 (1 row)Still the same one as before. Leave this transaction open. Then on client1 execute a simple SELECT 1:[client1] postgres=# SELECT 1;  ?column? ----------         1 (1 row)And it works! Let's see what client backend PID client1 is running on.[client1] postgres=# SELECT pg_backend_pid();  pg_backend_pid ----------------           21004 (1 row)Not the same one as before! So since one client was blocking the use of a connection and pool_size allowed for more, pgcat opened a new Postgres client backend that client connections could use. Now let's commit the transaction on client2 and check what Postgres client backend PID it has after that tra...

An in-depth guide to listening to Postgres database changes through the WAL. Covers logical replication, publications, replication slots, and an Elixir implementation.

Looking to make it to the front page of HackerNews? Then writing a post arguing that "Postgres is enough", or why "you don’t need Kafka at your scale" is a pretty failsafe way of achieving exactly that. No matter how often it has been discussed before, this topic is always doing well. And sure, what’s not to love about that? I mean, it has it all: Postgres, everybody’s most favorite RDBMS—​check! Keeping things lean and easy—​sure, count me in! A somewhat spicy take—​bring it on!

Can it be harmful to set maintenance_work_mem and work_mem limits very high?

Git 3.0 is coming in 2026 with SHA-256 security, Rust integration, and massive performance improvements. Learn what developers need to know about this major release.

Version 43.0.0 We needed a leap version to unequivocally signaling that it's not just a major version: it's a complete redesign of the legacy code at all levels, usage and API included. Why 43? Bec...

Puma 7 is here, and that means your Ruby app is now keep-alive ready. This bug , which existed in Puma for years, caused one out of every 10 requests to take 10x longer by unfairly “cutting in line.”  In this post, I'll cover how web servers work, what caused this bad behavior in Puma, and how it was fixed in Puma 7; specifically an architectural change recommended by MSP-Greg that was needed to address the issue. For a primer on keep-alive connections: what they are and why they are critical for low-latency, performant applications, see our companion post Learn How…

An "impossible" Ruby bug: Hashes becoming Strings at runtime. 2,700 identical crashes. No corruption. Just objects changing what they are. Deep dive.

Learn about the new features in the Next.js 16 release: why they matter, how they impact your workflow, and how to start using them.

An embeddable, lightweight, secure, high-performance JavaScript engine. - 6over3/hako

A deep dive into how thoughtful URL design can enhance usability, shareability, and performance. Learn what state belongs in URLs, common pitfalls to avoid, and practical patterns for modern web apps.

Sitemap-generating framework for node.js. Contribute to ekalinin/sitemap.js development by creating an account on GitHub.

⛳️ Typed command-line arguments parser for Node.js - privatenumber/type-flag

Node.js® is a free, open-source, cross-platform JavaScript runtime environment that lets developers create servers, web apps, command line tools and scripts.

Node.js® is a free, open-source, cross-platform JavaScript runtime environment that lets developers create servers, web apps, command line tools and scripts.

Learn how code execution with the Model Context Protocol enables agents to handle more tools while using fewer tokens, reducing context overhead by up to 98.7%.

システムを運用していると時折直面するのが「移行作業」です。 新しい技術に触れられるのは楽しいです。一方、大量のコードの書き換えはちょっと辛いですよね。単純な置換で済めばよいのですが、そううまくはいかないことがほとんどです。 今回はこの書き換えを Claude Code を活用して楽に、精度良く実施したお話です。 データ…

Avoiding cross-site scripting (XSS) attacks with new web APIs