Hacker News Reader

Comment by andrewstuart | Hacker News Reader

Postgres is the database I use for everything.

I find databases of all sorts extremely interesting and I've tried many of them, of all flavors.

In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do.

Why not use sqlite? Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite.

Another example - multitenancy is dead easy in postgres, makes things more secure and makes coding easier.

Another is that Postgres had an extremely rich set of data types, including arrays and json.

Also Postgres is designed for symmetric multiprocessing which matters in today's multicore world.

View on HN

it's important to note that the author of SQLite wasn't "competing" with RDMS engines -- it's competing with flat file access.

To that goal, I think it's wildly successful, instead of writing files I almost always reach for SQLite first.

When systems share a database, I, like you, reach straight for postgresql. Most people reach for mysql, which I do not prefer.

Ye I believe its best to think of Sqlite not as a database per se but more as a file format, kinda as you would a word doc but a file format that doesn't suck and is absolutely amazing at storing information.

If it is mostly text, then I would say git is better suited.

git is ACID?

I still believe Sqlite is the appropriate store. Unless.. does Git handle power issues elegantly? What happens if you yank power in the middle of a commit? Sqlite is designed to handle that.

that's one of the reasons I love Fossil so much. Way better backend than Git.

one of my team members really pushed fossil, and we did like the integrated wiki/ticket system. not bad at all.

... but it was so slow compared to git! at everything. merging, pushing, pulling ...

you did not experience same?

Fossil has rebasing? I thought they felt strongly against rewriting history, which is the biggest reason I am not considering it.

oops. corrected. thanks.

my main beef with fossil was performance. but i should clarify that this was 5 years ago or so. so maybe it's ok now.

Let's see:

- A - yes, update HEAD/branch text file is the key atomic update

- C - not sure how to apply this in the context of git, because commited files' content is kinda free form by design, no schema, no consistency problems

- I - there should not be concurrent use of commands, so no. practically this is mostly important for pushing to remote repository, and there it's handled by rejecting the push that comes second, if you make two commits from the same base and push them at the same time

- D - yes

And retrieving it, too.

If competing with flat files, and assuming you don't need concurrency, I don't think it's better than just using one or more json or csv files. Those are much easier to manipulate and manage (same benefits in terms of admin and backup), and you don't need to use a database driver in your code.

Even when systems don't share a database, if operational concerns - disaster recovery, failover migrations, metrics and monitoring, anything like that - matter to me, I'm reaching for PostgreSQL or MS SQL Server. I hear I also need to get over my 20 year old grudge against MySQL.

SQLite is really only my first choice for hobby projects and desktop apps that aren't running on the JVM.

> I hear I also need to get over my 20 year old grudge against MySQL.

I hear this too - but silently truncating my data (causing a great deal of data loss and grief for me) is likely going to continue to bias me against MySQL for the foreseeable future.

That was configurable for ages and default is on strict for a long time now ...

Compatibility with old decisions vs. doing the right thing™ is always a pain ...

> That was configurable for ages and default is on strict for a long time now

I'm not saying I'm being rational in holding my grudge. I got bit by that "bug" _bad_ early in my career and the scar tissue lingers.

I actually saw a really interesting article (probably on here) a while back about using SQLite files as your applications' document storage model, and I really like it. Like if you wrote a word processor, you could include a table of edits that would let you recreate Google Doc's timeline feature, among other things. I thought that was a really neat idea.

https://www.sqlite.org/appfileformat.html

Apple's Core Data persisted-object-graph engine (kind of like Hibernate, I guess?) uses SQLite as its default storage backend.

There is a lot of macOS/iOS software using Core Data who already have "SQLite as an application document storage model" without even realizing it.

[1] https://actualbudget.com/blog/porting-local-app-web

[2] https://news.ycombinator.com/item?id=23508923

Firefox uses SQLite for localStorage. When Firefox is force quit, the database becomes corrupted. https://bugzilla.mozilla.org/show_bug.cgi?id=1341070

This bug has occurred to me personally multiple times.

