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

New in PostgreSQL 10

wiki.postgresql.org

Post reply on HN
Print view View original

211–220 of 258 posts

Re: New in PostgreSQL 10

#211

Post by jeltz »

brlewis wrote: post #202
Illniyar wrote: post #173

Earlier quoted context omitted.

I hope those are interview questions for dbas because it's way outside common knowledge for backend developers.

Not for DBAs, but I also don't expect candidates to get all the answers in that comment. It's a decent data structure question to ask why databases generally use binary trees for indexes when hash lookups are faster. Non-DBAs usually have to think a little bit.

Databases do not use binary trees for indexes and tables, they use B-trees. B-trees are a family of self balancing tree data structures which usually are not binary because in a database you want the tree to have few levels.

Re: New in PostgreSQL 10

#212

Post by dhd415 »

jeltz wrote: post #187
dhd415 wrote: post #153

Earlier quoted context omitted.

That kind of clustering is a point-in-time operation. Subsequent inserts and updates aren't stored in clustered order. When the ordering is guaranteed, the query planner can be more aggressive in optimizing certain kinds of queries. A real clustered index could also be used as an implicit covering index as grzm mentions.

PostgreSQL can do all of the same query optimizations. The main difference is that for primary key scans PostgreSQL will have an additional layer of indirection meaning more work needs to be done when scanning on primary key and potentially more disk seeks. On the flip side PostgreSQL's approach is good when you query secondary indexes since these can point directly to the heap rather than to a primary key, removing…

I don't see how pgsql could perform such optimizations as eliminating a sorting step when ordering results by column(s) in a clustered index if there is no guarantee that new or updated data will also be ordered by the clustered index. I agree that both models have their pros and cons. While I am a big fan of pgsql, I do also appreciate those databases that offer both heaps and clustered indexes that I can mix and match in my schema as best suits my workload.

Re: New in PostgreSQL 10

#213

Post by nat »

skrebbel wrote: post #165
IMTDb wrote: post #34

PgSQL more and more looks like the "redis of databases". Whatever the problem, you can almost always find a good reason to use it :) You get great performance for the simple "dumb" use case (relational data in DB term, key/value store in redis case), and lots of awesome additional feature on top of it for more complex situations. I love it.

> the "redis of databases" That's like calling Swift "the Ruby of programming languages".

and it says more about redis and ruby than postgres and swift :p

Re: New in PostgreSQL 10

#214

Post by wiz21c »

kochthesecond wrote: post #193
irrational wrote: post #154

Earlier quoted context omitted.

Truly. We've recently moved from Oracle (after using it for 15 years) to Postgresql. It's like a breath of fresh air. The documentation for Postgres is unbelievably superior to Oracle. So far its performance is equal to or better than Oracle. We had to go through and rewrite thousands of queries, but the sql syntax of Postgres was always simpler and more logical than the equivalent in Oracle (I think Oracle has too m…

I think most Oracle installs are from the days ram was limited and incredibly expensive. Right now anyone can afford a db server with 128gb ram, enough to solve most problems #YourDataFitsInRam

>> I think most Oracle installs are from the days ram was limited

Could you explain ?

Personally I use Oracle (and hopefully one day PG) because it gives me guarantees on data integrity and these guarantees help me to think about my programs more easily.

Re: New in PostgreSQL 10

#215

Post by wiz21c »

skrebbel wrote: post #165
IMTDb wrote: post #34

PgSQL more and more looks like the "redis of databases". Whatever the problem, you can almost always find a good reason to use it :) You get great performance for the simple "dumb" use case (relational data in DB term, key/value store in redis case), and lots of awesome additional feature on top of it for more complex situations. I love it.

> the "redis of databases" That's like calling Swift "the Ruby of programming languages".

or the vi of emacs ? Or was it the other way around ? :-)

Re: New in PostgreSQL 10

#216

Post by wiz21c »

nine_k wrote: post #158
rb808 wrote: post #51

Shouldn't that be PostgreSQL X? Seriously good work. I'm not sure why my firm still buys Oracle licenses.

Well, this Postgres release introduces automatic partitioning; in Oracle, it has been available for like 20 years. Also, some shops are heavily invested in PL/SQL code. The price of migration may far outweigh the license savings.

>> The price of migration may far outweigh the license savings.

definitely. The cost of work is big compared to licensing. Now, PG has a "cool factor" so I bet some people would migrate to it off hours just for fun :-)

Re: New in PostgreSQL 10

#217

Post by beefhash »

manigandham wrote: post #163
striking wrote: post #160

Earlier quoted context omitted.

Debian and derivatives have it in repos by default. Please don't make claims like those.

That's more to do with the OS than the DB, and many repo entries are outdated. MSSQL hasnt existed on Linux before, but either way: > I'm surprised at how many people seem to have taken issue with a simple comment.

Due to Microsoft SQL Server licensing, I would be highly surprised if any Linux distribution were to make it available by default in their repositories.

Re: New in PostgreSQL 10

#218

Post by wiz21c »

adventured wrote: post #113
hardwaresofton wrote: post #96

Does anyone have any use cases where PostgreSQL falls down/loses to other DB systems? I know sharding/replication has long been a sticking point, but what else is there? Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all?

> Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all? Oracle: because the enterprise is its own unique beast. Companies frequently stick to what they know and have invested into, so long as it still works. Enterprise customers buy/adopt software in a very different way than everyone else. MySQL: if you've got a history with the product and it works well for what you're doing, there is absolutely n…

>>> Companies frequently stick to what they know and have invested into, so long as it still works.

This. For example I'm still on Java 1.6 because the cost of migrating to 1.7 (java is not the problem, the libraries we use are) is big. I'm still on Solaris because moving to Linux (for example) would imply redeployment of thousands of programs which translates to ten of thousands of man/hours.

Big corps go at geological speed :-)

Re: New in PostgreSQL 10

#219

Post by marcosdumay »

tatersolid wrote: post #201
Illniyar wrote: post #173

Earlier quoted context omitted.

I hope those are interview questions for dbas because it's way outside common knowledge for backend developers.

Why are back-end developers not supposed to have a deep understanding of their tools? I hear this sort of statement all the time, it’s infuriating. I once even had a dev team lead claim that it was not a dev’s responsibility at all if the app generates bad SQL; the DBA is supposed to make the queries fast (this person no longer works for me, but I consistently hear similar claims from consultants and others in the de…

> Why are back-end developers not supposed to have a deep understanding of their tools?

You mean all their tools? Like how does modern processors handle branch predictions in the face of out of order operations, or what differences in server cooling will affect throughput or peak performance?

Do you have a deep understanding of all the tools you use? Because I honestly doubt so.

Re: New in PostgreSQL 10

#220

Post by riku_iki »

Boxxed wrote: post #150
dhd415 wrote: post #144

Earlier quoted context omitted.

It's disappointing that pgsql has no index-organized tables aka clustered indexes. They are quite advantageous for a variety of workloads.

You can cluster a table by an index: https://www.postgresql.org/docs/9.6/static/sql-cluster.html Or am I not understanding what you're asking for?

Also this operation imposes full read/write lock on table.
Post reply on HN