5 ms·

Postgresql 9.2 will have boat-loads of performance enhancements

156 points by xyzzyrz 15y ago

whalesalad 15y ago
I know a lot of this will be up to the architecture and specific nature of each app... but has anyone recently compared a modern MySQL and Postgresql installation on a few baseline metrics? I'd love to see the differences. I am starting to use it more and more, being a Django developer surrounded by Postgres users. But, so far the core differences are scaring me away. psql template1? It doesn't make as much sense to me.
- ryanbraganza 15y ago
  What's the equivalent of template1 in mysql?
  - masklinn 15y ago
    I don't think there is one, mysql only creates empty databases it does not have a concept of db templates.
- obtu 15y ago
  Why connect to template1? It should be invisible. Create a user and a database for your webapp. Matching the unix account name is the simplest way to do that. In pg_hba.conf, let unix users authenticate as matching PostgreSQL users: local all all peer The postgres user on Debian/Ubuntu can create users and databases: sudo -u postgres createuser -SRD webappuser sudo -u postgres createdb webappuser --owner=webappuser sudo -u webappuser psql
- masklinn 15y ago
  > psql template1? It doesn't make as much sense to me. What doesn't make sense about it? It opens a postgres shell to the `template1` database, that's all... And there should be very little reason to connect to `template1`: it is the default template for creating a database (it's copied to the new db file basically), so the only point in connecting to it is e.g. creating a few common tables or enabling extensions so that all the databases you create have them by default. I've yet to ever need that.
FooBarWidget 15y ago
These are fantastic improvements. But I wish there is auto-sharding support for PostgreSQL, or at least an auto-sharding layer. For some data sets I really sharding and building things manually just takes too much time. Unfortunately sharding is not as easy as 'insert into server number object_id % NUMBER_OF_SERVERS'; a proper auto-sharding application should also handle moving data from one shard to another.
- wulczer 15y ago
  There's talk about making partition management easier, but I doubt it's going to be present in 9.2 You can look at PL/Proxy that makes some things related to partitioning across multiple machines easier.
- ianso 15y ago
  I have a proof-of-concept that illustrates a way of auto-sharding a Postgres database using a patched version of PL/Proxy: http://code.google.com/p/hotrepart/ http://code.google.com/p/hotrepart/ The big limitation is that your DB structure has to be hierarchical. I think a lower-level solution at the WAL level would be better.
- bad_user 15y ago
  Adding or removing shards can get complicated and it is better to handle this at your application layer. For instance, you could keep track of the configuration history - when adding a shard, you create a new version. And when trying to fetch, you could try the latest version of your configuration (which says, for example, that it's on server 3) and if not found you could try the previous version (which says that it's on server 1). You can also add the possibility of removing a shard, however this requires that for every write you'll have to do a secondary write to another server, for redundancy. In the background you could have a healer that whenever a new version gets pushed, it would go through all entries and move them accordingly. And at the end it would declare such a version to be "healed". However, what I described above works in case you want to introduce a new server in production immediately. Like say, in the case of something like Twitter, you want to write to the new shard right away and the most important messages are the most recent ones. But you may also want to create a configuration such that a new server isn't hit by the front-end until the healing script hasn't finished migrating. The thing is, a layer like this gets to be very optimized for your needs and there's no one size fits all.
chrislomax 15y ago
I like all the talk surrounding Postgresql recently, does anyone have any experience with moving from MSSQL to Postgresql? I am not asking if one is better than the other because it depends what I am using it for but what about performance / reliability?
- hkarthik 15y ago
  Such comparisons are often difficult since MySQL and Postgres typical perform significantly better on Unix based systems than they do on Windows. Also using the Windows/.NET libraries for connections tend to perform worse even if the database server itself is hosted a Unix box. In general, I would say you should stay on MSSQL if your server environments are Windows and only look at the OSS alternatives if you're willing (and able) to make a full stack switch to Unix.
  - chrislomax 15y ago
    Well we do currently use .net but I asked a question the other week on here about different languages etc and one good piece of advice I got was to create a REST service and interact with that. We have put a lot of thought into it and we were going to give it a go with WCF and REST but I don't see any issue why we cannot buy a UNIX box and put Postgresql on it and put a layer on it with some other language serving the REST? I will be benchmarking this with a current customer who has over 1 million products and a lot of visitors a day to see how it stands up against MSSQL, we are quite sensible into not jumping into decisions so I appreciate your comment and it shall definitely be part of the benchmark.
    - dos1 15y ago
      Having spent the better part of 3 years creating REST services with .NET, I strongly recommend you do NOT use traditional WCF. The WCF Web API stuff looks interesting but still seems immature. I've found ASP.NET MVC to be the best tool for creating REST apis.
      - chrislomax 15y ago
