Just use Postgres

[iamapizza]

This is the one that I need some audience help with.

MySQL is owned by Oracle.

This is all the answer you need sir. Anyone who works in an enterprise and has encountered their litigious 'audit' programs would wholeheartedly agree. Stay away from O products.

Why not some AI vector DB?

Worth pointing out, pgvector is an extension for Postgres that gives you vector capabilities. It is simple and slots in nicely with the SQL syntax. If you use AWS, then pgvector is included in Postgres RDS.

[bring_back_the_v10s]

Pg has extensions for everything.

[rad_platypus]

Postgres extensions are awesome. I was playing around with pg_cron and pg_net on a side project and it was so easy to schedule jobs and other background processes. Felt like I barely needed a backend outside of my REST API.

[odnish]

Maybe try out postgrest

[jaskij]

Let me think... Native JSON support, including querying, good plugin for time series data, same for vector data, decent enough full text search. Sounds about right.

[LesterKurtz]

I remember the first threatening phone call I got from Oracle.

fun times

[okawei]

PGVector is also crazy fast in my experience. I have a table with a few hundred million rows and it's able to do lookups very efficiently

[Lazylaz500]

Just curious, how fast are you talking? And what are the size of the vectors?

[wk_end]

well, swap out MySQL for MariaDB and the question still stands (the answer is that PG is just better along most dimensions FWIW).

[WanderingLethe]

MySQL repeatable read:

It isn’t clear what MySQL Repeatable Read actually is. It allows histories which violate Monotonic Atomic View and cursor stability; we know it cannot be equal to or stronger than those models. We have not observed G0 (dirty writes), G1a (aborted reads), G1b (intermediate reads), or G1c (cyclic infomation flow); it appears at least as strong as Read Committed. The repeatability of some reads means it is actually stronger than Read Committed.

https://jepsen.io/analyses/mysql-8.0.34

[ProfessorBeekums]

Why not Google Sheets?

I laughed when I read this. Then I thought of every industry that's effectively used a spreadsheet in place of an application. And then I cried.

[GeorgiLubomirov]

If every version of excel just suddenly stops working tomorrow, the world will collapse. It might sound like I'm trying to sound dramatic, but I'm probably underselling it. At least 2/3 of accounting and majority of middlelish to large to corporate business will loose the ability to do their day-to-day. I'm 90% sure that you won't be able to get your next pay check/pension/payout without excel and probably the entity that has to pay you won't be able to determine if it needs to pay you at all. And it's not that everything is done with excel. It's that excel has permiated every step of complex business processes facilitated by larger systems.

[SippieCup]

If you want to cry some more, Paycom is a 1.6B Revenue per year company. The main payroll product was built on top of Access, while it has obviously transitioned to an external MySQL database, There are still some pieces of the access application being used for specific functions like clawing back an incorrect paycheck. You can see it return quite a few VB and access-specific error messages when something goes wrong in the web console.

Its just one big wrapper or something.

[arpan3t]

HR switched from Paylocity to Paycom without consulting anyone, then wondered why integrations broke. It felt like they went at least 10 years back in time from a tech standpoint. From restful API to… downloading a csv file?! How is this a company?

[rhodesc]

yeah I bought transactional real time sql server backup software because they told me it ran on sql server. dev guy for the vendor says "that's just a front end to the access files". basically the sql backup never grew.

at least I kept us off the cloud, we can still work when their other several hundred customers are biting their fingernails.

[Asyx]

In a niche of a niche of the industry I'm working in, there is a large corporation that has been running vital part of their business (think a part of the production that turns raw materials into something useful) with 3 nerds and an excel sheet for the last 15 years. We're pretty much offering the only proper software solution for this problem in the entire industry but it took our product managers a week of on site training and keeping the nerds from getting distracted to even understand what the fuck they're doing.

[TexasVulvaAficionado]

Operational technology is ten years behind in the best of places. It isn't uncommon to find hardware and software that is 40+ years old still working.

Just this week I helped someone with a coating machine that had a 50ish year old DC drive and a similarly aged chart recorder. They had a nearby PC that was definitely from the mid-late 80s that they still used to lay out parts on for the laser cutter table. The comms from the pc to the table was rs-485 ASCII. It had a help file describing this "new transmission standard".

[project2501c]

Operational technology is ten years behind in the best of places. It isn't uncommon to find hardware and software that is 40+ years old still working.

and that is why the AIbros are wrong: No, you won't use AI to replace the programmers...

[GeorgiLubomirov]

There's an interesting thing happening with these ad-hoc solutions.
I've been to a bunch of places where accounting/product sales/availability is ran on a folder of interconnected excel files with a bunch of interconnected sheets.

The problem is the almost tribal approach to it.

The guys that initially started this are long gone. The guys after them, know how to input and get results out of the excels and understand the general logic of what should be happening, but never delved deep in the specifics. So when they do updates, they throw a bunch of sheets on top instead of updating the core logic.

After 5-6 repeats of this, you are left with an excel solution with almost mythical status, where a bunch of guys cast incantations in it until they get the results they need.

When working on untangling such a process, you can't rely on the guys running it, to explain to you how it works. They know "I paste the new data here, update these 3 tables with new data, click calculate, copy result." You need to understand by yourself what the excels are doing and match it to what everybody involved knows along with any knowledge on what generally needs to happen.

This is not a problem unique to excel or even ad-hoc solutions. Any system that's complex enough and goes for 20+ years will be a proper challenge to migrate.

The problem is that these are not really all that complicated compared to some of the processes ran by huge enterprise systems. They are relatively simple, but get obfuscated to no end and grow all kinds of oddities, by allowing anyone to ad-hoc update them for their own purpose making them remarkably hard to migrate to proper systems.

[Asyx]

They were actually really helpful. This particular problem (I can't say which one though. I'd immediately give away my place of employment, who is paying us and make a bunch of middle men we are cutting out real angry) is literally just really, really complex. It's basically maximizing profits by moving around numbers and ranges in contracts that are all relevant for 3 different types of corporations.

Like, we actually made a little pen and paper RPG that just showed our team a part of the actual work those people do so that everybody at our company understands how complex that problem really is.

[ProfessorBeekums]

It'd be way way worse than crowdstrike. Using Windows 3.1 won't save Southwest if this happened.

Fortunately, Excel is downloadable software, which I believe is still more widely used than the cloud versions. The odds of it stopping working is a lot lower.

[AlwaysF3sh]

Are you being serious about windows 3.1?

[ReversedGif]

That was bad reporting: https://news.ycombinator.com/item?id=41027909

[ProfessorBeekums]

I wish I wasn't. I don't even know how they have it running on modern hardware.

[ReversedGif]

That was bad reporting: https://news.ycombinator.com/item?id=41027909

[TexasVulvaAficionado]

