Slashdot Mirror

Why are you using a spreadsheet when you have that much data?

Because we don't have a license for an SQL server or an IT department prepared to support a free one. Duh.

So you can afford a license for MS Excel but not pay nothing for SQLite or PostgreSQL?

Oh, I "forgot" you said you had no IT department to help you with the free one... But you have an IT department helping you with all your Excel problems?

Or you don't? How the **** are you handling all the Excel problems then? If you don't have any, great, your workplace seems to be a place full of Excel wizards. But if they are, they should be able to learn how to use a SQL-engine/server without much trouble.

Learn to use the right tool for the job instead of using shitty tools. After the initial period of learning you will wonder why you were such complete and utter idiots for all those years, when you could have done real work instead in a fraction of the time and with much less headache from trying to debug poorly designed spreadsheets.

SQL doesn't mean having to store it on a server somewhere.

Yes, it does. If the SQL server software happens to be running on your desktop then it's a server, especially if you expect other people to access the data.

No, SQLite is "server-less", so there are options if you don't want "servers".

But servers aren't something evil, why are you so obsessed with not having servers? You are right in that any computer serving something to clients are technically servers. But in reality when you talk about servers you mostly mean dedicated servers, servers which is not used as a workstation for someone else.

My computer at my old work was always turned on, so for a while I ran FileMaker to share data with my coworkers, and later my PostgreSQL server which replaced FileMaker on it. When everyone realized it was really helpful to share date we got it a new home on a "real server". But those can be pretty much anything with a little computing power. Heck, you could run PostgreSQL on a Raspberry Pi if you wanted something that is out of the way, doesn't draw much power and doesn't generates noise. I wouldn't recommend it, but there are all kinds of solutions, and I bet you have some computers unused somewhere that could be used.

If you have machines capable of running Excel, you can sure as heck let them run some kind of SQL-server.

Then you've not looked very hard. Things that are very syscall intensive, like du across a filesystem, have been shown to pay a 50% tax. Tun/tap stuff in userspace pays a ~35% tax. Or if you want something higher level:

https://www.postgresql.org/mes...

