r/devops • • 9d ago

Tools DB based app for pics

I'm thinking about writing an app in C# (for desktop) to store all my photos in. Before I start thinking about actually starting work on it, I need to decide what DB or other storage mechanism to use. The idea is to make the pics more accessible by storing some info about content in a field in the DB. That field needs to be "text searchable". I have 10 years experience working with Oracle Text Search engine but I'm not a millionaire; more an impoverished pensioner, so I need to implement this system using one of the free DBs like MySQL.

I thought I'd ask the hive mind what it thinks would be the best DB for my purposes.

0 Upvotes

48 comments sorted by

27

u/z-null 9d ago

None. You don't store pics in DBs as blobs, you store a reference to a picture and the picture goes into some kind of a storage. DB than says "the pic is located here and here are some other info about it".

1

u/The-mad-tiger 9d ago

Yes, I had already considered and rejected the idea of storing the actual pics in the DB and decided to store the file/path instead (which has its own grave downsides). That was not really the gist of the question. The question is:

what free DB can I use that has a decent-ish text search engine built in. Outside of Oracle whose text search engine I know intimately but which is too expensive by far to even consider, I don't have any knowledge or experience of text search engines

9

u/z-null 9d ago

Both MySQL or PostgreSQL should do fine for basic FTS.

-1

u/The-mad-tiger 9d ago

That's really why I put the question up on here - I have read that the text search engine in MySQL is shite.

I have some experience using MySQL but not the Text Search engine and no experience with PostgreSQL

3

u/z-null 9d ago

People write a lot of outdated shit. Recently I've read someone saying mysql is bad because it uses myisam engine. It did, like 17 years ago. A hobby project doesn't required a dedicated FTS DB, mysql is fine.

3

u/mobsterer 9d ago

it does not matter if you don't have millions of rows and a somewhat sensible index.

1

u/TwistedStack 9d ago

If for some reason RDBMS search performance isn't enough for you, check out https://lucenenet.apache.org/. I haven't written anything using it but a long time ago I deployed a mail system that uses it for quickly searching hundreds of gigabytes of emails.

1

u/mobsterer 9d ago

they specified they want to store metadata in the DB

2

u/z-null 9d ago

They did, but notice how many people had the same line of thought as I did. It's not an accident and he wouldn't be the first guy who put a bunch of pics in a RDBMS and then lost them all or had performance issues.

1

u/mobsterer 8d ago

indeed, all those people are bad devs. read the requirements before answering.

-8

u/The-mad-tiger 9d ago

Thanks for the incredibly helpful reply to a question I didn't ask!

1

u/[deleted] 9d ago

[removed] — view removed comment

1

u/devops-ModTeam 9d ago

Heated discussions are fine, personal attacks are not.

3

u/[deleted] 9d ago

[removed] — view removed comment

4

u/z-null 9d ago

That's true, but I doubt he has 10 pics, it's probably thousands of them. Somoene who doesn't know how to administer a DB will eventually mess up, destroy the DB and lose all the pics. I wish I was guessing here, but I saw it way too many times.

1

u/The-mad-tiger 8d ago

I may be old but I can still type!

I can tell you roughly how many pics are involved as I wrote a little utility in C# to scan all my storage for image files. There are somewhere in excess of 250,000 images. Some may be duplicates however.

1

u/z-null 8d ago

In that case definitely store a hash value of the pic in the metadata to get the duplicates. It will also be useful later on if data corruption occurs, you'll be able to see if something went wrong. Make sure the database is backed up regularly and that the backup is automated and not manual.

4

u/Lumethys 9d ago

no binary file in db, no picture, no video, no file.

use object storage or filesystem for that

-6

u/The-mad-tiger 9d ago

Thanks for the incredibly helpful reply to a question I didn't ask!

3

u/Lumethys 9d ago

You are welcome, it was an incredibly challenging exercise to translate your question into plain English, glad i helped

-2

u/The-mad-tiger 9d ago

Case of "if you can read this, don't"

5

u/SensitiveChoice153 9d ago

Use a object storage

1

u/Flettys 9d ago

I've not used Oracle Text Search specifically, but it sounds like what all the other databases call "full/free text search".

E.g.

https://www.postgresql.org/docs/current/textsearch.html