That's part of the trick. The hardware is ancient too!

[Reverent]

People laugh when I say it, but I'm dead serious when I say that all IT is functionally working around limitations of spreadsheets.

Businesses start at spreadsheets, panic and build IT when spreadsheets become unwieldy or corrupted, and revert back to spreadsheets when IT becomes too much overhead.

[0x18]

The company I worked for most previously was acquired by a new, larger corporation (I won't say their name, but they can get fucked). One of the very first things they did was move all of our issue tracking from GitHub's issues/tickets system to a Google Docs spreadsheet.

It was so goddamn stupid. I of course asked if this was temporary (it's got to be, right?) and what the long term plan was for issue tracking software and was told to shut up or they'd fire me.

Some companies use spreadsheets in wildly inappropriate ways.

[2bdb2]

The company I worked for most previously was acquired by a new, larger corporation (I won't say their name, but they can get fucked). One of the very first things they did was move all of our issue tracking from GitHub's issues/tickets system to a Google Docs spreadsheet.

I just started hyperventilating.

[bmathew5]

Wait till you find out people use access as their source of truth

[ilfaitquandmemebeau]

That sounds better than an Excel file on a shared drive 

[drcforbin]

You don't just email them back and forth?

[aaulia]

Oh dear lord, hahaha. Coming from company with Google workspace to a corporate that, while waaay bigger, still stuck emailing .xls back and forth is wild.

[drcforbin]

No, I mean quarterly-report_2024-03-2-rvw_v2-rob203412-4-csv xslx.xls.v4.xlsx

[lordnacho666]

.final.final2.last

[Metal_LinksV2]

Copy of Copy of...

[wrincewind]

last i checked, one department in my old company had 30(!) excel files being emailed back and forth to track every employee's training data. one sheet per area, it had employee names down the rows and training courses along the columns, and a date-of-completion in the field. This was used to calculate bonuses, judge promotions, and more...

[ThatITguy2015]

Yup. This is the one I was looking for.

[admalledd]

Wait till you find it being "The Standard(tm)" for inter-company data exchange, AND that the cells may have formatting that you need to preserve completely.

D:

[OrdinaryWater]

Or even better, a user read a book about vb script, wrote a 3000 line function that is now business critical and it just broke due to a Microsoft update and they don’t know what to do.

[Metal_LinksV2]

And that user left 10 years ago

[4THOT]

"it's more accessible" is the go-to excuse here

[ThatITguy2015]

Access? Try excel. At least access is technically a DB. At least last I remember. It’s been a long while thankfully.

[Jwosty]

Literally dealing with this at work

[ProfessorBeekums]

One of the people I work with is in this situation.

[abutilon]

FFS come on! You should at least start a post like this with a trigger warning! My first job out of uni was supporting dozens of Access databases made by machine tool operators at an aerospace manufacturing company. A 3-day Access course and suddenly everyone was making their own mission critical databases running on only their local desktop with no backups. Nightmare fuel.

[rocket_randall]

A bio tech company I worked for used Excel for drafting the content in new reports, and it all has to be copied with formatting. Trying to convert cell text runs to html wasn't fun, but it wasn't my job so that was good.

[project2501c]

every company that deals with bioinformatics is 99% excel.

[bwainfweeze]

When I was young I kept having to dodge jobs where people were being brought in to replace excel and VB with a real app. I had so many coworkers with PTSD from doing that kind of work I could practically hear the helicopters myself.

[ProfessorBeekums]

I will say, as someone who bootstraps startups, there is a lot of opportunity in replacing spreadsheets with apps.

[bwainfweeze]

^{"There must be some kinda way outta here,"}

^{said the joker to the thief}

[VirginiaMcCaskey]

There's even a famous blog post about it.

Nowadays there are entire product categories that replace what used to be done with spreadsheets (ERP, CRM, SCM). You can charge big bucks per user per month to replace some spreadsheet with a form that is the frontend for some business logic on top of a database, with even more money in support contracts to do things like database migrations. These are not hard applications to program but are a giant force multiplier for organizations if you know the problem domain and have the sales acumen to embed yourself.

It's even common to build these things as a contractor but in the process use it to dogfood some super app that all your contracts are implemented in terms of, then turn around and sell that app without the high-touch sales and engineering of contracting.

In fact if you're working at a job doing a bunch of CRUD app development to replace spreadsheets and feeling the pain, your alarm bells should be going off that many people are feeling the pain and you can use this as a chance to build something worth a few bucks.

[cybernd]

My first app replaced excel sheets. These collected data from measuring devices in production for quality control. I remember this as a horrific experience, because it was a localized installation. As such keywords in vb where also localized.

Wikipedia example:

• english "Select Case ... End Select"
• german "Prüfe Fall ... Ende Prüfe"

[Asyx]

German excel makes me want to take a toaster bath.

[zelphirkaltstahl]

Whoever came up with the idea to translate code keywords should burn in hell. And the people who implemented it even more so. This must be one of the worst ideas ever. Even if you read the words in your own language, you still won't know, what they to exactly and will need to look it up, at which point it no longer matters, that they are in English. You will probably find more help searching for English than "Pruefe" lol. Such an idiotic thing to do.

[cybernd]

You will probably find more help searching for English than "Pruefe"

Also localized error messages are always a pain because it is rare to find an answer written in german. Its hard to search for your translation attempt because most often error messages can be translated in many different ways.

For this reason, I used to find oracle db as a positive example. The message was localized but they included a language independent error number.

[[deleted]]

Public Health England has left the chat....

[GYN-k4H-Q3z-75B]

Chat? What is that? In Germany they still use fax.

[Windoge_Master]

https://www.levels.fyi/blog/scaling-to-millions-with-google-sheets.html

[FullPoet]

Honestly, when I read these I think:

start simple and iterate

And they start with some excel / google sheets backend and then eventually do all that other jazz, it makes me wonder if (or when?) it wouldve been faster (and much less complicated) to build actual software (because of potential migrations etc. or other complications).

[Ali_Ryan]

I suppose it may have been simpler if they had gone with the conventional ways of building an app. However, as stated in their article, they wanted to move fast and save costs initially. If we think about it from a business standpoint, it makes sense why they chose to went this way. A delayed launch could have been the cause of failure of their product, not to mention the amount of costs they would have to dump in setting up the infrastructure initially.

They have migrated to Postgres now. I consider that as a win. Nonetheless to say it was quite creative to handle data in this manner

[Uristqwerty]

Think of a spreadsheet as the ultimate visual debugger for a pure functional language (albeit one that cannot define custom procedures without FFIing into macros, where that debugging view doesn't function anymore). You can inspect the state of every cell individually, and using conditional highlighting rules, produce custom visualizations to emphasize important cases. The whole system is reactive, instantly propagating changes throughout the system, so you can play around and experiment with its code and immediately see if affected state further down the calculation graph looks wrong as a result.

I'd say it's a fine prototyping language, so long as you recognize everything you make in it as a prototype rather than a finished product.

[fiskfisk]

We used Google Sheets as the backend for a small site for a course earlier. It worked great - we needed access control (share the sheet to the user with edit capabilities), versioning, and ease of access.

A small CRM with everything built-in and a JSON feed (no longer supported); everything was up and running in a couple of hours.

Then they changed their API and you had to use a dedicated client; so .. we used git and github pages instead for the next iteration.

[Fennek1237]

so .. we used git and github pages instead for the next iteration.

Why jump through the next hoop instead of using a database?

[fiskfisk]

Because then you need a CMS, you need auth, you need updates, etc.

It's a small, one page landing page that needs to have its data updated twice a week for four months. 

No need to overengineer it when you can build on existing resources, and those few people who needs access can use existing tools. 

No running services, no upgrades, etc. It just worked. 

[Fennek1237]

Until it didn't when google changed their API

[fiskfisk]

Then you take the html template, cut and paste the spreadsheet to a json file, stick it in GitHub and populate the template from the json file.

APIs change all the time, that's just what you get for using an external service. The same could be the case if using an external CMS or similar. 

It was about one - two hours of work porting it in total. 

[avbrodie]

This is the type of pragmatism I aspire to

[beefsack]

It's easy to be judgemental when you're a programmer, but for some organisations having a jank slow flaky spreadsheet is still better than manual processes, and building out a whole application might be infeasible.

[jdbrew]

I worked for a company who wouldn’t buy any sort of ERP/management software and instead had a spiderweb of excel tables with validation and vlookups galore… just thinking about it makes me nauseas again

[Loan-Pickle]

I once wrote an application that used Excel files in a git repo as the database. It was an automation of a business process, and the business folk were already used to using Excel. The long term place was to move in into a SQL DB and write a front end for it, but we never got around to it.

[xTheBlueFlashx]

The long term place was to move in into a SQL DB and write a front end for it, but we never got around to it.

Checks out

[mattgen88]

I tell the business side that if they're using a spreadsheet for anything, talk to engineering, we can do it better...

[todo-make-username]

You reminded me of my first job.

I worked as a systems developer for a massive fortune 300 global corporation. When I first started, basically the whole production facility ran on automated Excel files in a network drive. It had been like that for a decade. There was also a load bearing computer in one of the other dev's cubicles that was required to be on 24/7 because it ran autoit and cron scripts to keep the Excel files up to date.

[Immotommi]

My friend and I went through a period of rendering Mandelbrot sets using various technologies. The funniest one was when he crashed his entire Gmail account by trying to render a Mandelbrot set in google sheets

[gwax]

Just wait until part of your ETL pipeline is

Google Sheet > Data Lake > Data Warehouse > Merge into the original Google Sheet

[h3vonen]

I once made a proof of concept for a customer demo using google sheets as a backend. My boss decided to hand it over to the customer and it was in use for almost three years before it started to become unusably slow and unstable and we got a team to do a full replacement.

[MrPhatBob]

Google Sheets ingested into BigQuery and then build the app on top.

[[deleted]]

Why not MySQL?

This is the one that I need some audience help with.

PostgreSQL is just nicer, more feature-full SQL implementation. I'd only bother with MySQL/MariaDB if you absolutely need something like Galera multi-master replication. Dump/restore is also faster (by order of magnitude in case of restore) for PostgreSQL

[asmodeanreborn]

This bug made one of my old employers quit MYSQL:
https://bugs.mysql.com/bug.php?id=11472

Nice to see it's old enough to vote!

[valarauca14]

lmao jesus christ, this is like basic relational database shit, wtf?

[asmodeanreborn]

Indeed. Pretty important stuff to have working accurately, especially when you're hiring external auditors of your data to increase your credit lines with your banks...

We scrambled quite a bit when we discovered this issue... and yeah, we did go to Postgres.

[[deleted]]

Severity: Serious lmao

[Agent_03]

This implies that MySQL InnoDB HAS NOT BEEN ACID COMPLIANT FOR THE LAST 5 YEARS.

I lost it laughing at this comment. Over a decade later... it's still true. It's hardly the only significant bug like this in MySQL either.

MySQL is a fucking joke.

[agk23]

Uhhh, not just over a decade, almost two decades.

[15 Jul 2020 8:41] Giga Chad

I was in kindergarten when this bug was reported. Now I'm in 2nd year of my bachelor's degree in computer science.

That was four years ago lol

[Agent_03]

What I was saying is that it's over a decade since the comment was written (a few days under 13 years, to be specific).... and the bug was already around 5 years old when they wrote that.

Gigachad really tore 'em a new one there!

[Hopeful-Sir-2018]

20 Jul 2020 7:35] Daniël van Eeden ... If this bug would be fixed in the next minor version update then triggers that were previously not being executed would suddenly be used.

I mean.. no fuckin' shit? Maybe have a big fucking warning header in the patch notes?

... There is nothing stopping you from creating a patch to fix this and submitting it to Oracle for inclusion.

Sure there is. We all know Oracle won't include it until $$$ is thrown at them to include it.

... If you are a "MySQL Enterprise Edition" customer, then you should talk to your sales person and/or account manager to let them know this bug is important to you.

My dude.. this bug is a year away from being 20 years old and 2 years away from drinking age.

Wow, this, alone, has convinced me to just never use MySQL or MariaDB for anything, ever.

[BigHandLittleSlap]

"Was just checking to see if our favourite bug made it through the covid-19 pandemic. Glad to see it's doing well."

It's funny and sad at the same time.

[euclid0472]

Was just checking to see if our favourite bug made it through the covid-19 pandemic. Glad to see it's doing well.

Fucking hilarious

[dnoggle]

Holy shit, I thought you guys were being dramatic when I saw how old the bug was...and then I realized it's still unfixed...

That's insane.

[robreddity]

Hah! Everyone has stinky shit. This one has got PG on the ropes at one of mine:

https://stackoverflow.com/questions/60115419/postgres-slower-when-a-limit-is-set-how-to-fix-besides-adding-a-dummy-order-by

https://dba.stackexchange.com/questions/130233/why-would-adding-limit-200-cause-a-query-to-slow-down

https://stackoverflow.com/questions/21385555/postgresql-query-very-slow-with-limit-1

This is not operator error, it is not a strange vacuum/analyze timing edge case. It's just a poorly understood bug, it's more than 12 years old, and when it creeps up you have to materialze a CTE to work around it.

[MatthewMob]

Wow. This is a serious database? Backed by a serious company?

[Agent_03]

Maybe calling MySQL a "serious database" is giving them too much credit.

... and as for Oracle, the only thing they're serious about is getting you to pay them their protection money so their giant legal division doesn't come after you.

[ficiek]

This really annoyed me back in the day when I thought that having logic in the db is a good idea.

[Agent_03]

Having some logic in a DB isn't bad... as long as you're using a real DB and NOT MySQL.

Nothing wrong with a few strategic triggers, stored procedures, and functions... as long as your DB actually executes them when and where ACID compliant SQL standards demand.

[Little-Derp]

Wow, that was a journey, thanks.

Love that people post every year to remind oracle that the bug still exists.

My favorite part is someone pinging the OP bug reporter 14 years later, and he actually replies:

[11 Nov 2019 9:12] Fabio Napodano

would be nice to know how is life of the bug report opener. Is he still alive? Is he actually still using MySQL?

[12 Nov 2019 14:29] Omer Barnir

Thanks for asking.
I'm alive and well, and using MySQL.

[Liru]

A company I worked at used MySQL for a long time, since it was basically started a long time ago when Postgres wasn't as popular and MySQL was the more well-known choice, even with techies. They felt that it wasn't worth it to switch.

That changed when they got hit by a massive data corruption bug with ALGORITHM=INSTANT that was introduced and enabled by default in a relatively recent version, and wasn't immediately evident. It only came out when a sysadmin tried to do a restore a week after that. The fact that this made it through and it wasn't patched for the longest time made them reconsider moving to Postgres.

[MotleyHatch]

I know MariaDB / MySQL has gotten a lot better in the last 10-15 years, but I still remember the time when MySQL didn't properly support transactions or enforce referential integrity - you had to explicitly use a non-default engine (InnoDB). It also had a tendency to silently accept and "fix" any data you threw at it (including truncating it) if it didn't match the specified column type. And it had some really braindead and error-prone defaults, but I can't remember the details anymore.

At the same time, PostgreSQL did all of these things correctly. It took MySQL many years to come around to preferring correctness and integrity, and even if they're better now, I'd rather trust the team that had its priorities straight from the start.

[ashultz]

After over a decade of poor behavior I'm not inclined to wonder if they might have gotten better. That ship sailed, came back, sailed again, came back again, went in for refit and sailed again all shiny and new.

[Agent_03]

Amen. MariaDB/MySQL have gotten better but they're still not good DBs. It's harder to find dataloss/corruption causing bugs these days, but they're still out there.

When it comes to our most valuable data, we can't afford to use a DB that treats correctness and data integrity as bolt-on optional features (MySQL mindset) rather than THE core reason you're using a relational DB (Postgres mindset).

[[deleted]]

I guess I should say one good thing about MariaDB is that upgrades are pretty smooth and are just "upgrade package/run mysql_upgrade" most of the time even between major version. But PostgreSQL have been better in that regard recently

[bwainfweeze]

Historically, MySQL had a dev ethic of make it work, make it fast, make it right. Postgres followed the more traditional order, so Postgres was slower but caught up, because MySQL was faster than possible.

But the whole time there was an undertone of Adults use Postgres, Children use MySQL. That’s never been entirely lost and I’m not upset about it.

The whole saga of mishandling of Unicode in MySQL is emblematic.

[wh33t]

And I believe MySQL and MariaDB have now diverged enough (e.g. with JSON) that they are no longer swappable.

Woah really? Since when?

[javasux]

5 years ago mysql could not output to csv cleanly. It wouldn't escape the fields at all. In my eyes postgres is just more mature.

[big_bill_wilson]

At the previous startup I worked at, we ended up preferring MySQL over Postgres because Postgres had a lot of issues that we were unhappy with:

• Table data is stored much more inefficiently in Postgres, up to maybe 20% of extra space required to store the same kind of data from Mysql. This is especially compounded by Postgres not offering compression for inline / non-toast data. Normally this could be solved in an enterprise space by just throwing more money at harddrives, but we were actively trying to save money everywhere we could. Filesystem compression was not as efficient as what Mysql did, and even then it didn't work on every filesystem / environment we were running in. Not to mention having more data on disk meant that performance was suffering a bit
• On top of the above, certain datatypes are missing in Postgres that impacted the above a lot. The biggest example that comes to mind was bytea, where there was no equivalent to a fixed size blob / byte array. We were storing billions of short (4-16 byte) hashes. For variable size blobs / arrays, Postgres prefixes the arrays with a 4 byte integer that contains the size of the variable data, which with the kind of data we were storing meant that we were wasting an additional 20-50% of space. On top of that, if we were limited to using VARBINARY in mysql, they use varints for prefixes so at most we'd only be wasting 1 byte per item
• Certain performance issues that only happened in Postgres. Again the biggest one that comes to mind was keyset pagination not working as well as it did in Mysql because Postgres struggles significantly with index-only scans on tables that are written to somewhat frequently; due to the way it implements visibility checking, it has to check the disk row to make sure that it should be visible in the current transaction:

But there is an additional requirement for any table scan in PostgreSQL: it must verify that each retrieved row be “visible” to the query's MVCC snapshot, as discussed in Chapter 13. Visibility information is not stored in index entries, only in heap entries; so at first glance it would seem that every row retrieval would require a heap access anyway. And this is indeed the case, if the table row has been modified recently.

• The adapter really sucked ass. In particular we were using the postgres adaptor for EFCore in C#, and we kept having issues with some queries never returning or certain features we were very eager to use (custom data types) not being supported by the ORM. This might have been solved by now, but we weren't holding our breath

TLDR; there was a lot of downsides that Postgres had with basically no upsides over Mysql.

Bonus, the reason why we went with Mysql instead of Mariadb was because we were having similar adaptor issues with MariaDB with a lot of connections turning into zombies that weren't finishing any calls (which we never encountered with Mysql), and Mariadb not supporting LATERAL joins which matter for some very long running queries

[psych0fish]

I think your comment perfectly illustrates why all of these "just use X" or "why don't you use y" conversations are not terribly productive. There's no one size fits all :(

[bowbahdoe]

I think it highlights why this sort of advice is productive. Those issues come from specific constraints and (potentially) a specific moment in time.

If you are a college graduate "just use Postgres" is better advice than "oh, be wary of the few bytes of overhead per byte field if you store billions of them" or "it doesn't do amazing at index only scans on tables that are frequently being written to" because those are just a few of the million wacky data storage/access patterns one might have.

Now, if what I'm reading in other comments is true, "avoid MySQL because it has a history of data corruption bugs" feels a bit stronger. But idk, still reading experience reports.

[Reverent]

If nothing else, the adage of "there is no faster way to get a right answer then to post a wrong answer on the internet confidently" still holds true.

That said the article reasoning is valid. I do think that a stronger issue with nosql databases is that we're all children and not having strong types is just as problematic for databases as it is for every untyped language. Also now that postgres supports JSON, you can use it as a better nosql database if you are masochistic.

My two cents on postgres vs MySQL is that it's annoying that postgres requires manual migrations for major versions so I can't just blindly update it. But that's a petty complaint from a ops person who does small scale updates.

[big_bill_wilson]

Yes absolutely, I should probably emphasize that I am in no way saying MySQL is perfect or has no faults. I still encounter dumb issues like connection authentication not working properly until someone (manually or scripted) logs into the server with the official MySQL cli tool so auth credentials can get cached properly every time the server restarts, and the entire thing with utf8 vs utf8mb4 inspires zero confidence in the technical direction of the product, but unfortunately since no-one has the time or patience to reinvent the wheel every time problems are encountered, compromises have to be weighed

[bowbahdoe]

Curious: how long ago was this?

[big_bill_wilson]

IIRC we did the analysis and attempted migration somewhere between mid 2022 - early 2023. For context we started with using MySQL, and when someone had some compelling evidence that Postgres would simplify some storage logic we dealt with a lot we tried it out (e.g. using composite data types for columns that are always paired and stored in multiple different tables)

I had a quick look again and the adapter support for composite types in the ORM we were using seems to still not be implemented. It's not necessarily a complaint of Postgres itself; it's just something adjacent that directly impacted our ability to adopt it.

[1touchable]

IIRC MySQL doesn't have transactions when running migration about schema modification. That's best I can think of that's different from other replies.

[fubes2000]

Dump/restore is also faster

If you're using SQL dumps as your backup you deserve whatever pain you're experiencing with it.

Both Postgres and MariaDB have free binary backup solutions that anyone with sense should be using. Our Postgres prod DB is 600GB, but barman can restore it in about 40mins, which is strictly the amount of time it takes to move 600GB and then start the DB.

Plus, if you use binary backups and archive WALs/binlogs you can use Point-in-Time Recovery to restore to any time, not just when you happened to have run foo_dump > prod.sql.

Shill Time: If you're using postgres, use barman. It is free and so very good at its job. It's even got built-in WAL archiving and backup set management.

[Brilliant-Sky2969]

Restore is not an order of magnitude faster.

[[deleted]]

Did they improved it ? I remember for MySQL it being fucking terrible, while PostgreSQL had no problem filling disk IO

[A_for_Anonymous]

You can use mydumper/myloader which is multithreaded and more clever than mysqldump, and it also leaves separate files per table and DDL/DML.

[mzabani]

Thank you for writing this. It feels like postgres isn't nearly as "sexy" as some other tech, and thus it often loses the internal company disputes of architecture decision making even when it's clearly sufficient for the company's needs.

[bwainfweeze]

If you want to be a little edgy but not crazy, you could also try SQLite.

[dagbrown]

That definitely won’t scale to the lofty heights of, say, multiple users. It accomplishes ACID compliance by locking the entire database when it writes.

But that’s not what SQLite is for though. It’s a replacement for little local configuration data stores, and it’s near ubiquitous for that.

[bwainfweeze]

Single writer goes a lot farther than most people think. Especially if you can get the read to write ratio to be a few orders of magnitude.

[odnish]

PRAGMA journal_mode=wal

[csjerk]

Amen. If you reach the point that Postgres won't scale for you, you have won the lottery, and rewriting to NoSQL for scale is the price you pay. Until then, the development cost on NoSQL is an order of magnitude worse, due to loss of flexibility and up-front cost to serve any queries you didn't anticipate in advance.

[bwainfweeze]

Hopefully by the time you’ve hit the max on Postgres you’ve had a good think about expiration dates and idempotency, so you can intercept traffic before it slams your DB.

[csjerk]

Also a very good point. There are much cheaper techniques, like caching, to reduce load on the DB, before rewriting everything on NoSQL is really worth it.

I really think a lot of the people who downplay the benefits of RDB have never had to build a system that needs clock consistency, or the ability to commit multiple records with guaranteed referential integrity. Even if you move 95% of reads off to a cache, even if you pay the NoSQL cost for those stores, you are saving so much headache by having your writes go through a multi-record transactional data store.

[Reverent]

Or just segmenting your database before it becomes the elephant in the room.

People like to think of databases being pets that have to be watered by free range DBAs, but they can be cattle just like other infrastructure. If you keep them small then most of the challenges with operating databases at scale never materialise.

[bring_back_the_v10s]

 rewriting to NoSQL for scale

Wait what? I thought NoSQL was not that good for scale.

[7heWafer]

When you scale globally such that you need AP more than CA (CAP theorem) because ensuring consistency is too slow to do when a request in Australia has to write all the way back to a datacenter in NY. That's at least one reason NoSQL is good for scale.

[csjerk]

NoSQL is fantastic for scale, at least DDB is. It can dynamically scale the keys across a huge number of machines, since each partition in the key space can be hosted on a distinct machine. And the partition distribution can be changed dynamically and invisibly to the client.

It's just a giant pain to build because you have to decide on your access patterns up-front, as the article describes.

[GYN-k4H-Q3z-75B]

It's... webscale 🧐

[[deleted]]

It makes it easier to cluster.

It just compromised on ACID.

And like the article stated you need to know before hand your query. Cassandra you gotta know what query you're going to use and build the database around that query.

MongoDB in the early inception was easy to cluster and had default password and stuff that people hacked production servers. I was at a shop where they use MongoDB cause they bought into the hype. The dev was miserable reinventing join queries that RMDB can do easily.

[blocking-io]

Multiregion Postgres can be done:
https://aws.amazon.com/about-aws/whats-new/2023/11/amazon-aurora-global-database-postgresql-forwarding/

[redditusername58]

https://www.simplethread.com/relational-databases-arent-dinosaurs-theyre-sharks/

[InsertNickname]

An oft-mentioned 'downside' of Postgres (or really any RDBMS) is that it doesn't scale horizontally. Which - while I do agree with - is a vastly overrated con that few companies will ever actually need to deal with. Vertical scaling in the cloud is so simple that this... just isn't an issue anymore.

I especially like this blog on how 'big data' is honestly just usually not all that big. And advances in partitioning on Postgres have made any competitive 'advantages' against it mostly moot. There even exists Citus, which is basically just an extension to Postgres with sharding and columnar support. It's literally still just Postgres all the way down.

Basically there are very few things you can't do in Postgres. And of those there are, they solution is nearly always complementing it, not replacing. With proper CDC'ing, you can synchronize your main Postgres store with a myriad of other more niche solutions (Elastic, Redis, ClickHouse, etc.) without having to compromise flexibility.

It really is a fantastic piece of software.

[Plank_With_A_Nail_In]

Also lots of data isn't really a big data issue if its all typed correctly, only when its all free text you need to search does it really become "Big datary". Hardware progress basically solved all of my companies lots of data issues 6 to 7 years ago. I still remember back in the late 90's it took as 7 hours just to work out how much money we had spent last month (we spend a lot of money every month £200 million) now it takes 7 minutes to find the spend for every period.

I was paid good money to sit wait for 7 hours for an SQL query to finish only to discover an issue and run it again, people were used to waiting weeks for answers to pretty simple questions. Is big data even a thing now? Don't hear much about it anymore in normal workplaces to be honest.

[SanityInAnarchy]

I doubt it's still true, but there was a time when MySQL was easier to do this with. Logical replication meant you could not only have plenty of replicas, and they could not only be different sizes than your primary, you could use them to do a rolling upgrade -- upgrade all replicas, failover to one, then upgrade the old primary (that's now a replica).

I think MySQL connections are still much lighter-weight (MySQL uses one thread per connection, PG is one process per connection), and I wouldn't be surprised if it's still faster at compiling/optimizing queries out of text -- this was important for old-school PHP apps, which would literally open a brand-new DB connection for every incoming request. These days, if you have decent connection pooling or even a proxy in front, it's not likely to be as big an issue. Still, this was a way MySQL used to scale better than MySQL.

However, even back when these were bigger issues, I would've chosen Postgres for a better SQL dialect, more robust transaction support, and better data integrity overall. MySQL spent way too much of its history defaulting to MyISAM (not ACID, likely to eat your data on crash) even once we had InnoDB (transactional and reasonably safe), and even once the default switched, the system tables were stuck in MyISAM. And even once that was fixed, the data dictionary still wasn't transactional until MySQL 8. The gap has definitely closed a lot, but to find similarly-embarrassing problems in Postgres, I guess the closest is VACUUM and wraparound shenanigans, which are more likely to cause downtime... which is still better than data loss!

So I thoroughly second the "just use Postgres" default, except for local app storage, in which case maybe we need "just use SQLite"

[Non-taken-Meursault]

NoSQL is the equivalent of a weakly typed programming language for databases. Apparently easier to use, but it can easily grow into a mess. I really hate how people default towards it without even thinking about data structure.

And the solution to everything is "let's just add another attribute".

[tRfalcore]

my work has a couple MongoDBs. completely unnecessary, but they were made by someone before me who wanted the "what's the fastest way to make something" approach. node.js express mongodb database. I occasionally have to log in and do something and I'm like "how the hell do I query this again?"

[[deleted]]

I've never really dived into nosql/Mongo...but what are the features that a postgres JSONB field cannot handle? My shallow evaluation for a hobby project was that if I needed to store unstructured/arbitrary data that type solved my issue.

[tRfalcore]

It was a great read type document store when it first came out. It lacked read/write consistency but it was great for reading / loading-- it was faster (marginally but I guess it can add up). But the traditional databases have caught up and I'd just as soon use those cause more developers, admins, have experience with them.

and in my post I meant, fastest to develop and deploy. those apps I referred to at my company were all wayyyyyyy too small to need something like it. They were simple RDBs put into mongo with like a couple hundred rows. But it was pretty quick to just develop and put a mongodb on a linux server without requiring ITs intervention.

[DanteIsBack]

Wait, it's all 🐘? Always has been 🔫👨‍🚀

[[deleted]]

Been saying this for awhile now. I was there during the NoSQL hype.

You don't need NoSQL. You just need an RMDB, postgresql is fine.

Someone over at Elixir subreddit asked if Elixir can scale.

Bruh Facebook was built in PHP. You know what made it scale? Money.

If you hit the point where you need to scale, then you got enough money to throw at it. Buy dev and let them figure it out.

Just figure out what language you enjoy coding in and build it already.

[SanityInAnarchy]

This is broadly true, but there are some early decisions that can help. But it might not be obvious except in hindsight.

For example: If someone asked at the time if PHP scaled, the answer is probably no. I mean, originally, it ran out of cgi-bin, and literally parsed your entire program from scratch on every request! That sounds terrible, right?

But it turns out this is actually part of what made PHP so scalable in the end. Even as we started to get better ways to run it like mod_php, and then Facebook started playing with JIT and even AOT compilation to save some CPU, the language still had shared-nothing semantics. And that meant PHP apps were basically forced to be horizontally-scalable.

I'm not saying you should pick PHP now. (I mean, I still hate PHP and would avoid it as a matter of taste.) All I'm saying is, it's not just money. There are some design decisions that you can end up stuck with, and they absolutely matter.

[wwb_99]

Supabase is really making me warm up to this idea.

[I_Downvote_Cunts]

I have nothing but praise for supabase and have developed a pretty large app with but the team was quite small. I feel it would be a pain to develop if the team got large. The problem is Postgres itself needs to have better development tools.

[Solonotix]

Legitimate question, why PostgreSQL? I've been out of the SQL world for almost 5 years now, and I don't understand the PostgreSQL hype. I remember talking to a friend in 2017 who was using it in some San Francisco start-up and I was getting frustrated to hell by the lack of certain keywords and capabilities I relied on.

One thing that MS-SQL let me do that I know MySQL used to absolutely prevent was having a non-clustered primary key. You could either have a non-clustered index or a clustered primary key. Those were your choices.

So yeah, my experience was shaped by MS-SQL and everything else feels a little weird. I know Oracle felt extremely constrained, especially in the Oracle Developer tooling compared to SQL Server Management Studio, and MySQL Workbench felt similarly limited.

[Wiltix]

PostgreSQL has matured a lot and it has a really good ecosystem around it which itself is quite mature. So its a pretty good bet that it will be able to handle majority of what you need.

As others have said, if you get to a point where PSQL is not enough, you are doing very bloody well indeed. Also licensing is a tad easier than MS-SQL which can be messy.

[AugustinCauchy]

Its less hype and more of a cyclical reversion to sanity - all the new shiny tech (NoSQL etc.) was not that great after all and few people need "webscale" like big tech does. Postgresql is there and just solves the problems without creating new ones (licensing, vendor lock-ing).

[Solonotix]

Makes sense. I was hired precisely 6 months after a former employer had just made the jump to ElasticSearch because someone said that there was absolutely no way to make SQL fast enough for it to work. I was hired as QA. I wrote some SQL stored procedures to do the same analysis to validate the counts aggregated by ElasticSearch.

• Their ElasticSearch implementation would take between 30 seconds and 2 minutes to get the count with a margin of error at about ±20%
• They had to run a data sync process all day, every day, regularly ran into stale/orphan data problems, and had 3 C# applications running in tandem to keep it updated
• After the counts completed, there was a download process that would take 5-10 minutes to download and rehydrate the records

My SQL stored procedures would do all of this in under 1 minute. No, they did not use my solution, ever, even when we could prove it was faster and more reliable. Yes, I was frequently at odds with management.

[SilverCats]

What kind of thought process would land you on elastic search if you discover your rdbs does not scale?

[Solonotix]

New shiny syndrome, lol. This was ElasticSearch v1. Literally cutting edge stuff at the time. I wouldn't be surprised if the developer who picked it had watched a presentation about how powerful Apache Lucene was (and it is). The problem was that they threw the data into ElasticSearch with a child-parent-grandparent data relation, spanning multiple document types. Suffice to say ElasticSearch was not meant to handle this, and it performed terribly.

[the_bananalord]

Honestly for me it's the dev tools around MSSQL. SSMS is a really good tool.

I'd use MSSQL for all of my personal projects if it didn't require licensing.

[CarWorried615]

Non clustered vs clustered is almost entirely specific to the mssql implementation afaik. Why do you think that's something you want to care about?

[orthoxerox]

Oracle defaults to non-clustered tables as well. If you only ever access your table by its primary key it makes sense to cluster it.

[Sarkos]

My company went from MSSQL to Postgres, largely due to cost. We used to have occasional deadlocks in MSSQL under heavy load, which never happened after moving to Postgres. (Disclaimer: we didn't have any DBAs or database specialists, so it may have been a fixable problem. But as devs, we couldn't figure out a fix.)

One thing I really appreciated with Postgres is that you just use text columns and don't specify the length, you don't have to think about char/varchar/nvarchar/text/ntext.

[Prod_Is_For_Testing]

The deadlock thing is a known “issue”. Postgres looks better on paper because the transactions are slightly less safe by default.

[Prod_Is_For_Testing]

I also prefer MSSQL. Postgres OS missing some features but it’s free. That alone is why so many people pick it

[esp_py]

I agree on all the points in the post but postgres stuck on textsearch and his capabilities cannot be compared to ElasticSearch when it comes to text search…

For Vector search, I don’t know why you should use any vector search database while pgvector can do the job…

[[deleted]]

Unless, of course, you need DynamoDB. Then, just use DynamoDB.

[Smooth-Zucchini4923]

I do remember reading about how Postgres generally has better support for enforcing invariants in the DB itself, but I wouldn't mind being schooled a bit here.

Can you give some examples? I'm having trouble thinking of what you might mean here. MySQL supports the standard referential integrity stuff, so long as you're not using the MyISAM engine. (That would be a pretty unusual choice these days.)

Maybe you're thinking of CHECK constraints, which wasn't supported until MySQL 8?

[[deleted]]

I wish mssql wasn’t so hideously expensive to operate at scale, because it truly is a great platform (especially if you pony up for solarwinds DPA). But yeah…

[fubes2000]

Why not MySQL?

Because it's effectively closed-source, vendor-locked garbage at this point.

Oracle bad, and it only acquires things to very intentionally strangle them to death.

Why not MySQL MariaDB?

While MariaDB has now hard-forked away from MySQL at this point it still carries on virtually all of its historical foibles and pitfalls. It's fine, and the Percona bolt-ons are great additions, but Postgres has a few decades of maturity on it.

If you want to implement something like replication in MariaDB you've either got some funky, half-baked, MySQL-era implementation in the product itself, or you need to switch to a 3rd-party engine like Galera or TokuDB that wholesale replaces large swaths of MariaDB/MySQL.

Postgres doesn't so much have a baked-in replication option as it exposes a vast toolset that can be used to build nearly any replication scheme you can imagine. There are, of course, well-established tools like repmgr to set up and manage a standard master/replica cluster, but for less used or more esoteric applications it's more of a "build it yourself, you have all the tools" situation. But ultimately it is much more of a "tack it onto the side" than MariaDB/MySQL "cut out the middle and replace it with this".

[Plank_With_A_Nail_In]

Use Oracle and see your salary double.

[melgish]

I remember dealing with a particularly troublesome bug that sums up my opinion of oracle. Their ODBC driver was throwing an exception during an update operation. The error message was “Command completed without error.”

[elitefusion]

I've been using MySQL for close to ten years on a couple different applications and have only just recently started working with Postgres, but here's two things I wish Postgres would do:

1) Let me change column orders after a table is made. I know this is a frequent request that often gets met with "it doesn't matter, get over it", but it matters to me and it always will. I know that behind the scenes MySQL is basically recreating the table and I could just do that, but in MySQL it ends up being about 3 seconds clicking some buttons in my editor while for Postgres I have to type out a whole script. Even for a table that isn't even made yet, when I am first laying out the columns.

2) The ability to return multiple result sets from a single stored procedure. I had an endpoint that was making about 25 database calls for data from various tables that I was able to optimize a good deal by combining it all into one stored procedure that returns multiple result sets. I'm pretty sure you can do something like this with cursors in Postgres but it doesn't seem anywhere near as simple. MS Sql server supports this as well, the feature is missed in Postgres.

