
How times have changed for PostgreSQL - davidw
http://opensource.com/business/14/3/postgresql-ugly-no-more
======
njharman
I was 76% expecting a treatise on how various time and date fields and
functions have evolved over PostgreSQL versions.

------
iagooar
I have been using Postgres since I was a student, but I must recognize that
being a better programmer than sysadmin, I always used it after some kind of
abstraction layer. Today, this layer is Rails with ActiveRecord.

I want to convince my boss to use Postgres instead of MySQL in a new project
we've just started. Does anybody know about a good comparison of both
databases, or a nice list of unique Postgres features? I have been googling a
little bit but the findings weren't that good.

~~~
gdulli
Reasons I like PostgreSQL compared to MySQL:

\- Timestamp with time zone

\- More robust, fewer crashes, less corruption of data

\- More features (JSON data type, partial indexes, function/expression
indexes, window functions, CTEs, hstore, ranges/sequences/sets, materialized
views, too many to list)

\- More disciplined (doesn't do things like auto-truncate input to get it to
fit into a column)

\- Not owned by Oracle, it's actively developed, regular major release
schedule, developers/maintainers are talented and trustworthy, etc.

\- Better Python driver (don't know about other languages)

\- Choice of languages for database functions/procedures (Python, JS, etc.)

\- Better partitioning support

\- Better explain output, explain analyze, buffers

\- Multiple indexes allowed per table in a query (I hear MySQL has made a
little progress here since I last used it)

~~~
antjanus
Add "built-in fulltext search" and the fact that you've just replaced Redis,
Sphinx/Solr/Lucene, and Mongo with a single application.

THAT, I think, is the biggest sell, at least to me. The fact that PgSQL does
so much and so well.

I used Pg for full-text search in the past and the fact that I did not have to
bother with setting up Solr or an interface was a wonderful feature. The
search index lived right alongside my data.

~~~
jlogsdon
Technically mysql has "built-in fulltext search" as well, but the
implementation leaves something to be desired.

~~~
antjanus
I couldn't find anything nearly as powerful as Pg so I would discount it
fully.

------
DrJokepu
If you want job security and fat paychecks for the next decade, this is a very
good time to learn PostgreSQL well and specialise as a PostgreSQL consultant.

~~~
iagooar
The question I always ask myself is: how does one learn specialized stuff like
managing a DB without working with it professionally? This also applies to Big
Data, scaling, etc.

I would love to work building highly scalable systems, but I don't get to do
it at my current position, and all the job offers out there require having
experience doing it. Looks like a chicken-and-egg problem to me.

~~~
DrJokepu
Contribute to open source software, attend meetups, talk at meetups.

~~~
iagooar
Can you recommend some interesting open source projects for an experienced
Ruby / Rails developer, but with very little open source experience?

------
iblaine
It is worth pointing out that AsterData and Redshift are built on pgSQL. From
what I have read, this is due to licensing issues. MySQL is ok but I'm
unconvinced that MySQL is a good option for single instance terabyte size
databases. I'm not sure if pgSQL is any better but you hardly see large
unwieldy pgSQL instances. Large slow MySQL instances are everywhere. And it's
frustrating.

------
PublicEnemy111
What are the best resources for one to learn PostgreSQL?

~~~
captainmojo
I'd start here:
[http://www.postgresql.org/docs/9.3/static/index.html](http://www.postgresql.org/docs/9.3/static/index.html)

I've always found this documentation to be great.

If you've worked with RDBMS' before, you can skip around the chapters. If not,
I'd read up through chapter 14 and go from there.

You should be able to easily install it on whatever OS you're running.

------
new_test
Since we are on the subject of PostgreSQL, does anyone know of a good resource
that would teach me how to create webapps where each "customer" would get
their own "database"? What are the best practices for that? Also, what are the
best practices for ALTER'ing tables in production (e.g. adding/deleting
columns).

Here's a concrete (made-up) example: I want to write a webapp where people
would sign up to do some personal tracking. They create variables they are
interested in (weight, mood, calory intake, etc.) And then enter their data
daily. So for each customer I need to have a different database with different
columns, and I want them to be able to add/delete variables "on the fly". Is
it very straightforward, and hard to get wrong? Or are there "best practices"
for this sort of thing? Thanks.

~~~
bananas
Look up the following terms

1\. SaaS tenancy models. This is the data separation.

2\. Custom fields would be usually represented as an EAV model (Entity-
Attribute-Value).

3\. Don't ALTER in production if you can help it. If you're going to do it,
use migrations which are scripts which first add columns, then transform data,
then reapply constraints.

~~~
arethuza
I've also seen applications basically just assign a fixed number of additional
columns on the relevant tables for custom fields - although this approach
doesn't win any awards for elegance it can be pretty straightforward.

From what I've seen, applications that use EAV tend to evolve to suffer from
bad cases of the "inner platform effect":

[http://en.wikipedia.org/wiki/Inner-
platform_effect](http://en.wikipedia.org/wiki/Inner-platform_effect)

NB There is nothing "wrong" with using EAV - just that it seems prone to
misuse (a bit like XML).

~~~
bananas
The only problem with a limited number of custom fields attached to the table
is that they aren't necessarily optimised for sorting, might extend past the
row size limit of database tables (this is a pain!), can't be reliably typed
and a client will always want one more.

EAV is a sort of inner platform thing I agree, but the correct solution i.e a
document store with full field level indexing that works with enterprise loads
doesn't exist (yet). CouchDB was promising on that front but didn't go all the
way.

XML is fine. Just don't stick it in database columns (my favourite chunk of
pain!)

~~~
wiredfool
I like HStore a lot better than XML for postgres. And it's Waaaaay better than
the wide table model.

Way back when, I did a data model that added columns for arbitrary data fields
that the users wanted in PG, and wound up with a wide table model. PG can
store a surprisingly large number of fields, I think it got up to the mid to
high hundreds after years of this. At the time, hstore wasn't there, xml was
either not there yet, or just recently added. And the queries for EAV looked
surprisingly awful, especially when added to the not exactly straightforward
queries we were doing on the events.

It wound up being an extremely large, extremely sparse table, with some fields
having 100% usage, most having >>.01%, and a few getting used in the 1% range.
On the plus side, it was possible to index any of the fields, which was
especially useful with functional or 2 column indexes.

If I had to do it again, I'd be on hstore, or maybe hstore/json. It wasn't
pretty but it wasn't the fatal flaw in that startup.

------
juretriglav
Yes PostgreSQL is awesome. From my experience with a large installation, we
had many more issues with the actual hardware on EC2, than we had with
postgres which ran on said hardware. However, I was pleasantly surprised
Amazon's RDS now supports PostgreSQL too, so you can abstract away most of the
hardware considerations, assuming they've optimized their stack:
[https://aws.amazon.com/rds/postgresql/](https://aws.amazon.com/rds/postgresql/)

------
tuxychandru
If I have a bunch of records that need to be fetched together often and can't
be dumped in a hstore of JSON column, how can I ensure data locality on disk
in postgres?

Since primary key is clustered in MySQL, I choose an appropriate key. Is there
an equivalent mechanism or is periodically running the cluster command the
only option?

~~~
jeltz
You cannot do this other than relying on covering indexes and index only
scans. PostgreSQL does not support index organized tables.

------
mverwijs
" I see experienced enterprise software professionals who are versed in
operating systems and Windows, for example, taking the training to advance
their careers. "

That had me laughing.

