Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres 11 – A First Look

craigkerstiens.com

Post reply on HN
Print view View original

181–190 of 193 posts

Re: Postgres 11 – A First Look

#181

Post by manigandham »

threeseed wrote: post #178
manigandham wrote: post #175

Earlier quoted context omitted.

PostgreSQL is completely open-source with no backing company so everyone is reliant on some vendor if they want support. It makes no difference that Citus is one of them, with some core PG devs onboard. Why would you talk about AWS RDS and then skip over Citus Cloud which is a managed service? If you consider this a fork then any plugin for PG is also a fork, which then makes it hard to have a productive discussion.

The reason I am talking about AWS RDS is because most companies aren't multi-cloud. Data needs to be housed and secured within that private VPC. And so Citus Cloud is irrelevant because it is an externally housed product. If Citus allowed their proprietary product to be run within existing clouds that could be something but it isn't. And again since it is a significant add-on i.e. beyond just a simple plugin that alm…

What does RDS have to do with being multi-cloud? It's a hosted offering which only works in AWS. And Citus Cloud does offer VPC connectivity so you can access it privately. As stated before, the extension is also open-source and you can run it in any cloud or datacenter yourself, for free. Why would you claim that's not allowed?

You seem to be stuck on a strange intrepretation of vendor support when PG has no backing vendor, so you can pick whichever one you want. If you aren't comfortable with Citus and prefer AWS then that's fine, but you make the trade-off in the features as well. Not every managed postgres service chooses to support every PG extension so it's nothing unique in that regard, and is ultimately no different than the choices involved in picking any other database with vendor backing.

You've posted several comments now that seem to show that you do not know much about Citus or are willingly misleading people about it. I'm not sure why but perhaps it would be better if you read up on the details or disclosed your angle more clearly.

Re: Postgres 11 – A First Look

#182

Post by shrumm »

LunaSea wrote: post #97
shrumm wrote: post #36

I’m already loving PG10’s native partitioning support. PG11 closes the loop on a few convenience features like being able to set foreign and primary keys at the master table level. With PG10 I can’t use ON CONFLICT clauses - looking forward to upgrading and sorting that out.

I encountered the same limitations as you while implementing partitioning a few months ago and it's possible to use ON CONFLICT if the unique index is set on the partitioned table. So after initialising a new partition, I also create a unique index attached to that partition directly instead of the parent table.

wow thanks, I never thought to try it, just dismissed it and never dig further. I’ll try this!

Re: Postgres 11 – A First Look

#183

Post by shrumm »

welder wrote: post #49
shrumm wrote: post #36

I’m already loving PG10’s native partitioning support. PG11 closes the loop on a few convenience features like being able to set foreign and primary keys at the master table level. With PG10 I can’t use ON CONFLICT clauses - looking forward to upgrading and sorting that out.

Check out TimescaleDB, a Postgres extension that auto-creates partition tables for you while PG11 native partitioning you still need to manually run CREATE TABLE. https://news.ycombinator.com/item?id=18026699

would love to but this is a no-go at this point, since I’m on RDS. I think it’s great you’re solving this though - I currently use a cron job to create new partitions.

Re: Postgres 11 – A First Look

#184

Post by tfehring »

twic wrote: post #134
pmontra wrote: post #87

Earlier quoted context omitted.

Yesterday a friend developer told me he was trying to code a query on MySQL to get the latest message of every conversation stored in a table like conversation id, timestamp, user id, message. I did that years ago so I told him he'll find a lot of solutions on Stackoverflow, ugly ones with MySQL and much better ones for PostgreSQL which has a nrow() function. A MySQL DBA solved that problem in that project I worked o…

Given: create table conversations ( message_id integer primary key, conversation_id integer, timestamp timestamp, user_id integer, message varchar ); Then: select distinct on (conversation_id) * from conversations order by conversation_id, timestamp desc; Should do it. Although i believe "select distinct on" is a PostgreSQL extension rather than standard SQL. Now, what i don't know is how this is planned for a large…

Extension or not, that's a really useful shortcut. Coming from SQL Server, the best option I know of requires a CTE or subquery:

    WITH CTE_messages AS
    (
        SELECT
             *
            ,row_nbr = ROW_NUMBER() OVER(PARTITION BY conversation_id
                                         ORDER BY timestamp DESC)
        FROM
            conversations
    )
    SELECT
        *
    FROM
        CTE_messages
    WHERE    row_nbr = 1
    ORDER BY
        conversation_id
This does give a performant plan in SQL Server with an index on (conversation_id, timestamp), for what it's worth. I imagine the same is true for this syntax in PostgreSQL. But SELECT DISTINCT sometimes does strange things to execution plans in general, and I wouldn't be surprised if that extends to SELECT DISTINCT ON.

Re: Postgres 11 – A First Look

#185

Post by dotancohen »

edoceo wrote: post #85
dotancohen wrote: post #53

Earlier quoted context omitted.

