+----------------+
| |
| Use PostgreSQL |
| |
+----------------+SQLite as an embedded server-side database requires extra work and configuration to make it a viable alternative to PostgreSQL. It lacks good write concurrency and recoverability by default. It is, however, continually improving but gaps remain.
Since it does not have a wire protocol, SQLite is rarely connected to a data warehouse via ETL so it does not fit well as an alternative to TiDB.
No, it depends on the server application. An all-read or read-mostly server requires nothing special. Same for any server that expects a low number of users or database per user(s).
Mozilla uses it on servers for documentation sites. You can also run your own Firefox sync server using SQLite.
It has the advantage of requiring zero administration. All you need is a file: backups or copies are just a matter of copying the file.
How much time and money do you have?
"None" -> Use PostgreSQL
"A little" -> Pick a database which matches your application use cases
"A lot" -> Use PostgreSQL
"FAANG" -> Roll your own
MySQL was basically the default database for developing web applications from 1998-2013. (It is the "M" in the LAMP stack) It gained this position by being free to use, reliable and stable. This began a virtuous cycle where more companies catered to MySQL users. Deploying, managing, and MySQL was easier since everyone catered to the audience which drove a virtuous cycle of more developers using MySQL.
A lot PostgreSQL's popularity can be traced to Heroku where it was the default choice for a database. Heroku made it even easier for developers to deploy their applications to the internet. Instead of having some janky build process you would just type "git push heroku master" and you changes would be live in a matter of minutes. This ease of deployment drove the virtuous cycle for PostgreSQL.
For the web it's unlikely that choosing either database would be a mistake. They both are great options. Going into the technical reasons why I believe PostgreSQL is a superior if you don't know what to choose would be a separate post in and of itself, but I've already gone on long enough.
I would say for anyone starting out and worrying about how large a single node can scale, Postgres can run well on aa 64/128core server, 2TB ram and 20TB ZRAID6 with a chain of read replicas. This can be done out of the box on Postgres without much issue and can get many businesses quite far, but once you go to the lots and lots of TB, or you have specific write latency, consistency, or other requirements, you have to evaluate multiple databases against your company's specific usage patterns and data, as no benchmark will give you a good idea.
With a bunch of read replicas you have to deal with eventual consistency when you get replication lag as well as the inability to scale writes.
If you shard out horizontally you get write scaleability, and read scalability with much less chance of replication delay. You like likely be reading from the master node most of the time. You also gain a bunch of operational flexibility, and smaller failure domains.
I am just curious, how does Vitess do that?
I personally advise people to just start with PG and if they encounter very unique requirements that for some reason PG can’t be tuned to - then do the switch.
As a developer I’ve used MySQL a lot, installed it myself in local environment and used it with fairly large tables. It’s very reliable and I’m generally comfortable with it. Once it’s running I don’t give it much thought frankly, so I’m not itching to change.
There’s maybe a 1% case down at the bottom for cases that had reached a hard limit in production where you could fit a box entitled “read the linked article”
I'd appreciate if anyone could share their experience with using PostgreSQL for large enough data.
TiDB distinguishes itself with HTAP; transparently incorporating OLTP/ETL/OLAP in a single cluster. You have to specify the ETL layer and data warehouse in addition to PostgreSQL to make an apples to apples comparison; that is the core of HTAP positioning.
SAP HANA is the poster child for HTAP, a data warehouse with good enough OLTP performance to replace Oracle RDBMS; a single system is used for both SAP app tiers, Business Suite and Business Warehouse. The same value proposition applies to cloud apps. Independent OLTP/ETL/OLAP is still robust and is more modular while HTAP is more tightly integrated and simpler to operate.
I'm not convinced HTAP can actually work - the they OLTP and OLAP works internally seems too different.
In-Memory HANA is freakishly good for running OLAP BW queries against fresh data. Column stores like HANA and IQ are both good at this, but to be honest, I don’t know how BW systems were typically configured before HANA/IQ.
But we definitely ended up in a situation where it was hard to get to some important data, since the team couldn't SLT it to BW because "it's too many transactions" and trying to get it from Sidecar was blowing up, because it's too much data. And that was already with S/4.
But again, I don't know why it was the case. It would have been great and saved us a lot of work if it worked out and we could've done stuff in HANA directly, instead of copying data to different OLAP system daily. Especially now, when everyone is trying to get on the realtime-train.
There really isn't a very good free one part solution here - so either you pay big bucks for the likes of Google BigQuery or Snowflake so they can become gatekeepers to your own data, or you end up burning a lot of engineering time to get the likes of Hadoop or Spark on K8s or Trino working.
I realize parquet is used by the hadoop/spark ecosystem, but do you really need those systems? I'm thinking that a lot of companies reach for a hadoop cluster when some parquet files in a regular for system would be much simpler. I've done things like this and in my experience it works quite well. But only for personal projects.
For smaller data (and here that would mean few hundreds GB which is pretty huge by normal standards) OLTP databases would cover you pretty well. Oracle has some fancy bitmap indexing, MS SQL even has columnar tables, and pretty much anything has partitioning.
Parquet is really cool - especially there's like 30 x ratio between "generic oracle table" and gzip compressed parquet file so scan time are really in a different world.
But by itself parquet doesn't solve a whole lot - where are the files stored and what scans them? What happens if someone is updating the files while someone else is reading them?
Re: concurrent reading and writing, for my use case the files are immutable so that isn't a concern. But I agree, I don't think pure parquet is a good fit there.
DynamoDB, which I was slow to come around on, is attractive for the same reason, although it only works if the use case fits, obviously.
Eventually your dropping of the database is consistent. So back it up no matter what.
Occasionally PostgreSQL gets used, as kind of staging database for small teams on a department level, with a db link for the big boy database used at corporate level.
Ada flavoured PL/SQL.
The Java and .NET drivers, with support for advanced stuff like distributed clustered transactions and direct mappings of UDTs into source language types.
Support for nice stuff like OLAP cubes, bare metal databases and APEX provides a nice way to quickly build database frontends.
As mentioned I am familiar with PostgreSQL, and honestly other than a couple of SQL extensions that are easier to use, I don't see much value when I compare everything that is on the box, and usually at the project scale I work on (just yet another cog on the enterprise wheel), license costs aren't the biggest hurdle to care about, there are other pain points where money matters more.
Don’t get me wrong, we love Postgresql, and MariaDB, but Oracle is still a great database, with all the features and stability you could possibly want, just at a hefty price.
+------------+
| Use a file |
+------------+
json = json.dumps(database)
with open("database.json","w") as f:
f.write(json)
:)
- easily inspectable and editable with any text editor or cli via jq
- backup and diff
- language agnostic
If you really must use JSON, at least use SQLite in place of open().
I have to partition the ext4 filesystem with type small othervise I run out if inodes before diskspace!
Here is what it looks like in action: http://root.rupy.se/link/type/task/847068548006606746
The front end: http://talk.binarytask.com
If you're going to do this, at least write to a temporary file, fsync the file, rename into place, and fsync the directory. But I recommend SQLite any time someone is tempted to write these lines.
Since it was a long running job with many failures (throttling and connectivity issues), I had to constantly kill and restart the script.
I needed to know what ids to skip over on restart but for some reason listing a folder with a few tens of thousands of files is very slow. So restarting takes forever. I also ran into issues with nonatomic file writes where even though a json file was written, it was incomplete or empty.
I think if I had just inserted them as json strings into sqlite it would've been more robust? I am okay with losing writes (since I will just redownload them), but it was the incomplete writes and long time to reload the set of seen ids on restart that drove me crazy.
edit: grammar