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

141–150 of 258 posts

Re: New in PostgreSQL 10

#141

Post by linux2647 »

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…

Aside from those reasons, are there technical reasons for Oracle over Postgres? My coworkers and I want to move our data warehouse over to Postgres and my boss has been warming up to the idea. (I know switching databases with that much data can be complex and troublesome, but those concerns are outside the scope of this question)

Re: New in PostgreSQL 10

#142

Post by kawera »

mcdoh wrote: post #126

Can anyone recommend a good resource for learning PostresQL? I have a decent understanding of SQL, I've used MySQL in the past, but every time I see the latest features of Postres I'm left thinking I'll need to buy a book and take a pretty deep dive to figure out how to apply the new features.

You may like PostgreSQL Exercises: https://www.pgexercises.com/

Re: New in PostgreSQL 10

#143

Post by manigandham »

wjossey wrote: post #133
manigandham wrote: post #93

Earlier quoted context omitted.

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

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 BigTable until it's written to disk, just like Cassandra with memtables flushed into on-disk SSTables.

Citus is more manual work than MemSQL and doesnt support columnstores native, although you can use it with their cstore extension to get both. It's just a different design giving you a postgres database that automatically shards vs MemSQL's many optimizations around HA, replication, and very fast analytics performance.

Try the features page: http://www.memsql.com/features/

Re: New in PostgreSQL 10

#144

Post by dhd415 »

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?

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

Re: New in PostgreSQL 10

#145

Post by manigandham »

lambda wrote: post #136
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-...

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/mss…

Yes, you have to add the repo, the same as if you wanted the latest postgres bits on any distro. You also have to edit configs for both and you can also choose to install extra tools for both.

I'm surprised at how many people seem to have taken issue with a simple comment. Postgres is easy to install... and now so is MSSQL compared to the complex slow GUI installer they had before. Maybe it'll help the original commenter, maybe not. Let's just leave it at that.

Re: New in PostgreSQL 10

#146

Post by rc_bhg »

ianamartin wrote: post #135

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

"best-in-class documentation"

?? what am I missing. I dread having to lookup how to do things in postgres because their documentation is so horrible to find things in.

Re: New in PostgreSQL 10

#147

Post by grzm »

rc_bhg wrote: post #146
ianamartin wrote: post #135

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

"best-in-class documentation" ?? what am I missing. I dread having to lookup how to do things in postgres because their documentation is so horrible to find things in.

Can you provide an example? In my experience I find the index[0] particularly useful, as well as the SQL command reference[1], the datatype reference[2], the function reference[3], and the server configuration section[4]. I know other people have different usage patterns and different amounts of familiarity with the docs, so I'm aware that my experience may not match yours.

[0]: https://www.postgresql.org/docs/current/static/bookindex.htm...

[1]: https://www.postgresql.org/docs/current/static/sql-commands....

[2]: https://www.postgresql.org/docs/current/static/datatype.html

[3]: https://www.postgresql.org/docs/current/static/functions.htm...

[4]: https://www.postgresql.org/docs/current/static/runtime-confi...

Re: New in PostgreSQL 10

#148

Post by takeda »

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.

I would recommend repmgr[1] over solutions like DRBD suggested. The simple reason is that DRBD replicates on block level and doesn't understand postgres data structures, while repmgr relies on mechanisms provided by Postgres.

So if there is a failure, you're far less likely to learn that all your data is gone, because some important block on disk was not replicated and the database is not readable. With repmgr the worst what could happen is that you might lost few latest transactions, but your data should always be consistent and in working state.

[1] http://repmgr.org/

Re: New in PostgreSQL 10

#149

Post by takeda »

ww520 wrote: post #137
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…

I would strongly discourage this kind of solution. DRBD is to replicate disk. As pointed out is not Postgres specific, and it doesn't understand the data it replicates.

One of nightmare of replication is that there's latency and data never makes it instantly to the other host. This means that there is a possibility that once the master fails, the database file on the other host might be in a corrupted state.

If your data is important you should use replication mechanisms provided by the database. Repmgr[1] makes it easier to set up replication, and perform failover. It also has mechanisms to do auto failover, but I would also be careful with that.

[1] http://repmgr.org/

Re: New in PostgreSQL 10

#150

Post by Boxxed »

dhd415 wrote: post #144
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?

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?

Post reply on HN