What database do you use?

[Tim_S]

I'm reviewing how we're storing data and looking at databases. As I'm looking through the hundreds of database options out there I began wondering what other people are using for databases and what they are using it for.

Starting off... Currently using files for local test record storage, but also use SQL Server to aggregate data.

[ShaunR]

Locally, SQLite. Remotely, MySQL/MariaDB.

Edited July 19, 2017 by ShaunR

[smithd]

well locally I think the only real option is sqlite.

For a central server, unless you have strenuous requirements it probably doesn't matter. If you need to write to a database from a vxworks crio, then mysql/maria is right since they have a simple raw tcp connector. Postgres seems to be the current favorite. For time series data I dont have an answer, I looked around as well and wrote what I found here:

The one I'm most excited about is https://www.timescale.com/ which is built on top of postgres, but another interesting option is mariadb's columnstore format. The disclaimer to this is that I have TBs of data to handle, and if you don't I'd just use postgres or mysql/maria and not worry.

[ShaunR]

42 minutes ago, smithd said:

For time series data I dont have an answer, I looked around as well and wrote what I found here:

Yup. No ideal solutions for a fast, searchable time series database. The best I have used so far is SQLite. It can have 32k columns so does for most things I have to deal with. Inserting 32k into a row at a time isn't all that quick, though. 10k points is usually an acceptable trade-off for dumping graphs.

[drjdpowell]

14 hours ago, Tim_S said:

Starting off... Currently using files for local test record storage, but also use SQL Server to aggregate data.

I’m using JSON for test record storage.  That can be stored locally, but can also be easily loaded into a JSON-aware database (including SQLite, Postgres and MySQL).  Then one can query against particular items in the JSON.  I’ve done this with the JSON_tree function of SQLite and it worked quite well.   

[drjdpowell]

I asked a similar question a year ago.   I decided on PostgreSQL, though my project that needs it was put on hold.

[Tim_S]

Appreciating people's responses.

2 hours ago, drjdpowell said:

I asked a similar question a year ago.

Thanks for the link!

[Tim_S]

On 7/20/2017 at 7:40 AM, drjdpowell said:

I asked a similar question a year ago.   I decided on PostgreSQL, though my project that needs it was put on hold.

Did you try to get ODBC working with PostgreSQL? Having some out of the box issues with that and see you posted a communication package you were working on in the other thread.

[drjdpowell]

On July 25, 2017 at 2:58 PM, Tim_S said:

Did you try to get ODBC working with PostgreSQL? Having some out of the box issues with that and see you posted a communication package you were working on in the other thread.

I didn’t try ODBC, as I was using pq, a dll that takes to Postgres more directly.  I would think that ODBC to Postgres would work, though.

[Tim_S]

On 7/29/2017 at 6:14 PM, drjdpowell said:

I didn’t try ODBC, as I was using pq, a dll that takes to Postgres more directly.  I would think that ODBC to Postgres would work, though.

Thanks.

I'm assuming someone will want to mine data outside of what I provide, so ODBC is part of my requirements.

I tested with PostgreSQL 9.6 64-bit. The 64-bit ODBC would setup in Windows ODBC Data Source Administrator and test as working. Soon as I tried using it I got an error of version incompatibility (64-bit ODBC with 32-bit application). I tried the 32-bit ODBC driver and that worked OK. There was strange behavior with the SELECT and INSERT statements any time I tried specifying a column in a table where the error indicated the column was not part of the table (happens even in pgAdmin 4). Could be corruption, bug in something I did, corporate IT settings, butterfly flapped its wings... Not sure what is going on.

MariaDB 10.2 has worked out of the box.

There are features I prefer in PostgreSQL (better documentation, built-in backup and recovery strategies, method of accessing information in JSON...) but it's (at least initially) more complicated to set up. HeidiDB has a more straightforward interface, but it looks like installing plugins to get the features I'm looking for is going to be interesting (there's documentation on how, but what I've found is written for UNIX/Linux rather than Windows though that seems to be true of much of PostgreSQL as well).

[Stagg54]

I've used Postrgres with ODBC and I've had no issues (other than the 32/64 bit problems that it seems you have already figured out).

As far as installation and setting it up, I just download the turnkey linux appliance.  It already has everything set up. 

 

[Tim_S]

17 hours ago, Stagg54 said:

I've used Postrgres with ODBC and I've had no issues (other than the 32/64 bit problems that it seems you have already figured out).

As far as installation and setting it up, I just download the turnkey linux appliance.  It already has everything set up. 

 

I'm using Windows, so I wonder if there is an issue with the build in the installer for Windows rather than the Linux packages. Did a uninstall/reinstall/setup-from-scratch and had the same issue.

[Stagg54]

On ‎8‎/‎1‎/‎2017 at 7:17 AM, Tim_S said:

I'm using Windows, so I wonder if there is an issue with the build in the installer for Windows rather than the Linux packages. Did a uninstall/reinstall/setup-from-scratch and had the same issue.

You could try running the linux version in a VM.

[Tim_S]

On 8/4/2017 at 1:53 PM, Stagg54 said:

You could try running the linux version in a VM.

Hadn't thought of a VM... that's a good thought. It's a good technical solution, though I'm not sure it's a good for non-technical reasons (e.g., can the customer work with it when we're not given remote access to the machine). I'll have to give that serious consideration.

[ShaunR]

1 hour ago, Tim_S said:

It's a good technical solution,

Hardly. It's like pitching your tent inside your house because you don't have a hammer for tent pegs. Sure, the tent is up. But now you have to move your entire house when you want to go camping! I get really annoyed with the Linux community who's only answer for any Windows issues is to install Linux (or Cygwin)..

Edited August 9, 2017 by ShaunR

[Omar Mussa]

12 hours ago, ShaunR said:

Hardly. It's like pitching your tent inside your house because you don't have a hammer for tent pegs. Sure, the tent is up. But now you have to move your entire house when you want to go camping! I get really annoyed with the Linux community who's only answer for any Windows issues is to install Linux (or Cygwin)..

I think another way to do Linux on Windows now is via Docker Linux containers...  Also, Windows 10 now supports running Linux as a subsystem:

https://msdn.microsoft.com/en-us/commandline/wsl/install_guide

[ShaunR]

8 hours ago, Omar Mussa said:

I think another way to do Linux on Windows now is via Docker Linux containers...  Also, Windows 10 now supports running Linux as a subsystem:

https://msdn.microsoft.com/en-us/commandline/wsl/install_guide

I think you misunderstood my objection.

[Stagg54]

On ‎8‎/‎9‎/‎2017 at 4:58 AM, ShaunR said:

Hardly. It's like pitching your tent inside your house because you don't have a hammer for tent pegs. Sure, the tent is up. But now you have to move your entire house when you want to go camping! I get really annoyed with the Linux community who's only answer for any Windows issues is to install Linux (or Cygwin)..

I hardly consider myself part of the "Linux Community".  I certainly use it.  My answer for any Windows question, starts with why?

[Stagg54]

Also using a turnkey linux virtual machine is simply a super-easy to test out new software packages and play around with them and see if it will meet your needs.  Once you figure that out, it's pretty easy to setup a real server somewhere.

In the case of the original poster he mentioned wondering if it was the Windows Installer for postgres.  Well running it in Linux would help to figure it out pretty quickly.  A VM seems to be the quickest and easiest way to do that.