News for hackers. Stuff that matters.
Re:Creating an advanced search engine with PostgreSQL by rane on Wednesday July 12, 2023 @02:57PM (#36699946)
How does SQLite fare against PostgreSQL for this particular use case?
Re:Creating an advanced search engine with PostgreSQL by shortrounddev2 on Wednesday July 12, 2023 @02:58PM (#36699961)
Why do I see so much discussion about SQLite on HN? I've literally never seen it used in production, my usage of it is purely as a database for local testing
Re:Creating an advanced search engine with PostgreSQL by samwillis on Wednesday July 12, 2023 @03:10PM (#36700136)
With the move to the "edge" for compute, having your database at the edge reduces latency and basically makes it viable. So the theory is that SQLite read replicas will be the answer to a lot of these plans. See what Fly and Cloudflare are investing in with the edge network and SQLite plans.

On top of that SQLite is now viable in the browser with WASM and the "origin privet file system" api. And so people are increasing looking at moving more state and persistence to the browser with an eventually consistent sync. This is what the "Local First" movement, myself included, are excited about.

Re:Creating an advanced search engine with PostgreSQL by vmfunction on Thursday July 13, 2023 @04:59AM (#36706561)
It is what Deno is doing, by putting what traditionally known as the middleware on the edge with a db (localStore). Essentially emulating the browser stack in some ways. Be very interesting to see different kind of code sharing amongst Deno edge nodes and browser.
Re:Creating an advanced search engine with PostgreSQL by bshipp on Wednesday July 12, 2023 @03:04PM (#36700059)
Sqlite is extremely powerful for any locally-embedded databases, such as apps with an offline component. For example, searching text in notes, books, cached news, etc. It's very simple to implement inside Sqlite with much less overhead than including a Postgres/Mysql instance.
Re:Creating an advanced search engine with PostgreSQL by Mustachio on Wednesday July 12, 2023 @03:13PM (#36700171)
We do natural language processing with some custom tokenisation, the data it uses at runtime is put into an sqlite db at build-time. Works fine for our usecase.
Re:Creating an advanced search engine with PostgreSQL by klysm on Wednesday July 12, 2023 @03:09PM (#36700110)
It’s used in production quite a bit, probably just not in your field as much. Anytime I’m thinking about storing something in a file I use SQLite instead
Re:Creating an advanced search engine with PostgreSQL by TX81Z on Wednesday July 12, 2023 @03:09PM (#36700115)
Lol, you’re using it “in production” right now! SQLite is everywhere, almost definitely in the browser you’re using to view this post.
Re:Creating an advanced search engine with PostgreSQL by shortrounddev2 on Wednesday July 12, 2023 @03:26PM (#36700349)
I guess it's a difference in industry. Everything I do is accessed over a network, and for caching we use simpler key-store libraries rather than relational databases. I don't develop client software
Re:Creating an advanced search engine with PostgreSQL by TX81Z on Thursday July 13, 2023 @01:24PM (#36712792)
Oh I don’t currently have it in my stack per se, more that it is all over the damn place and can do a ton.
Re:Creating an advanced search engine with PostgreSQL by 0xbadcafebee on Wednesday July 12, 2023 @04:15PM (#36701023)
HN has fads like everything else. If this were 10 years ago they'd be asking about MongoDB. 5 years ago they'd be asking if the Blockchain could power their search engine. Now they want an AI search engine.
Re:Creating an advanced search engine with PostgreSQL by appletrotter on Wednesday July 12, 2023 @04:34PM (#36701269)
I feel like MongoDB was on the wane even 5 years ago.
Re:Creating an advanced search engine with PostgreSQL by callalex on Wednesday July 12, 2023 @04:55PM (#36701551)
Also, the interest may not have all been…organic.
Re:Creating an advanced search engine with PostgreSQL by thewataccount on Wednesday July 12, 2023 @05:18PM (#36701815)
Sqlite has nothing to do with AI searching in the way I think you're suggesting.

It was not designed for "AI usage" or as a "AI database"

SQlite is from 2000 and is more comparable to a single file postgres/mysql database.

It's also on literally billions of devices including whatever you're reading this on.

Re:Creating an advanced search engine with PostgreSQL by dgb23 on Thursday July 13, 2023 @05:50AM (#36706876)
Tech that has stood the test of time is the opposite of a fad.
Re:Creating an advanced search engine with PostgreSQL by thewataccount on Wednesday July 12, 2023 @05:16PM (#36701802)
Put simply - SQLite is incredibly powerful and simple - it can handle a lot more then people might think - and AFAIK it is the most "used in production" database whether you're aware of it or not.

Depending on the use case it can scale incredibly well, and is tiny and battle hardened.

It's the most deployed database, it's on every smartphone, "smart device", computer, it's inside of many of your apps, powers many of the websites you use, etc.

---

tl;dr - If you're following "use the simplest, but reliable tool for the job" then sqlite is a valid option for a crapload of use cases, most people aren't aware of what it can actually do because it doesn't "web scale".

Sqlite isn't new, it's old, and it's "used in production" count is in the literal billions.

Re:Creating an advanced search engine with PostgreSQL by osrec on Wednesday July 12, 2023 @05:19PM (#36701829)
It is used in a huge number of production environments, including within your browser (most probably).

Probably not something you want to run a multi tenant SaaS database with, but it is useful if you are going the one tenant per database route.

Re:Creating an advanced search engine with PostgreSQL by hot_gril on Friday July 14, 2023 @12:56PM (#36726581)
SQLite has been the default store for CoreData in iPhone apps for years afaik. I imagine that's a lot of apps.

Of course, CoreData sucks, but that's for unrelated reasons.

Re:Creating an advanced search engine with PostgreSQL by bshipp on Wednesday July 12, 2023 @03:01PM (#36700020)
I've had good luck using FTS5 in Sqlite for small-scale use scenarios, such as examining scraped data, etc. It's remarkably powerful and quick.

https://www.sqlitetutorial.net/sqlite-full-text-search/

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

Re:Creating an advanced search engine with PostgreSQL by anitil on Wednesday July 12, 2023 @07:43PM (#36703219)
I put together a demo of this for work where I pulled all our private repos and built an index, then used datasette as the front end.

It was incredibly fast, so much so that I found it more useful than github search. The index is pretty out of date now but I still use it purely based on convenience and speed

Re:Creating an advanced search engine with PostgreSQL by rahkiin on Wednesday July 12, 2023 @03:00PM (#36699992)
There is https://www.sqlite.org/fts5.html but I cannot deduce if it has the same features; the way of working is completely different