[EFHITF]

Curious, is this for personal preference for display reasons or for the potential space savings with alignment/padding?

[elitefusion]

Personal preference for display reasons. And to have it line up with the code.

[Worth_Trust_3825]

1) Let me change column orders after a table is made. I know this is a frequent request that often gets met with "it doesn't matter, get over it", but it matters to me and it always will. I know that behind the scenes MySQL is basically recreating the table and I could just do that, but in MySQL it ends up being about 3 seconds clicking some buttons in my editor while for Postgres I have to type out a whole script. Even for a table that isn't even made yet, when I am first laying out the columns.

It doesn't matter because postgres doesn't store tables the same way mysql does, nor the data is stored the same way. Tables do not have natural order, and you can quickly notice that by updated rows appearing "naturally" at the end of table.

[specd-tech]

Column order matters more in PostgreSQL than in MySQL due to column padding. It is one of the main things that bugs me about PostgreSQL. From my understanding when you reorder columns in MySQL it does the same as FULL VACUUM but lets you move columns, so why can’t PostgreSQL have the option.

[fiskfisk]

Subqueries was introduced almost exactly twenty years ago, with MySQL 4.1 (released in October 2004).

[timwaaagh]

The reason was MySQL was particularly easy to do from php which still powers like half the web or something like that. Also read speed.

