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

131–140 of 258 posts

Re: New in PostgreSQL 10

#131

Post by fdr »

merb wrote: post #29
dalyons wrote: post #16

Earlier quoted context omitted.

Lots of cloud managed dbs are/can be publicly accessible. Eg all heroku hosted Postgres instances

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 checking (and let's get real: it's hard enough to use that many people will not validate when developing from their laptops, simply backspacing out the optional cert validation connection option, a elision that is invisible to the server) obsolete. It should be possible to modify the definitions of pg_hba.conf to require a channel-bound SCRAM connection, which would mean that the client is certain to have checked for an untampered certificate.

This implementation of SCRAM doesn't have that yet, but it's been an ambition of the author for some time to do so.

Re: New in PostgreSQL 10

#132

Post by bonesmoses »

gshulegaard wrote: post #9

Just...wow. * Native partitioning * Parallel query Honestly, some very welcome quality of life improvements for use cases even outside of what I would consider "Big Data". > This means that users no longer need to create triggers for routing data; it's all handled by the system. Trigger routing has always been a performance foot gun...to the point that it's sometimes better to handle table routing in application logi…

So far as the speed is concerned, we ran some tests a while back and it came back as nearly native performance when compared to interacting with a non-partitioned table:

https://blog.2ndquadrant.com/pg-phriday-dearly-de-parted/

Re: New in PostgreSQL 10

#133

Post by wjossey »

manigandham wrote: post #93
wjossey wrote: post #76

Earlier quoted context omitted.

Citus seems really cool (this is not a critique of the product), but probably not something I’d use. They seem to be very close in feature set to bigquery, and if I have to choose between the two bigquery wins for me. There are definitely situations where citus is a better solution, but I think the scenarios where I could need a scale out analytics platform, I’d choose BQ. Still, very supportive of what they are doin…

MemSQL would be the on-premise alternative to BigQuery, RedShift, Snowflake and the others. Citus is more focused on scalable OLTP rather than OLAP scenarios, giving you essentially an automatically sharding postgres database. Heap does use it for analytics but that's more so because of the JSON support and indexing in postgres itself.

I’m not sure I agree with this statement. BigQuery is not in memory, nor is redshift (not super familiar with snowflake). Whereas memsql is and would be prohibitively expensive to operate on datasets that are analytics based, because it is in memory based. It’s my understanding that citus is best suited to analytics workloads and parallel queries, and really isn’t comparable to memsql as it is attempting to provide scale out support with disk, which memsql doesn’t natively prefer.

Can you share some docs with me around how citus is more OLTP based? Looking to be educated.

Re: New in PostgreSQL 10

#134

Post by bonesmoses »

pbnjay wrote: post #39

> Cross-column Statistics Holy crap I didn't even know this was feasible much less in development! Can't wait to test this out, as significant amount of my data sets have these kinds of relationships. Parallel query will also be great for certain queries I do regularly. Been looking forward to this! Thank you Thank you to all the devs that had a part of this!

Cross-column statistics are one of my favorites. The way Postgres 10 handles it is really a _huge_ advancement that addresses a painful edge case that has been around for ages.

https://blog.2ndquadrant.com/pg-phriday-crazy-correlated-col...

Re: New in PostgreSQL 10

#135

Post by ianamartin »

Postgres isn't just my favorite database. Postgres is an example for all people about how a project should be run. There are lots of stakeholders who want different things. The dev team looks at what's being asked for and what is reasonable within a certain timeline. And then the team delivers features and supports them. And then on top of that there is best-in-class documentation.

Thank you to all of the team members who put your time and effort into this project. It's not only a wonderful tool that I use every day, it's also a model of how to manage a project.

Re: New in PostgreSQL 10

#136

Post by lambda »

manigandham wrote: post #88
jackmott wrote: post #64

Earlier quoted context omitted.

agreed. after years of having used Oracle and SQL Server at work, to find I could have a working database in about 10 seconds with sudo apt-get install postgresql was amazing to me!