Does it really have advantages over a flat file?

if you're corrupting sqlite files you're for sure going to corrupt other formats in a force quit situation.

This is quite surprising. SQLite is also used in embedded where you don't have comparatively civil "forced quits" but also straight power offs. SQLite seems to do everything it can to handle this gracefully.

I use Firefox almost exclusively and have never encountered this.

Whatever capabilities SQLLite has to recover corrupted or inconsistent storage, it certainly exceeds what you get with flat files (none, unless you implement it yourself).

Per that bug report, you are likely to encounter it at https://codesandbox.io/s/sentry-js-sdk-unhandled-promise-fuq....

Which suggests an application error to me. I'd give decent odds to the real problem being some bounds check or pointer writing to the wrong place. To prove it, I'd instrument SQLite to log every single query that it was asked to do, then try to run those queries outside of the application. If the queries work fine, then the copy of SQLite in the application is being corrupted somehow.

In which case it really isn't fair to blame SQLite.

What's getting corrupted is Firefox's own SQLite DB.

I'm perfectly aware of that.

The question is how it is getting corrupted. Is it that SQLite has a bug? Or the copy of SQLite in Firefox is getting corrupted first?

Given the complexity and relative levels of testing of those two products, my bet is that the problem is Firefox.

Sqlite is certified for use in planes. This requires a high level of tests.

I think the odds are likely that Firefox is doing something unsuspected.

https://www.sqlite.org/testing.html

- Four independently developed test harnesses

- 100% branch test coverage in an as-deployed configuration

- Millions and millions of test cases

- Out-of-memory tests

- I/O error tests

- Crash and power loss tests

- Fuzz tests

- Boundary value tests

- Disabled optimization tests

- Regression tests

- Malformed database tests

- Extensive use of assert() and run-time checks

- Valgrind analysis

- Undefined behavior checks

- Checklists

"Valgrind is perhaps the most amazing and useful developer tool in the world."

It seems that about once a month I read something that makes me like SQLite even more. I guess that they like Valgrind as much as I do is the reason this month.

Valgrind is fine if you can live with the false positives, limited stack validation and execution slowdown. In a development environment, AddressSanitizer is a far better choice.

I am trolling on the person you to whom you replied...

<<This bug has occurred to me personally multiple times.>>

Hello, I am AirBus engineer (not really, only pretend). Does my plane crash? No, it does not!

Dear Random Internet Person who complains that open source software is broken. Are you joking? We are talking about SQLite!!??

SQLite has whole pages dedicated to "why you should NOT use SQLite". How many open source projects are /so/ good they can do this? Incredible!

The test coverage feels like NASA.

Why would that not happen to any other form of flat file storage? When you force quit a process, unless there's some nuance I'm unaware of, you're not only bypassing any graceful cleanup but there could be an FS operation in progress. Although I'm certain there are mitigation techniques, I would never expect a process to guarantee no file corruption when it's forcefully quit.

Interestingly enough, I don't think I've ever had this issue. I've force quit Firefox many times in the past (not often, but I'm sure I've done it countless times in the 10+ years I've been using it) and I still have localStorage data intact. There's old crap in StackEdit.io, which uses localStorage, that I haven't touched in years. (no, I have neither that service or Firefox itself set up to sync with anything)

EDIT: Maybe it's OS specific? I've been on macOS, and maybe there's something about other file systems where corruption is more likely to happen with a force-quit? Just throwing darts here.

I believe for a proper implementation it would be done transactionally. So if the program crashed then that record wouldn’t be written but the DB wouldn’t be corrupted.

I force-quit Firefox regularly (easiest way to get my tabs to stay open), on both Linux and Windows.

I've never seen any error messages from Firefox suggesting that anything has been corrupted.

Are there other symptoms that I would expect to see, if this corruption is happening?

Some sites break due to not expecting the exception being thrown– deviantart.com won't work, for instance.

For context, before the rise of SQLite, BerkeleyDB was often used for local file databases.

I never programmed for berkelyDB but I don’t believe it was SQL, so I think that’s what made SQLite so successful. The combination of an SQL api backed by local files