bostonaholic 15y ago
Wait, is a boat-load more or less than a butt-load?
- DennisP 15y ago
  Boats are bigger than butts.

156 points by xyzzyrz 15y ago

I know a lot of this will be up to the architecture and specific nature of each app... but has anyone recently compared a modern MySQL and Postgresql installation on a few baseline metrics? I'd love to see the differences. I am starting to use it more and more, being a Django developer surrounded by Postgres users. But, so far the core differences are scaring me away. psql template1? It doesn't make as much sense to me.

ryanbraganza 15y ago
What's the equivalent of template1 in mysql?
- masklinn 15y ago
  I don't think there is one, mysql only creates empty databases it does not have a concept of db templates.
obtu 15y ago
Why connect to template1? It should be invisible. Create a user and a database for your webapp. Matching the unix account name is the simplest way to do that. In pg_hba.conf, let unix users authenticate as matching PostgreSQL users: local all all peer The postgres user on Debian/Ubuntu can create users and databases: sudo -u postgres createuser -SRD webappuser sudo -u postgres createdb webappuser --owner=webappuser sudo -u webappuser psql
masklinn 15y ago
> psql template1? It doesn't make as much sense to me. What doesn't make sense about it? It opens a postgres shell to the `template1` database, that's all... And there should be very little reason to connect to `template1`: it is the default template for creating a database (it's copied to the new db file basically), so the only point in connecting to it is e.g. creating a few common tables or enabling extensions so that all the databases you create have them by default. I've yet to ever need that.

FooBarWidget 15y ago

These are fantastic improvements. But I wish there is auto-sharding support for PostgreSQL, or at least an auto-sharding layer. For some data sets I really sharding and building things manually just takes too much time. Unfortunately sharding is not as easy as 'insert into server number object_id % NUMBER_OF_SERVERS'; a proper auto-sharding application should also handle moving data from one shard to another.

wulczer 15y ago
There's talk about making partition management easier, but I doubt it's going to be present in 9.2 You can look at PL/Proxy that makes some things related to partitioning across multiple machines easier.
ianso 15y ago
I have a proof-of-concept that illustrates a way of auto-sharding a Postgres database using a patched version of PL/Proxy: http://code.google.com/p/hotrepart/ http://code.google.com/p/hotrepart/ The big limitation is that your DB structure has to be hierarchical. I think a lower-level solution at the WAL level would be better.
bad_user 15y ago
Adding or removing shards can get complicated and it is better to handle this at your application layer. For instance, you could keep track of the configuration history - when adding a shard, you create a new version. And when trying to fetch, you could try the latest version of your configuration (which says, for example, that it's on server 3) and if not found you could try the previous version (which says that it's on server 1). You can also add the possibility of removing a shard, however this requires that for every write you'll have to do a secondary write to another server, for redundancy. In the background you could have a healer that whenever a new version gets pushed, it would go through all entries and move them accordingly. And at the end it would declare such a version to be "healed". However, what I described above works in case you want to introduce a new server in production immediately. Like say, in the case of something like Twitter, you want to write to the new shard right away and the most important messages are the most recent ones. But you may also want to create a configuration such that a new server isn't hit by the front-end until the healing script hasn't finished migrating. The thing is, a layer like this gets to be very optimized for your needs and there's no one size fits all.

chrislomax 15y ago

I like all the talk surrounding Postgresql recently, does anyone have any experience with moving from MSSQL to Postgresql? I am not asking if one is better than the other because it depends what I am using it for but what about performance / reliability?

hkarthik 15y ago
Such comparisons are often difficult since MySQL and Postgres typical perform significantly better on Unix based systems than they do on Windows. Also using the Windows/.NET libraries for connections tend to perform worse even if the database server itself is hosted a Unix box. In general, I would say you should stay on MSSQL if your server environments are Windows and only look at the OSS alternatives if you're willing (and able) to make a full stack switch to Unix.
- chrislomax 15y ago
  Well we do currently use .net but I asked a question the other week on here about different languages etc and one good piece of advice I got was to create a REST service and interact with that. We have put a lot of thought into it and we were going to give it a go with WCF and REST but I don't see any issue why we cannot buy a UNIX box and put Postgresql on it and put a layer on it with some other language serving the REST? I will be benchmarking this with a current customer who has over 1 million products and a lot of visitors a day to see how it stands up against MSSQL, we are quite sensible into not jumping into decisions so I appreciate your comment and it shall definitely be part of the benchmark.
  - dos1 15y ago
    Having spent the better part of 3 years creating REST services with .NET, I strongly recommend you do NOT use traditional WCF. The WCF Web API stuff looks interesting but still seems immature. I've found ASP.NET MVC to be the best tool for creating REST apis.
    - chrislomax 15y ago

bostonaholic 15y ago

Wait, is a boat-load more or less than a butt-load?

DennisP 15y ago
Boats are bigger than butts.