Trivial PostgreSQL txns (where you don't end up IO or scanning bound, but instead are measuring system call path) pay a 17% tax. On Skylake. Where the penalty is comparatively less.

Some PostgreSQL results have just been released: up to 23% performance loss. This is indeed huge.

I remember this came up on Postgres mailing lists earlier this year: https://www.postgresql.org/mes... Perhaps someone can code some machine learning code-converter to do it :-p

My experience is that simply occasionally check in to see if the Customer found a solution, then they ask for details so they can feed their offshore help desk database.

Nevertheless, I have not used them myself for at least a decade so I have not idea if that got better or worse. Postgresql https://www.postgresql.org/ does everything I want, and does it better.

You've been able to use Python for a while in Postgres, MySQL, SQLite, and even DB2.

I can't quite figure out why anybody would want to use Microsoft SQL Server.

PostgreSQL has had Python support for years. It also has R support.

But to be fair to SQL Server, it is a very good RDBMS. Both SQL Server and PostgreSQL make MySQL look really, really, really bad. Even SQLite is making MySQL look pathetic these days!

PostgreSQL has had Python support for years. It also has R support.

But to be fair to SQL Server, it is a very good RDBMS. Both SQL Server and PostgreSQL make MySQL look really, really, really bad. Even SQLite is making MySQL look pathetic these days!

Compared to the people who did openssl, who started out (and to an extent still are!) laypeople rolling their own encryption. Of course, by now they have a large body of work and presumably learned a thing or two from their sometimes very public mistakes. But the sheer size leads to problems too, witness the rationale for "libressl" (a project in turn run by self-styled "experts" in securing C in specific ways but who aren't that great with crypto either, o delicious recursive irony).

On a similar-but-different note, I can get why mozilla is "doing" rust and why torbrowser is following suit. Have you looked at their code? rust may well end up an improvement for them. Which is not to say that using rust is a good idea, just that them using rust is a better idea than them using the "C/C++" bastard mix they've been using so far. Even with rust being a safe space language with a code of conduct against microaggressions and all that.

Compare with this line of thinking:

Is anyone working on porting PostgreSQL to Rust? [...]
My motivation is primarily I don't want to learn all the over-complicated details of C, but at the same time I would like to be productive in a safe system language, a category in which Rust seems to be alone.

In other words, "please someone else please do lots of hard but pointless work for me so that I may pretend to be productive."

But who knows? Maybe something good will come out of throwing rust at the torbrowser project. I for one, etc.

In postgres, you could put NOTIFY in a trigger for when your table is updated and have your software LISTEN to be notified when the table has changed.

In postgres, you could put NOTIFY in a trigger for when your table is updated and have your software LISTEN to be notified when the table has changed.

postgresql is still the best. Can be slow if configured incorrectly but it's otherwise an amazing database. https://www.postgresql.org/

Why?

Since about 2004, I've at least 4 times searched for PostgreSQL vs MySQL. Each time PostgreSQL came ahead in most areas, including referential integrity, fewer gotcha's in the use of NOT NULL and other SQL features.

I've worked with both, and find PostgreSQL easier to setup, manage, and to query.

Have a look at:
https://wiki.postgresql.org/wi...
http://insights.dice.com/2015/...
MySQL vs PostgreSQL - Why you shouldn't use MySQL: https://www.youtube.com/watch?...

Best to think about what is important in YOUR project given YOUR situation, and do YOUR own search. Note that the pros & cons forever change!

What is applicable in one situation, may not apply to another. However, I would expect PostgreSQL to be the better choice in most situations.

So people like to put expensive DBs on expensive servers?

Anyway, I have no idea what that web site is trying to say. I can't find a single mention of Postgres anywhere. It's all MSSQL, Sybase, Oracle, DB2, and some databases I've never heard of. Does the benchmarking software only run on those databases or something?

Check out the why: https://wiki.postgresql.org/wi... The entire industry including some open source databases target this benchmark. Obviously you don't have to play ball, but others are doing.

Splitting a query into parallel chunks only helps when your database server is idle, you know.

Many applications do not have many clients at the same time, but require peak performance. An example is GIS applications, the more obvious is crunching data for visualisation.

It sounds to me that your needs are different than what the rest of us expect from a database. Data warehousing perhaps?

We are talking about software that can handle hundreds of gigabytes of data. But the parent suggest PostgreSQL as example for the diversity SQL Server has to offer. If some article on PostgreSQL is written and someone replies SQLite does all that... what will your reply be?

Emphasis mine:

In PostgresSQL, Oracle, and SQL Server, MERGE is not atomic.

PostgreSQL still doesn't provide MERGE . Perhaps you meant to write "DB2" instead?

- T

It was not part of "traditional" SQL and is rather new.

That's because UPSERT is a poor substitute for standard SQL MERGE INTO. While I'm no fan of Larry Ellison, Oracle gets MERGE INTO right (except for fine-grained access control). So do DB2 and MSSQL, though both offer non-standard extensions. I'm actually disappointed with this addition to PostgreSQL, because it likely means that support for MERGE INTO will be delayed even longer than it already has, perhaps indefinitely, or discarded entirely.

Since 9.1, I would achieve the effects of MERGE INTO using a writable CTE, though the documentation still does not explicitly state whether the entire query, including the WITH clause, is guaranteed to be atomic and consistent. I have always assumed there must be some gotchas, otherwise the PostgreSQL team could have implemented MERGE INTO simply by means of a syntactic rewrite into a corresponding writable CTE.

- T

It's sad that not only is PostgreSQL over a decade behind but they implemented the sloppy non-ANSI standard version.

You mean as opposed to the sloppy ANSI standard version that makes no guarantees about concurrency behaviour, something that Postgres's new feature goes to considerable lengths to handle correctly and intuitively?

It's sad that not only is PostgreSQL over a decade behind but they implemented the sloppy non-ANSI standard version.

You mean as opposed to the sloppy ANSI standard version that makes no guarantees about concurrency behaviour, something that Postgres's new feature goes to considerable lengths to handle correctly and intuitively?

Although PostgreSQL claimed SQL compliance

Liar

What's broken with the transaction isolation model? The only thing I see is that they don't do the non-transaction "read uncommitted" transaction that lets you see records that other transactions have not committed.

If someone has to be told to put a mult-statement SQL write operation in a transaction...

I kind of think that transactions go without saying, even for UPSERT (according to the Wiki page, UPSERT will "guarantee insert-or-update 'atomicity' for the simple cases", but leaves me questioning what a "simple case" is).

I don't know about you but postgresql's built-in regex support is good enough for me:
http://www.postgresql.org/docs...

Yes. The company 2ndQuadrant created something called Postgres BDR, and it is designed specifically for geographically diverse master-master replication.

Here are more details.

Depends on what kind of replication you need.

It does pretty decent asynchronous master->slave replication.

You can also have a mirror with synchronous writes.

Multi-master replication- there are some 3rd party tools to do that, still pretty young and immature AFAIK.

Clustering- there are some 3rd party projects to do that, some commercial.

More info here: https://wiki.postgresql.org/wi...

--Coder

Postgres also has a lot of nice features. For example, I love table inheritance and don't understand why it's not more common of a feature - I probably use it in about 80% of the databases I make these days. It's just so logical and useful for real-world data (which often has at least some degree of heirarchial structure), and avoids having to hack together triggers or query logic to emulate it.

For a project I'm working on, I started to play around with the OpenStreetMap data as a source for locations (from a guy who's never used GIS info systems), so I think I'd be a good insight into getting started with using this great resource.

Notes:
- I develop in Java mostly, but I have a generally well rounded skill set.

Firstly, I had to make the jump to Postgress and PostGIS, which are annoying to setup if you're not familiar with them. I had a MySQL instance running, but for the life of me, I couldn't get osmosis to import before getting the setup just right, which unfortunately wasnt' as simple and stright forward as I'd have liked to see in any docs. So after finally banging PostGIS over the head enough to accept the import, I was hit with a huge knowledge gap on how to actually access spatial and hstore based data. Admittedly, once you get the handle of them, the SQL access the data is quite expressive and powerful.

For DB imports, I used Osmosis for data import. I couldn't find any stand-alone Java based libaries for actually using the DB data which would help a lot (maybe I'll end up writing an open source one if it doesn't already exist). So, I basically dropped down to writing PostGIS based SQL queries, which is really quite expressive and well structured when the data is good (depends on the world region, mostly good for North America from what I found so far).

Secondly, there was the OpenStreetMap data itself. As someone who primarily wants to work on geographic barriers and political boundaries, there's a big disconnect between the polygons of the system and the political ones. Generally, there's always a node (think of a pin on a map) to represent a proper place name (New york city for instance) and a polygon that encompass what New York's political boundaries are, but quite often there won't be explicit ties between the two, so you're left with bridging the two yourself constructing queries for where nodes are within city / state / country / etc.. Anyways, thats as far as I've gotten so far, so good luck!

Some links that helped me:
http://www.postgresql.org/docs...
http://postgis.net/docs/manual...
http://wiki.openstreetmap.org/... (Make sure to read carefully, becase its rather unforgiving and terse about bad environment setups)

Postgres stopped using OIDs for regular tables in version 8.1, many years ago. You can force the old behavior with default_with_oids, but no one does that anymore. OIDs haven't been needed for referential integrity in quite a while. Only the system tables still use them to connect tables on a typical server, which is mainly because those need to be read during bootstrapping before all the SQL features are available.

Oracle has features like Edition-Based Redefinition for this job. They are a lot more work for the simple job of transactional DDL.

There's an old but accurate at the time page comparing this feature across databases at Transactional DDL in PostgreSQL: A Competitive Analysis.

> I don't know if PostgreSQL

Does this help?

see http://www.postgresql.org/docs...

Just about every modern DB engine has something like it. If you are not even aware of it you should really learn more of your tool set.

That's a nice outline of Postgres features; small and very pedantic correction for you. CREATE INDEX CONCURRENTLY in PostgreSQL isn't really asynchronous, since the client running it is stuck there waiting for it. And it does still need a full table lock to complete. It just only needs that for a brief moment in most cases, to install the index when it's built. But that's not guaranteed. I added a caveat to the docs a version or two ago that warns about the bad case; see building indexes concurrently, in particular the bit starting with "Any transaction active when the second table scan starts..." It's really rare that happens, but if you have long-running transactions eventually you'll run into it painfully.

I would restate the situation as "You can even create indexes with minimal locking of large tables when a new index is added". The code can't quite avoid locks altogether and remain transaction safe.

Let me look that up for you.

Point is -- there's no magic. ACID and network together means you have to go either with 2PC or with eventual consistency (thus getting just half-a-C from ACID). Of course, a well paid salesperson will know how to handwave that away.

I'd say that (perhaps besides really big iron), PostgreSQL is on a par with Oracle (and much easier to install and handle.

MySQL? Pfeh.

PostgreSQL clustering, replication, and connection pooling are all jokes compared to Oracle.

No. They all really are.

PostgreSQL doesn't do true database clusters - it's all based on replication from a single master database. That's not a cluster. Not only that, failover reliability absolutely sucks because even the "best" option listed in your link - pgpool - is a giant flaming turd. (Don't think so? Set up a pgpool/PostgreSQL cluster and start doing things like pulling network cables and turning machines off and watch the "cluster" fall apart. I've done it. Problems like pgpool processes would hang waiting for a signal from a process that pgpool itself had already shut down. My open-source-enamored customer was not happy that they wound up paying me more to prove PostgreSQL wouldn't work than it would have cost just to buy Oracle licenses in the first place...)

