When, why, and how to upgrade spreadsheets to PostgreSQL

This post was originally written by Kevin Whinnery.

Many businesses are run off of spreadsheets. From your local coffee shop to the US military, spreadsheets handle a lot of heavy lifting in many workflows. More than 3 billion people use Excel and Google Sheets every month. Unfortunately, there comes a time when even this powerful and familiar tool becomes a liability. Spreadsheets can get out of sync when emailed back and forth, and are vulnerable to typos and human error. At a certain level of complexity, you should strongly consider moving from spreadsheets to a relational database, which provides more guarantees about the integrity of your data.

Read on to learn about the warning signs of impending spreadsheet disaster, how to recognize them, and how to move to a database when the time is right.

Recognizing when spreadsheets are falling over

As your spreadsheets grow in complexity, here are some pains you might start to feel - if you read through these pain points and find yourself nodding along, it might be time to consider upgrading to a database.

1. You’re struggling to manage large amounts of data. Your key spreadsheet has dozens of columns and tens of thousands of rows of data. At around 40,000 rows, you’ve started to notice that scrolling and calculations are slowing down.
2. You have too many editors, and you keep stepping on one another’s toes. Spreadsheet software like Google Sheets supports multiple people editing at the same time (i.e. multiplayer collaboration), but they lack version and access controls. Your key spreadsheet is difficult to keep in a predictable state, because so many people need to change and update it.
3. It’s hard to get the insights you want from data in spreadsheets. Maybe your business uses several key spreadsheets, and you have a business intelligence question that needs data from all of them. You find it hard to use the relatively limited query and data modeling capabilities of spreadsheets to get all the insights you’d like from your data.
4. Costly mistakes and bad data because of human error. Spreadsheets are very malleable by default, and don’t have built-in mechanisms to ensure data integrity, like transactions, foreign key constraints, and data validation. As a result, sometimes your data is inaccurate, which impacts decision making and workflows that depend on spreadsheet data.

These pain points typically appear when your use case evolves from data storage, analysis, and visualization. Spreadsheets actually do those things reasonably well. What spreadsheets don’t do well is manage large, complex data sets, and relationships between different data points. They also don’t work well to model business processes and operations, which often have multiple steps and validations that need to happen at different steps. When your use case shifts from analyzing static data to driving business processes, that’s when you’re likely to encounter trouble.

No-code tools provide a partial solution

Some teams might upgrade their spreadsheet use cases to no-code tools like Airtable. Airtable has become a popular alternative to spreadsheets by providing more structured data storage. And it’s easy to understand why it’s been so compelling - Airtable combines the simplicity of a spreadsheet interface with the basic functionality of a relational database. It also enables teams to create customizable views and simple apps, and integrate with other tools.

This upgrade might be sufficient for some spreadsheet use cases, if your primary pain point is just modeling relationships between data points. There are a few spreadsheet problems that aren’t solved at this stage of evolution, however.

1. Performance issues and storage limits : As the amount of data you store in Airtable bases grows, interacting with data through the GUI can start to slow down (similar to spreadsheet software). There are also limits to the size of your data set enforced by the product (250k rows in an Enterprise plan as of this writing).
2. Modeling business workflows : Airtable is a major upgrade over spreadsheets in data modeling, but it’s hard to model more complex, multi-step business processes. The ability to create forms and customized user-facing views is nice, but they are still tightly coupled to pushing data in and out of a spreadsheet-like data store.
3. Inconsistent interfaces to data : because views and schemas in Airtable are still quite malleable by default, data integrity can still become an issue without great care taken in configuration.
4. Limited query capabilities : Airtable has a REST API to access data and resources, but its query capabilities are limited when compared to languages like SQL or GraphQL. So while this is probably a step up from a spreadsheet, it might still not be flexible enough.

No-code solutions such as Airtable provide a meaningful upgrade over spreadsheets, but still experience some of the problems at scale that you might be feeling in a spreadsheet. If you find yourself struggling still when using these tools - it’s probably time to consider moving your data to a relational database.

Building with a PostgreSQL database

The final evolution from spreadsheets is a relational database - like our personal favorite PostgreSQL. A relational database like Postgres can potentially address many issues in a spreadsheet that has ballooned into an unmaintainable state.

