Standardising on PostgreSQL

[Kiran Jonnalagadda]

Hello list, been a while!

We lost track of this list while the team's discussions moved to HipChat and face-to-face, but it's time to make amends and be more active here.

Among the developments in the last few months is our increased reliance on key/value data models. We keep coming up with them all over the place, primarily because they are such a natural fit in Python and JavaScript.

I've been resisting the move to document storage databases such as MongoDB (or raw filesystems) as many other aspects of our data are best defined relationally, and trying to run two databases at once seems more trouble than it is worth.

So: PostgreSQL 9.3's new support for JSON columns (first introduced in 9.2) and the existing HSTORE column for string-only key/value pairs are starting to look very good. We are already using PostgreSQL in production (was MySQL earlier) and moving all our development environments from SQLite to PostgreSQL.

The question: should we commit to PostgreSQL and make use of HSTORE/JSON/ARRAY types?

Use cases:

1. Lastuser needs a way to store properties on User objects that are available to apps (avatar URL, etc)

2. Nodular could see better performance storing properties as HSTORE instead of as a separate table

3. Funnel needs a customizable form per event, which means data will be stored as JSON instead of in individual columns

There are many use cases where a column almost always has a single value, but occasionally has two -- for instance, authors on a blog post. Using secondary tables everywhere for such small possibilities is overkill and makes queries painful, while using an ARRAY makes more sense.

Your thoughts?

-- 

Kiran Jonnalagadda

+91-99452-35123

http://hasgeek.com

[Stefane Fermigier]

Hi Kiran,

I'm personally using Postgres as my production database, but I also like to run tests (unit and integration) using SQLite. I even sometimes end up with SQLite in the production sites, out of laziness.

I can understand that some of the new features of Postgres 9.3 are tempting to use, but at this point I'm fighting the urge and keep my code multi-platform (well, multi == 2 at this point), even if that means adding a little bit of complexity here and here.

Regards,

  S.

--
You received this message because you are subscribed to the Google Groups "HasGeek Code" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hasgeek-code...@googlegroups.com.
To post to this group, send email to hasgee...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hasgeek-code/9CEE9C5391474092AAE308931A272AF0%40hasgeek.in.
For more options, visit https://groups.google.com/groups/opt_out.

-- 
Stefane Fermigier - http://fermigier.com/ - http://twitter.com/sfermigier - http://linkedin.com/in/sfermigier
Founder & CEO, Abilian - Enterprise Social Software - http://www.abilian.com/
Founder & Board Member, Nuxeo - Open Source ECM - http://www.nuxeo.com/
Co-Founder and Chairman, Free&OSS Cluster - http://www.gt-logiciel-libre.org/
Co-Founder & Vice-President, National Council for Free&OSS - http://cnll.fr/
Vice President, Open World Forum 2013 - http://openworldforum.org/
"Well done is better than well said." - Benjamin Franklin
"There's no such thing as can't. You always have a choice." - Ken Gor
"Le vrai courage, c'est de faire ce qui est juste." - Dr Benjamin Justice

[Kiran Jonnalagadda]

On Wednesday, 25 September 2013 at 3:55 PM, Stefane Fermigier wrote:

I'm personally using Postgres as my production database, but I also like to run tests (unit and integration) using SQLite. I even sometimes end up with SQLite in the production sites, out of laziness.

I can understand that some of the new features of Postgres 9.3 are tempting to use, but at this point I'm fighting the urge and keep my code multi-platform (well, multi == 2 at this point), even if that means adding a little bit of complexity here and here.

Stefane, in our case we have two considerations:

1. We're increasingly dependent on key/value pairs as a critical data structure and putting each of them in a separate table row doesn't seem great for performance, and

2. We're slowing down as a development team because of the data modelling complexities.

The question therefore is really about whether anyone has objections to standardising on PostgreSQL. Do you see any scenarios where using PostgreSQL is not an option at all?

Shared hosting is an example, for instance, but how many of us are still dependent on MySQL-only shared hosting?

Kiran

[Kiran Jonnalagadda]

Just had my first panic moment with PostgreSQL. Look at the timestamps:

jace=# select now();

               now                

----------------------------------

 2013-09-26 00:54:36.333046+05:30

(1 row)

jace=# select now() at time zone 'utc';

          timezone          

----------------------------

 2013-09-25 19:25:33.475028

(1 row)

We've been using SQLAlchemy's func.now() as timestamp in coaster, on fields that don't store timezone. It appears PostgreSQL simply discards the timezone component in this case, so coaster's tests started failing when I ran them with PostgreSQL. I'm switching to datetime.utcnow until we can investigate this fully.

Our servers are configured to UTC, so there shouldn't be actual data loss from this.

In other news, I just pushed out an update to coaster that uses the JSON type for the JsonDict column if connected to PostgreSQL >= 9.2.

Kiran

-- 

Kiran Jonnalagadda

+91-99452-35123

http://hasgeek.com