PostgreSQL doesn't do active-active shared-storage clusters. So you need to pay for two or three times more online disk storage because of that. Oracle can use one set of backend shared storage for all servers in the cluster. If you have to buy 1,500 terabytes of enterprise-level redundant disk storage instead of 500 TB for your three-server cluster serving half a petabyte of data because PostgreSQL doesn't do shared storage, that license fee you pay for the Oracle database is small potatoes compared to the cost of that extra fucking PETABYTE of disks that the limitations of PostgreSQL force you to buy.

And for databases that big, PostgreSQL is a lot slower.

Now, add back in the fact I mentioned above that pgpool/PostgreSQL "clusters" are not reliable. Period - they're not.

That's who Oracle's customers are. And once you get a few of those in house, why bother with other types of solutions? You have the Oracle expertise in house (or otherwise available) and your Oracle DBA/server SA staff can install/maintain small Oracle databases in their sleep. Or just make your 100 MB tiny DB available on the 500 TB big iron database in half an hour.

And if you've never worked on databases like that, your experience is with toys.

Oracle's competition isn't PostgreSQL - get over your grass-roots open-source fetish. It's DB2 and now - thanks to funding from Google and other large corporations - NoSQL databases.

Let me look that up for you.

Point is -- there's no magic. ACID and network together means you have to go either with 2PC or with eventual consistency (thus getting just half-a-C from ACID). Of course, a well paid salesperson will know how to handwave that away.

