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

231–240 of 258 posts

Re: New in PostgreSQL 10

#231

Post by manigandham »

wjossey wrote: post #221
manigandham wrote: post #143

Earlier quoted context omitted.

MemSQL has in-memory rowstores and disk-based columnstores. Every columnstore table has a rowstore to aid in fast data ingest. Rowstores are meant for access of individual rows, lots of data updates, and smaller reference tables while columnstores are for the bulk of data requiring large scans and analysis. We have tables with 20 billion rows that take up BigQuery streaming ingest also uses in-memory buffering into B…

Thanks for the education on MemSQL. I wasn’t aware of the analytics use case (had never come up in discussions I was a part of for that tech), so it was news to me. This article had a nice overview. Somewhat outdated but mostly still up to date. https://lnkd.in/g4anCf5 I’ll say it seems precarious to me to mix and match row store in memory data with a columnar store. Is it never a performance issue to have these two…

It's just data in memory. Rowstores are read from memory and columnstores are read from disk. The architecture uses leaf nodes and aggregator nodes so aggregrator memory is used for assembling the final results of a query.

Version 6 is even faster, you can try out the beta: http://www1.memsql.com/beta-6.3-features.html

Re: New in PostgreSQL 10

#232

Post by anarazel »

pgaddict wrote: post #199
ComodoHacker wrote: post #184

Earlier quoted context omitted.

To be honest, skewed data problem can be addressed with partitioning in many cases. Cross-column statistics is a great feature nevertheless.

Yes, but partitioning also has a number of limitations that may be quite undesirable. Like inability to create UNIQUE constraints or primary keys, for example.

More importantly it doesn't help at all with cross-column correlations. It can help with skew in between various rows though.

Re: New in PostgreSQL 10

#233

Post by dsr_ »

wiz21c wrote: post #214
kochthesecond wrote: post #193

Earlier quoted context omitted.

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.

Oracle started before microcomputers (PCs) were anything but toys. In 1983, they were multiplatform: mainframes and minicomputers. a 1983 minicomputer might have 2 CPUs, each good for 2 MIPS, 8 MB of RAM and primary storage of as much as a gigabyte or two of disks.

In the early 2000s, a serious database machine -- say, a Sun E10000 - had up to 64 CPUs, running at 400-500MHz each, 64 GB of RAM, and a huge cabinet full of disks.

Now you can call up a SuperMicro VAR and order a 64-core AMD EPYC server with 2TB of RAM and 4 TB of NVMe drives, that talks to other machines over a 100Gb/s ethernet, for a price around the same as a used Tesla S85.

If your database is under 2TB, it all fits in RAM on commodity hardware, where commodity is defined as "anyone with a credit card can order it without even talking to sales".

Re: New in PostgreSQL 10

#234

Post by grzm »

bjt wrote: post #172
grzm wrote: post #151

Earlier quoted context omitted.

Indexes in PostgreSQL require lookups into the table to access the data values in the rows. Index organized tables have indexes which include the data values in the index itself, removing the need for the lookup in the table itself. Here's some more detail: https://news.ycombinator.com/item?id=10451095

Perhaps I'm missing something, but your description doesn't seem consistent with my understanding of the index-only scan feature that's been in PG since 9.2 https://wiki.postgresql.org/wiki/Index-only_scans

Yes, you're right about index-only scans, in the sense that the data in the indexed columns can be used in some cases. As I understand it, indexed organized tables goes further than that in that row data for non-indexed columns is also included.

Re: New in PostgreSQL 10

#235

Post by gigatexal »

patates wrote: post #183
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…

Do you plan to write more about the migration, like a blog post? That would be very interesting to read.

+1 to this.

Re: New in PostgreSQL 10

#236

Post by takeda »

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.

> "redis of databases".

That's like saying is a "Prius of 8 wheelers".

That's really weird comparison. Especially that PostgreSQL is more of a database than redis is.

Re: New in PostgreSQL 10

#237

Post by takeda »

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.

My previous company migrated to Postgres by using EnterpriseDB[1]. This allowed them to run existing Oracle data with PL/SQL as they were working on migrating it to the raw PostgreSQL. So where there is a will there's a way.

[1] https://www.enterprisedb.com

Re: New in PostgreSQL 10

#238

Post by hobolord »

Manozco wrote: post #117
hobolord wrote: post #75

Earlier quoted context omitted.

do you find that it's significantly slower than just using psql?

Yes, in many cases (until I stopped using it), it messed up my benchmarks. I was testing some queries, and often it was: - pgserver fast, returns 10000 rows, pgcli takes time to parse -> result: total time 10s - pgserver slow, returns 1 row, pgcli instant -> result: total time 10 sec. As we are working in efficient C/C++, our internal time to parse the query set is closer to psql than pgcli, so we tend to prefer the…

yeah this was my experience as well when I first tested pgcli a couple months back. Lots of neat features, but the differences in time to parse bugged me too much

Re: New in PostgreSQL 10

#239

Post by collyw »

Every time I see stuff about Postgress it sounds so much better than MySQL.

Every time I get a new job, the existing system is using MySQL.(Every time it causes problems of some sort of other - it's not a bad database, but has too many quirks like JavaScript that will end catching you out at some point).

Last time I got to build something new the time pressure was so great I had to use MySQL, because it was there, set up and working and I knew it. I wanted to use Postgres, but at the end of the day the extra time setting it up wouldn't really have been much benefit as it was a fairly standard Django app, and didn't need any of Postgres's extra features. (I quit that job shortly afterwards).

Re: New in PostgreSQL 10

#240

Post by collyw »

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?

MySQL has native bitcount operator (and more data types last time I checked). Using it ended up being the fastest way to implement one problem I had - I tried Python with Numpy, Java, but generating large combinations in MySQL (joining a table to itself multiple times) and using bitshift and bitcount operators to filter the rows ended up being far faster than generating the combinations either language that I knew or Postgres lacking the bitcount operator. Probably spending a fair bit more time in C or Rust would have been the real solution. But that would have involved way more time than my work were willing to let me spend on it.

Also initial setup is easier with MySQL. It is installed everywhere and setting it up uses involves less messing about. (I would imagine there are far more insecure MySQL install around as a result).

Post reply on HN