818
Good Morning (lemmy.ml)
submitted 1 year ago by to c/programmer_humor@programming.dev
56 comments fedilink hide all child comments
 
you are viewing a single comment's thread
view the rest of the comments
[–] 44 points 1 year ago (18 children)

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.

  • permalink
  • fedilink
  • source
  • hideshow 18 child comments
    •
  • [–] 20 points 1 year ago (1 child)

    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.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 1 child comment
    •
  • [–] 7 points 1 year ago

    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.

  • permalink
  • fedilink
  • source
  • parent
  • [–] 11 points 1 year ago (5 children)

    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.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 5 child comments
    •
  • [–] 13 points 1 year ago* (last edited 1 year ago) (1 child)

    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^1^ ≈22s
    DuckDB 8.27 MB/s^1^ crashed

    ^All^ ^actions^ ^were^ ^performed^ ^through^ ^Datagrip^

    ^1^ ^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

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 1 child comment
    •
  • [–] 1 point 1 year ago (2 children)

    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.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 2 child comments
    •
  • [–] 8 points 1 year ago

    Clickhouse has a unique performance gain when you have a system that isn’t operational data that is normalized and updated often. But rather tables of timeseries data being ingested for write only.

    An example, stock prices or order books in real-time. Tens of thousands per second. Clickhouse can write, merge, aggregate records really nicely.

    Then selects against ordered data with aggregates are lightning fast. It has lots of nuances to learn and has really powerful capability, but only for this type of use case.

    It doesn’t have atomic transactions. Updates and deletes are very poor performing.

  • permalink
  • fedilink
  • source
  • parent
  • [–] 5 points 1 year ago (7 children)

    I have a colleague like that too, and then the other camp that loves MySQL.

    Why do you like postgres

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 7 child comments
    •
  • [–] 21 points 1 year ago* (last edited 1 year ago) (4 children)

    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

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 4 child comments
    •
  • [–] 7 points 1 year ago* (last edited 1 year ago) (1 child)

    Oracle:

    Only the best in B2B marketing for our shit software.

    EDIT:

    hah ok, round two, more directly playing on the actual company name:

    Oracle:

    We tell you what you think you want to hear.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 1 child comment
    •
  • [–] 1 point 1 year ago

    I have to admit though, I've never admined the Oracle DB, but I did talk a lot with people who did.

    I remember over 10 years ago discussing transactional DDLs as I heard Oracle does it, too, just to listen to 5 minute lecture about how it's nowhere near as simple.

  • permalink
  • fedilink
  • source
  • parent
  • [–] 5 points 1 year ago (1 child)

    As a complete newb to Postgres, I LOVE arrays.

    Postgres feels like all of the benefits of a database and a document store.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 1 child comment
    •
  • [–] 6 points 1 year ago

    Yeah, that was the goal.

    First make it feature-complete document-oriented database, then make if peroformant.

    And you can feel the benefits in every step of the way. Things just work, features actually complement each other... and there's always a way to make any crazy idea stick.

  • permalink
  • fedilink
  • source
  • parent
  • [–] 6 points 1 year ago (1 child)

    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.

  • permalink
  • fedilink
  • source
  • parent
  • hideshow 1 child comment
    •