Doubt regarding mysql or postg for a very simple application
 

Hi,

I have one question regarding DB performance between mysql and postgree

My application will use a simple sql table. It will only store couple of fields like car plate, timestamp , filename, etc.

It is a access control system so we will just do inserts and some select to gather data. My question is which DB is better for this ? mysql or postg ? THe DB schema is very simple however I will have a lot of data on this table because lots of car will be passing through.

the language I am using is c++ under ubuntu

Can anyone please help me?
Thanks

I suggest sqlite. Then you wont need a seperate server.

https://sqlite.org/

Honestly, with the information that we have, the answer is "it doesn't matter."

Whichever is easiest/cheapest. SQLite would be my second choice. Is there a good reason to even use a RDMS for an application this simple? Flat files are probably easier.

Yeah, I'd say an indexed database would work better than flat files.

But an important question to ask is how many actual entries there will be over time, how you will want to access and use the data, and whether you see many functional extensions in the future.

With a simple data structure as you describe basic performance will not be an issue.

If you never much access the historical data other than in/out like a parking lot then it really doesn't matter how you store it. But if you need to generate reports and summary data periodically, or correlate with other data then an RDBMS would be your best bet.

Also, if you expect the application to be extended in other ways in the future, use of an RDBMS would isolate the data and facilitate future extensions.

sqlite is excellent.

Setting up a full blown SQL database is not a lot of fun.

Solid advice from previous posts. In a case like this, for me it is down to what I know how to use. If you have a personal preference or extensive knowledge, just use what you know already.

Formally, any version of mySql that is not for personal use is a paid product on any OS.

OK

This is the right answer.

I would consider only a true server-based RDBMS for an application like this ... which disqualifies SQLite in this case ... but, insofar as which server-based system to use (there are dozens besides the ones you named here), I suggest that you defer this decision until you have fully developed the technical requirements of your entire application, and of the technical context in which it will be expected to operate.

So, how do you reach the determination that only a true server-based RDBMS is appropriate before that rigorous requirements study has been completed?

Definitely. If what you ordinarily might be looking at is "flat files," or a collection of them, then SQLite is an excellent, very high-performance choice. You get all the simple advantages of local files, plus "400 cubic inches, V-8 and a four-barrel carburetor." :)

There's only one fairly-huge caveat: "use transactions!" If you don't, then SQLite is designed to physically verify every disk-write and will not cache any reads, which makes its performance seem horrible until you catch-on to what it's doing and why. For both reads and writes, start a transaction, do your work, commit. As long as you do that, it's: "grab ahold of your hat and hang on!"

---
Now, am I saying "SQLite is the right thing to do here?" No. In this case, I think that a server-based system is called-for and the app-specific research work has not yet been done which will demonstrate which system will turn out to be best.