Andy Webster

Oracle DBA, techie

[Chris Power @typecraft_dev]

I have an announcement

[Paul Bronks @SlenderSherbet]

When the work meeting is almost over and that one co-worker keeps asking questions.

[Craig Kerstiens @craigkerstiens]

How to understand Postgres scan types -
https://t.co/7qsPigWdnm

[Crunchy Data @crunchydata]

In Postgres, varchar(255) behaves just like a text column behaves, only the varchar has a length constraint. varchar(n) has always been this way in Postgres. When you see a varchar(255) column in Postgres, many of them are because someone learned with a different database first, and carried with them their prior assumptions about `varchar(n)` behavior.

When do you use text and when do you use varchar? When there is a business requirement to enforce a maximum length or the field is btree indexed, consider `varchar(n)`. When there is no such requirement, use `text`, and don't feel like you are doing somethign wrong. In fact, the Postgres documentation says:

> * While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

It takes nothing to change a `varchar(n)` column to a `text` column. Why? Because it's just a metadata change, and the underlying storage is the same. The metadata is in the pg_attribute table, and changing the type is just a matter of updating that metadata table. The data itself is stored in the same way, so there is no need to rewrite the table or change the storage format.

It's different for char. When migrating from char to text, the casting of values runs `rtrim` against the char. This happens because the implicit cast from char to text strips the spaces automatically. This is a more expensive operation than just changing the metadata, and it requires rewriting the table.

How PostgreSQL actually stores strings

It's a variable-length storage format that PostgreSQL uses for all text-like types. It's either inline storage or TOAST (The Oversized-Attribute Storage Technique), which handles compression and out-of-line storage when values (or rows) get large (typically when a row exceeds ~2KB). `varchar(n)` and `text` both can use TOAST.

When you might use `varchar(n)`?

Use `varchar(n)` when the length limit is a constraint the database should enforce, like a country code, currency code, or SKU:

```sql
country_code varchar(2), -- ISO 3166-1
currency_code varchar(3), -- ISO 4217
us_zip_code varchar(5), -- 5 digits
sku varchar(20), -- printer limitations
```

Should you use varchar for country_code and currency_code? Sure, it is an international standard that defines the length of those codes. For SKU, the length limit may be limited by external requirements. Having written enough data structures with `us_zip_code`, they eventually have a need for extended ZIP+4 codes. Eventually, those zip_code columns can become postal_code to accept international postal codes. Using varchar gives the constraint and flexbility on columns.

Simply put, varchar helps enforce a business rule.

Indexed columns? Consider varchar(n)

Indexing text columns is not a problem, but ...

varchar(n) can act as an operational safety-net when a column is indexed (like when a `username` is used for lookups and not just a label, maybe authentication or profile lookup). Why? Because Postgres refuses to index a column using btree when a value exceeds 2704-ish bytes. Actually, it's not a straight 2704 bytes, it's a compressed 2704 bytes, so it's hard to give a precise character limit as different strings of characters compress differently. These varchar length limits can prevent upstream applications from inserting unindexable values, and the variable length of the strings make the errors seem random. Yes, the index will act as it's own safety guard and rollback the transaction on failure, but size is inconsistent due to compression and the index failure error messages will be a bit cryptic:

```
ERROR: index row size 2832 exceeds btree version 4 maximum 2704 for index
```

Where varchar gives a clearer error message, like:

```
ERROR: value too long for type character varying(45)
```

[Turbine Traveller @Turbinetraveler]

THE ONLY PHOTO EVER TAKEN OF CONCORDE AT MACH 2
This remarkable image shows Concorde G-BOAG cruising at Mach 2 (1,350 mph) over the Irish Sea in April 1985.

Captured from an RAF Panavia Tornado fighter jet, the rendezvous lasted just four minutes as the Tornado, rapidly running low on fuel, struggled to keep pace with the supersonic airliner.

At Mach 2, Concorde's 204-ft airframe stretched by nearly six inches due to aerodynamic heating. Temperatures reached approximately 127°C (261°F) at the nose and wing leading edges, while its distinctive high-reflectivity white paint helped dissipate the intense heat generated by supersonic flight.
📸: ConcordePhotos

[Raul Junco @RaulJuncoV]

A senior developer is a problem avoider.

AI can't replace that.

I've worked with two kinds of senior developers.
The first kind comes to meetings with new tools, HackerNews posts, and companies we should be more like.