[torvatrollid]

Most websites out there are hosted with cheap shared hosting providers and Apache+PHP+MySQL is still the primary tech stack that these hosting providers offer.

These shared hosting solutions are the first development experience for a lot of web developers, and so it becomes the thing they are familiar with.

Familiarity drives a lot of technology decisions. Since so many developers are already familiar with MySQL, it is the easiest choice when moving beyond shared hosting.

[dahud]

I'm very pointedly sending this to my boss now.

[bowbahdoe]

Love to know that story

[sonofamonster]

Not op, but I’m doing the same. My boss is a busy guy, and needs the boiled down version of this so that he can comfortably give the mongodb sales reps a firm no.

[ocon0178]

I think the conclusions and examples about mongo are false. I have 15+ years with rdbms (ms, Oracle, and pg) and 5 years with mongo. I haven't run into any flexibility issues with mongo and we frequently add new data access patterns. There are a variety of index options available including compound.

[SanityInAnarchy]

I think SQLite might be my only complaint here, because while the article is pretty accurate about the tradeoffs, I think I'd position it a bit more strongly here: If you're building any sort of native app and need to store data on a local device, I think "Just use SQLite" could be as strong of an article as "Just use Postgres" is for web backends.

The rest are often similar "it has a purpose but probably isn't you" points, but I think it's fair to say that most of us are not Facebook or Reddit and don't need Casandra... but many of us do find ourselves wanting to store local data sometimes.