I'd say that (perhaps besides really big iron), PostgreSQL is on a par with Oracle (and much easier to install and handle.

MySQL? Pfeh.

PostgreSQL and Firebird make Oracle and MySQL look like pathetic jokes.

Postgres-XL is currently compatible with Postgres 9.2. Postgres 9.3 introduces better support for JSON data that bring it closer to the functionality of a NoSQL DB. It looks like XL will be compatible with 9.3 in the near future.

For another project that has solid documentation, see PostgreSQL. Highly recommended, both the database and the documentation.

Not everywhere. One free software project has the best documentation I've ever seen. We need to point people at shining examples of excellent documentation so they can realize how important it is.

OpenSSL is used to add SSL support when compiling PostgreSQL on Windows. It's a constant headache to the developers and packagers of the database. We were all complaining about how much the OpenSSL license sucks, too, before it was cool to rag on OpenSSL.

You're incorrect. Please see Cyclic Tag System and Turing Machine in SQL.

Well, yes and no. PostgreSQL had a text-only JSON data type since long time, and was able to index keys using expression indexes. That's nothing new.

The 9.4 improvements are that the (a) JSONB is stored in a binary form, and (b) a lot of ideas from HSTORE data type, plus new ones were implemented. That means that you can create "universal" index without prior knowledge of what keys will be interesting. So then you can ask for data containing arbitrary keys, sets of keys, values, documents etc. See http://www.postgresql.org/docs...

