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

111–120 of 258 posts

Re: New in PostgreSQL 10

#111

Post by pdimitar »

cbowal wrote: post #81
pdimitar wrote: post #79

Earlier quoted context omitted.

Not sure about SSL, but in the past customers of mine have copy-pasted full Heroku PG URLs to me and I was able to get in via `psql` immediately. So yes they're public but their addresses are basically impossible to guess.

Known as "Security through Obscurity" [0] [0] https://en.wikipedia.org/wiki/Security_through_obscurity

Oh, I am not saying it's a good practice at all. I was just answering the question.

I still think it's a low-friction solution. But a secure one -- hardly.

Re: New in PostgreSQL 10

#112

Post by pdimitar »

deepsun wrote: post #110
pdimitar wrote: post #79

Earlier quoted context omitted.

Not sure about SSL, but in the past customers of mine have copy-pasted full Heroku PG URLs to me and I was able to get in via `psql` immediately. So yes they're public but their addresses are basically impossible to guess.

Sometimes people do a conference talk or just share the screen, and it's easy to take picture of that URL.

True. I am not saying it's the best idea around, only that it's low friction. I'd probably approach it differently but I can see why they did it like they did.

Re: New in PostgreSQL 10

#113

Post by adventured »

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 no necessity-based reason to switch to PostgreSQL. You should switch from MySQL to PostgreSQL if there's a very good reason/s to, a meaningful gain to be had, it's that simple. Otherwise, it's unnecessary optimization. There are a vast number of considerations when operating a business, upgrading to the latest & greatest (speaking very broadly) just to do it, is not a good reason most of the time. There should be a specific prompt to action based on a gain/s or capability that PostgreSQL provides you, in/for your use case, over eg MySQL.

Re: New in PostgreSQL 10

#114

Post by deepsun »

dk8996 wrote: post #99
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?

Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite, thus causing a super high IO load.

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.

Re: New in PostgreSQL 10

#116

Post by jeltz »

xfalcox wrote: post #109

What happens if I try to insert into a master partition table, and there are no child tables that can store my data? For example, data is partitioned by month, and I have no table for the month in the data I'm trying to insert.

It will throw an error. Partitioning support in PostgreSQL 10 only solves a few simple use cases well, but the next version should get much more generally useful partitioning. This is how PostgreSQL usually releases major features.

Re: New in PostgreSQL 10

#117

Post by Manozco »

hobolord wrote: post #75
prima-facie wrote: post #65

For anyone using postgres on a daily basis I would highly recommend pgcli. It has a very good auto-complete, and can even suggest JOIN statements (parts of it actually).

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 pgserver fast, but the benchmark using pgcli did not clearly show the best time.

Also, when you are testing a query on one machine, you have to carefully monitor the CPU usage to differentiate the CPU pegging by postgres from the one by pgcli.

All those issues were gone when I got back to standard psql.

Re: New in PostgreSQL 10

#118

Post by dk8996 »

deepsun wrote: post #114
dk8996 wrote: post #99

Earlier quoted context omitted.

Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite, thus causing a super high IO load.

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.

Re: New in PostgreSQL 10

#120

Post by mst »

zackelan wrote: post #102
manigandham wrote: post #88

Earlier quoted context omitted.

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-...

That strikes me as being comparable only at the most superficial level. I started that SQL Server container and it's using 825mb of RAM (according to systemd-cgtop), just sitting there idle with no data in it. I started a Postgres 9.6 container also using their official Docker container and it's sitting idle at 58mb of RAM. Insult to injury, if you're using Docker in a VM (as you must on a Mac) the instructions on th…

That 58Mb RAM number isn't necessarily a good thing - something postgres has regularly fought against adoption-wise is that the defaults exist to be sane on a shared machine, not provide maximum throughput on a dedicated database server. While I appreciate this choice personally, I also suspect a good container default would idle higher.

(I'm a huge pg fan if that isn't clear - just ...)

Post reply on HN