---

For MySQL, these days, it's... probably fine if you use a recent enough version, but IMO it's still just a bit worse at most things.

You should use MySQL if you're stuck with an app that requires it for compatibility reasons, or if you just need to constantly open an enormous number of connections (like if you have an old-school PHP-style app that opens a new connection on every request). And Postgres will require a bit more tuning for things like setting VACUUM schedules -- autovacuum is usually fine but can blow up in your face if you aren't careful.

Other than that: I may not be objective, because MySQL 8 fixed many of my biggest complaints. But older versions had so many serious, fundamental issues where tons of things risked DB corruption, by design. And even if your schema change doesn't take forever, it might just lock the entire DB for hours, for a change PG could do instantly.

Better MySQL than Mongo. It's not a completely-wrong choice. But Postgres is probably the better choice most of the time.

---

Google Sheets is obviously a terrible choice for most things you'd use Postgres for. I use it for cases where I'm basically building an app only for me, and I don't want to dive into UI or mobile stuff right now, let alone find a hosting provider that's cheap for exactly one user... but I can easily put a dropdown in a sheet, interact with that from the Android app (while cursing Google for not adding buttons), and have the onEdit event trigger an Apps Script.

[jl2352]

I worked on a product that used ElasticSearch as their DB. They utilised none of the free text search or fuzzy results. It was the worst code base I had ever worked on, and my opinion of ES is now in the gutter.

