181–190 of 222 posts
Big thanks for the community for the hardwork! I wonder if we set the "sync=off" in the test, will it be way higher than the OP results?
Postgres has been my DB of choice for nearly a decade. The only times I wind up working with another db are because: (1 it is a better technical fit for a very specific problem (2 there is already a legacy db in place I have been voted down at a couple of startups that wanted to run a "MEAN" stack, invariably all of those startups moved from MongoDB or shutdown. The only time I will advocate for anything other than P…
I have been using it a lot lately, and do like it. It isn't a popular opinion on HN, but I will still advise for Oracle or SQL Server in terms of tooling, cluster scaling, server side programming and DB drivers. Then again, we work with customers whose Oracle and SQL Server licenses costs aren't an issue.
If you take the issues of open-source and licensing out of the equation (both important issues in their own right, but not related to the point at hand) then Oracle and SQL Server are both ridiculously good.
I personally try to avoid them (due to the cost, and the lock-in) but they are astoundingly performant and featureful RDBMSs with a huge amount of support and documentation behind them.
Earlier quoted context omitted.
There's no reason for SELECT COUNT(DISTINCT x)) to perform badly in Postgres, as long as you have an appropriate table design and indexes. Also MSSQL's query planner isn't better than Postgres', I work with both. Postgres does have its quirks though, especially with the MVCC row expiry.
> There's no reason for SELECT COUNT(DISTINCT x)) to perform badly in Postgres, as long as you have an appropriate table design and indexes. Meh. Postgres' planner doesn't know how to generate a skip-scan/loose index scan for DISTINCT. You can write it yourself, but it's a bit painful: https://wiki.postgresql.org/wiki/Loose_indexscan If you have a low cardinality that can be a huge efficiency difference.
EXPLAIN ANALYZE SELECT COUNT(DISTINCT(calc)), calc FROM price_history GROUP BY calc;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=10713.04..11381.06 rows=10 width=5) (actual time=1010.383..1073.263 rows=11 loops=1)
Group Key: calc
-> Sort (cost=10713.04..10935.68 rows=89056 width=5) (actual time=1010.321..1049.189 rows=89041 loops=1)
Sort Key: calc
Sort Method: external merge Disk: 1392kB
-> Seq Scan on price_history (cost=0.00..3391.56 rows=89056 width=5) (actual time=0.007..20.516 rows=89041 loops=1)
Planning time: 0.074 ms
Execution time: 1076.521 ms
(8 rows)
EXPLAIN ANALYZE SELECT COUNT(DISTINCT(calc)), calc FROM price_history GROUP BY calc;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.29..2804.82 rows=10 width=5) (actual time=0.117..47.381 rows=11 loops=1)
Group Key: calc
-> Index Only Scan using price_history_calc_idx on price_history (cost=0.29..2359.52 rows=89041 width=5) (actual time=0.054..18.579 rows=89041 loops=1)
Heap Fetches: 83
Planning time: 0.208 ms
Execution time: 47.416 ms
(6 rows)
Earlier quoted context omitted.
The only issue that I have with this top-most comment is that it presents PG as a silver bullet. But, there are a lot of different types of databases for a reason. For instance, at my current startup, we employ at least 7 different databases (including PG). And, I don't say that to brag - each has a specific use for the problem at hand. You have to consider the needs and trade offs of your specific project. And, if y…
I agree with you 100%. An abstracted interface for your datalayer is a must have. Many startups and projects begin with a single db and grow into new dbs as the business requirements change. Obviously if you know your data model well enough you can foresee a lot of these requirements and pick the right tool for the job. However, you will usually need to pick a db to start with and hope that it will accommodate as man…
Earlier quoted context omitted.
> invariably all of those startups moved from MongoDB Why? Especially after point #1 and assuming the document-store was a good fit for the data model.
Because NoSQL is a hype. Many of the NoSQL essentially takes us back to 60s before Codd came up with relational model[1] These ideas tend to come back once in a while [2][3], but so far nothing is better than relational model. NoSQL still makes sense in many cases (generally when your specific use case does not need all guarantees of ACID), you can get in return higher performance or horizontal scalability. MongoDB i…
MongoDB: for when you don't need consistency, availability, or partition tolerance.
There are some really good NoSQL products out there. I seriously think RethinkDB is on par with Postgres. I've also used Cassandra and BerkeleyDB and they're both decent. But unless some core part of your business logic is pathological to implement in SQL (like Reddit's comment trees) you should go with Postgres.
Earlier quoted context omitted.
I am in no way a fan of Oracle but, c'mon, that's a little over the top, don't you think?
A silicon valley amateur radio club recently moved their study and test sessions from Oracle to Google, because Oracle changed their non-profit rate to $2000/day for a room. Google doesn't charge as long as a contact employee takes responsibility for the meeting. So yeah, Oracle, evil to the core.
Postgres has been my DB of choice for nearly a decade. The only times I wind up working with another db are because: (1 it is a better technical fit for a very specific problem (2 there is already a legacy db in place I have been voted down at a couple of startups that wanted to run a "MEAN" stack, invariably all of those startups moved from MongoDB or shutdown. The only time I will advocate for anything other than P…
How would you handle replicating a DB to mobile devices? This is the reason why I've been using CouchDB, but if Postgres or a plugin offered something comparable I'd have gone for it for sure.
Earlier quoted context omitted.
>32 bit integers for file offsets, so the largest possible mnesia table (for now) is 4Gb Do they mean 4GB? Surely 0.5GB/4Gb is a bit small even for 32bit?
> Do they mean 4GB [rather than 4Gb]? "No one" measures sizes that aren't network throughput numbers in bits. "Everyone" uses bytes. :) And I mean -honestly- if you were shooting for the Pedant badge, you should have also quibbled about GB vs GiB. ;)
Earlier quoted context omitted.
There's been a large migration off of mysql to postgres simply because Oracle got the rights to mysql when they purchased Sun. That is what made me consider , ditching mysql. The final straw came when I stood up a mysql 5.6 instance to use as a data warehouse for about 5TB of data (15 billion rows). To my horror after spending a few weeks on this project I discovered that mysql only supported a small subset of the SQ…
> There's been a large migration off of mysql to postgres No there hasn't: http://db-engines.com/en/ranking Which makes sense since they aren't really playing in the same space. The benefits of PostgreSQL are largely lost on typical MySQL use cases (light load, simple CRUD access patterns, limited use of JSON/BSON etc).
Earlier quoted context omitted.
> There's no reason for SELECT COUNT(DISTINCT x)) to perform badly in Postgres, as long as you have an appropriate table design and indexes. Meh. Postgres' planner doesn't know how to generate a skip-scan/loose index scan for DISTINCT. You can write it yourself, but it's a bit painful: https://wiki.postgresql.org/wiki/Loose_indexscan If you have a low cardinality that can be a huge efficiency difference.
Actually that's wrong: EXPLAIN ANALYZE SELECT COUNT(DISTINCT(calc)), calc FROM price_history GROUP BY calc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=10713.04..11381.06 rows=10 width=5) (actual time=1010.383..1073.263 rows=11 loops=1) Group Key: calc -> Sort (cost=10713.04..10935.68 rows=89056 width=5…