How times have changed for PostgreSQL
opensource.com
opensource.com
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.
For example, 'str1' + 'str2' results in '0' and a warning in the default mysql config. A real rdbms should fail and tell you + is not for string concatenation in that system instead of running your query and perhaps overwriting your data and then warning you about it after the fact ('That query you just ran is broken, but we overwrote your data anyways - hey, here's a warning.').
Broken, horrible design.
Auto-increment primary keys get reused when, say, you create a record, then erase it, then pull the power plug. Mysql does not save the highest number used, meaning that you turn on the machine again, add a new record, and it has the same ID as the previous one.
My very subjective impression is that every time I have anything to do with Mysql, sooner or later, some weird thing like that jumps out and bites someone in the ass.
http://stackoverflow.com/questions/3718229/stop-mysql-reusin...
http://dba.stackexchange.com/questions/16602/prevent-reset-o...
Like I said before, you can even create an empty table and define the auto increment to be 10000 if you wanted. So there is a persistent value that's stored. However I suspect the issue here is that I'm using a different MySQL engine to yourself.
Granted, Mysql is catching up in terms of features like that, but for a long time, many people were using it without them (MYISAM).
That said, for all non trivial work loads, the latest postgres is quite the work horse, but of course requires some tuning for performance. We ended up switch to it after MySQL consistently sucked on smaller joins.
Can't argue on the replication deal: it's a work in progress.
At the cost of ACID. In my experience PostgreSQL is faster than MySQL-InnoDB.
Apparently it's decently fast in 9.4, but... that's been 20+ years where it's not been anywhere near as fast as MySQL for most common use cases (blogging, forums, etc where paginating records is common).
In my experience over the years, on any moderate sized db (more than, say, 100k rows in a table) select count() has always been faster under mysql - both myisam and innodb (although innodb doesn't claim to be 100% accurate all the time).
Why should I* have to keep a computed column when the core engine has all the data all the time? And it's something pretty fundamental to the data - how much of it is actually in there.
>Why should I have to keep a computed column when the core engine has all the data all the time?
The core engine does not have that information, keeping that information for no reason would be foolish. You should keep a computed column for performance, obviously. That is your complaint remember? How is this any different than "select users.id, users.name, count(photos.id) from users left join photos on photos.user_id = users.id where users.id = ?" being slow? How do you solve that being slow? You use a computed column. The fact that you complain about a complete non-issue because you inexplicably refuse to use the standard solution to the problem in this one particular instance of the general pattern is neither logical nor reasonable.
Experts understand that "estimate the number of rows" and "count the number of rows" are two different operations. They know that "count()" is supposed to count, not estimate and that in MySQL it does an estimate instead. They even know a simple way to provide quick estimates.
Non-experts know that "count()" is faster in one tool than another.
select n_live_tup from pg_stat_user_tables where relname = 'mytable';
Use MySQL when your application is incompatible with other databases. 99 times out of 100, that means that the database has inconsistencies, and the application depends on them, so be carefull when doing that.
- 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)
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.
The advantage of having your fulltext search inside your database is huge though and can outweigh the current disadvantages. For example you do not have to manage another database, indexing can be transactional and instant, and you can can have both fulltext search and normal SQL stuff in the same query (often reducing latency).
Yeah, right. Partitioning in PostgreSQL is as braindead as setting sequence value in Oracle. IMHO the devs made wrong choice: https://wiki.postgresql.org/wiki/Table_partitioning#Possible... . You want a partition for every month of stored data? Prepare to copy & paste a lot as you need a set of triggers for all child tables. Oh, the table You are spiting was referenced as foreign keys in other tables? Drop the constraint, it isn't supported even if is the same column you are partitioning over.
I run a Postgres instance that partitions daily (for reasons of space management). When creating the partitions in advance, it is easy enough to alter the trigger (or a function called by the trigger) in the same script that creates the tables.
DDL via copy-edit-paste is just asking for problems anyway. Those operations should be automated.
Of course, if your DBAs (or whatever passes for a DBA in your outfit) are conversant in both already -- or if they are equally clueless with both -- this doesn't save you much.
This blew my mind when I switched from MySQL (which doesn't even support transactions for inserts/updates in most engines).
I've had issues with Postgres in the past, but it's trustworthy software developed by a trustworthy team.
strict treatment of invalid data
mysql views with aggregates DISTINCT GROUP BY HAVING LIMIT UNION or UNION ALL Subquery in the select list are too slow to be useable
mysql has no check constraints
mysql has no conditional/expression indexes
mysql only has broken geospatial support
mysql has no full text indexing on innodb tables
postgresql has an extensible type system (and booleans!)
postgresql has sequences
postgresql has table inheritance
mysql triggers don't fire on cascaded actions or replication
mysql has no window functions
mysql can't set default values to be a function (there is a workaround for only dates, and only one per table)
postgresql has schemas
postgresql has a transactional DDL (you can roll back an alter table)
rollbacks in mysql are incredibly slow, and if you cancel one it can corrupt the table
mysql limit can't accept variables!?
mysql subqueries are limited: can't modify and select from the same table
mysql stored procedures can't have default args
mysql functions (and thus triggers) can't use prepare/execute, so no dynamic sql
mysql functions can't be called recursively
mysql triggers can't alter the table they are being called against
mysql stored procedures can't be called from dynamic sql (prepare)
mysql can't log error producing queries
mysql slow query log has a resolution of seconds
it has this in recent versions, but it's shockingly bad at matching.
Further I've written a couple of posts that don't directly compare to MySQL, but many of the points are pretty relevant for MySQL vs. Postgres
http://www.craigkerstiens.com/2012/04/30/why-postgres/
http://www.craigkerstiens.com/2012/05/07/why-postgres-part-2/And yes, I think typically orgs. today are using SQL Server or Oracle for these databases.
There's this thing I wish existed but AFAIK doesn't: a guide that shows computer people the relative salaries of a bunch of different specialties. So people who have one specialty but would enjoy something else can see what the salaries are like.
Glassdoor is a very rough start on this.
For postgres itself, make sure you know what all the settings in the configuration means, why they make sense 90% of the time and definitely do not make sense in 10% of the time (such as a low memory server with super-fast SSD disk arrays). And in general ofcourse a good knowledge of SQL, index usage/performance. Postgres extensions (arrays, JSON, etc.) are decent, but in my experience it's something you can get into relatively fast if you are solid on the rest.
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.
Normaly a job is not so specific that you can't start into some path like that, and once you are on the path, you can change positions.
But, if your job is that specific, well, that sucks. You'll have to build something on your free time.
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.
Play a space battle MMO, but using SQL. The game is also completely open source so if you want to find out how to write an entire application layer in PostgreSQL, it is a pretty good example of that.
http://www.craigkerstiens.com/categories/postgres/
http://postgresguide.com/
http://www.postgresweekly.comHere'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.
[1] Generic Data Model: http://c2.com/cgi/wiki?GenericDataModel
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.
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
NB There is nothing "wrong" with using EAV - just that it seems prone to misuse (a bit like XML).
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!)
I've seen systems that used XML in a database column and were perfectly sensible, I've also seen systems that did terrible things with XML in a database (e.g. using complex XML string as part of a query).
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.
So they built Salesforce basically...
Totally insane but it sort of works.
If you really need to separate data by customer then consider using PostgreSQL schemas. But I'd steer away from any solution that involves adding and dropping columns for different customers.
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?
That had me laughing.