FYI’s 100-Bucket Fix Made Its Database Breathe Again

The scariest sound in a growing software company can be silence: a screen that stops responding, a query that never comes back, a database that must be rebooted while customers wait. At FYI, an Adelaide company that makes document automation and practice-management software for accountants, that silence had begun arriving without an invitation. The system would slow. Sometimes it would stop. The team would explain to customers that it understood the database problem and was working to contain it. Then it would return to the same uncomfortable question: how do you keep a fast-growing product from becoming a victim of the growth everyone wanted?

FYI had launched in 2018. By the time co-founder and chief technology officer Alan McLeod described the experience in a Pythian customer video, the bootstrapped business had expanded to about 52 employees. Its software was importing and maintaining millions of documents through the day, accepting data from third parties and moving customers away from legacy systems. This was not a showroom workload. It was heavily write-focused, migration-heavy and expected to be available all the time.

The database sat under nearly every promise the company made. A responsive system meant accountants could get to their documents. A stable migration meant a firm could leave its old software without fearing what might be lost in transit. Reliability was not a line item hidden behind the product; it was the product’s nervous system.

The bill for growing quickly

The early response was familiar: scale up, then try to solve as much as possible in code. That bought time, but it did not make the underlying behavior predictable. FYI saw lock contention, slower queries and intermittent downtime. Servers sometimes had to be rebooted. In the broader customer account, the AWS Aurora PostgreSQL database had grown beyond three terabytes, with the platform handling more than 250 million documents. Cloud spending was rising disproportionately, too, even though the company had expected and planned for growth.

McLeod had another constraint that will sound familiar to any small technical organization: specialization has a payroll. He wanted the team’s skills to remain broad, and he knew that a self-managed relational database could create a continuing need to hire people with deep, narrow expertise. Aurora appealed because its controls for scaling up and down felt intuitive. A new team member could learn the interface quickly, while FYI could use more AWS services out of the box and keep the organization functional without turning every developer into a database administrator.

Ease of operation, however, does not repeal database physics. A managed service can remove chores; it cannot decide how a company’s most important table should be shaped. As FYI’s data accumulated, the team needed someone to look below the instance size and ask what the database was actually spending its time doing.

A health check, not a sales pitch

FYI asked its partners at AWS for options and spoke with people it knew in the industry. It interviewed more than one practice. What distinguished Pythian, McLeod recalls, was a manner that felt less sales-driven and more absorbed by the problem. The engagement began with a deep examination of the infrastructure and interviews with FYI’s team. There were hypotheses, not theatrics. The output was a health report that exposed activity the internal team had not fully seen.

The diagnosis sharpened the story. According to Matt Pearson, Pythian’s lead database consultant, a single central table represented well over 70 percent of the database instance size. Size alone was not proof, so the consultants tested metrics and found that the assumption held. This table was not simply large in an abstract, impressive way. It was the place where query work and housekeeping work were colliding.

The table’s data was organized around customers. The engineering puzzle was how to divide it internally without disrupting that logical model or damaging data. PostgreSQL’s available hash partitioning provided a route. Records could be assigned to partitions using the customer-based partition key, distributing one unwieldy table into smaller physical pieces.

The team settled on roughly 100 buckets. Pearson is refreshingly plain about the number: there was no perfect metric declaring it sacred. Too many partitions introduce their own problems; too few leave each partition too large. One hundred was a reasoned middle, and testing showed that the middle worked.

The second win hiding inside the first

Faster queries were the obvious prize. A query that used the partition key could avoid rummaging through the entire giant table and work against a naturally smaller target. Yet the more instructive benefit was what happened to autovacuum, PostgreSQL’s essential garbage-collection process.

In Pearson’s explanation, autovacuum produces one process per table. Give it a very large table and the process may struggle to finish. That unfinished cleanup can become another drag on customer performance. Partitioning changed the unit of work. Instead of one marathon, the database had many shorter runs. Smaller chunks could be cleaned more quickly, and more autovacuum processes could work at the same time because PostgreSQL now saw multiple tables.

Calling the schema change “simple” would conceal the risk. The idea could fit on a whiteboard; migrating live, important data could not. FYI and Pythian devised a rollout plan and a comprehensive testing strategy, then scheduled work across carefully chosen periods. The goal was not merely a technically correct end state. It was reaching that state with minimal downtime while customers continued to access the application.

When reliability leaves the server room

The technical result was a more stable, responsive data engine. Pythian’s written case study also describes tuning production instances, addressing heavy indexing and unoptimized autovacuum behavior, and right-sizing over-provisioned AWS capacity so recurring infrastructure costs became more predictable. But McLeod talks most vividly about something harder to graph: confidence.

Before the work, the database had been narrowing the company’s imagination. Teams were discussing “all sorts of crazy things” simply to keep scaling. Engineers who wanted to implement features and solve customer problems were instead solving emergencies beneath the product. A sales or implementation colleague making promises to a prospective customer had to wonder whether the foundation would cooperate.

Afterward, FYI could look at the customer volume it anticipated and believe the system could support it. Product people could return to product. Sales and implementation teams could speak about where the service was going with evidence under their confidence. This is how infrastructure spreads through a business: instability turns every department cautious; stability lets them move in the same direction.

That confidence should not be mistaken for completion. McLeod says FYI continued to work with Pythian daily. The company began putting processes in place to review the database regularly, surface new signals and build plans for the next two or three months. The point was to notice small problems while they were still small.

This may be the least glamorous and most reusable lesson in the entire account. Database performance is not an appliance repair: broken, fixed, forgotten. It is closer to tending a working landscape. Data keeps arriving. Customer behavior changes. New features create new query patterns. Yesterday’s sensible capacity becomes tomorrow’s waste, and today’s harmless table can become next year’s majority shareholder.

The architecture of permission

FYI’s product promise is to give accounting practices one cloud workplace for documents, collaboration and automation. Its current site says the platform powers more than 33,000 accountants across Australia, New Zealand and the United Kingdom. That geographic reach is visible at the interface, but it is made possible by decidedly unglamorous decisions underneath: how a row is assigned, when dead data is cleared, whether a maintenance process can finish before the next wave of work arrives.

The 100-bucket choice is memorable because it is concrete. It is also useful because it resists a common mythology of scaling. The solution was not infinite hardware, a heroic rewrite or a magic setting. It began with measurement. It changed the structure at the actual hot spot. It paired the change with testing, timing and ongoing observation.

There is a pleasant modesty in Pearson’s telling. The number was a middle-ground judgment. The “simple change” was, as he immediately corrects himself, complex. Expertise here did not mean pretending uncertainty had vanished. It meant containing uncertainty inside a process that could protect the data and the people relying on it.

For customers, the ideal ending is almost boring. A document opens. A migration finishes. The application is there tomorrow morning. For the team behind it, that ordinary experience is not dull at all. It is the sound of a database breathing—and of a company allowed to think beyond the next reboot.

Reporting is based on the supplied Pythian video transcript and primary materials from Pythian’s FYI customer story, its AWS partnership page and FYI.