1. Performance and scale: A database can store extremely large data sets, far beyond what’s reasonably possible in a spreadsheet-like tool. And because database software is designed to hold such large data sets, it is also designed to access that data fast, at any scale.
2. Robust data modeling : Relational databases are designed to support very complex data models - whatever types of data your business cares about, it should be possible to model it in a database.
3. Data integrity : Relational databases are designed to promote data consistency and integrity. The format of data is strictly enforced, and prevents many classes of human error.
4. Advanced querying : A database that supports SQL queries can enable you to answer just about any question from your data, joining individual data points across multiple tables within the database. SQL is the “lingua franca” of data access for a reason!
5. Supports building application software on top : A SQL database can be combined with a wide array of software development tools to build applications that interact with your data. Custom software enables you to model any kind of business workflow, rather than just pushing data in and out of a spreadsheet. And if you choose platforms like Retool, those applications can be almost as easy to create as customizing a spreadsheet or arranging a PowerPoint presentation.

So let’s say you are sold on the utility of a relational database, but your data is still locked in Airtable or a spreadsheet. And maybe you’re pretty comfortable with a spreadsheet interface, but have never used a relational database before. If this sounds like you, Retool Database might be the best first step into the world of managing data in a relational database. It offers both an optional graphical interface that is spreadsheet-like, and is integrated into Retool, which makes it much simpler to build software and visualizations on top of your data than typical app development tools. Let’s take a look, at a high level, how you could migrate from a spreadsheet to Retool Database.

From spreadsheets to Retool Database

Your first step in migrating from spreadsheets to a relational database will be exporting your data from either Airtable or your spreadsheet software as CSV files. This bare-bones text representation of your data can make it portable into different environments, like a relational database. Retool Database, through its graphical interface, allows you to import CSV files as new tables in your database.

To migrate your spreadsheet or Airtable data to Retool Database, just upload your CSVs into Retool. Retool will automatically create PostgreSQL tables for you. There’s no setup or configuration required.

Uploading a CSV into Retool Database

Once you’ve uploaded your CSV, Retool Database will create PostgreSQL data type columns and insert your data for you. Double-check that the names and field types match your preferences.

Schema mapping

From there, you can quickly build out your data model and schema with the Retool Database UI—add tables and configure fields in a few clicks.

With Retool, you can start with a PostgreSQL database and build custom applications that work for you and your users. If you want to use Retool Database, you get up to 5GB of data storage free for one year. To get started, create a free Retool account.

Custom apps and workflows

With your data now in a PostgreSQL database, you can use Retool, a developer platform for business software, to build custom apps and workflows on top of your database much faster than coding from scratch. These apps can be complex and custom enough to handle many use cases, and can scale to thousands of users.

Retool comes with several features to help you build faster.

• Drag-and-drop interface : Create user interfaces quickly and easily using Retool's visual editor, without needing to write extensive frontend code.
• Pre-built components : Leverage Retool's library of hundreds of pre-built components, such as tables, forms, and charts, to streamline your app development process.
• Seamless integrations : Connect your Retool app to your Postgres database and other third-party services using Retool's built-in integrations.
• Collaboration and source control : Work together with your team on app development and track changes with Retool's source control features.
• Automate ETL tasks and alerts : Use Retool Workflows to schedule background tasks that operate on your databases, like periodically ingesting data from 3rd-party APIs or running GPT-4 against your database to answer questions with plain English.

An optimal path forward

There’s a time and place for every tool in your toolkit, and spreadsheets are a valuable arrow in your quiver. But when your spreadsheet becomes the system of record for critical business data, and your business processes rely on manual efforts that modify the spreadsheet - I’m afraid you’re asking for trouble at some point down the line. At some point, human error or software limitations will create problems for your business.

Relational databases provide an alternative to mega-spreadsheets that is scalable and reliable, and opens up the possibility of creating custom software on top of your database to run your business processes in a safe and repeatable way. If you’re coming from the world of spreadsheets, Retool Database can help ease the transition to relational databases with a graphical interface, and the power of the Retool platform to build business processes and data visualizations on top of your data.

If you have questions or want to share your experience working between spreadsheets and PostgreSQL, join us on Discord to chat about it live​, or post about your experience in our community forums.