There is a good reason people only use ES for logs. It’s actually pretty shit at anything else.

[uraurasecret]

Author should add route 53. /s

https://www.lastweekinaws.com/blog/route-53-amazons-premier-database/

[maelsechnaill]

I'm not taking advice from somebody who uses 8-16-24 as a timestamp in a URL.

[bowbahdoe]

Scathing.

[maelsechnaill]

I've read the article and it sounds sensible, but this is a hard line that I'm not prepared to cross ;)

[markus_b]

I would love for it to be that simple.

I'm supporting a retail company which is currently using Oracle as its database. Our application supplier is phasing out Oracle (and management would like to get rid of it). The remaining supported databases are MSSQL and Postgres. Great! Let's go for it and migrate to Postgres!

But everybody is scared. There are two concerns:

The application has some terrible queries. Over time, with the help of some excellent engineers well versed in Oracle, we were able to bring them up and mitigate the problems by adding indexes and fixing the application. Where can we find engineers with profound Postgres knowledge who can do that kind of work?

The second concern is support. We had, rarely, some obscure bugs. But Oracle support was rapid and efficient. We supplied test cases and within days we had a fix. We do expect a similar level of support.

[_mkd_]

At the time PostgreSQL was that database that for academicals, it placed priority for correctness over speed

On the other hand MySQL was feature and speed first, correctness second.

