PostgreSQL is the worlds’ best database • Hacker News Board

Re: PostgreSQL is the worlds’ best database

#321

Post by JoshuaDavid » Fri, Apr 03, 2020, 11:50 PM UTC

romwell wrote: post #309

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…

>And before starting to bash me, please do this. Make a small application that will show a map, put 100 million points of interest on that map, that are contained in the table we talk about, and now as you scroll the map, select the middle of view as your circle and select on a small radius only those points of interest inside that radius. No more then a thousand points of interest, lets say. When you do that within…

While SQL will not necessarily give you the optimal solution, a btree index on a geohash will still give you O(log(n)) lookups on spatial data, which will be good enough for most use cases, and pretty much every database engine in existence can create an index on a string, which is all you need for that solution.

Re: PostgreSQL is the worlds’ best database

#322

Post by skylanh » Sat, Apr 04, 2020, 12:17 AM UTC

AnonymousPlanet wrote: post #274

al_ak wrote: post #246

Earlier quoted context omitted.

This is very typical in an enterprise shop where you have end users connecting directly to the database for reporting purposes. Being able to take advantage of your existing directory structure and security groups is huge.

Why do end users have direct access to a database? What do they do with it? Copy and paste from their Excel sheets into a GUI database client? Why is there no layer of software in between?

Situations where you have enterprise setups have users measured in the 10s of thousands.

There will be existing tooling around ACL management (typically AD). User Access Management groups, geo-separated local account manager, multiple help-desks to allow for disaster plans (pandemics, fires, localized grid failures).

Access can be as simple as:

- my application will use a system-account, and we'll manage access

- my application will pass user credentials (based on AD), these are the groups (based on AD) that have read/write/delete access; regional team leaders will request access using existing UAM, regional managers will authorize requests, your existing tooling will process those requests as you are the designated authority over those groups

Changing this requires either exceptional levels of observable returns on effort, or extreme will-power and political power.

During evaluation of MS SQL / PostgreSQL / whatever, fitting into existing UAM will be a requirement.

Re: PostgreSQL is the worlds’ best database

#323

Post by nl » Sat, Apr 04, 2020, 12:47 AM UTC

ptrwis wrote: post #208

drdec wrote: post #162

I think that PostgreSQL itself is great. However, the developer client tools (pgAdmin) leave something to be desired. The old pgAdmin3 was fine, if lacking bells and whistles. The new pgAdmin4 however, was not very good last time I tried it [1]. It was implemented as a client/server web application and had frequent issues. Can anyone recommend a good client for PostgreSQL? [1] - I see that there have been some new re…

pgAdmin4 is not that bad, I'm using it every day

Have you tried anything else? It's really not great.

Re: PostgreSQL is the worlds’ best database

#324

Post by jatone » Sat, Apr 04, 2020, 12:53 AM UTC

jakearmitage wrote: post #192

smt88 wrote: post #153

Earlier quoted context omitted.

> I still use Redis as a cache on top of my PostgreSQL queries Why? Postgres has a "Redis cache" (in-memory query cache) built in already[1]. Your application layer doesn't have to worry about query caching at all. 1. https://www.postgresql.org/docs/current/runtime-config-resou...

https://www.peterbe.com/plog/redis-vs-postgres-blob-of-json

this is so egress of a misrepresentation its absurd.

redis in its default configuration is in memory. postgresql stores to disk.

I doubt they was running anything in parallel. (python)

if you tune postgres by using a RAM disk and actually use parallelism it blows redis out of the water hands down.

Re: PostgreSQL is the worlds’ best database

#325

Post by jatone » Sat, Apr 04, 2020, 1:03 AM UTC

jakearmitage wrote: post #149

I get all the comparison, but Redis? I still use Redis as a cache on top of my PostgreSQL queries. PostgreSQL simply can't be as fast as a K/V like Redis.

yes it can. in fact it can be faster due to the fact it isn't single threaded. learn about table spaces and RAM disks.

Re: PostgreSQL is the worlds’ best database

#326

Post by ncmncm » Sat, Apr 04, 2020, 1:35 AM UTC

patrec wrote: post #305

INTPenis wrote: post #74

Earlier quoted context omitted.

Well I was 18-19 so to me these were myths I heard re-told. But all I know is that they had been throwing hardware on a MySQL install to make it work better. He migrated them to postgres and they got much better performance and could get away with less hardware than they had with mysql. That was as much detail that I remember. Keep in mind I said by sheer luck I became a fanboy. Not by experience and competence. That…

This isn't hard to believe. MySQL basically started out as a key-value store pretending to be a relational database. If you wanted to do KV stuff and didn't care too much about data corruption (or knew the five zillion magic incantations to mostly avoid them) it was fast. But If you wanted joins or expected the query optimizer to do anything for you, you were SOL.

The dirty secret of database engine development is that all the things that are hardest to do in the database proper are super-easy to do in the application, given a few judicious architectural choices; but despite that, nobody can count on app writers to do them right. So, DB engine developers have to bust their humps to cover for users' incompetence.

A database for competent users can be amazingly simple and fast. But the Market Has Spoken, loud and clear: databases must absorb an indefinitely large amount of complexity to make the job of app writers a tiny bit easier.

Re: PostgreSQL is the worlds’ best database

#327

Post by luord » Sat, Apr 04, 2020, 2:09 AM UTC

Might be a bit... Forcibly put, but I gotta say that PostgreSQL is one of my favorite pieces of software ever created. And I don't even like C all that much.

Re: PostgreSQL is the worlds’ best database

#328

Post by wdb » Sat, Apr 04, 2020, 2:20 AM UTC

kingdomcome50 wrote: post #239

PostgreSQL is a great database, but pgAdmin (or any client I have tried) is just painful compared to SSMS. And while SSMS certainly has some pain points, I have yet to find a DB client tool that even comes close in terms of usability. For this reason alone I prefer MS SQL.

Never got SSMS working on my work laptop. Showed the splash screen and that was it. Weird

Re: PostgreSQL is the worlds’ best database

#329

Post by mfreed » Sat, Apr 04, 2020, 3:49 AM UTC

willvarfar wrote: post #18

Jonnax wrote: post #2

This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.

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…

Hi @willvarfar: Just to avoid any confusion, TimescaleDB's compression is available in our community edition, which is fully free to use. Once you specify some compression configs, it's automated and transparent.

https://www.timescale.com/products/features https://docs.timescale.com/latest/using-timescaledb/compress...

(The "community" edition is available under our Timescale License. It's all source available and free-to-use, the license just places some restrictions if you are a cloud vendor offering DBaaS.)

Re: PostgreSQL is the worlds’ best database

#330

Post by mfreed » Sat, Apr 04, 2020, 3:52 AM UTC

willvarfar wrote: post #141

rrickardt wrote: post #98

Earlier quoted context omitted.

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.

You've probably seen this, but reposting:

https://blog.timescale.com/blog/building-columnar-compressio...

Our own experience was that most users were getting 3-6x compression running TimescaleDB with zfs.

With our native compression -- which does columnar projections, where a type-specific compression algorithm is applied per column -- we see median compression rates across our users of 15x, while 25% of our users see more than 50x compression rates.