SQL Server on Linux in a container is just as easy now: docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=password' -p 1433:1433 -d microsoft/mssql-server-linux EDIT: Or with apt-get too: sudo apt-get install -y mssql-server https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...

Those aren't all of the instructions for apt-get. The full instructions are on https://docs.microsoft.com/en-us/sql/linux/quickstart-instal... and appear to consist of:

  curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
  sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"
  sudo apt-get update
  sudo apt-get install -y mssql-server
  sudo /opt/mssql/bin/mssql-conf setup
  sudo apt-get install -y mssql-tools unixodbc-dev
  echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
  echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
  source ~/.bashrc
And at that point, you have a limited period trial license of a release candidate version installed, and so once you want to use it for production, you have to start dealing with license management issues, which could limit your ability to easily set up dev envs, test envs, hot spares, etc.

All of that is on top of the issues of how heavyweight it is due to containing a platform abstraction layer that consists of a very large amount of Windows running in user mode, as discussed by others in this thread.

Re: New in PostgreSQL 10

#137

Post by ww520 »

dantiberian wrote: post #59

What is the current best option for Postgres failover? I looked at this at the start of the year, and found lots of options, but all of them seemed to have various drawbacks, and none were natively supported or built-in to Postgres.

For local cluster, try DRBD to replicate at the disk level. The DRBD replication can be synchronous and are very fast.

Add Linux-HA for health monitoring and automatic failover. When failed over, the standby machine just starts up PostgreSQL and recovers from the transaction log. Uncommitted transactions on the primary machine that haven't been written to disk and replicated will be lost, which is consistent with the transaction semantic. Committed transactions are replicated and recovered from the transaction log at the standby.

Depending on the heartbeat interval and how fast PostgreSQL starts up and recovers from the log, the automatic failover can happen within seconds.

Configure a virtual IP for the cluster and let the standby machine to take over the IP at failover. Linux-HA does all that for you. The clients talk to the virtual IP and don't have to know the primary or standby machine. They just need to reconnect when disconnected at the failover.

The nice thing about DRBD and Linux-HA is that it's not just for PostgreSQL. Any disk based application can be benefited.

Re: New in PostgreSQL 10

#138

Post by eloff »

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.

MyISAM I assume? InnoDB, which I hope you're using, uses MVCC too, just like pretty much all mainstream SQL databases of the last 30 years. Updates occur in place, with the older version of the columns relocated to UNDO space. Not sure what you're gaining over PostgreSQL there. Also, if the column is indexed, the index will contain all versions for each row. This is what allows index scans.

Re: New in PostgreSQL 10

#139

Post by [deleted] »

eloff wrote: post #138
dk8996 wrote: post #118

Earlier quoted context omitted.

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.

MyISAM I assume? InnoDB, which I hope you're using, uses MVCC too, just like pretty much all mainstream SQL databases of the last 30 years. Updates occur in place, with the older version of the columns relocated to UNDO space. Not sure what you're gaining over PostgreSQL there. Also, if the column is indexed, the index will contain all versions for each row. This is what allows index scans.

[deleted]

Re: New in PostgreSQL 10

#140

Post by ianamartin »

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?

Dynamic SQL in Postgres is really frustrating. I understand why. It's because you shouldn't fucking write SQL that writes SQL.

On the other hand, sometimes you just have to. SQL Server . . . I won't say it makes it easy, but it doesn't make it so goddam impossible.

If I lived in a world without Postgres, I would use SQL Server without thinking twice. MySQL/Maria I'm not fond of, and I kind of don't want to touch them. Oracle has some cute features and language extensions, but you have to deal with Oracle.

I can't think of a use case where Postgres falls down compared to other RDBMSs.

What we are seeing is Postgres competing with NoSQL and Mongo-type stuff. With full-text search support for JSONB types, we're looking at Postgres moving into ElasticSearch territory with a vastly more simple deployment model and still carrying ACID guarantees.

It's pretty remarkable.

Post reply on HN