BerkeleyDB was a key/value store. I'm not sure how much uptake it actually had vs other solutions. Prior to widespread availability of SQLite3, it was not unheard of to write your bespoke format if you needed something sophisticated.

My exposure to BDB was through sendmail, spam assassin, and subversion. All three a big chunk of my job in 2006.

BerkleyDB is NoSQL and is internally used in popular No-SQL solution like DynamoDB.

> In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do.

Well, depends on how wide your definition is:

Filesystems are also databases. (And eg inside of Google, they are using databases to store filesystems.)

These days, we often think of relational databases. Filesystems are more like the hierarchical databases of yore that Codd (also) talked about in his original papers.

Would love to be able to have a database file system and be able to use standard Linux tools to query and make changes, back up, etc.

For example, having a folder of contacts with each file named after the person and having key/value pairs. Similar to how static site generators use YAML/TOML/JSON.

That sounds like ndb:

https://9fans.github.io/plan9port/man/man1/ndb.html

https://www.youtube.com/watch?v=e9Y0iDXXQh8&feature=share

Try your nearest shell to experiment with this? In my scripts and programs I've stored data just in files many times: simple, transparent and effective. Works everywhere. There's so much in search that you can just brute-force through unless you have a huge amount of data or a lot of concurrent tasks that it doesn't matter much in today's hardware.

The maildir format does something like this.

This makes me wonder how efficient filesystems are with millions of file in a single directory? Do they create some sort of index? Are there limits to the number of files a directory can hold? Is that what inodes are for? I remember seeing “inode” limits on some VPS I was using a while back.

Indeed, putting too many files in a single directory is inefficient, both for read and write. When writing a file (or changing metadata like permissions), the entire directory inode may have to be rewritten. When searching for a file or opening it, the entire file list for the directory needs to be read (in the worst case where the file is at the end of the list).

When you need to store lots of files on disk, it's a common pattern to spread them out in subdirectories. For instance, instead of `files/2d8af74bcb29ad84`, you would have `files/2d/8a/f74bcb29ad84`.

What about on the other end? Why not have each hex digit be a directory along a path? Then you have very, very few files per directory at the cost of deeper hierarchy. What's the practical downside?

[epistemic status: amateur speculation]

I’d expect it to take up slightly more disk space. More importantly, the more indirections, the longer it’ll take to get to the file.

Directories are just another type of file. So, if you do this, and your filenames are n characters long, you'll end up needing to do n file accesses just to find the file you're looking for. Unless the underlying file system does something to make that particular access pattern fast, well... it's going to be stupidly slow after a certain point.

> Unless the underlying file system does something to make that particular access pattern fast

I don't know exactly how Linux does it. Windows hands off whole paths to the filesystem, so this idea is possible there.

In FreeBSD, there is a generic routine (lookup(9)) that goes component by component, so at each step the filesystem is only asked to resolve a single component to a vnode. I think a clever filesystem implementation (in FreeBSD) could look at the remaining path and kick off asynchronous prefetch... but I am not aware of anything doing this.

I've played around with fuse a bit.

There are two modes you can implement for your filesystem: in the so-called 'high level' mode you get the whole path. In the 'low level' mode the filesystem asks you for one piece of the path at a time.

The low level mode seemed faster in my tests, and I think it's also closer to how Linux kernel works internally?

You then need to do more stat calls — one per hierarchy level — and you waste more inodes.

More overhead for each extra layer. Kernel serialization/locking, random seeks (dirent and inode per level), maybe other concerns.

The random seeks aren't a given: your filesystem could do something clever with memory or disk layout to avoid most of them.

Anything particular in mind? You could probably get a good academic paper or patent out of this.

That's sometimes true but it depends a lot on the filesystem used, and sometimes on the options used when building it. ReiserFS, ext4 and FAT 32 won't have the same performance profile.

I think breaking down large collections in subdirectories is mainly done in order to ensure that it'll work even with FS that don't deal well with very large directories and also because it makes inspecting the files manually a little more convenient given that many file explorers (and especially the GUI ones) can have trouble with large directories.

