Sqlite vs Postgresql
Sqlite is one of the most deployed database in the world. Its small(<1 MB>), fast and reliable(supported till 2050). Postgresql is world's most advanced open source object-relational database. It has been developed over 35 years in the wild with open-source community. Out of 177 ANSI SQL features, it complies with 170, highest of any database. While sqlite offers 5 datatypes(NULL, integers, real, text and blob), postgres boasts of 43 datatypes including xml, JSON and ip_addresses. And more(time series, geometry, raster) can be added through the extensions. It is clear that while sqlite is a fast rabbit, postgresql is the elephant in the room.
Rather than comparing the documentation of these two wonderful databases, we will look at some of hoops we will need to jump in order to support sqlite for a web application.
1. Flexible typing
In this article talks about how flexible typing in sqlite caught them off guard and they ended up storing longer slug in 256 byte string and stored Uuid in integer column type. They only got to know this once they migrated their database from sqlite to postgres. This would have caused bug that would be difficult to reproduce and find out.
CREATE TABLE articles (id integer primary key, title text, wc integer);
insert into articles values (1, 'hello world', 3);
insert into articles values (3, 'hello world 2', 'coca cola');
The above command would run totally fine in sqlite
sqlite> select * from articles;
┌────┬───────────────┬───────────┐
│ id │ title │ wc │
├────┼───────────────┼───────────┤
│ 1 │ hello world │ 3 │
│ 2 │ hello world 2 │ 4 │
│ 3 │ hello world 2 │ coca cola │
│ 4 │ hello world 2 │ 10 │
└────┴───────────────┴───────────┘
while it may not cause any problem as is because the aggregate functions(max, sum) will just ignore the column that is not castable. Migrating this data/database to a strict typing database may be a problem.
However, this can all be avoided by just making strict tables.
This will throw error
CREATE TABLE articles_strict(id integer primary key, title TEXT, wc integer) STRICT;
insert into articles_strict values (1, 'hello world', 3);
insert into articles_strict values (2, 2, 'hello world 2');
2. Single thread writer
Sqlite allows only one writer at a time. In its default rollback journal mode, It uses a locking-based mechanism where a write operation will lock the entire database and other read/write operations will have to wait in queue. Similarly write operation will have to wait in queue for the read operation.
However, this can all be avoided with WAL (write ahead logging mode) where reader will not block writer and writer will not block readers. But writer will block other writers and they will have to wait in queue
3. Recursive depth first search
- Depth-first recursive function: Sqlite does NOT support graph search functions in its recrsive search. However, a depth-first/breadth first thing can be implemented in sqlite using
order byclause.
CREATE TABLE org(
name TEXT PRIMARY KEY,
boss TEXT REFERENCES org,
tenure INTEGER
) WITHOUT ROWID;
INSERT INTO org VALUES('Alice',NULL, 10);
INSERT INTO org VALUES('Bob','Alice', 1);
INSERT INTO org VALUES('Cindy','Alice', 10);
INSERT INTO org VALUES('Dave','Bob', 1);
INSERT INTO org VALUES('Emma','Bob', 10);
INSERT INTO org VALUES('Fred','Cindy', 1);
INSERT INTO org VALUES('Gail','Cindy', 10);
while querying we can traverse the tree in depth-first order while also sorting the siblings by tenure. amazing. This can be used to solve the comment ordering in lobsters.
WITH RECURSIVE
under_alice(name,level, tenure) AS (
VALUES('Alice',0, 10)
UNION ALL
SELECT org.name, under_alice.level+1, org.tenure
FROM org JOIN under_alice ON org.boss=under_alice.name
ORDER BY 2 DESC, 3 DESC
)
SELECT substr('..........',1,level*3) || name || ' '|| tenure FROM under_alice;
┌─────────────────────────────────────────────────────────┐
│ substr('..........',1,level*3) || name || ' '|| tenure │
├─────────────────────────────────────────────────────────┤
│ Alice 10 │
│ ...Cindy 10 │
│ ......Gail 10 │
│ ......Fred 1 │
│ ...Bob 1 │
│ ......Emma 10 │
│ ......Dave 1 │
└─────────────────────────────────────────────────────────┘
4. Materialized views
There are no materialized views in sqlite. The closest workaround suggested in the thread above is to CREATE TABLE AS and run a job to refresh it every hour/day.
5. Full text search
For this experiment, we will use the sakilla database to showcase how to run a fulltext search query. Simplest way to do a search query to use like operation, but this will trigger a full table scan.
sqlite> .schema film
CREATE TABLE film (
film_id int NOT NULL,
title VARCHAR(255) NOT NULL,
description BLOB SUB_TYPE TEXT DEFAULT NULL,
...
PRIMARY KEY (film_id),
);
sqlite> explain query plan select title, description from film where description like '%shark%';
QUERY PLAN
`--SCAN film
Fortunately, sqlite provides fts5. If the query below returns 1, fts5 is enabled in the sqlite installation.
SELECT sqlite_version(), sqlite_compileoption_used('ENABLE_FTS5');
create the virtual fts table
CREATE VIRTUAL TABLE film_fts USING fts5(title, description, content='film', content_rowid='film_id');
INSERT INTO film_fts(film_fts) VALUES('rebuild');
Then, query the tables using MATCH operator.
sqlite> select * from film_fts where title match 'suit';
┌────────────┬──────────────────────────────────────────────────────────────┐
│ title │ description │
├────────────┼──────────────────────────────────────────────────────────────┤
│ CORE SUIT │ A Unbelieveable Tale of a Car And a Explorer who must Confro │
│ │ nt a Boat in A Manhattan Penthouse │
├────────────┼──────────────────────────────────────────────────────────────┤
│ SPEED SUIT │ A Brilliant Display of a Frisbee And a Mad Scientist who mus │
│ │ t Succumb a Robot in Ancient China │
├────────────┼──────────────────────────────────────────────────────────────┤
│ SUIT WALLS │ A Touching Panorama of a Lumberjack And a Frisbee who must B │
│ │ uild a Dog in Australia │
└────────────┴──────────────────────────────────────────────────────────────┘
However, if I insert a new record in film, it won't be automatically available in film_fts. To make the data available in the film_fts, we will need to run the rebuild again.
INSERT INTO film (film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update)
VALUES (1001, 'CORE SUIT 2', 'A Unbelievable Tale of a Car And a Explorer who must Confront a Boat in A Manhattan Penthouse', 2006, 1, NULL, 6, 0.99, 86, 20.99, 'PG', 'Deleted Scenes,Behind the Scenes', '2006-02-15 05:03:42');
However, running rebuild again and again can be expensive, a better solution is to use triggers.
Trigger for insert
CREATE TRIGGER film_insert AFTER INSERT ON film
BEGIN
INSERT INTO film_fts(rowid, title, description) VALUES (new.film_id, new.title, new.description);
END;
Trigger for update
CREATE TRIGGER film_update AFTER UPDATE ON film
BEGIN
INSERT INTO film_fts(film_fts, rowid, title, description) VALUES ('delete', old.film_id, old.title, old.description);
INSERT INTO film_fts(rowid, title, description) VALUES (new.film_id, new.title, new.description);
END;
Trigger for delete
CREATE TRIGGER film_delete AFTER DELETE ON film
BEGIN
INSERT INTO film_fts(film_fts, rowid, title, description) VALUES ('delete', old.film_id, old.title, old.description);
END;
In Conclusion
SQLite and PostgreSQL both serve their purposes, and you can technically use either for almost anything. However, the real question is: why force one to handle something beyond its capabilities when there’s an option that excels at it? Yes, you can configure and tweak SQLite to make it work, but why shoehorn it when you can let PostgreSQL handle your load efficiently?
Top comments (0)