The second kind comes with questions.

"That adds complexity. What's the actual cost of not doing it?"
"What breaks if we skip this entirely?"
"What's the simplest thing that could work here?"
"Who asked for this besides us?"
"What are we not building if we build this?"
"Can we delete it in six months if we're wrong?"
"Is this solving a problem we have, or one we're afraid of?"

I used to find that frustrating. It felt like resistance.
Now I know it's the job.

Every line of code I've written is a liability I handed to the next person.

Every new table, every new condition, every new abstraction is complexity someone has to maintain, debug, and explain at 2am when something breaks.

The senior developer I want on my team asks whether the problem deserves a solution at all.

AI will write your code faster than any human alive.
But it won't tell you to stop writing.

[Nightingale Associates @FCNightingale]

The owner of a vacant office building at 146 Navarro St. San Antonio, Texas is seeking to sell the building.

Three years after finishing upgrades, the building is still empty.

BH Properties executives said they spent more than $10 million replacing windows, installing outdoor seating and greenery, updating the lobby and adding about 20,000 square feet of retail on the street level.

An affiliate of BH Properties bought the building from CPS Energy for $22.25M in 2021.

Seven story parking garage was built in the late 1960s to provide parking for HemisFair '68.
Three stories of offices were added in 1987.

San Antonio Express-News
#commercialrealestate

[Crunchy Data @crunchydata]

Under what conditions does Postgres materialized a CTE?

Just to catch everyone up, think of a CTE as a named result set defined before the main query, and is referenced in the main query. When Postgres materializes a CTE, it executes the CTE once and stores the result in memory or temporary disk space. The unmaterialized CTE, on the other hand, is inlined into the main query and executed as part of the main query's execution plan.

So, when does Postgres materialize a CTE? The answer is: when the CTE is referenced multiple times in the main query. If a CTE is only referenced once, Postgres will inline it into the main query and execute it as part of the main query's execution plan. But if a CTE is referenced multiple times, Postgres will materialize it to avoid re-executing the same logic multiple times.

Take a look at the annotated attached image of an EXPLAIN for a particularly complex query. In this query, the CTE is named "my_cte" (shout out to Perl folks). You'll see that it is executed once, then referenced as a "CTE Scan" in the main query's execution plan.

random() caveats

If a CTE has a volatile function (like `random()` and `gen_random_uuid()`), Postgres will materialize it even if it is only referenced once. This is because the result of the CTE could change between executions, and Postgres wants to ensure that the same result is used throughout the main query. Of course, if you want to maximize chaos, you can force it to be `NOT MATERIALIZED` (more on that in a bit).

Other scenarios where Postgres will materialize and cannot be inlined include:

• When the CTE is recursive (WITH RECURSIVE)
• Locking clause (SELECT ... FOR UPDATE)
• Data modification (INSERT, UPDATE, DELETE) using a returning clause

But those make sense, right? Don't want to lock a table multiple times or have multiple inserts happening inline in a query (you can't choose to maximize chaos with this, as NOT MATERIALIZED will be ignored with these caveats).

Forcing (Un)Materialization

With CTEs, force materialization or inlining using the `MATERIALIZED` and `NOT MATERIALIZED` keywords.

```sql
WITH my_cte AS MATERIALIZED (...
```

or:

```sql
WITH my_cte AS NOT MATERIALIZED (...
```

A scenario that might improve with MATERIALIZED on a single use CTE would be if the CTE is expensive on it's own, and creates additional overhead when inlined into the main query. This may happen because of an incorrect ROW estimation by the planner causing it build an unoptimized query plan.

A scenario that might improve with NOT MATERIALIZED on a multi-use CTE would be if the CTE is already a efficient index scan. The materialization of an optimized query can be slower. By forcing the CTE and main query to be inlined, the optimizer may benefit from combining WHERE conditionals for the CTE and main query (called "Filter Push Down").

Most of the time, just let Postgres handle it.

[Mike Netter @nettermike]

In 1951, Rose Totino walked into a Minneapolis bank asking for $1,500 to build her frozen pizza empire. The manager said no.

He didn't just reject the application. He didn't know what she was selling. She called it "pizza." He had never heard the word. It was a regional food, mostly confined to East Coast Italian immigrant neighborhoods. In the Midwest, it was alien. Rose had a high school education and a recipe from her mother. The bank had strict lending rules for women. The answer was a flat denial.

