How does the native partitioning compare to something like Timescale's hypertables?
New in PostgreSQL 10
171–180 of 258 posts
Re: New in PostgreSQL 10
#172Earlier quoted context omitted.
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?
Indexes in PostgreSQL require lookups into the table to access the data values in the rows. Index organized tables have indexes which include the data values in the index itself, removing the need for the lookup in the table itself. Here's some more detail: https://news.ycombinator.com/item?id=10451095
Perhaps I'm missing something, but your description doesn't seem consistent with my understanding of the index-only scan feature that's been in PG since 9.2 https://wiki.postgresql.org/wiki/Index-only_scans
Re: New in PostgreSQL 10
#173Earlier quoted context omitted.
I think it's a neat feature. But note that orderedness isn't just useful for range queries, it also helps to satisfy ORDER BY and to allow for merge joins without a sort steps. There's also no yet support for index-only scans (probably never), no constraints, and no multi-column index support. There's also still some performance kinks to work out with the current hash index performance - large when growing the index…
Stop it! You're giving away all possible answers to one of my favorite interview questions. Edit: not seriously asking you to stop, but the interview question part is true
I hope those are interview questions for dbas because it's way outside common knowledge for backend developers.
Re: New in PostgreSQL 10
#174Earlier quoted context omitted.
Having provisioned these sorts of "big data" systems in the past, it's now about how much you have today, it's how much you'll have over a growth period. The advantages of a scale-out system like cassandra, riak (RIP), memsql, big-table, cockroach, etc., are that they can grow with you from 3TB, to 9TB, to 81TB (as an example, if you're on some exponential growth curve with your data). It's not that you can't do it w…
Designing for the future is a guaranteed project failure. If one is starting a new project, hence contemplating what DB to use, starting with an ACID db is a safe bet in most cases(unless of course they are already starting with a huge amount of data). By the time the outgrow the ACID database they will have a better idea of what exactly they need and more importantly they will have the resources to make the switch.…
> Designing for the future is a guaranteed project failure.
Yes, I understand this. I would not want to get bogged down on infrastructure when trying to deliver early versions of a product. At the same time, I think database choice is one of the more important tech stack decisions you should make early on.
Cassandra also sounds like a good bet for the type of data I'm interested in storing (ML model inputs, so lots of key -> numerical value data). But my experience is mostly around RDBMS and some NoSQL. Hence the question. Thanks for the answer!
Re: New in PostgreSQL 10
#175Earlier 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.
You're talking about DDL. They're talking about an in-place rewrite of the value of a single column, which, yes, InnoDB will do with way less write load than postgresql.
Can you provide specifics? AFAICT that applies only to indexes (where there is another layer of indirection in MySQL).
Re: New in PostgreSQL 10
#176Earlier quoted context omitted.
you can comfortably fit 8TB of data on a single box running postgres, more dependent on your hardware :)
That's about 1/250th of our working set size. :) Of course, that's in a Spark cluster running massively parallel queries and not on a single (however large) node.
You did not ask that question. I assume you knew before that PostgreSQL is not the tool for your workload.
Re: New in PostgreSQL 10
#177Earlier quoted context omitted.
That's about 1/250th of our working set size. :) Of course, that's in a Spark cluster running massively parallel queries and not on a single (however large) node.
You did not ask that question. I assume you knew before that PostgreSQL is not the tool for your workload.
I didn’t ask a question. I love PostgreSQL for small relational stuff (where “small” is up to a few TB) and use it any time it’s appropriate. I wouldn’t classify it as a Big Data store, though.
That’s no criticism at all. I wouldn’t use Spark for a transactional DB because it’s not the best tool for the job. Same for PostgreSQL when the dataset grows far beyond what a single instance can reasonably process.
Re: New in PostgreSQL 10
#178Does 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?
The biggest case almost completely unhandled by PostgreSQL is if you need a clustered index like in MySQL. There are basically two common ways of organizing rows in a database: store everything in a tree according to the primary key, or store everything in a heap, and have a separate tree relating values to pointers into the heap. MySQL stores rows in a tree, and PostgreSQL stores rows in a heap.
The strategy used by PostgreSQL is simpler, more flexible, and cleaner, but there are some cases where MySQL's way of storing things can be significantly faster.
There has been talk of PostgreSQL supporting clustered indexes, but that would probably require reimplementing a ton of stuff to support both storage formats efficiently. Also, it turns out that there are a bunch of ways in which PostgreSQL can cheat such that in most workloads there's no difference at all - though not all of these cheats have been implemented.
Note that I didn't say MySQL is faster or slower than PostgreSQL. Such statements don't make sense, because it depends entirely on your workload, and as long as you aren't literally using PostgreSQL as a key/value store instead of a relational database, you can probably always find a way to make it as fast as necessary.
Re: New in PostgreSQL 10
#179I noticed hash indexes are now crash proof and replicated -- this seems to make them actually usable in production. In other words, this release effectively "adds" a new index type. That seems like a much bigger deal than is being talked about, is there any reason to believe that new databases shouldn't be using hash indexes for columns that won't be supporting range queries? (In other words, pretty much all keys.) I…
Equality operations are also cheaper with hash indexes than btree because less data pages need to be fetched, so this can be a gain for certain columns with a high cardinality. You should really look at if those could be used or not, you may gain in performance with a switch.
Re: New in PostgreSQL 10
#180What does native partitioning do?
"This means that users no longer need to create triggers for routing data; it's all handled by the system. "