371–378 of 378 posts
Earlier quoted context omitted.
He means a flat file of JSON records. Your sarcasm is unnecessary and uncharitable.
How do you know he means that?
For many use-cases, a full database isn't needed. And 5~10 years ago, "NoSQL" databases didn't exist: You just used a BDB as your key-value store.
It's not new and sexy, but it backs a lot of things.
Earlier quoted context omitted.
The sarcasm was perhaps a little mean but he has a point. If a flat text file storing JSON could replace your database, then you probably never needed a DB in the first place.
If you can use SQLlite, you probably didn't need a real database. So the use cases overlap a bit with flat-files and JSON.
Voted "other". We're using the App Engine master/slave datastore for getcloak.com. We're moving over to the HRD soon; the role of the HRD in App Engine's future wasn't clear when we started building our app.
How do you move data between datastores? Do you have to create a second app and migrate manually?
Earlier quoted context omitted.
How do you move data between datastores? Do you have to create a second app and migrate manually?
Yes; you can use their bulk dump/upload tools to help make it happen...
Earlier quoted context omitted.
If you can use SQLlite, you probably didn't need a real database. So the use cases overlap a bit with flat-files and JSON.
what's your cutoff line for something to be a "real database"? sqlite is fully acid compliant.
Earlier quoted context omitted.
I'm probably starting to sound like a broken record around here, but: 1. I build products on Oracle at a startup. 2. Oracle has many compelling features that really don't have first-class open source alternatives: OLAP, encryption (wire and at-rest), VPD, materialized views with query rewriting, object and document storage, monitoring and tracing, among others... 3. Oracle got more compelling since being offered--lic…
It is perplexing that Microsoft (presumably SQL-Server) has got so many votes. In my experience, absolutely nobody chooses to use it. They just have aggressive sales people who use it as a bargaining chip to elbow out Oracle and help win other business.
I've never had a bad experience with it, it needs a lot less maintenance than Oracle does (or did, at least), it performs pretty well (we're an insurance company with large datasets), is integrated in a .NET stack (while you might not want to go there, especially as a startup, it is very nice for corporate work), it came with Reporting Services which we used to replace Crystal Reports, it has lots of (admittedly non-standard) extremely useful SQL functions, data types, etc. And I love SQL Server Management Studio and the other Microsoft tools.
MySQL felt like a toy database in comparison (especially the management aspect), and the non-relational stuff is out of the question for now. We might not have done due diligence by not looking at other alternatives (notably Postgre I guess) but they seem like a poor fit given our developer's strong Microsoft-centric backgrounds. Most of us also have at least one Microsoft training course in their SQL server, and they have very strong support in my country (Uruguay) against nonexistant for most other platforms.
I'd vote for PostgreSQL multiple times if I could. I'm a consultant DBA (-ish; I do other stuff as well, but that's what puts most of the food on my table), and have multiple clients using pg.
Why do you use pgsql over mysql? I've used mysql a lot, and pgsql a little. I can't tell the difference, other than pgsql being slower and having less support. Some people swear by it, so I'm curious what I am missing.
Some features that make Postgres awesome:
* Transactional DDL. You can do "create table" in a transaction. _Everything_ is transactional, it's not a tacked-on feature, it's the basis of everything.
* No legacy cruft. Compared to MySQL, which is filled to the brim with historical warts. The Postgres people have been careful to weed out obsolete functionality. There are essentially no sneaky border cases that a developer needs to be aware of, no weird special cases like "0000-00-00 00:00" having special meaning.
* No need for a "strict" mode, since Postgres is always strict. Postgres doesn't allow invalid dates, doesn't allow byte sequences that violate character encodings, etc. It diligently enforces contraints and generally doesn't allow you to screw up. To Postgres, data integrity is paramount.
* PostGIS. Simply awesome. (MySQL's geospatial stuff also tries to implement the OGC API, but last I looked, it was a half-hearted attempt that negelcted to provide the fast R-tree-based (actually GiST-based) indexing that makes PostGIS so super fast.)
* Replication. It's late to the party, but I rather prefer how Postgres has implemented its replication, even though it has some downsides where it will abort a long-running query if some data has changed under its feet (but if you're using transactions it's easy to simply restart the query). 9.1 will be getting synchronous replication, which is pretty cool.
* Extensions. Postgres can integrate languages like R and Ruby as first-class languages that can be called from SQL. It also has a module system that can extend the type system (a bit of trivia: This was originally the main reason why Michael Stonebraker invented Postgres) with new types, eg. multidimensional matrix columns, or new features, like remote tables.
* The "text" type. Seriously, why should do people keep writing things like varchar(255)? Postgres' text type is an unlimited string. Unlike MySQL's text type, it can be efficiently indexed without limitations. (Varchar is internally implemented as a bounded text type.)
* Cost-based planner backed by row-level statistics. This is the stuff that allows Postgres to do complex nested queries and still perform incredibly well.
* Partial indexes. You can do something like "create index ... on themes (name) where color = 'blue'". Whenever you do a query that falls within the expressions's range, Postgres will use that index, potentially vastly reducing the search space.
* Functional indexes. You can do something like "create index ... on (lower(name))". If you then do a query such as "select ... where lower(name) = 'xyz'", then Postgres will recognize that it's the same expression, and it will be able to use the index.
* Windowing functions and recursive queries, both from ANSI SQL99 iirc. Look this up, they're great.
There are some bad points, none of them significant and all of them a matter of taste:
- I have never really liked Postgres' text indexing, which feels a bit creaky and antique. At least with 8.x, GIN index updating was slow as hell.
- Partitioned tables are a great feature, but I will never use it because of the requirement that one does the plumbing yourself (creating partitioning rules and so on); I keep waiting for something like Oracles's automatic partitioning.
- Stored procedures -- ie., running logic inside the database -- feels wrong to me, and always has. For some people this is a requirement, so I'm not really complaining. In some cases, writing a stored procedure can be essential to speed up queries/operations by saving on database roundtrips.
- Still no "upsert" SQL command (aka "insert or replace", "insert or update") for asserting the existence of a row atomically.