Access Versus SQLite Versus PostgreSQL for a Small App

A team that has decided to leave a spreadsheet for a database faces a choice that sounds technical and is mostly practical. Microsoft Access is already on many office PCs. SQLite is a file that a script can create in a second. PostgreSQL is a full database server. All three work for a small application of a few tables and a few thousand rows. They differ in who can use them, how many people can write at once, what interface comes with them and what it costs to run. This post compares the three on the points that decide a small project. It is a spoke of when a spreadsheet has become a database.

What each one is

Microsoft Access is a desktop database product that bundles an engine (the Access Database Engine, formerly Jet), a table designer, queries, forms and reports in one application. Data lives in a file with the extension .accdb. It runs on Windows only. It is part of some Microsoft 365 business plans and not others.

SQLite is a database engine as a library. It stores a database in one file, has no server and no user interface of its own, and is built into Python, many languages and most phones. You use it through code, a command-line shell or a GUI such as DB Browser for SQLite.

PostgreSQL is a client-server database. A server process holds the data; applications and tools connect over the network with accounts and permissions. It runs on Linux, macOS and Windows, and is free and open source. It has no forms or reports of its own, and you pair it with tools.

The comparison

The numbers differ in kind rather than degree. Access's two gigabyte limit and SQLite's single writer are constraints you can hit; PostgreSQL's constraint is operational, in that someone has to run it.

Access

Access gives a small team something none of the others do out of the box: a way to build screens. You can create a form for entering orders, a report for printing invoices and a menu to open them, without writing a program. For a team with no developer and Windows machines, that is a real advantage, and many small businesses run on Access databases for years.

The limits show up with use. The file is capped at 2 GB, including temporary space. Several users sharing one .accdb on a network share is fragile, and the standard answer is to split the database: the tables in a back-end file on the share and the forms in a front-end copy on each machine, linked to it. Locking on file shares can produce corruption when connections drop. It is Windows-only, and the web and Mac are not options for the forms. User-level security was removed in newer formats, so protection is file permissions plus passwords.

A middle path exists: keep Access as the front end and move the tables to SQL Server or PostgreSQL through linked tables. People keep their forms, and the data gets a server's concurrency and safety. This is a common route for outgrown Access applications, and it is easier if the tables were designed cleanly in the first place (designing tables from a messy Excel sheet).

Microsoft has been steering new work toward other tools (Power Apps and Dataverse, SQL Server). Access remains supported, so the risk is not abandonment but being tied to Windows and to a single vendor.

SQLite

SQLite's strength is how little it asks of you. A database is a file you can copy, email or put in version control. There is nothing to install for Python users. It is fast for reads and handles hundreds of thousands of rows easily. For an application with one writer, such as a script that loads data nightly, a desktop tool used by one person or a small web app on a single server, it is often the best choice (moving from Excel to SQLite for a small team).

The limits are about concurrency and distribution. Writes lock the database file, so many simultaneous writers queue up and may see "database is locked". Network filesystems do not provide the locking SQLite needs, so a database on a shared drive that several people write to can be corrupted. There are no accounts. It is not a server, so other machines cannot connect to it without a program in front. Typing is looser than in the other systems unless you use STRICT tables.

SQLite has no interface for people. For non-programmers you need a GUI tool, which lets them view and edit, and for anything like forms you need to build something.

PostgreSQL

PostgreSQL is the heavyweight: reliable, standards-compliant, with strong types, constraints, transactions, JSON support, full-text search, extensions and a long record. It handles many concurrent writers, has roles and permissions down to the row level, and scales from a small team to very large systems on the same technology. A spreadsheet replaced by PostgreSQL can grow into an application without another migration (moving from Excel to PostgreSQL step by step).

The cost is operational. Someone has to install it, keep it patched, set up backups and test restores, manage accounts and secure the network access. Managed services (cloud providers' hosted PostgreSQL) take most of this away for a monthly fee. It has no forms of its own, so you must add a front end: a web application, an admin tool, a reporting tool or Excel through ODBC. That is more work than Access for a team without a developer.

Choosing by situation

One person or one program writes, a few readers. SQLite. Put the file on one machine, back it up, and let others read via exports or a small service.

A few Windows users, no developer, forms needed. Access, with a split database, or Access over a server back end if the data matters.

Several people write at once, or accounts and permissions matter. PostgreSQL, ideally managed, with a front end that suits the users.

The data is sensitive. PostgreSQL (or another server) for roles, row security and audit logging (audit trails and history that Excel cannot give).

It is a prototype that may grow. Start in SQLite for speed of iteration, write standard SQL, and move to PostgreSQL when concurrency demands it. The move is mostly mechanical if you avoid SQLite-specific tricks.

The team lives in Excel and will not leave. Keep Excel as the reader of whichever database you pick (keeping Excel as the front end to a database).

The questions that decide it

Answer these five and the choice usually makes itself.

1. How many people will write at the same time? One: SQLite or Access. More than a handful: PostgreSQL.

2. What machines do they use? All Windows: Access is possible. Mixed: not Access.

3. Is there a developer? No: Access or a ready-made tool. Yes: any.

4. Who will keep it running? Nobody: SQLite, or a managed PostgreSQL.

5. How will it grow? Unknown or large: PostgreSQL.

Speed is rarely the question. All three answer queries over thousands of rows in milliseconds. What fails first is concurrency, file size or the ability to administer.

Moving later

Whichever you start with, assume you may move. Keep the schema clean, with real keys and constraints. Avoid vendor-specific features you do not need. Keep the data export scripts working: a nightly dump to CSV or SQL is a lifeboat in any system. And document the model. Moving data between these systems is straightforward if the design is sound, and painful if the tables are copies of the spreadsheet.

When none of the three is the right choice

You need a ready-made application. If the job is tracking customers, tasks or inventory, an existing product may beat building one. Evaluate it first.

The data is documents or free text. A document store or search engine fits better than relational tables.

It is analytics over files. For analysis of large files, DuckDB reads CSV and Parquet without a server (loading Excel files with DuckDB).

It is a handful of rows. A shared table in a collaboration tool is simpler than any database.

See the pages on SQLite's appropriate uses, the PostgreSQL about page and Microsoft's Access specifications. To convert a small extract to JSON, use the CSV to JSON converter.