Need advice about which tool to choose?Ask the StackShare community!
PostgreSQL vs SQLite: What are the differences?
Developers describe PostgreSQL as "A powerful, open source object-relational database system". PostgreSQL is an advanced object-relational database management system that supports an extended subset of the SQL standard, including transactions, foreign keys, subqueries, triggers, user-defined types and functions. On the other hand, SQLite is detailed as "A software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine". SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
PostgreSQL and SQLite can be categorized as "Databases" tools.
"Relational database", "High availability " and "Enterprise class database" are the key factors why developers consider PostgreSQL; whereas "Lightweight", "Portable" and "Simple" are the primary reasons why SQLite is favored.
PostgreSQL is an open source tool with 5.44K GitHub stars and 1.8K GitHub forks. Here's a link to PostgreSQL's open source repository on GitHub.
According to the StackShare community, PostgreSQL has a broader approval, being mentioned in 2739 company stacks & 2169 developers stacks; compared to SQLite, which is listed in 314 company stacks and 477 developer stacks.
Backend:
- Considering that our main app functionality involves data processing, we chose
Pythonas the programming language because it offers many powerful math libraries for data-related tasks. We will useFlaskfor the server due to its good integration with Python. We will use a relational database because it has good performance and we are mostly dealing with CSV files that have a fixed structure. We originally choseSQLite, but after realizing the limitations of file-based databases, we decided to switch toPostgreSQL, which has better compatibility with our hosting service,Heroku.
I try to follow an 80/20 distribution when it comes to my choice of tools. This means my stack consists of about 80% software I already know well, but I do allow myself 20% of the stack to explore tech I have less experience with.
The exact ratio is not what’s important here, it’s more the fact that you should lean towards using proven technologies.
I wrote more about this on my blog post on Choosing Boring Technology: https://panelbear.com/blog/boring-tech/
We have chosen Tibero over Oracle because we want to offer a PL/SQL-as-a-Service that the users can deploy in any Cloud without concerns from our website at some standard cost. With Oracle Database, developers would have to worry about what they implement and the related costs of each feature but the licensing model from Tibero is just 1 price and we have all features included, so we don't have to worry and developers using our SQLaaS neither. PostgreSQL would be open source. We have chosen Tibero over Oracle because we want to offer a PL/SQL that you can deploy in any Cloud without concerns. PostgreSQL would be the open source option but we need to offer an SQLaaS with encryption and more enterprise features in the background and best value option we have found, it was Tibero Database for PL/SQL-based applications.
Pros of PostgreSQL
- Relational database755
- High availability506
- Enterprise class database437
- Sql379
- Sql + nosql299
- Great community171
- Easy to setup145
- Heroku129
- Secure by default128
- Postgis111
- Supports Key-Value48
- Great JSON support46
- Cross platform32
- Extensible29
- Replication25
- Triggers24
- Rollback22
- Multiversion concurrency control21
- Open source20
- Heroku Add-on17
- Stable, Simple and Good Performance14
- Powerful13
- Lets be serious, what other SQL DB would you go for?12
- Good documentation9
- Scalable7
- Intelligent optimizer7
- Transactional DDL6
- Modern6
- Reliable6
- One stop solution for all things sql no matter the os5
- Free5
- Relational database with MVCC4
- Full-Text Search3
- Developer friendly3
- Faster Development3
- Excellent source code2
- Great DB for Transactional system or Application2
- Free version1
- Text1
- Open-source1
- search1
- Full-text1
Pros of SQLite
- Lightweight160
- Portable134
- Simple121
- Sql80
- Preinstalled on iOS and Android28
- Free1
- Telefon1
- Tcl integration1
- Portable A database on my USB 'love it'1
Sign up to add or upvote prosMake informed product decisions
Cons of PostgreSQL
- Table/index bloatings9
Cons of SQLite
- Not for multi-process of multithreaded apps2
- Needs different binaries for each platform1
Sign up to add or upvote consMake informed product decisions
What is PostgreSQL?
What is SQLite?
Need advice about which tool to choose?Ask the StackShare community!
What companies use SQLite?
Sign up to get full access to all the companiesMake informed product decisions
What tools integrate with SQLite?
Sign up to get full access to all the tool integrationsMake informed product decisions
Blog Posts
+11
+8
+32
+8
+12
+23
+13
+42