Instead of leaving, she asked the manager if he had an oven at home. He did. She told him not to eat lunch the next day.

The next morning, she stood in the small kitchen she shared with her husband, Jim. They were barely scraping by. Rose spent the hours before dawn kneading dough, simmering tomatoes, and slicing cheese.

The raw pie went into a square box. She carried it back to the bank. Bypassing the tellers, she walked straight into the manager's office. She handed him the box and gave him baking instructions. He took it home.

At the time, the banking industry relied heavily on character loans, but character was defined by collateral and male guarantors. The commercial lending guidelines of the early 1950s made no provision for culinary demonstration. Women could rarely secure business capital without a husband's signature, let alone for a product the loan officer couldn't pronounce.

The manager baked it. He ate it.

He called her the next morning. He approved the $1,500.

They opened a takeout shop. It was so small they had to store fifty-pound bags of extra flour in the backseat of their family car. But the city had tasted it. Lines formed around the block. They paid off the loan. Then they bought a factory.

Rose realized that if she froze the dough, she could ship it across the country. Nobody had successfully mass-produced the item before. She figured out how to keep the crust from turning to cardboard. She patented the process.

They shipped them in refrigerated trucks. The food the bank manager couldn't pronounce was now in freezers from California to Maine. By 1970, Totino’s was the top-selling brand in the United States. Pillsbury bought the company in 1975 for nearly $20 million.

She didn't argue with the rejection. She just gave the manager baking instructions.

She became the first female vice president in Pillsbury’s history. She sat in boardrooms with men who had Ivy League degrees. She still tasted the test batches herself.

The original takeout shop closed years ago. The brand she started now sells hundreds of millions of units a year. The recipe has changed. The boxes look different. The freezer aisle in every grocery store in America is built on a $1,500 gamble by a man who didn't know what he was eating.

Rose Totino: the woman who taught America how to eat pizza.
One woman’s vision created a culinary shift in America
Did you know this?

[Spike Brehm @spikebrehm]

they did it. the mad lads actually did it.

i never talked about my time at DOGE last year because it was so controversial and contentious (remember that?)

early last year, @jgebbia recruited a handful of his most trusted early Airbnb engineers to embed at the Office of Personnel Management to solve the "retirement paper" problem.

processing a federal retirement took months, and in the extreme retirees could wait up to 6 months for their full pension to arrive. what was the holdup? paper. remember hearing Elon talk about "the mine" in Pennsylvania? we got to visit it. in deep underground caverns blasted out of limestone, there were literally acres of file cabinets, as far as the eye could see, storing files detailing federal employees' employment and paystub history. a simple "case" might be only a quarter or half inch thick, but really complex cases filled up whole filing cabinets. one famously took up a whole pallet.