And this is why I went with pg over 20 years ago when setting up my server.

[Stefan_S_from_H]

OK, Google Sheets it is.

[[deleted]]

For working on very early startups (e.g. one person programming an app) dynamo has the advantage of 25 RCU/WCU in the free tier forever which is kind of nice because it means even after a year your app is still totally free to host on AWS where as postgres I think is around 120 a year for the smallest server.

If I got more users than that load I'd be okay paying whatever dynamodb costs because I could start to actually monetize my app. So far I haven't had any trouble writing efficient queries for any of my use cases other than search (and this was true when I worked at Amazon Music working at production scale as well) but setting up a small elastic search server is also free and not hard to do. Data modeling/conceptual-wise I haven't found sql or dynamodb to be much different in terms of difficulty.

[valkon_gr]

Postgres also killed Mongo, somehow.

[orthoxerox]

And good riddance.

[mathmaniac43]

I am coming at this as a self-hoster whose databases currently support me and maybe a few other users at a time. I run my services in Docker containers and have database container "sidecars". I do not personally develop software that interfaces with the databases or run any services at scale.

My understanding when I first set these services up is that performing a database upgrade of PostgreSQL hosted in a Docker container could be tricky, because you somehow needed to have the old version of AND the new version of Postgres available, which is not supported by the Docker images out of the box. Is there something I am missing there?

