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

201–210 of 258 posts

Re: New in PostgreSQL 10

#201

Post by tatersolid »

Illniyar wrote: post #173
brlewis wrote: post #66

Earlier quoted context omitted.

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.

Why are back-end developers not supposed to have a deep understanding of their tools? I hear this sort of statement all the time, it’s infuriating. I once even had a dev team lead claim that it was not a dev’s responsibility at all if the app generates bad SQL; the DBA is supposed to make the queries fast (this person no longer works for me, but I consistently hear similar claims from consultants and others in the dev community).

DBAs are expected to understand the crappy inefficient row-at-a-time ORM in use by the devs, which implies understanding a particular programming language, but not the other way around? There’s nothing magical about SQL databases that a decent developer can’t understand.

Re: New in PostgreSQL 10

#202

Post by brlewis »

Illniyar wrote: post #173
brlewis wrote: post #66

Earlier quoted context omitted.

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.

Not for DBAs, but I also don't expect candidates to get all the answers in that comment. It's a decent data structure question to ask why databases generally use binary trees for indexes when hash lookups are faster. Non-DBAs usually have to think a little bit.

Re: New in PostgreSQL 10

#203

Post by [deleted] »

snuxoll wrote: post #162
Tostino wrote: post #15

Earlier quoted context omitted.

It makes parallel query much more useful. There were very few places that parallel query helped with my workload in 9.6, that is not true for 10.

Parallel index scan is going to be huge for me, I’ve got a table with 80 million rows I have to constantly dig through, indexes help a lot but when they get so stinking big being limited to one thread really hampers performance.

[deleted]

Re: New in PostgreSQL 10

#204

Post by gshulegaard »

bonesmoses wrote: post #132
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/

That's really awesome to hear. I think it is fair to say that app-level partitioning on top of Postgres is now obsolete.

Re: New in PostgreSQL 10

#205

Post by jeltz »

mrfusion wrote: post #197
jeltz wrote: post #194

Earlier quoted context omitted.

It basically just adds a convenient syntax for creating partitions plus adds automatic tuple routing (routing inserts on the partitioned table to the right partition). This is for most users not a huge improvement over using PostgreSQL's table inheritance to implement partitioning, but it is a very important stepping stone for implementing competitive partitioning in PostgreQL. There are lots of promising patches in…

Thanks. I guess I meant to ask what a partition is used for? When is it useful to use one and why?

Oh, you meant partitioning in general. It is useful when you have huge tables with many rows, and works by splitting a table into multiple smaller tables which acts like it was one big table to the outside. Some advantages:

- You can partition by for example month and cheaply delete or archive old data by instead of deleting all rows just dropping the entire partition which is much cheaper.

- You can have different indexes on different partitions. This is commonly used when some indexes are only used by queries which operate on recent data.

- You can put different partitions and their indexes in different table spaces. E.g. you can put your older and less frequently accessed data on cheaper and slower disks.

- Random inserts into B-tree indexes are expensive and when you for example have time series data on the format (device_id, timestamp, v1, v2, ...) it can be useful to partition on the device id so all rows are inserted in strictly increasing timestamp order in each partition. This way you can get more write performance out of your hardware.

- Most databases have a maximum table size (PostgreSQL's maximum is 32 TB) and you can get around that by splitting your huge table into multiple smaller partitions which all fit in the maximum table size.

- The partitioning condition can be used as a very coarse index to entirely skip looking at some of the partitions (even at plan time for some queries). This does not always give you much over if you just had had one big table with one index over all of it, but in some cases this can be a big win.

- For PostgreSQL specifically it looks like you can have some of your partitions be foreign tables. Meaning some of your data can be on an entirely different server, not necessarily PostgreSQL or even a relational database system at all. I am not sure how useful this will be in practice.

Partitioning has a maintenance overhead and adds a bit of extra work to query execution and planning so it is not always worth it.

Re: New in PostgreSQL 10

#206

Post by jackmott »

grzm wrote: post #101
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-...

Let's not let this devolve into a db war. This submission is about PostgreSQL 10. Your parent expresses how easy it is for them to get PostgreSQL up and running in their environment—no comparison saying that others are bad (edit: though they did have some difficulty in the past). It's great that you've got a good solution that works for you! And I'm sure there are other ways of doing this as well for other dbs. If yo…

parent here. yeah SQL Server is pretty nice to work with as well!

Re: New in PostgreSQL 10

#207

Post by KingMob »

unixhero wrote: post #186
patates wrote: post #183

Earlier quoted context omitted.

Do you plan to write more about the migration, like a blog post? That would be very interesting to read.

This needs to become a thing. Where people produce writeups on Oracle To Postgres, and how much better it is; under something like #RunsMuchBetterWithPostgres ... It is strange that there aren't more writeups on Postgresql migrations. Yandex had a good one. Posted here a long while ago: https://news.ycombinator.com/item?id=12489055

> It is strange that there aren't more writeups on Postgresql migrations.

It's probably because switching databases is very painful and rare.

Re: New in PostgreSQL 10

#208

Post by KingMob »

KayEss wrote: post #166
Manozco wrote: post #117

Earlier quoted context omitted.

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…

For bench marking and optimising queries you almost certainly want `EXPLAIN ANALYZE`. That will give you what you want about what Postgres thinks and the time actually taken for the query.

`EXPLAIN ANALYZE` - I read that in a Dalek's voice.

Re: New in PostgreSQL 10

#209

Post by _jal »

manigandham wrote: post #129
zackelan wrote: post #102

Earlier quoted context omitted.

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…

SQL Server does use it's own mini-OS layer and this what lets it virtualize and run on Linux too, so there are higher system requirements. It seems you're just guessing on that last part though - SQL Server has been in production for decades and already has lots of tooling and there are even new CLIs available for the Linux environment. Production SQL Server comes with support from MS, you're definitely not on your o…

"Assumptions and hyperbole"? That MSSQL isn't available as a service like RDS? That tooling and integration that's been building up for decades around PG (or Mysql/Maria for that matter) doesn't exist? These are just facts.

I'm sure you're right that MS would be happy to sell support. What I don't see is a reason to care, unless I run in to a Windows app that can't use another DB that I suddenly need to run under Linux.

Re: New in PostgreSQL 10

#210

Post by ddorian43 »

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…

Every BigQuery query, actually loads all the data (for that query) in-memory until it finishes.
Post reply on HN