each case was hand processed by case workers in cubicles deep underground. they checked calculations, made sure forms were filled out properly (many weren't), and handled a long tail of complex issues. we'd watch as they keyed data into a black and white terminal, transmitting to the COBOL mainframe built many decades ago.

since cases were processed by hand, there were multiple rounds of human review, and additional rounds for complex cases. case files were walked around between one worker's outbox and another's inbox. sometimes it would sit in one place for days, waiting to be picked up.

to OPM's credit, they'd done multiple rounds of "digital transformation" spanning decades, so some systems were newer than others. there was a big effort in the mid-90s. but the systems were disparate, and it was a total maze getting them to talk to each other. there was a big effort to build a web app where employees applying for retirement could digitally fill out the necessary forms — just to be mailed to the mine and stuffed into the paper file. and few federal agencies were even using it.

when we arrived, OPM was midway through a fresh attempt at digital transformation, delivered by a software contractor.

the blackpill was seeing the terrible quality of the software and interacting with the contractors. coming from silicon valley, i couldn't believe how low the talent and quality bar was for selling software to the government. it's clear, as the OG USDS people explained to me a decade ago, the primary skill these vendors have is securing government contracts. it's a huge moat. delivery of quality product be damned.

we fired the vendor and took over the project. they'd been working on it for more than a year, and there was another year before they were going to deliver it. at first we tried to bend it to our will, to actually connect all the various data sources and get to a decent UX for case workers in the mine to use, but we soon realized we were going to have to rebuild the whole stack from scratch.

it was around this time I had to go back to new york — i had a new job waiting for me, a four month old, and a wife whose patience was running out. but i got to watch from afar as the team cranked day and night, hitting early milestones. and now they've fully done it.

huge congrats to Joe and the team. @yatshitcray was the hero in the trenches. indefatigable, unrelentingly optimistic, and determined to see this project through. when i recruited him for "ok i can do two, maybe three months", he stuck it out over a year making this project a reality.

while the retirement project was under the DOGE banner, it operated different from what you heard from the breathless, negative media — we came in with the attitude of partnering with career OPM employees. we were team members determined to bring our software talents to bear on the problem they've been trying to fix for years, which they hadn't had the resources to solve before. they were wary at first, not sure about us, but they quickly saw how authentic and determined we were to work together toward the same goal. props to Joe for developing those relationships, setting the example of how to collaborate together.

what's the end result? lifelong federal employees, veterans, postal carriers get their full pension installments almost immediately. days instead of months. peace of mind for these people to devoted their careers to serving our country. massively streamlined operations inside of OPM. and NO MORE PAPER 🫡🇺🇸

[Retro Tech Dreams @RetroTechDreams]

Texas Instruments Business-Pro Computer (1986)

[Charlie Bilello @charliebilello]

Semiconductor stocks were up 237% over the last 14 months, surpassing the 234% surge during the peak of the dot-com bubble.

The AI mania has taken semis to a place only seen once before.

History doesn’t repeat, but extremes tend to rhyme.

Video: https://t.co/LBXJJFkuG1

[Chris Power @typecraft_dev]

new @PlanetScale video just dropped!

https://t.co/W2kmDbMgDn

[FFmpeg @FFmpeg]

FFmpeg's native AAC encoder has just been rewritten, and now beats both fdkaac and qAAC according to current metrics and listening tests.
This is not a small change. @X and @OBSProject use it, as well as many others. It's been a critical piece of the internet, and is now the best

[Uros Popovic @popovicu94]

Whenever I post about building a Linux distro, someone says "just do Linux From Scratch."

LFS is great. It's also a weekend of compiling before you boot anything.

You can grasp the core idea in 10 minutes instead.

A distro is two things: the kernel and user-space binaries. When the kernel finishes booting, it hands control to exactly ONE process: init. Your shell, your apps, your services all descend from it.

That's the entire model. You can prove it with an init that's just "Hello World," then scale up to something like systemd when you want to.

Lightweight build, running in 10 minutes: https://t.co/EOP8mQQPRU

[The Lunduke Journal @LundukeJournal]

The Boot/Root distributions! Back when you could squeeze Linux onto a floppy (or two)!

Both Torvalds and Winstead had early floppy “distributions” back around ‘91.

But HJ Lu’s “Boot/Root” was where it really took off. Linux Kernel version 0.12 in 1992 (?) was great. One floppy to boot, then another floppy with the extra stuff.

Tom’s Root Boot (tomsrtbt) came a little later (late 90s) and was an effort to put modern Linux onto a floppy. Which was pretty awesome.

[Linux Inside: The Ideal Blog for Sysadmins & Geeks @tecmint]

Most sysadmins scroll through systemctl status for 5 minutes to find one broken service.

systemctl list-units --failed answers the same question in two lines.

New guide with 5 real examples, including the flags that make it script-safe.

https://t.co/dG3Gf4z07l

Follow @tecmint for more #Linux tips.

[Commodore Computer Museum 🕹 @MuseumCommodore]

In Memory of Jay Miner, the Father of the Amiga

Today, June 20, marks 32 years since we lost Jay Miner — the brilliant engineer and visionary who gave the world the Amiga.

Born on May 31, 1932, Miner’s passion for pushing hardware to its creative limits changed computing forever. His legacy still inspires retro enthusiasts, game developers, and tinkerers decades later.

Jay is best remembered as the driving force behind the AMIGA, launched in 1985 by Commodore. The Amiga 1000 was revolutionary: stunning graphics (up to 4096 colors), rich 4-channel stereo sound, and true preemptive multitasking years before it became standard on other platforms. Powered by a Motorola 68000 CPU and Miner’s custom chips — Agnus, Denise, and Paula — it became the ultimate machine for gamers, artists, musicians, and video creators.

From Defender of the Crown and Deluxe Paint to the Video Toaster, the Amiga didn’t just run software — it unleashed creativity like nothing before it. "Only Amiga Makes it Possible" was how Amiga was known!

Before the Amiga, Miner helped define home gaming and computing at Atari. He co-designed the groundbreaking TIA chip for the Atari 2600 (the heart of classics like Space Invaders and Pong) and led the graphics architecture for the Atari 400/800 computers.

Jay didn’t just build machines — he built dreams. He believed computers should empower people to create, play, and imagine. On June 20, 1994, we lost a true pioneer and every modern system owes a debt to his forward-thinking designs.

What did Jay Miner’s work mean to you?

RIP Jay Miner. Thank you for the Amiga

[Connor on SQL and Database @connor_mc_d]

The perfect guide to the oracle optimizer
https://t.co/ZEm9PP0DcX
Kudos @vldbb

[Jafar Najafov @JafarNajafov]

A 22-year-old computer science student in Rochester, New York got so frustrated with the way JavaScript worked differently in every browser that he wrote a library to fix it.

He released it for free at an unconference in January 2006. That library became the foundation of the web for an entire generation.
It now runs on 77 percent of the top 10 million websites on Earth. He never started a company around it. He gave it away and went to work on education and Japanese art.

His name is John Resig. The library is called jQuery.

Here is the story.

John was born on May 8, 1984 in Boston, Massachusetts. He grew up fascinated by computers and programming. He attended the Rochester Institute of Technology and studied computer science with concentrations in economics and psychology. During college he lived in Computer Science House, an on-campus living and project community where students build things for fun. His housemates voted him Member of the Year during his sophomore year.

The problem that consumed him at RIT was cross-browser JavaScript. In 2005 the web was a mess. Internet Explorer, Firefox, Safari, and Opera all handled JavaScript differently. Writing code that worked the same way in every browser was painful, repetitive, and fragile. Developers spent more time fighting browser inconsistencies than building features.

John spent years at RIT building personal tools and libraries to make JavaScript easier to write. He wanted to share them with the world in a clear and concise way. In 2005, while still a student, he started building jQuery.

In January 2006 he released it at BarCamp NYC, a tech unconference whose core rule was no spectators, only participants. He was 22 years old. The library was small, elegant, and solved the exact problem every web developer was struggling with. You could traverse the DOM, handle events, animate elements, and make Ajax calls in a few lines of code that worked the same way everywhere.

The adoption was immediate. jQuery was the only open-source JavaScript library at the time that shipped with documentation. Most libraries expected developers to read the source code. John's first hire was not a developer. It was a community manager, someone to help answer questions from developers who were adopting the library. He understood from the beginning that adoption was a human problem, not a technical one.

Drupal selected jQuery as a core component. WordPress built on it. Microsoft adopted it. Apple used it. Google, IBM, Amazon, and AOL incorporated it into their websites. Django and Rails integrated it. Mozilla built with it.

By the early 2010s jQuery was everywhere. It became the most deployed JavaScript library in history by a margin so large that the second place contender was not even close. As of recent measurements, jQuery runs on approximately 77 percent of the top 10 million websites.

John worked at the Mozilla Corporation from 2007 to 2011 as a JavaScript evangelist and tool developer. He authored two books, Pro JavaScript Techniques and Secrets of the JavaScript Ninja. He created Processing.js, a port of the Processing language to JavaScript. He created Sizzle, a standalone CSS selector engine. He created QUnit, a testing framework.

In 2011 he joined Khan Academy as Chief Software Architect. He has been there ever since, building the technology behind one of the largest free education platforms on Earth.

Then he did something nobody expected from a JavaScript legend. He became a scholar of Japanese woodblock prints.

John is a Visiting Researcher at Ritsumeikan University in Kyoto, studying Ukiyo-e, the Japanese art of woodblock printing from the 17th to 19th centuries. He built ukiyo-e .org, a comprehensive database and image search engine that lets anyone search across hundreds of thousands of prints from museums and collections worldwide. He applied the same engineering instincts that made jQuery work, simplify, document, and make accessible, to a centuries-old art form.

He was inducted into RIT's Innovation Hall of Fame in 2010. He lives in the Hudson Valley of New York.

A frustrated college student wrote a JavaScript library in his dorm room and gave it away for free.

It became the invisible foundation of the modern web.

He went to Kyoto to study woodblock prints.