> Why? Notice that parallel to MySQL's rise a particular loosely-typed, never-except, often-wrong programming language also became popular. To this day I feed my family with that language. The typical coder (not that I do not say "developer") who codes in PHP does not care about correctness. He does not understand why monetary values cannot be stored in floats, he does not know what bitwise manipulation is, he does n…

That's a broad generalization of PHP "coders" I think it's safe to say we've all seen good code a crap code -- regardless of language. It's not the wand, it's the magician. (nb: I've been building software for 20+ years, I use PHP (among others) and knew JOIN and types in PG before I ever saw PHP, I cannot be the only one)

You're not the only one, but you are one in a thousand.

When I work with Python, Java, C++, bash, SQL, or Javascript in a team I feel mediocre at best. I can get around, but I know that I'm easily outclassed. Contrast with PHP, where I'm almost always the best dev in the room. True that I have much more experience with PHP than in the other technologies that I've mentioned, but PHP has the dual curse of having a very low bar to entry, and seems-to-work enough that most "PHP coders" never feel the need to progress beyond the most basic of understanding.

When I meet new devs I deliberately try to postpone mention of PHP as long as I can, to avoid attaching myself to the tainted stigma that PHP has acquired.

Re: Postgres 11 – A First Look

#186

Post by chrisgd »

dotancohen wrote: post #53
mrep wrote: post #37

Earlier quoted context omitted.

100% naive question: why is mysql and their similars so popular then? Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?

> Why? Notice that parallel to MySQL's rise a particular loosely-typed, never-except, often-wrong programming language also became popular. To this day I feed my family with that language. The typical coder (not that I do not say "developer") who codes in PHP does not care about correctness. He does not understand why monetary values cannot be stored in floats, he does not know what bitwise manipulation is, he does n…

Is dollars not as floats just a MySQL issue? I am not sure why you wouldn’t want to do this.

Re: Postgres 11 – A First Look

#187

Post by Tostino »

chrisgd wrote: post #186
dotancohen wrote: post #53

Earlier quoted context omitted.

> Why? Notice that parallel to MySQL's rise a particular loosely-typed, never-except, often-wrong programming language also became popular. To this day I feed my family with that language. The typical coder (not that I do not say "developer") who codes in PHP does not care about correctness. He does not understand why monetary values cannot be stored in floats, he does not know what bitwise manipulation is, he does n…

Is dollars not as floats just a MySQL issue? I am not sure why you wouldn’t want to do this.

No, that is not just MySQL. Just do a little googling and you'll run across enough information on why it's bad practice in any language or database. You need to avoid converting dollar values to a floating point at every place in your stack otherwise you will introduce floating-point rounding errors.

It's not just the data type that it's stored in, if you convert it to that at any point, you lose the exact precision and introduce those errors. It can add up to some serious differences over a lot of values.

I did some analysis on my database to see if I had stored any of those numbers is floating points, what would the amount difference be in total, and what would be the absolute difference for any single account. it was awhile ago, but I believe that the absolute difference per client was around $20 or $30, but the absolute difference per account within that client was as high as $100. Obviously that's pretty damn unacceptable.

Re: Postgres 11 – A First Look

#188

Post by xdanger »

threeseed wrote: post #177
mslot wrote: post #172

Earlier quoted context omitted.

PostgreSQL has excellent time series capabilities. It can load millions of rows per second, efficiently scan by time range, build rollup tables, has expressive SQL with excellent support for time (timezones, ranges, timestamps, intervals, conversion, etc.), it can combine multiple indexes to query large volumes of time series data quickly, it can do sampling, and it can expire data efficiently through partitioning. O…

Congratulations. You just described the capabilities of ANY SQL database. But again there are capabilities that exist only in InfluxDB because all it does is time series data. It's not multi-purpose. Which again is why you see it all over the place in the metrics/monitoring ecosystem but you never see PostgreSQL, MySQL etc.

Actually you can use PostgreSQL in those with timescaledb plugin. ;)

Re: Postgres 11 – A First Look

#189

Post by dvlsg »

y4mi wrote: post #43
eitally wrote: post #39

Earlier quoted context omitted.

ctrl+c also works on Crosh (Chrome Shell).

But not on Windows cmd I think it worked in powershell however, can't test right now though Also: chromeos is Linux. It's a safe bet they'll support standard signals

You mean in general or specifically for PostgreSQL? Ctrl+c definitely works in cmd in general, which is what my original post was referencing. I use it all the time.

Not sure about for PostgreSQL in cmd off the top of my head (posting from my phone). I suspect because I got down voted that it may not work there.

Re: Postgres 11 – A First Look

#190

Post by Something1234 »

chinhodado wrote: post #138
GordonS wrote: post #96

Earlier quoted context omitted.

PgAdmin recently became an in-browser tool, and is much improved - in particular, it no longer uses GBs of memory and 90% of my CPU!

pgadmin 4 is still horribly slow, both when starting up and during operation, horribly bloated in size (100mb compared to pgadmin 3's 10mb). It still hogs CPU and RAM like hell.

What ever happened to pgadmin3? Why did development stop on it? It was definitely a beast, with a lot of weirdness to it, and some minor pain points, but it was good. It was lightweight resource wise.
Post reply on HN