Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL is the worlds’ best database

2ndquadrant.com

Post reply on HN
Print view View original

141–150 of 365 posts

Re: PostgreSQL is the worlds’ best database

#141

Post by willvarfar »

rrickardt wrote: post #98
willvarfar wrote: post #18

Earlier quoted context omitted.

The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new…

Postgres with timescaledb extension (community) along with zfs compression works flawless for me. Different approach than Myrocks, but worth a try!

Have you tried using the timescaledb compression instead of zfs? And how does this compare to uncompressed and to native Postgres tables?

I think that if timescaledb can support time-based or mru-based compression and even row-store partitions then it would take things to the next level.

Re: PostgreSQL is the worlds’ best database

#142

Post by hylaride »

INTPenis wrote: post #29

My first job in IT, I was 18, it was the early 2000s. I was a nerd but in professional IT I was essentially a blank slate. I lucked up and ended up with a real hacker for a boss. As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres. He was a BSD guy and a postgres guy. He made me into a fanboy of both those technologies. So out of sheer luck I've preferred Postgres for over 15…

> Unlike BSD, which often disappointed me once I learned how much easier and mature Linux was to use.

With the exception of package management (at least with debian) I've found the opposite to be true. It's configuration has always been a bit of a mess, especially with regards to networking. Lately systemd has made things only more confusing. And that's before dealing with the myriad of ways different distributions do things (which thankfully now is really only 2 different bases, debian or red hat).

While linux "won" in the end and I use it professionally now, I'm glad I rarely have to interact with it directly anymore thanks to ansible or other orchestration tools.

Re: PostgreSQL is the worlds’ best database

#143

Post by BiteCode_dev »

sscarduzio wrote: post #102
Joeri wrote: post #84

Earlier quoted context omitted.

I agree, but I also see the benefits from the other side, so I’ll play devil’s advocate: - it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway. - it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice. - the access…

The tooling is just fine for me, DataGrip or not: I wrote a ton of PostgreSQL triggers and utility functions in Javascript thanks to the excellent plv8 (free) extension. Development and unit tests for these functions is as easy as plain Javascript.

> Development and unit tests for these functions is as easy as plain Javascript.

Since most popular product in the JS world are product to avoid writing Javascript (webpack+babel, typescript, coffeescript, jsx, etc), and plv8 supports non of that nor does it support standard unit tests framework like Jest, I'm not convinced.

To make things even worst, remote debugging is not supported anymore: https://github.com/plv8/plv8/issues/131#issuecomment-2377111...

Re: PostgreSQL is the worlds’ best database

#144

Post by oftenwrong »

As mentioned, PostgreSQL's documentation is some of the best in the software world. With most software project documentation, one ends up scouring StackOverflow, mailing list archives, and GitHub issues trying to figure out how to do something. With Postgres, I can just go straight to the official documentation, and read it. It has yet to fail me.

Re: PostgreSQL is the worlds’ best database

#145

Post by holtalanm »

jka wrote: post #106
unnouinceput wrote: post #72

For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about. Performance. Nothing else mattered, not license price, not whistles and bells, not hype. My clients wanted to have data in front of their eyes the same second when they clicked the button. And when you have a table with 100 million rows in it, and an application is not…

While I do love PostgreSQL (and PostGIS which is excellent at transforming, indexing and querying geographic content) - I feel like MSSQL should get a bit of a shout-out in relation to your comment on performance. An experienced database developer with help from "SET STATISTICS IO ON"[1] and query plans[2] can achieve incredible MSSQL query optimization results. PostgreSQL has good query plan output via the EXPLAIN[2…

as others have said, EXPLAIN (ANALYZE, BUFFERS, VERBOSE) will give you IO stats within your query at a very granular level.

Re: PostgreSQL is the worlds’ best database

#146

Post by yjftsjthsd-h »

ggm wrote: post #51

PostgreSQL and ZFS is a marriage made in heaven. Block aligns to disk behaviour, snapshots make great low latency db dumps trivial. Never regretted making the combo

And compression! I've gotten compression ratios above 3x on real production databases, which is the difference between "we need more disks by next month" and "this hardware will last the foreseeable future". Not to mention that it improves performance when lz4 can decompress faster than the disks can read.

Re: PostgreSQL is the worlds’ best database

#147

Post by phoboslab »

unnouinceput wrote: post #72

For most of the projects where the DB really mattered, throughout my 10+ freelancer carrier, it came down to one thing that client really cared about. Performance. Nothing else mattered, not license price, not whistles and bells, not hype. My clients wanted to have data in front of their eyes the same second when they clicked the button. And when you have a table with 100 million rows in it, and an application is not…

I'm curious to hear why and how Maria failed you.

For a reddit-like social site, we have a Maria database with around 3 billion rows in total, with the largest table (votes) spanning about 1,5 billion rows.

We can select data within ~3ms for about 20k-40k concurrent visitors on the site. Our setup is a simple Master-Slave config, with the slave only as backup (not used for selects in production). Our 2 DB servers have 128gb of RAM and cost around 200 EUR/month each - so, nothing special.

Of course you have to design your schema with great care to get to this point, but I imagine PG to be as lost as any other database if a crucial index is missing.

Re: PostgreSQL is the worlds’ best database

#148

Post by duckmysick »

lllr_finger wrote: post #127

Postgres use always reminds me of this presentation: http://boringtechnology.club/ I self-admittedly love esoteric databases and storage engines to a fault. I'll try to shoehorn things like RocksDB into whatever personal project I'm working on. At work however, the motto I spread to the teams I work on is "use Postgres until it hurts". And for many, many teams - Postgres will never hurt. I'm very happy for its contin…

> I self-admittedly love esoteric databases and storage engines to a fault.

Any interesting notes and observations after using those esoteric tools?

Post reply on HN