When I decided to build my chat application, I naturally started with PostgreSQL. It’s been my go-to database for a while - stable, feature-rich, and with a solid reputation for handling a variety of workloads. Here’s why I initially chose it:

  • A Well-Established Ecosystem: PostgreSQL has been around for decades. The community support is fantastic, there’s extensive documentation, and a lot of extensions are available.
  • Data Integrity: PostgreSQL’s ACID compliance means I can trust the consistency of my data.
  • Powerful Querying: Its SQL engine is robust, supporting complex queries, indexing strategies, and even full-text search.
  • Flexibility: With support for JSON/JSONB data types, custom data types, and procedural languages like PL/pgSQL, PostgreSQL can adapt as my application’s needs evolve.

The Initial Setup

To get things rolling, I set up a chat_room table to store the chat data:

CREATE TABLE chat_room (
  id SERIAL PRIMARY KEY,
  room_id INT NOT NULL,
  user_id INT NOT NULL,
  chat TEXT NOT NULL
);

This setup will work fine for simple cases. But as I started thinking about scaling, I realized there might be issues down the road.

The Scaling Challenge

Chats can grow rapidly, especially with lots of active users and chat rooms. Imagine needing to fetch all the chats for a specific room quickly—it has to be fast to keep users happy. Even with an index on room_id, I started to worry. How big will the database get? Will performance start to lag as the data grows?

The main operations in a chat app are reading and writing messages for a specific room, and they need to be snappy. Ideally, you’d want all the messages for a room stored close together on disk to speed up retrieval.

But with a traditional relational database like PostgreSQL, all the chat messages are stored in the same table, meaning data from different rooms is mixed together on disk. As the table grows, it becomes less efficient to fetch messages quickly.

Rethinking the Approach

I thought about partitioning the chat data by time—like storing chats in weekly partitions. This could help control the size of each partition and make data management easier. But implementing this in PostgreSQL can get complicated, and I wasn’t sure it would fully solve the performance issues.

That’s when I started exploring other databases and came across ScyllaDB.

Why ScyllaDB?

ScyllaDB is a NoSQL database designed for high performance and scalability. Here’s what attracted me to it:

  • Automatic Data Partitioning: It handles data partitioning across nodes automatically, which helps distribute the load and keeps things running smoothly.
  • Speed: ScyllaDB is built for fast read and write operations—perfect for a chat app where performance is key.
  • Easy Scalability: As the app grows, I can add more nodes to the database cluster without a lot of hassle.

Making the Switch

With ScyllaDB, I restructured how I store chat data. Now, messages are partitioned by room and by time (like weekly). This ensures that all messages for a room within a specific time frame are stored together, improving retrieval speeds.

Here’s an example of the table structure in ScyllaDB:

CREATE TABLE chat_messages (
  room_id INT,
  bucket INT,
  message_id bigint,
  user_id INT,
  chat TEXT,
  PRIMARY KEY ((room_id, bucket), message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);

By using a composite partition key (room_id, bucket), related chats are stored together on disk. This setup makes reads and writes more efficient and helps the database scale as more data comes in.

The Benefits

Switching to ScyllaDB brought several advantages:

  • Improved Performance: The app can handle more users and messages without slowing down.
  • Scalability: Adding more nodes is straightforward, allowing the system to manage increased load seamlessly.
  • Easier Data Management: Time-based partitioning makes it simpler to manage older data, like archiving or deleting old messages.

Conclusion

Building this chat application taught me a lot about choosing the right tools for the job. PostgreSQL is a fantastic database, but for the specific needs of a high-throughput chat app, ScyllaDB turned out to be a better fit.

There’s no one-size-fits-all solution when it comes to databases. It’s all about understanding your application’s requirements and picking the technology that best meets those needs. In my case, making the switch to ScyllaDB allowed me to build a faster, more scalable chat application. In the future i will write more about ScyllaDB and it’s distributed architecture.