Sure, it's not perfect and the index may get somehow big, but well ...

A few hundred million rows is no trouble to PostgreSQL, if configured right. And if you go beyond that there are some great ways to deal with the problem:

1. Partitioning: Make a large table composed of smaller subset tables. This is a great way to deal with what is primarily historical data, since you can partition by month, quarter, or whatever time period makes sense for your application. Then, when it comes time to archive or delete old data, all you have to do is migrate that month's table to the archive location, or just drop it. MUCH less expensive than a DELETE with a WHERE clause.

2. BigSQL: if you want the power of NoSQL but the querying ability of PostgreSQL, check out this package.

3. If you are starting to get serious data, hopefully you are making serious money. There are scores of commercial entities that can help you get a lot more performance out of PostgreSQL. Some of them have add-ons for performance, or have just gotten a lot of experience and good ideas on how to deisgn a solution.

These steps may sound like a pain, but NoSQL brings all sorts of pain with it, also. Limited querying ability, many extra measures required for data integrity, stability issues... bizarre limitations in some areas... Think these things through carefully, and don't fall for anyone's hype.

no idea, but I know Postgresql has had JSON columns for a while now, so you get the benefit of 'typeless' data storage (ie a blob of JSON data) and all the benefit of relational data if you want it (as its just another column).

MariaDB did it differently, merging Cassandra as a storage back-end, and "dynamic columns' so you can have different columns of data per row in a table. (and you can get all the dynamic column data out as a JSON blob).

EnterpriseDB is an important part of PostgreSQL development with several contributors, but they still work within the larger development community of contributors. There are other companies with just as many contributors, with one example being how 2ndQuadrant is adding logical replication features.

One way you can tell if an open source project has a real community is whether the project would go on even if the largest company contributing code disappeared. Linux would survive RedHat disappearing, and PostgreSQL would certainly survive EDB going out of business. That's not even a theoretical question, because the PostgreSQL community is informed by having seen it happen once already. A company named Great Bridge hired a good percentage of the PostgreSQL community once, and then failed after running out of VC cash.

EnterpriseDB is an important part of PostgreSQL development with several contributors, but they still work within the larger development community of contributors. There are other companies with just as many contributors, with one example being how 2ndQuadrant is adding logical replication features.

One way you can tell if an open source project has a real community is whether the project would go on even if the largest company contributing code disappeared. Linux would survive RedHat disappearing, and PostgreSQL would certainly survive EDB going out of business. That's not even a theoretical question, because the PostgreSQL community is informed by having seen it happen once already. A company named Great Bridge hired a good percentage of the PostgreSQL community once, and then failed after running out of VC cash.

I don't know what database technology they are using but if they are using SQL, they are probably using an ENUM: http://docs.oracle.com/cd/E179... https://dev.mysql.com/doc/refm... http://www.postgresql.org/docs...