Yes, though I think there's some POSIX compatibility stuff that also makes big directories a hassle.

also ext4 (then 3 i think) used to look for dir entries linearly (it was a linked list of entries).

> When writing a file (or changing metadata like permissions), the entire directory inode may have to be rewritten.

That isn't how inode filesystems work -- if you change a file's permissions, it's just an inode update on the file -- not the containing directory.

Even in DOS-type filesystems (FAT/exFAT), it's just a record update in the corresponding dirent for that file.

If you add a new file to a directory, that causes an mtime update on the directory's inode.

The rest is accurate -- many older filesystems have lookup performance that scales poorly with directory size (for DOS filesystems and BSD UFS, you have to do a full directory scan). Also ls defaults to sorting output, which is O(N log N) and can be slow in large directories.

Large directory sizes suck on NFS and parallel file systems like Panasas, Lustre, GPFS, and the like. Python and Rust's Cargo also suck on networked file systems and would be greatly improved by pushing things into a sqlite file.

most things suck on networked file systems. I still have trauma from having to use NFS more than a decade ago, on systems that would freeze on boot because the network couldn't be reached or the NFS server was down.

I'd also never put sqlite on NFS. Locking is often broken on NFS. Unless you can guarantee a heterogeneous environment. I can imagine some Excel guy in marketing is going to launch his SQLite UI on Windows and completely hose it all.

Nfs locking broken.. excel? Sqlite ui on Windows? Are you sure you're not confusing SMB with nfs? I've had SMB cache files that ducks everything up. Not nfs.

if this was the case - even if rewriting the inode was O(n) - you would have noticed. try ls on a large directory, now touch a file on same directory.

"Modern" file systems (say, ext3 and onwards) scales pretty well.

The problem is that most tools that operate on file systems doesn't. Things like readdir() is a linear scan and takes a long time on a million files.

So in practice it's not optimal. A thousand files, no problem. A million, start looking at doing it in-memory (or use some database tool).

> Are there limits to the number of files a directory can hold?

Yes, depending on the file system.

For example, ext4 with default settings uses 32-bit hashes. Upon the first collision, you can no longer add more files to the directory (ENOSPC error).

Source: https://blog.merovius.de/2013/10/20/ext4-mysterious-no-space...

If you do this, you can add all the files to git and get historical views, changelogs... and if you distribute the "database", you even can know who made what changes!

Along the lines of what I was thinking. Would be really neat! I already do this to an extent, but with a virtual file system it could automatically sort/categorize/etc like a regular database.

This reminds me a little bit of the filesystem from BeOS (RIP) and Haiku. You cant attach any metadata to any file you want, and it has a system to create queries on that metadata.

Very much miss this. I love how they used this technique to build their email client. Emails were just files on the filesystem with a filesystem plugin that made their metadata queryable. So the email client was just a file browser window with some pre-saved queries that would let you find all emails by date, sender, etc.

you can :) even across nfs ... maildir etc ...

A job for fuse. :) I'd like having a nice sysfs like view on all tables and other objects in a database. Even better if editable.

Or alternatively a midnight commander for databases. :)

OMG yes. Now this would be perfect for SQLite.

WinFS was an attempt at this. It'd be interesting to learn why it was cancelled.

https://en.wikipedia.org/wiki/WinFS

Because it tries to make people work in a way they don’t want to work. People want to organize their files in folders. They don’t want to tag them all with labels or tags and they don’t want to input all that metadata.

Perhaps they want to tag a few, and it’s useful to have some autodetected metadata but they don’t want to tag them all and they don’t want a gigantic ‘Untagged files’ list. They want folders and they want more than a flat folder list, they want nested folders.

You can implement it, it’s not hard and most modern file systems have all the features you need. But users will hate it and won’t use it the way you want.

You are right that users don't want to do that busy work.

But I am less sure users actually want folders.

Some power-users, sure. But most normal people don't want to deal with folders, either.