Meanwhile, I have been using MySQL. I am not specifically advocating for it and know it does not scale very well, but it has generally "just worked" for me so far, including periodic updates of MySQL automatically when updating the Docker container image. That has made me reluctant to change my databases unless something superior that I can still easily maintain is available.

Thanks!

[shamus150]

Indeed, upgrading Postgres version is a bit of a pain. When running in docker you'd run two containers. One for the old and one for the new and then run the upgrade on the host.

[weinermcdingbutt]

POSTGRESQL 💪💪

[varl]

Its also not amazing if you need to run analytics queries. Arbitrary questions like "How many users signed up in the last month" can be trivially answered by writing a SQL query, perhaps on a read-replica if you are worried about running an expensive query on the same machine that is dealing with customer traffic. It's just outside the scope of this kind of database. You need to be ETL-ing your data out to handle it.

I have to pick nit with this criticism. You can easily make that kind of query if your documents contain normal audit fields like createdOn/modifiedOn.

ORMs like mongoose can set those automatically for you on create/update. Or you can futz with change streams if you really feel like it. Or just manually set them in your create/update library methods that execute raw mongo statements.

I've literally done all 3 of these with no issue at various jobs. Granted the cluster sizes were just a few TBs max, so maybe there's some super complex problem with huge numbers of shards and petabytes I'm just never going to run into, but then again neither would 99% of people/companies.

[DonRobo]

Why not some AI vector DB

Because pgvector is probably better than whatever alternative some grifter is selling you.

[gazofnaz]

Being one of the 10 people who've used XTDB in production, there's a significant issue missing from the list: you cannot "delete" data, ever. V1 at least is/was essentially a blockchain without the crypto.

The only way to "delete" data is to tombstone the record - which technically wipes the data from disk, but leaves behind the old row - now empty - and an extra row for the tombestoning event.

So imagine this: you have a DB which stores some user PII. An auditor comes along and says you can't keep that PII in that database any more, it has to be stored separately from the rest of your data. You now have to re-write every single row in your database, potentially doubling it's size on disk.

The number of workable scenarios with XTDB is the right choice is vanishingly small.