The caveat to the rest of this is that I'm somewhat new to the web development frontier. (a yearish of unpaid experimentation)

Having said that here are a few technologies/languages that I find highly useful, perhaps you will too.

Redis, everyone needs a cache/queue, if someone says otherwise they're lying to you. The really cool part about redis is that operations done with it are (mostly) atomic at the operating system level, and it has persistent records of transactions, which sets it far apart from its other competitors. Redis is something I would use in any sort of web application backend.

At least one of: rails/vibe.d/yesod/spray. These tools, while not ideal solutions, are extremely useful for maintaining separation of concerns, as well as rapid prototyping, I would consider knowing some framework essential.

Ember.js/angular.js are both javascript frameworks that are extremely powerful, the necessity of these increases with the more interactivity you want, they're probably a bit overkill for most webapps, but I'd definitely recommend looking into one of these at some point.

Foundation/Bootstrap are frontend frameworks for easily organizing the layout of your pages and styling them, again, for simple projects these will probably add more complexity than necessary to the project, but these are another thing I would consider essential.

Postgresql is a rdbms similar to Oracle that has very powerful/fascinating features that no other db system has such as hstore which is essentially a key value store that allows you to input whatever in a column, which is great for unstructured/semi-structured data (e.g. user uploads). Mongo can't beat an ACID compliant json store. ;)

I could go on for a while in this direction, but I hope this helps.

Personally, I use rails/ember/foundation for frontend stuff and use spray/redis/postgres for the backend, they seem to fit my workflow the best and solve the problem at hand the most effectively.

The caveat to the rest of this is that I'm somewhat new to the web development frontier. (a yearish of unpaid experimentation)

Having said that here are a few technologies/languages that I find highly useful, perhaps you will too.

Redis, everyone needs a cache/queue, if someone says otherwise they're lying to you. The really cool part about redis is that operations done with it are (mostly) atomic at the operating system level, and it has persistent records of transactions, which sets it far apart from its other competitors. Redis is something I would use in any sort of web application backend.

At least one of: rails/vibe.d/yesod/spray. These tools, while not ideal solutions, are extremely useful for maintaining separation of concerns, as well as rapid prototyping, I would consider knowing some framework essential.

Ember.js/angular.js are both javascript frameworks that are extremely powerful, the necessity of these increases with the more interactivity you want, they're probably a bit overkill for most webapps, but I'd definitely recommend looking into one of these at some point.

Foundation/Bootstrap are frontend frameworks for easily organizing the layout of your pages and styling them, again, for simple projects these will probably add more complexity than necessary to the project, but these are another thing I would consider essential.

Postgresql is a rdbms similar to Oracle that has very powerful/fascinating features that no other db system has such as hstore which is essentially a key value store that allows you to input whatever in a column, which is great for unstructured/semi-structured data (e.g. user uploads). Mongo can't beat an ACID compliant json store. ;)

I could go on for a while in this direction, but I hope this helps.

Personally, I use rails/ember/foundation for frontend stuff and use spray/redis/postgres for the backend, they seem to fit my workflow the best and solve the problem at hand the most effectively.

Why in this day and age, would you pay or even BOTHER with those toolsets when you can almost certainly design and build such a system for far less, and better technology (Eclipse, PostGRES, LINUX, ANDROID) using far less hardware?

The GIS extensions in Java and PostGRES are fab.

You can check that out at:

http://www.postgresql.org/about/news/1387/

-Hackus

You might want to look at http://www.postgresql.org/ - I've done at least 5 intensive searches on the Internet over the last 14 years, and each time it postgres has come ahead of MySQL in performance, reliability, ease of use etc. I did some DBA & Java programming in MySQL in one job, and have a client with MySQL.

Where I have a free choice, I use postgres.

http://wiki.postgresql.org/wiki/PGStrom