My chaotic journey to find the right database

What are the challenges of building a local-first database?

Creating a local-first database presents several complexities, given the need for seamless offline functionality and efficient data management. The primary challenges include:

• Data Model Synchronization: Maintaining a consistent data model as applications evolve quickly is crucial. Developers need to ensure that changes made offline synchronize correctly with the server once connectivity resumes.

• Handling Deletions: Implementing soft deletions poses another hurdle. Instead of removing entries outright, they are marked as deleted to prevent reappearance on other devices during synchronization.

• Offline Functionality: A robust sync layer is essential. It allows users to access, modify, and search their data without network dependency, enhancing user experience in offline scenarios.

Figma also faced unique challenges in scaling Postgres for consistency and minimal developer impact, illustrating the need for tailored solutions in local-first databases.

How did T3 Chat handle database migrations?

Migrating T3 Chat's database from Redis to SQL on PlanetScale involved careful planning and execution. Initially, a Redis setup stored user data in a simple key-value model. Each user's data was compressed and stored as a single blob.

As the application grew, handling large payloads became inefficient. The solution was to transition to a more scalable system — SQL on PlanetScale.

The migration process involved:

• Creating a new SQL Schema: Tables were defined for messages and threads, ensuring data integrity and efficient queries.

CREATE TABLE messages (
  id INT PRIMARY KEY,
  user_provided_id VARCHAR(255),
  user_id INT,
  data TEXT
);

• Updating Endpoints: Backend logic shifted from managing compressed blobs to operating on structured SQL tables. The GET and POST routes now interact with the new SQL model.

• Data Backfill Strategy: On loading, legacy data was retrieved from Redis if needed, then transferred to the new SQL layout. This ensured continuity and data was properly migrated without user disruption.

if (oldDataExists) {
  const legacyData = getLegacyData(userId);
  backfillToSQL(legacyData);
}

The transition resulted in improved performance and scalability, addressing key limitations seen with the Redis setup.

Why was Dexy chosen for the local database solution?

Dexy emerged as the preferred choice for the local database solution due to several compelling factors. Its ability to wrap around IndexedDB provided simplicity in managing local data, which is often complex and cumbersome to work with directly.

Pros of Using Dexy:

• Simplicity: Dexy simplifies interactions with IndexedDB through a friendly API. It minimizes the challenges of handling a complex key-value store.

• Flexibility: The library allows defining data shapes and indexes, optimizing how data gets retrieved and stored. Customization became more manageable, crucial for agile development needs.

• React Integration: Dexy’s compatibility with modern frameworks, such as React, provided natural integration with T3 Chat's stack. This supported real-time updates without reinventing components.

Cons of Using Dexy:

• Limited Open Source: DexyCloud, a cloud variant, has limitations in open-source accessibility. Its unusual pricing model and licensing raised concerns about evaluating performance limitations at scale.

• No Direct Sync Engine: While handling local data efficiently, Dexy doesn't directly include a sync engine for more complex requirements like real-time collaboration or remote sync.

Choosing Dexy offered a robust foundation for T3 Chat's local-first architecture, but it wasn't without its challenges.

What were the performance issues with Redis?

Redis encountered significant performance challenges, primarily due to its handling of key count and data retrieval inefficiencies.

Key factors contributing to these issues included:

• Excessive Key Count: Originally, the key-value setup meant each user had a single key storing all data. Transitioning to individual keys for every message and thread vastly increased the number of keys—reaching nearly a million within hours. This overwhelmed Redis, as it functions best with fewer keys to manage.

• Inefficient Data Retrieval: Retrieving vast amounts of data from multiple keys strained Redis’s capabilities. The sheer volume of queries, particularly when fetching thousands of items simultaneously, slowed performance significantly.

These factors highlighted Redis’s limitations in managing a high volume of granular data entries. It’s optimized for rapid access to a modest set of keys, not for extensive datasets requiring frequent retrieval operations. This realization underscored the need for a more scalable solution conducive to high-load environments.

How does PlanetScale SQL improve database performance?

PlanetScale SQL significantly boosts database performance, particularly in handling extensive data operations. It utilizes Vitesse, an advanced MySQL scaling technology, offering several advantages:

• Scalability: PlanetScale's Vitesse allows for efficient data sharding. This feature distributes data across multiple nodes, enhancing the capability to manage large datasets seamlessly.

• Speed Efficiency: With Vitesse, even with millions of rows, query response times remain swift. For T3 Chat, row reads consistently ranged from 9 to 200 rows per second without incurring additional costs beyond the $30 a month base tier.

• Reliability: As a solution based on proven technology, it maintains consistent performance under heavy load. Even when migrating from Redis, PlanetScale absorbed the data effortlessly, showcasing its robustness in demanding scenarios.

Overall, PlanetScale SQL offers a superior performance model, effectively handling large-scale operations with remarkable efficiency and reliability.