Was chasing a failure in a Postgres client where the connection is lost after 90 minutes. Both client and db are on Windows.

I ended up on Cisco official doc to fully understand what was going on.

This one is interesting.
Routers track connections they see in a local state table for routing decisions and filtering out invalid packets.
This state is cleared when either party closes the connection, to make room for other connections, especially that the memory is limited in routers.

I really like what the Linux kernel folks are doing with uncached buffered io.

Picture shows consistent ~150GB/s reads with uncached buffered reads, compared to fluctuated reads to between 96GB/s with normal buffered with page cache

Let’s expand on why this is faster .. Normal buffered reads and writes from user space go through the file system page cache.

ie a 1000 writes to a file will be buffered in the kernel memory page cache and then later, at the kernel discretion, “flushed” to disk. Once.

Same thing if 1000 processes read the same file, the kernel reads it once and put the blocks in the page cache, and simply do a memory copy to the processes memory.

This prolongs disk life & performance.

(Users could also force an on demand flush with the fsync system call. But that is a can of worms I don’t want to open rn)

The problem with the page cache is its managed by the kernel. And under memory pressure, the kernel may “reclaim” the some of its memory causing unpredictable performance we saw in the picture.

So database folks prefer to use the l option zero O_DIRECT which skips the page cache since we already have shared buffers where we, well, buffer….

O_DIRECT of course has its own complexity and not always available or supported.

So what the kernel folks did (credit to Jens Axboe for this work) is do buffered io not using page cache, but instead in another area in the kernel. Termed uncached buffered io.

This gives more predictable buffered io but without the unpredictability of the page cache. Of course O_DIRECT still has the advantage of zero copy, while uncached buffered io still copies to kernel.

This TCP option can improve backend and frontend network latency.

But how does it work?

thread 1/9 When an app writes to a socket connection, the raw bytes are copied from the user space process to the kernel where TCP/IP takes place. 2/9

What is the difference between the two urls?
one has an @ and one doesn't.

But also the first downloads version 15 of postgres from GitHub and the second one resolves to v15 dot zip domain which can also downloads a zip file that sure doesn't have postgres in it.

You see,… twitter.com/i/web/status/1… medium.com/@bobbyrsec/the…

Database Indexing and Hotel rooms
(a thread)

If you’ve been to a hotel often you would see a sign looking something like this on the way to your room. This helps finding rooms convenient and fast especially if the hotel has many rooms. Take a hotel with 9 floors. Your room number is 917. The first step is to find which floor your room is at, usually the first digit represents the floor. This means your room is on the 9th floor

Memory Management and Fragmentation (a thread) When allocating items like arrays, strings or integers, they usually go to random places in the process memory. This leaves small gaps of unused memory scattered across the physical memory, a problem referred to as fragmentation.

The brilliance of @ShopifyEng database engineering (a thread) Shopify’s idempotency token is time based ULID, because these are unique which provide the idempotency) and also ordered by time requests can be quickly looked up in index and guess what! chances that you are querying requests that are next to each other temporally

I like debugging connection problems to backend applications and databases. I ran into an interesting one with SQL Server on aws and thought I’ll share it here (thread) Had two security groups one has the database replicas (sgDB) and the other has the backend applications (sgBK) added a rule so Port 1433 is allowed from sgBK to sgDB.

My approach for designing and architecting software. I thought might help some of you guys. I block 2-3 hours of uninterrupted time slots. No meetings, emails or any notification. I use a note app that is clutter free (I use write-monkey for Windows & Focus for Mac) full screen dark mode. This way its only me and what I type.