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

241–250 of 258 posts

Re: New in PostgreSQL 10

#241

Post by riku_iki »

dk8996 wrote: post #118
deepsun wrote: post #114

Earlier quoted context omitted.

Mmm, but it's the same for MySQL, no? Whenever we change a column in one of our tables (pretty big), the whole server hiccups for several seconds. We're using Google's Cloud SQL. At least PostgreSQL allows you to wrap schema changes in BEGIN/COMMIT/ROLLBACK transactions, unlike MySQL.

UPDATE client set enabled = true; So something like this will rewrite the whole table because of MVCC. MySQL will update the record in place without rewriting the whole table.

Because FS operates pages, and not individual bytes, MySQL will also, for each column, read whole page with row included, and write it back, thus rewriting whole table..

Re: New in PostgreSQL 10

#242

Post by riku_iki »

fortytw2 wrote: post #87
peatmoss wrote: post #26

Earlier quoted context omitted.

> you don’t need Cassandra or other BigData database. If you did, you wouldn’t ask the question probably. ...and by the time you do, Postgres improvements will have rendered your Big Data medium-sized anyway :-)

you can comfortably fit 8TB of data on a single box running postgres, more dependent on your hardware :)

Curious, how long it will take to create index on single node for a such large table?

Re: New in PostgreSQL 10

#243

Post by mmgutz »

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.

Maybe you meant the swiss knife of databases.

Re: New in PostgreSQL 10

#244

Post by pritambaral »

bratao wrote: post #80

What I miss the most from Postgree is the compression feature in the same level as RocksDB or Tokudb. The actual compression is very bad compared to them. Do anyone know if they have something planned in this department ?

Advanced compression of data is really something that should be looked at separately from database design. Look into using ZFS beneath Postgres for some pretty good compression.

Re: New in PostgreSQL 10

#245

Post by jacques_chester »

user5994461 wrote: post #223
jacques_chester wrote: post #198

Earlier quoted context omitted.

I was pointing out that 2TB/day is not a very high write volume for modern OSS RDBMSes, especially distributed ones. As for elasticsearch, I have found it to be temperamental. The "out of the box" experience was usually unhappy. For logging I've seen people move to other tools (splunk, stackdriver) more than once.

I have found elasticsearch to be one the easiest database I ever had to setup and maintain. Some tuning to do when you have TB on multiple boxes but nothing hardcore. 2 TB/day is fairly easy... as long as you have a bit of experience AND one of the database that can scale horizontally AND the company buys the amount of hardware that is required. StackDriver/Sumologic/logentries are cloud solutions, so obviously one d…

I think it's anecdote vs anecdote, but clearly you have more experience. In a fulltext indexing situation I'd probably look at elasticsearch again; I don't deliberately hamstring myself to try and be right on HN.

On the other hand, if I have structured-enough data, I'm going to prefer relational tools for it. Hammers and algebraic nails and so on.

Re: New in PostgreSQL 10

#246

Post by _jal »

manigandham wrote: post #226
_jal wrote: post #209

Earlier quoted context omitted.

"Assumptions and hyperbole"? That MSSQL isn't available as a service like RDS? That tooling and integration that's been building up for decades around PG (or Mysql/Maria for that matter) doesn't exist? These are just facts. I'm sure you're right that MS would be happy to sell support. What I don't see is a reason to care, unless I run in to a Windows app that can't use another DB that I suddenly need to run under Lin…

I'm not sure what you're arguing. MSSQL is available on RDS and to quote the parent comment: > ...SQL Server on Linux for your production instances, but you're well off the beaten path...first advice you'll hear seems likely to be "try it on Windows and see if it reproduces there"... You dont think this is hyperbole? Have you actually used SQL Server or Microsoft support before? They do not treat enterprise customers…

Oops, you're right; I thought it wasn't on RDS. My mistake, and a good reminder to double check these things.

Re: New in PostgreSQL 10

#247

Post by jeltz »

dhd415 wrote: post #212
jeltz wrote: post #187

Earlier quoted context omitted.

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 ma…

PostgreSQL can use that optimization since an index scan in PostgreSQL still returns all tuples from the heap in index order, not in heap order. During an index scan PostgreSQL traverses the B-tree in index order and for each matching tuple found in the index it fetches the data from the heap (which is a quite cheap O(1) operation).

Re: New in PostgreSQL 10

#248

Post by jeltz »

Ygg2 wrote: post #225
unixhero wrote: post #224

Earlier quoted context omitted.

I would think that not having to pay the Oracle/MS/Sybase/Teradata licenses are a good enough incentive... Speaking of economic incentives. That could be a thesis topic for my hereto unwritten PhD in Behavioral Economics: "Choosing to pay" What are the behavioral aspects of top leadership - Why do people still chose proprietary" A quantitative case study."

If I had to wager, it's support. Oracle/MS generally provide much better support that open source software.

Oracle is definitely not known for their good support, and there are plenty of companies which provide really good PostgreSQL support.

Re: New in PostgreSQL 10

#249

Post by tikhon »

alceta wrote: post #19

If anyone even remotely involved with the maintenance and development of pg reads this thread - Thank you! - for all your efforts in building and improving a first class product that keeps me amazed at the strides it takes with each major. release.

And they didn't even ICO

Re: New in PostgreSQL 10

#250

Post by ioltas »

fdr wrote: post #131
merb wrote: post #29

Earlier quoted context omitted.

are they? even without ssl? by default?

SSL is (was?) required. I left Heroku about a year ago and it's nearly inconceivable that this would be changed, having been the case for many years. I don't think they've implemented certificate validation since I've left though. My naive hope, going on many years, is that SCRAM with channel binding would have landed years ago (the first versions of the patch began to show up then), making client-side certificate ch…

A patch implementing channel binding has been presented for integration into Postgres 11: https://commitfest.postgresql.org/14/1153/. Two channel types are presented: tls-finish and endpoint. Per the RFC 5802, it is mandatory to use SSL if you want channel binding as the data needed for binding validation is either the TLS finish message which can be found after the SSL handshake between the server and the client (which happens before the password-based authentication), and a hash of the server certificate. All those things are actually supported by a set of APIs in OpenSSL.
Post reply on HN