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

301–310 of 365 posts

Re: PostgreSQL is the worlds’ best database

#301

Post by anarazel »

manigandham wrote: post #289
anarazel wrote: post #253

Earlier quoted context omitted.

> Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. Not that trivial if you're not just dealing with points.

So you have more complicated SQL then, but it doesn't make a difference between DBs selecting from a 100M table with indexes. If it ends up being a full table scan then PG only recently got query compilation and parallel execution so at best it would be even with, but not better than, MSSQL/Oracle that can do vectorized executions on columnstores.

You need different index structures. Yes, it matters.

Hard to believe that spatial queries over geometries more complex than points will meaningfully benefit from "vectorized executions on columnstores".

Re: PostgreSQL is the worlds’ best database

#302

Post by g4nt1 »

me551ah wrote: post #83
hnarn wrote: post #60

Earlier quoted context omitted.

> You add all kinds of select queries, joins and foreign keys and when traffic hits scramble to make it scale. NoSQL is hard to design but you can atleast be sure that once traffic hits, you don't have to redesign the schema to make it scale. Surely this depends on how you set up your SQL database to begin with? I'm not familiar with NoSQL, so can you explain why "schemas" aren't necessary and scaling happens automat…

You could design a SQL database to be denormalized from the start, but then you are losing many of the advantages of a SQL database. I never said that schemas aren't necessary, just that using a strict NoSQL database forces you to think about scaling constraints early. This avoids(atleast partially) a schema redesign later.

Ah yes! Optimize early, that's what my teachers always told me to do. /s

Re: PostgreSQL is the worlds’ best database

#303

Post by stickfigure »

dpc_pw wrote: post #257
stickfigure wrote: post #245

Postgres is my go-to RDBMS, but I do have one serious complaint: Connections are too expensive. This is a side-effect of the old-school one-process-per-connection architecture that Postgres uses. MySQL (ick) easily handles thousands of connections on small servers; with Postgres you will need a LOT of RAM to sustain the same, RAM that would be better served as cache. I've found (at least, for my current app) that the…

It might be possible write a small proxy in Rust, that would run next to a PG instance, accepted connections using `async` and then forwarded queries using some limited size connection pool.

These are active connections running transactions - unfortunately there's no substitute for just having another connection.

Re: PostgreSQL is the worlds’ best database

#304

Post by remus »

chatmasta wrote: post #120
remus wrote: post #113

Earlier quoted context omitted.

It also means you need a per-user database connection which isn't feasible in many applications as the recommended number of concurrent connections is typically quite low.

Not necessarily. You can SET ROLE at beginning of transaction and DISCARD afterward. This is what postgraphile and postgrest do, for example.

Oh cool, I didn't know that. Good to learn!

Re: PostgreSQL is the worlds’ best database

#305

Post by patrec »

INTPenis wrote: post #74
krainboltgreene wrote: post #67

Earlier quoted context omitted.

> As early as 2001-2002 he had saved entire businesses by migrating them from mysql to postgres. I'm extremely curious how this worked out.

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.

Re: PostgreSQL is the worlds’ best database

#308

Post by patrec »

bernawil wrote: post #220
onion2k wrote: post #155

Earlier quoted context omitted.

the code is not reusable accross different storage layers. So not portable. In my 23-and-a-bit years of web development I've literally never changed the database engine on a project. Maybe that happens on other people's projects, but it's not something I consider important or even useful really. The notion that you can swap out your database for a different one without changing the application code to take advantage…

I've literally never changed the database engine on a project And some of us do it several times a day because we deploy to prod with Postgres but run testing and CI with SQLite.

Well, you're doing it wrong. It's easy and fast to run tests against real postgres (takes a fraction of a second to spin up the DB for your tests if you do it right).

Re: PostgreSQL is the worlds’ best database

#309

Post by romwell »

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 a second, you got yourself a good database. For me PGSQL was the only one capable to do this reliable.

This is a bad example. An SQL database is a wrong tool for the job if you care about performance.

You want a data structure like a range tree[1] or a k-d tree[2]. The results would be near-instantaneous.

100 million lat/lng pairs is something you can fit in RAM without any problem. And you can even do the geometry query on the client side for performance in milliseconds.

Other examples might need SQL, but here, this would be doing things the wrong way.

[1]https://en.wikipedia.org/wiki/Range_tree

[2]https://en.wikipedia.org/wiki/K-d_tree

Re: PostgreSQL is the worlds’ best database

#310

Post by patrec »

aeyes wrote: post #242

I love Postgres as much as the next guy but there are three things that really annoy me: Postgres is still laid out for for 9 to 5 workloads, accumulating garbage during the day and running vacuum at night. Autovacuum just doesn't cut it in a 24/7 operation and it is by far what has caused the most problems in production. No query hints and no plan to ever implement it. Making the planner better only takes you so far…

Do you have a good Postgres DBA? Autovacuum can indeed ruin your day if you have a 24/7 DB that is delete or update heavy (the default setting are not very aggressive and once it falls behind badly it will never catch up and you'll need to do some emergency manual vacuuming). But I can assure you it's possible to run 24/7 DBs fine without running into autovacuming problems by making sure things are configured right. I've experienced both regular problems due to bad config and their complete disappearance.
Post reply on HN