For evidence: look at the guy who saves everything on his overflowing desktop.

Any system that allows people to find their stuff, and perhaps make a few annotations, will be good for them.

Google Photos is almost a good example: I don't have to do annotate anything, yet I can search for eg pictures of snow or by location.

(I say only 'almost', because while impressive, that system isn't good enough yet to find obscure stuff or to work on contextual cues like 'those pictures I took at home after we came back from shopping sometime in the last few months'.)

That’s all very nice but a filesystem needs to be able to deal with every type of file on the planet. Which means you can’t automatically detect the contents.

And really, a lot of users don’t want an interface that doesn’t allow them to do what they want just because someone else just dumps all their files on the desktop.

Apple tried this on iCloud and had to go back. Because, while it makes for nice presentation and usability, there’s a lot of users that it can’t cater for.

I think the difference of a Database and a Filesystem is:

In a Filesystem you know where the Data lives, in a Database you know that it lives...but yeah with stuff like Ceph that view gets a little bit foggy.

What do you mean by `you know where'?

For me, all I know is that my filesystem store its data on disk somewhere, I don't really have any clue how that's organised. That's pretty much exactly the same level of knowledge I have about my db.

(And yes, I could look into both of them to learn more. And yes, we also have databases and filesystems that get accessed over the network..)

File system paths perhaps sound like a location, but there are no more and no less a location than eg tablenames to me. Or URLs.

> inside of Google, they are using databases to store filesystems.

I'm struggling to picture this, do you have any links I could read?

Googler here, I think OP is referring to the relationship between colossus and bigtable [1]. Basically, what happens is that colossus uses bigtable for storing metadata. So, when you issue a command like `ls /some-cluster-directory/myfolder/mysubfolder` you are really querying bigtable instead of a distributed ls on a cluster fs.

It is much more complicated than that, but the idea is that using bigtable you create a resemblance of a fs that feels like a fs to use for the most part.

[1] http://www.pdsw.org/pdsw-discs17/slides/PDSW-DISCS-Google-Ke...

Yes, that's something like I was going for.

(I'm an ex-Googler, but I am glad that my spotty memory gives me ample protection against accidentally giving out company secrets.)

I suspect Google Drive is also backed by something that's not a traditional file system, even thought Google Drive tries to look a bit like a file system to the user.

From the outside one clue is that you can have two files with the same name, another is the url to a file doesn't change when you move it. Also it's dog slow to list a directory, like 30sec for 50 files.

You can experiment with something kind of like this yourself pretty cheaply. Here's an example [0] user-space filesystem that stores its data in an in-memory sqlite file.

You could just as easily replace those in-memory calls with a networked DB (perhaps with speculative pre-fetching or something, I dunno, I probably wouldn't try to make a python filesystem too performant).

The salient detail here is that as far as your kernel is concerned a filesystem is an API for interacting with data (whether that's with a daemon process like the linked example or with raw function calls built into the kernel). Those APIs can and often do interact with structures physically stored on a local disk, but that isn't a requirement.

[0] http://www.rath.org/pyfuse3-docs/example.html#in-memory-file...

You used to be able to store your own filesystems on google's databases [0]. No idea if this still works, though.

[0] http://sr71.net/projects/gmailfs/

Filesystem is like a database. Except for transactions, which is very important when multiple processes can access it.

But filesystems are secure (mostly, ignoring unicode issues) whilst sqlite is an insecure hack.

You'll have hard time to harden sqlite, removing all the insecure defaults, fix the broken and exploitable full text search apis, but esp. its built-in hacks. Like explained here https://github.com/rurban/hardsqlite or here https://research.checkpoint.com/2019/select-code_execution-f...

> Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite

Not sure exactly what you mean by 'remote access', but if it's to debug something on a remote DB from an SQL IDE, I do this quite often. I use sshfs to mount the remote filesystem and open the SQLITE file in DBeaver.

Even in case of Postgres, you would have to connect to a remote DB via SSH or via VPN. It's just that you will be using sshfs to connect to a remote SQLITE file.

I just scp that son of a b down and run all the queries on the SQLite file locally.

Sounds like a pain for larger databases.

Well it depends how large you are talking about. With todays internet speeds, anything under 10GB is a less than a few minutes.

I once accidentally locked a production MySQL database with some kind of recursive sub query.

Running queries locally on a copy avoids lock/mutation issues, and I have a snapshot to re-run queries on if I need to go back and see the source of the data.

I guess there's a lot of cases I just want to peek into a table live without pulling it all down. I've only used postgres so it's weird to me you can't talk to any MySql style DB like it does.

Anything that needs to be coded I'll pull it down though, it's nice to have options.

Note that using SQLite over SSHFS is dangerous if you're not the only user of the DB, because SSHFS does not support file locking.

According to: https://www.fossil-scm.org/forum/forumpost/8749496886

> There's a very real possibility that you could corrupt the SQLite DB by running it over SSHFS.

I wasn't aware of this. Thanks for the information. Is this true even when you use a read-only sshfs mount?

No. Read-only cannot mutate the database. I don't know if Sqlite ensures you won't observe inconsistent results if there is a concurrent (single) writer.

The docs [1] and this [2] suggest that you'll probably observe inconsistent results.

WAL mode supports it ("reading and writing can proceed concurrently"), but "does not work over a network filesystem" like SSHFS [3] because it either requires shared memory on the same machine, or exclusive locking [4] (which SSHFS does not provide, and which again does not allow readers concurrent with a writer).

Thus overall, you can only rely on your read queries over SSHFS if you know that there is no concurrent writer.

[1]: https://www.sqlite.org/lockingv3.html [2]: https://stackoverflow.com/questions/1005206/does-sqlite-lock... [3]: https://www.sqlite.org/wal.html [4]: https://www.sqlite.org/wal.html#noshm

If you're just using sshfs for RO queries for debugging, it should be harmless.

It will try to replay the journal on open, though, even without running a query if I'm not mistaken. (There's a read only flag for open, just have to be sure you're using it.)

> In the end, I always come back to Postgres. It's unbelievably powerful, lightweight and there's not much it can't do.

How lightweight?

SQLite itself is around 600 KB. That's with all features enabled. I believe you can get it it down to about half that if you disable features you don't need. RAM usage is typically under a dozen or so KB.

It doesn't even require that you have an operating system. All you need for a minimal build is a C runtime that includes memcmp, memcpy, memmove, memset, strcmp, strlen, and strncmp, and by default also malloc, realloc, and free although it has provisions for providing different memory allocators, and you have to provide some code to access storage if you are running on a system that doesn't have something close to open, read, write, and the like.

This means it can even be used on many embedded systems far too small to run a Unix.

Heck, it is not even too unreasonable to compile SQLite to webasm and include it on a web page if for some reason you need client side SQL on the web.

Depends on what you need. It does not seem like a great idea to run Postgres in a browser, but it is usually less resource hungry than the JVM-based app using it to return HTML or JSON, so I can call it lightweight for server use.

> Heck, it is not even too unreasonable to compile SQLite to webasm and include it on a web page if for some reason you need client side SQL on the web.

With the deprecation of Web SQL, I highly doubt this is a use-case anyone cares for anymore.

Anyhow, I see your main point: SQLite is indeed "Lite", so it makes sense to use in embedded or small single-consumer cases. I just don't think those are very interesting tools to make in 2021, we expect collaboration and concurrency, so PostgreSQL wins in my imagination-space. I am a web developer though, so I am inherently biased.

I also am not the person you were just discussing with

> With the deprecation of Web SQL, I highly doubt this is a use-case anyone cares for anymore.

WebSQL didn’t die as a standard because noone wanted client SQL on the web, it died because SQLite was the only reasonable way of providing it, preventing multiple really-independent implementations.

There are people compiling sqlite to webasm, so it's a use-case some care for.

> This means it can even be used on many embedded systems far too small to run a Unix.

A good example is that SQLite is used inside many video games. You wouldn't put Postgres in your game engine.

Is using something with that small a foot print even worth it in today's age? Surely postgres and other don't have THAT big of an overhead, at least for the features they provide.

I like everything about Postgres except how arcane it is. It feels like 1980s Unix software, and everything about it is manual. It seems like there are multiple ways to do many things and I never feel like I am sure I am doing it the "right" way (as in both correct and modern and what you should do).

I've been watching CockroachDB as a more modern alternative that can auto-scale easily, do master/master easily, etc. Those are all painful on Postgres to the point that hosted and managed pgsql is a cash cow for cloud vendors.

It can’t run embedded. Having no live parts is a real paradigm shift for so many scenarios.

What does "can't run embedded" mean exactly?

I've run Postgres in a pure RAM only configuration, never touching the disk except to start.

If you mean "it can't be compiled in to another application as a library", then yes that's true - it's not an embedded database.

Can it be contained within the application, like SQLite?

Or has it to run as a separate process?

"can't run embedded" means you have to have a dedicated DB server process. For anything I would use a database for this is a non issue, but if you want a DB in your mobile app (for example) it might be.

So if SQLite is typically run embedded in the client application, does this mean it's not supported to have multiple clients operating on the same SQLite DB concurrently, or is there some support for this?

This is covered early on in the article.

It’s sad when most people discuss things based on a one line title of something they haven’t read. Many questions here were answered in that article. I recently asked someone on a different site “didn’t you read the article?”. The obvious answer I alluded to was “No, I don’t have time.” Yet they managed to post about 15 different comments asking more questions about the article (title). I really wish there was a way to have a public forum with proper discourse with the caveat that people must read the material before they comment on it.

The technical blog of the public broadcaster of Norway has implemented exactly this: https://nrkbeta.no/2017/08/10/with-a-quiz-to-comment-readers...

The quiz module is open-source (https://nrkbeta.no/2017/03/06/our-comment-quiz-module-is-now...) and available on github https://github.com/nrkbeta/nrkbetaquiz

Thanks for the links. :-)

It's mentioned briefly in the article, but no instructions are given on how to properly configure busy_timeout and WAL to scale above more than a few concurrent writes per second.

It uses filesystem locking mechanisms to synchronize concurrent access from multiple processes. One writer, multiple readers.

Kind of - your web application needs to partially handle that, and SQLite also depends on the OS filesystem to handle concurrent access.

But if the metric is "need in most cases," then that's fine. You don't need an embedded database in most cases.

The other way around. For most problems you don't need a DB. Only for the subset of those that do you need a dedicated process/container/machine.

We have learnt to use full-fledged RDBMSs as a default because they proved really flexible and powerful over the last 30+ years. But they do have limitations and cost.

And Postgress can be used as an event store + document store.

It also has javascript V8 functions to update data for migrations.

( eg. migrating your events to a new model, instead of keeping the different versions)

You can store files/documents within SQLite dbs as well.

There is JSON support, yeah.

But while you need to do everything with a JSON1 extension for managing the json ( https://www.sqlite.org/json1.html ).

Postgress has build-in v8 javascript functions to manage this ( as stated before). Which makes it much more useable.

There's a good library for .net that handles postgress documents, which has an insane amount of usefull functions ( https://martendb.io/documentation/documents/ ). I think halve of those wouldn't be possible on another database.

You don't really need v8 JavaScript functions built-in in the database when you use SQLite, because SQLite is running in the same process than your application. The round trips between your application and SQLite cost almost nothing.

Also see "Many Small Queries Are Efficient In SQLite": https://sqlite.org/np1queryprob.html

I was literally talking about using v8 functions when managing data ( eg. updating event versions should be a clear use-case)

Round-trips and small queries don't have anything related to my comment?

Since SQLite is embedded in your program, why not use your programming language directly. SQLite can even callback in custom functions you define. For example in Python: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne....

The main reason why main client-server databases have so many built-in functions is because of the round-trips. Let's say you want to filter rows based on the result of some function, if your function is implemented client-side, then the database would have to send each row to the client.

"Relational database engines act as bandwidth-reducing data filters." (https://www.sqlite.org/whentouse.html)

I didn't consider it that way, thanks.

My initial comment was a bit cryptic to be honest :)

You resolved it perfectly.

My experience with flatfiles dbs was not enough to interpret it correctly :p

It's unbelievably powerful, lightweight and there's not much it can't do.

I agree, but there is one thing that it cannot do, one very important, critical thing: synchronous multimaster replication.

Well, vanilla PostgreSQL cannot. Vertica can.

What makes multi-tenancy easier in Postgres than in any other DB?

I wrote a full multitenancy row level security system in 3 short Python functions in Django. Simple, complete, compatible with Django's architecture. All the other multitenancy solutions I researched were complex and fiddly and heavyweight and required all sorts of considerations and caveats to implement and run.

I’d love to see the code if you can share.

I'm preparing it into a minimal open source project over the next couple of weeks. I'll post to HN when it's ready.

The simple explanation is that you set a postgres environment variable prior to each query. The Postgres row level security system looks at this variable and returns nothing if the ID in the variable does not match an ID in the table.

So I wrote a function in Django which intercepts every database query and prefixes each query with a postgres environment variable set command.

That's pretty much it.

if you dont mind sharing, I'm curious what the methods + method signatures are!

One of postgres's most underrated features. RLS is amazing, can be unseen/basically work silently if your programming language-side tools are good enough, and is documented well (like everything else):

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

But the power of PG is that it doesn't stop there, if you combine this with a plugin like temporal_tables and you can segment by user and time:

https://github.com/arkhipov/temporal_tables

All of this mostly unknown to the thing that's accessing the DB. If that's not enough for you, why not add some auditing with pgaudit:

https://www.pgaudit.org/#section_three

All this value is just out there. There's even more if you can stomach browsing "ugly" sites like PGXN. Most of it works out of the box, though you may need to tinker for performance and some edge cases but it's there.

I think it might not actually be hyperbole to say that Postgres is the greatest RDBMS database that has ever existed.

I've seen mysql used in multi-master ha configuration (percona-based solution) successfully, do you have anything to recommend for a true multi-master postgresql solution (ideally open source)?

Postgres-XL and Postgres-BDR are both multi-master. I don't think they're open-source.

> Why not use sqlite? Here's one example - I like to access my database from my IDE remotely - my understanding is that remote access is not possible with sqlite.

I found https://kristaps.bsd.lv/sqlbox/ to be an interesting approach.

Can you share more on how you do multitenancy? Are you using it for web/mobile? What language/Library do you use to access the database?

I’m building a multi tenant web application on node/sequlize/feathers and implementing logic in code. I’ve seen table row level security but haven’t looked deeply.

Refer to this comment elsewhere in this thread: https://news.ycombinator.com/item?id=26817468

I use Python/Django.

I use Postgres row level security.

Read this entire comment thread for more info from other posters on row level security.

By multi tenancy is dead easy in Postgres, are you talking about schemas?

If so, have you run into any scaling issues with the number of tenants you can support. I looked into using schemas for multi tenancy but the various reports I found said around 50 tenants or so things started breaking down very quickly.

Refer to this comment elsewhere in this thread: https://news.ycombinator.com/item?id=26817468

No I am not using schemas - I found them to be brittle and complex as a solution for multitenancy.

I use Postgres row level security.

I use Python/Django.

Read this entire comment thread for more info from other posters on row level security.

A lot of IDEs and other graphical tools can point towards a SQLite database. Does it really have to be local though? I assumed you could hit it from any external file share? Or did you mean something else?

You can remote access sqlite database with sshfs or curlftpfs. Or simply sqlite cli over ssh

What's wrong with MySQL? Genuine question.

There’s nothing “wrong” with MySQL/MariaDB - it powers huge portions of the web.

Postgres has a larger feature set. Whether or not you need it depends on your use case. There are ample pages out there that will give you a side by side comparison if you’re curious.

Reply on news.ycombinator.com