Good Morning

This is literally me at every possible discussion regarding any other RDBMS.
My coworkers joked that I got paid for promoting Postgres.
Then we switched from Percona to Patroni and everyone agreed that... fuck yes, PostgreSQL is the best.
- After having suffered with T SQL at MSFT for a number of years... yep, PostGres is almost always the best for almost any enterprise setup, despite what most other corpos seem to think.
  Usually their reasons for not using it boil down to:
  We would rather pay exorbitant licescing fees of some kind, forever, than rework a few APIs.
  Those few APIs already having a fully compatible rewrite, done by me, working in test, prior to that meeting.
  Gotta love corpo logic.
  
  Yes, had those issues as well, though lately not a big corp, but mid-sized company.
  One manager just wanted MySQL. We had trouble getting required performance from MySQL, when Postgres had good numbers. I had the app fully ready, just to be told no, you make it work in MySQL. So we dropped some 'useless stuff' like deferring flushing to disk and such.
- I used to agree, but recently tried out Clickhouse for high ingestion rate time series data in the financial sector and I’m super impressed by it. Postgres was struggling and we migrated.
  This isn’t to say that it’s better overall by any means, but simply that I did actually find a better tool at a certain limit.
  
  I've been using ClickHouse too and it's significantly faster than Postgres for certain analytical workloads. I benchmarked it and while Postgres took 47 seconds, ClickHouse finished within 700ms when performing a query on the OpenFoodFacts dataset (~9GB). Interestingly enough TimescaleDB (Postgres extension) took 6 seconds.
  Insertion Query speed
  
  Clickhouse 23.65 MB/s ≈650ms
  TimescaleDB 12.79 MB/s ≈6s
  Postgres - ≈47s
  SQLite 45.77 MB/s¹ ≈22s
  DuckDB 8.27 MB/s¹ crashed
  ^All ^actions ^were ^performed ^through ^Datagrip
  ¹ ^Insertion ^speed ^is ^influenced ^by ^reduced ^networking ^overhead ^due ^to ^the ^databases ^being ^in-process.
  Updates and deletes don't work as well and not being able to perform an upsert can be quite annoying. However, I found the ReplacingMergeTree and AggregatingMergeTree table engines to be good replacements so far.
  Also there's !clickhouse@programming.dev
  
  If you can, share your experience!
  I also do finance, so if there is anything more to explore, I'm here to listen and learn.
- I have a colleague like that too, and then the other camp that loves MySQL.
  Why do you like postgres
  
  I made several lengthy presentations about many features, mainly those that are/were missing in MySQL.
  In short, MySQL (has been) shit since its inception, with insane defaults and lacking SQL support.
  After Oracle bought it, it got better, but it's catching up with stuff that Postgres has had for 20+ years (in some cases).
  Also, fuck Oracle, it's a shit company.
  Edit: if I had to pick the best features I can't live without, it would be 'returning', copy mode and arrays
  
  I usually tell people running MySQL that they would probably be better off using a NoSQL key-value store, SQLite, or PostgreSQL, in that order. Most people using MySQL don't actually need an RDBMS. MySQL occupies this weird niche of being optimised for mostly reads, not a lot of concurrency and cosplaying as a proper database while being incompatible with SQL standards.
- Sure, once you make the move it’s great. It’s just that it takes time and resources to actually make the move
  
  I mean, with mysql_fwd, I migrated the data quickly, and apart from manual 'on duplicate update' queries (or rare force index) it works the same.

	Insertion	Query speed
Clickhouse	23.65 MB/s	≈650ms
TimescaleDB	12.79 MB/s	≈6s
Postgres	-	≈47s
SQLite	45.77 MB/s¹	≈22s
DuckDB	8.27 MB/s¹	crashed

What's that? Did you say you needed an RDBMS that can also handle JSON data? Well have I got good news for you!
- Mysql / Mari can handle it too! Just use BLOB 🤣
  
  pg can actually query into json fields!

Things happen magically with docker. Container needs PostgreSQL? Expose the port, define a volume, username and password, connect service to that port, forget PostgreSQL’s existence until data corruption.
- Not data corruption, but I replaced by mistake my .env file for authentik, containing the password for the postgresql database...
  Cue a couple existential crisis for not having set up backups, thinking about nuking the whole installation, learning about postgresql, and finally managing to manually set another password.
  Yeah, I feel several years older now...
- forget PostgreSQL’s existence until data corruption.
  Oh, so about 2 hours then LMAO
  
  ...ok, I'm morbidly curious. How did you manage to do that?

As a (data) scientist I am not super familiar with most databases, but duckdb is great for what I need it for.

first thing i'd ask it is how to pronounce SQL
- Yup, and it's S-Q-L not sequel (🤢)
  
  squeal gang rise up!
  
  ?
  I've been working on and with sql dbs since... 2011?
  Earlier than that if you don't count professional work.
  Always pronounced it Sequel, as has everyone I have worked with, at least of those who actually have some kind of software dev related role.
  Its got two syllables.
  Quicker and easier to say than three syllables.
- Sequel with external collaborators.
  Squeal with the homies.
- 15 years ago I called it S-Q-L and then I was told that it's wrong and it's "Sequel", and they kept calling it Sequel in college so for the past 10 years I've called it Sequel, My-Sequel, Sequel-lite, Postgres, transact-sequel, etc. Now y'all are telling me it's not Sequel
- That's easy, but PostgreSQL is pronounced Postgres-Q-L.

No need. It's the best DB... Until you need something portable
- My laptops runs postgres, but it is still pretty portable
- What do you mean by "portable"?
  
  Just if you need to be able to take it with you.
- I'm running it on a raspberry pi, how much more portable could you need?