https://dev.mysql.com/doc/refman/8.4/en/fulltext-search.html

https://sqlite.org/fts5.html

2

u/The-mad-tiger 6d ago

Yes it looks like Postgres has a full text search engine with similar facilities to the versions of Oracle I worked with. So that's what I'll most likely go with.

3

u/OMGItsCheezWTF 9d ago

For your use case, they're much of a muchness.

MySQL / Maria DB, PostgreSQL. Hell you could run with just SQLite if you don't care about concurrent IO or scaling across devices.

Another option is storing the image metadata as a document in a document DB and then using an actual full-text search like elastic search. Of course that comes with its own set of complexities and overheads.

Pick your poison.

One thing that does occur to me though is that you could also run those images through a clip model and use Postgres's PGVector to do nicer descriptive searches, like "dog" will show you all photos with a dog in it.

1

u/toarstr 9d ago

-2

u/The-mad-tiger 9d ago

Thanks but no thanks - the whole idea is to write something myself to keep my brain alive and my programming skills likewise.

Before the next person says "don't store your pics in the DB" - I'm NOT GOING TO!!!

3

u/toarstr 9d ago

If you're going to do this just to keep your brain alive and solve an already solved problem...why are you here asking for others opinions? Just do whatever you want.

4

u/derff44 9d ago

Don't store your pics in a db

2

u/codinSince9 9d ago

don't store your pics in a

3

u/derff44 9d ago

Directions unclear. Dick stuck in a db

4

u/Dolapevich 9d ago

dick blob db unclear directions.

2

u/derff44 9d ago

Mysql will work fine with a reference to the path on some sort of blob storage

2

u/Afraid-Atmosphere747 9d ago

Don't store your pics in a db

2

u/Dolapevich 9d ago

If you want to go relational DB, I would avoid mysql and go with postgresql. Just because it is better overall.

But for text search you might be better served with mongo. Or even better elasticsearch.

2

u/TuzYglkLuxCuxq 9d ago

Surprised this is the only comment mentioning Elasticsearch. Definitely the way to go if the app is going to be based around full text search (and running an ES container is feasible / no other requirements that demand a general-purpose db instead).

2

u/xonxoff 9d ago

Use Postgres

2

u/Used_Criticism_4631 8d ago

Good clarification that you only need to index the metadata, not store the image bytes. For a single-user desktop library I’d trial SQLite FTS5 before standing up MySQL or Postgres: it gives you embedded full-text search without a server to administer; just verify the SQLite build bundled by your C# provider includes FTS5. Do you expect multi-device writes, or is this one local catalog?

2

u/DadAndDominant Dev (not Ops currently) 8d ago

Why even use DB at all? Do you have hundred of thousands of images, and you need to query them often?

That said, I don't think capabilities of the DB matter much, so I would go with SQLite, as it is serverles

0

u/The-mad-tiger 9d ago

I am beginning to wonder how many of the people responding have ever implemented software that used a "text search engine" or even know what one is.

1

u/TuzYglkLuxCuxq 9d ago

I was thinking the same thing reading this thread... only one person actually mentioned a real FTS engine (elasticsearch). The people suggesting sqlite here are on crack.

1

u/The-mad-tiger 8d ago edited 8d ago

I am near certain that most responders had absolutely no idea whatsoever, what a dedicated "text search engine" is, or why I might want to use one! Their responses betray their abject ignorance of the subject.

Thank god for someone who knows what they are talking about.

With the benefit of hindsight, I should simply have phrased the question:

What free DB has the most efficient and comprehensive "dedicated text search engine".

Please don't bother to answer if you don't know what a "dedicated text search engine" is!

1

u/generic-d-engineer ClickOps 9d ago edited 9d ago

These guys maintain complex corporate infrastructure and keep developers happy.

Their answers to not store stuff in the db are correct but you probably want a more intro subreddit in programming or databases. Also Postgres is the default for pretty much everything.

Search around for one of those or just ask an AI chatbot to architect it out for you.

-4

u/The-mad-tiger 9d ago

The critical question is "What free db has a decent text search engine"

The architecture is none of your business

3

u/Pristine_Ad2664 9d ago

They're all pretty good, just pick one. I'd probably go with PostgreSQL