Hacker News Reader

Comment by wongarsu | Hacker News Reader

For me, using PostgreSQL is like using static typing: a bit more work, but it catches a lot of bugs.

SQLite favours a very permissive approach, while PostgreSQL favours locking everything down with strict types and strong integrity checks, and it's very easy to verify lots of details of your data before it's accepted into the database. That takes work to set up properly and maintain, but it catches bugs early and reduces the number of headaches you get when trying to use your data in new code.

Scalability is a very different problem that happens to favour PostgreSQL, but most projects indeed never get far enough that this matters.

View on HN

Strictly speaking, SQLite is dynamically typed in a sense, because the database does not enforce the values in the rows match what the columns claim about the type.

But from a developer experience point of view, the main difference between dynamic and static typing is the following:

Dynamic typing requires a lot of extra checks to ensure everything is what you expect it to be, and a lot of annotations to document what everything is supposed to be.

In other words, dynamic typing has a high cognitive tax.

With this in mind, I find that PostgreSQL imposes a cognitive tax in terms of maintaining a separate process (potentially on a different machine) that a lot of times is outside your direct control. Not to mention all the strange ways in which it can break.

SQLite just works. There's nothing to configure. So there's a lot less cognitive tax.

There is still of course the tax of having to ensure that all your sql statements are valid and that they return what you expect them to return, but this is also the case with PostgreSQL.

I agree with your point, but disagree with the example.

RE static vs dynamic typing, I'd say that it's dynamic typing that has higher cognitive tax - you as a programmer are fully responsible for ensuring types agree up everywhere, whereas with static typing, all of that job is done by compiler. It's easier to change stuff in the code when you know the compiler will catch your dumb mistakes. And I say that as someone who loves writing in Common Lisp.

But the point about SQLite vs a typical RDBMS is spot-on, IMO. SQLite is a library. It operates on files. That's it. It's entirely local. You can use it without having to become a sysadmin, without having to set up a whole service on the OS on which you might not even have root privileges anyway. You don't have to make global changes to the system just for your program. Moreover, if your product is of the distributable kind (desktop apps, self-hosting web stuff) you don't need to make your users become sysadmins, manage a system-wide service, acquire root rights they might not have, etc.

SQLite is local. Its data is local. That's, IMO, its strongest benefit.

> I'd say that it's dynamic typing that has higher cognitive tax

Yes, that's exactly what I said.

In your experience, what are the strange ways that PostgreSQL breaks?

Initial setup is confusing. Users and rules are confusing. Taking backups and restoring them is confusing.

Reply on news.ycombinator.com